What can be done when SQL Server Replication is slow.
A lot of SQL Server Replication users think, SQL Replication that gets created with SQL Wizard – is all you can do.
But there is a ton of knobs you can turn.
How do I know there are MANY people that don’t know? Ummmhhh… Cause I was one of them. For longer, than I will admin here.
I did not know there were so many things that can be adjusted to make SQL Replication Performance go faster. A lot faster.
Here are a couple of ways to tune SQL replication, make it go faster and reduce replication impact to Production:
- Use another server for the housing SQL server replication distribution DB. You can also move distribution DB to a replication subscriber. This offloads all work which happens in distribution DB away from PROD.
- Use “PULL” subscriptions instead of the “PUSH”.
- Replicate only articles that must be in replication and only those columns which you really need to replicate.
- Use separate publications for big tables.
- May set a “distribution” database to Simple recovery mode.
- Enable/create “distribution” DB clean-up job (default: Distribution clean up: distribution).
- If the Distribution Clean-up job exists, check the retention and reduce it down.
- If the Distribution Clean-up was not set up – better to clean-up the archive tables manually in chunks to avoid locking/blocking issues.
- Check Distribution database auto-growth settings. You want to make sure it grows by not percentage but set MB size, say 512MB. The goal here is to avoid growth operations, as they slow you down.
- Consider disabling “Copy nonclustered indexes”.
- If you have large transactions in the replication, consider changing -MaxCmdsInTran on Log Reader Agent job.
- If enabled – disable immediate sync and keep @min_distretention set to 4 to avoid locking/blocking on the cleanup procedure.
- Create multiple publications or use –SubscriptionStreams parameter for the Distribution Agent – this allows to have the parallel replication.
- Run subscription agents continuously instead of on very frequent schedules and independent agents rather than shared.
- You can split articles (or tables).
I am probably forgetting a few.
Just remember, that SQL Transactional Replication (most frequently used type of SQL replication) has lots of parts to it. And each has settings you can adjust.
SQL Replication Agents
- SQL Server Replication uses agents to manage and monitor replication.
- Snapshot Agent
- Log Reader Agent
- Distribution Agent
- Each publication has its own agent(s)
- Push and Pull Agents
Few SQL replication notes:
- The word “Replication” sounds so good, that it makes sense to think that Replication is perfect for Disaster Recovery function. It is not.
- Do not use Replication for real-time source of data
- You can’t put the “distribution” database into an AlwaysOn availability group. But you can replicate tables in AlwaysOn dbs.
- Be default, nothing in replication is encrypted! But you can enable SSL communication.
Conclusion: Here are at least 14 items you can do to improve SQL Server Replication where it’s slowness is causing problems.
To make this list complete, I’d have to explain the “why” for each item. Ideally with examples and screenshots. Don’t have time for that. At least not now. So I went for the next best option – just list out what options you have to speed up replication.
Anything you come across, that can be added to this list?