I’ve worked on a few system that use replication and the same issues tend to crop up. There’s a discussion on SQL Server Central about what needs to be improved, so I thought I’d share my experiences and some indexes I find useful.
Replication wish list
- Better default indexes (See below)
- Built in canary tables for monitoring latency.
- I created some scripts I call REPLMon for this and published it on GitHub.
- QueryStore for distribution databases
- Fix the silent fail issue where replication can timeout and fail to create tables or insert \ delete SPs.
- Increase the compression limit for snapshots, maybe compress each article rather than the whole snapshot.
- Reduce \ eliminate locking when creating snapshot.
- Why is the compatibility level of the distribution database 2005?
- Ability to create pull subscription jobs on Web edition (here’s my scripts)
- Ability to synchronise the indexes between the publisher and the subscriber. So new indexes and changes are automatically replicated out.
- I intend to add this to REPLMon at some point.
- Improved logging and diagnostics for errors.
- Better ability to see command information.
- Ability to analyse the transactions per object (possible with scripts) to help identify tables that are being updated too often and causing unnecessary strain.
- Ability to optimise the order of the snapshot (inspect the dependency and reorder using sp_changearticle and sp_changemergearticle).
- Through Aireforge we’ve written but not released something that does this. If people are interested, let us know and create a SSMS plug-in.
Indexes for transactional replication
Indexes for merge replication
These indexes have worked for me but your system may differ or they could be improved. I’d be interested to hear any feedback about them and I’ll update if needs be.