Transactional Replication Concerns

We are debating an architecture that employs sql server 2012 and transaction replication. Idea is to offload reporting activity to a secondary server and have the ability to include/exclude what is replicated by keeping historic data on the reporting server.

Couple of questions:

• Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.

• If the subscriber server goes down, will the primary still function

Thanks!

1.Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.

There will be an impact on the publisher, but depending on how you set up your transactional replication schema that can be very small. One big problem that can become an issue is if you have a local distributor, especially if you are dealing with a large amount of publications being replicated to multiple subscribers in a push configuration. If that is the case, one way to alleviate the server impact is to have a remote distributor.

Also, by design with transactional replication the Log Reader Agent is doing just that: reading the transaction log of the publisher. There are many strategies to reduce this impact, such as replicating stored procedure executions instead of the resulting data.


2.If the subscriber server goes down, will the primary still function

Yes, the publisher will still function. Commands to get replicated will be queuing up, and depending on how long your retention is setup for and how long the subscriber is disconnected you may need to reinitialize the subscription.

I recommend this TechNet reference: Enhance Transaction Replication Performance. There are a handful of great strategies in that document that should be considered.

StackExchange Database Administrators Q#40934, answer score: 8