Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.
  2. If the subscriber server goes down, will the primary still function

Thanks!

share|improve this question
add comment

2 Answers

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.

share|improve this answer
2  
Good answer. We use Tx Replication in production and the impact is negligible. We used to have our subscriber and distributor on the same instance but we just recently created a dedicated distribution/ETL server. –  swasheck Apr 25 '13 at 17:03
    
Thanks Thomas, this clears up some doubts! –  user23025 Apr 25 '13 at 17:08
    
@user23025 Glad to help. If you have any other questions regarding this, please feel free to ask. But if I've answered your question to your satisfaction, please mark this answer as correct. –  Thomas Stringer Apr 25 '13 at 19:25
add comment

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

Many factors that it depends --

  1. On the activity on the primary server which will be the Publisher server. Also, if the distribution agent on the same or different server matters a lot as it will be doing the heavy work of delivering the transactions of the articles that are published by the publisher server.
  2. On the geographic location of the Publisher - subscriber servers due to the limitation of the connectivity between the 2 regions (if geographically distributed).

  3. On how many articles and the the amount of data you are publishing. Remember if for some reasons, the articles subscription expires, then you have to reinitialize the subscribers by a snapshot and the amount of data will affect it. Only replicate what you need. Dont replicate the entire database.

I have seen subscribers lagging data by hours even in same region and same data center due to massive amount of transactions generated by Publisher.

To be concise, there will be latency on the subscriber server and depending on the amount of activity on the publisher server along with whether Distribution database is on the same or different server, there will be some or heavy activity on the publisher (primary -- not accurate term for replication) server.

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

Yes, if the subscriber goes down, there will be no impact on Primary, EXCEPT the replication jobs will be failing and depending on the expiration policy you have setup, you might end up re-initializing the subscribers with a snapshot.

share|improve this answer
    
Thanks for ur help, Kin! –  user23025 Apr 25 '13 at 17:27
    
Quick point on 2. - It could impact primary and cause it to fail. First the DISTRIBUTOR database will continue to fill up with unsent transactions. Once that fills up, the transaction log of the primary will continue to fill up. At that point you could have performance issues with log file fragmentation or cause all writes to fail if the disk fills up completely. –  Ali Razeghi Apr 25 '13 at 19:36
1  
@AliRazeghi It can happen in worst scenarios. But when setting up replication, you set up alerts as well to monitor it like -- Replication: agent failure ; Replication: agent retry; Replication: agent custom shutdown .. Some things to be proactive rather than be reactive . –  Kin Apr 25 '13 at 19:44
    
Agreed, but the user seemed really new to replication based on his questions and just wanted to clarify that since it can really hurt if it happens. I did like your answer though, thanks for posting it. –  Ali Razeghi Apr 25 '13 at 22:18
    
@AliRazeghi Thanks .. IE1 .. remember ..CA ? –  Kin Apr 26 '13 at 0:18
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.