- Replication Stored Procedures
- Modifying Publications
- Subscription Deactivation and Expiration
- Factors Affecting Replication Performance
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Factors Affecting Replication Performance
Replication performance tuning is quite a large subject and certainly deserves a separate article or an entire book. Here, I simply provide a few tips and suggestions for ensuring that transactional replication performs at its best:
Always use a dedicated distribution server, if possible. Any production environment that needs to deliver transactions to subscribers in a timely fashion should have a dedicated distributor. Even if distribution server isn't as powerful as the transactional servers, it will still help with improving replication performance.
Ensure that you have sufficient network bandwidth. If you have one million transactions a day on your main server, you must be able to handle two million transactions after you set up replication; that is, if you replicate all transactions to a single subscriber. As the number of subscribers increases, so does the network traffic you must be able to support.
Replicate only the data that you have to have on subscribers. Figure out which tables need to be replicated to each subscriber and set up a different publication for each subscriber, if necessary. You can further reduce the load on the distribution server by adding filters to your articles. This might mean extra planning to do in the beginning, but it will also translate in significant savings when the number of transactions delivered to subscribers is reduced two-fold, ten-fold, or even 100-fold.
Be careful with replication filters. If the WHERE clause is very complicated, replication has to run that complicated query each time the INSERT, UPDATE, or DELETE statement affects the published article. Doing so can cause significant performance issues on the publishing server. If the filter becomes very complex (for instance, if it involves joining several tables), you might be better off removing the filter completely.
Ensure that you have adequate hardware for the distribution server; this includes memory, processor, disks, network cards, etc.
Reduce the number of times snapshot agent(s) have to run. If you don't make many changes to the publications and you monitor replication closely, you might only have to run the snapshot agent once: for initial synchronization. If you have multiple snapshot agents running on the same publisher ensure that they don't run in parallel.
Use pull subscriptions rather than push subscriptions whenever possible. This helps reduce the load on the publisher.
To further optimize replication you might want to try altering the default parameters of replication agents (snapshot, log reader, and distribution).