Wish List: SQL Server Replication
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
- Better default indexes (See blog)
- Vote | Improve replication latency monitoring (built-in canary tables).
- Vote | QueryStore for distribution databases
- Fix the silent fail issue where replication can timeout and fail to create tables or insert \ delete SPs.
Vote | Increase the 2GB 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.
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.
Vote | 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.
Here’s what we've been up to recently.
xTEN is now Cyber Essentials Plus certified
At xTEN security is a priority. Recently taken over by the IASME consortium (as of 1 April 2020), the Cyber Essentials certification consists of a self-assessment of 5 basic security controls which is then verified by a qualified assessor.