Database sql server Mirroring or replication

Question

I have SQL server 2008 R1 with standard edition.

I have a large database i.e. 500GB that i.e. growing rapidly 10GB a month .It is a cloud based solution. We have another data center where the database is replicated to . I need to set up another database for OLTP reporting. I have huge Transaction logs

Is Transactional replication with initial snapshot replication the best solution or mirroring the best solution.

Are there any best practices that are available while setting up transactional replication for the database.

Answer 1

Is Transactional replication with initial snapshot replication the best solution or mirroring the best solution.

With database mirroring, the mirror will be offline and not queryable. If you want the replica to be queryable, you'll need to either use replication or log shipping to standby mode.

As for Replication:

Are there any best practices that are available while setting up transactional replication for the database.

A full how-to guide (or link to same) is beyond the scope of the site, but Technet says:

For each database that will be published using transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database might require more space than the log of an identical unpublished database, because the log records are not truncated until they have been moved to the distribution database.

Something to keep in mind.

Answer 2

From the blog of sqlmag.

SQL Server uses three different types of replication mechanism, these are:

1. Snapshot
2. Transactional
3. Merge

Transactional replication offers the now deprecated "updatable subscriptions" feature, which allows Subscribers to replicate data from the Subscriber back to the Publisher. Peer-to-peer replication (a SQL Server Enterprise edition feature) also allows bi-directional replication of data modifications of transactional replication databases.