Monday, January 15, 2018

A quick note about multi-hop replication in SQL Server

Many people use SQL Server replication in a multi-hop setup. Publish from A to B, republish from B to C, and so on. This is a well-known use case, even if it makes those of us who have to maintain it a bit crabby at times - throw in some other stuff like Change Data Capture that uses the Log Reader Agent and it's a recipe for fun (but that's another post for another day).

One thing I've seen a number of people run into trouble with is making changes to publications in this sort of environment. It's simple enough when you're adding new things - you start at the start and move down the chain. Where I've seen folks have the most trouble is with removing articles and publications - for that, you have to start at the last hop and work your way back to the source.

Managing this kind of setup can become tedious, so I urge you to get comfortable with the various stored procedures, views, and tables that are present in SQL Server. Microsoft's documentation on the topic is thorough and helpful, and spending some time in the lab poking around will amply repay you. Document your environment, learn what replicates where, and when it comes time to make those changes you'll be in much better shape.

If you don't have a lab, don't fret! Simply sign up for a Visual Studio Dev Essentials account at VisualStudio.com, and you can download SQL Server Developer Edition at no cost to you.

No comments: