SQL Server Replication - The Dark Side of Microsoft

Anyone who has been working with Microsoft technologies over the past decade or so knows about the ‘new’ Microsoft brand: transparent, developer-focused and committed to open-source practices. 

However, there is another Microsoft still lurking in the shadows, the ‘you eat what we tell you to eat’ Microsoft: a place where the spectre of IE6 never completely left, where developers are left to their own devices, and documentation is either non-existent or deliberately opaque.

Readers, I have travelled there and back, and I can tell you such is a place does exist - and there lives a product called SQL Server Replication.

Now don’t get me wrong: SQL Server Replication is an extremely useful tool and an impressive piece of software. In a nutshell, the product allows developers to synchronize two or more separate databases in nearly real-time, by taking snapshots of a parent database and then sending that information, along with incremental data from the transaction log, to subscribers at other locations. In theory, the tool promises a fairly straightforward method of syncing databases.

In practice, it was anything but.

The first thing we noticed when setting up replication was that a large number of our tables were missing from the publication articles, essentially the list of items scheduled to be synced. Not only that, but these were literally the most important objects in our database, and the ones we most needed added to our backup location. The culprit? Temporal tables, a feature that Microsoft itself introduced to help preserve records with important historical data.  And not only could the tables not be added, having any reference to them at all would cause the replication to fail, since any other object that referenced them would throw an error.

After considering the problem, we finally came to the conclusion that the best approach was to scrap the temporal tables entirely, and simply build our own implementation using triggers, which required making not-insignificant changes to our database schema.

Excited that we had all the objects captured, we finally set up syncing. And then on the first production release of our database with replication…the deployment failed. Parsing the error messages, we quickly understood why: Azure DevOps, which we used for our deployment - and which by the way, is also a Microsoft product - does not play well with replication. In fact, a more appropriate comparison would be to two children in a sandbox, screaming and throwing sand in each others’ faces. During deployment, if DevOps noticed any small change in a table, it would respond by dropping and recreating it, to which the replication would angrily respond by stopping the deployment entirely, providing only a vague error message as a guide. After an exhaustive search, we finally found arguments that seemed to make the two co-operate – for the time being, at least.

What made the entire situation especially egregious is that unlike a lot of other Microsoft technologies, where the company provides a wealth of videos and tutorials there is little transparency and guidance available. Microsoft does provide documentation on configuration and basic implementation, but after that you’re on your own. Trying to integrate replication with your CI/CD pipeline? Best of luck on Stack Overflow. Search long enough, and you can find an MS webpage indicating that temporal tables are not compatible with replication, but there is no reason as to why, what alternatives are available, or when, if ever, this functionality will be added. And unlike other products, like .NET Core, there is no GitHub repository available where issues can be submitted and questions answered.

The situation, however, is not hopeless. What I propose to you, Microsoft is this: take the principals from the replication team, the SQL server team, the DevOps team, the open source guys and whoever else you need, and get them into a room with one specific mission – bring SQL Server Replication into the ‘new’ Microsoft. Heck, it’s 2020 – this could all be done with everyone sitting around in their sweatpants, remotely connecting  on MS-Teams (which by the way, is a great product with more complete developer documentation),

Look, I’ve been around since the IE6 days. I remember spending hours of time winding down rabbit holes on the Web, trying to find solutions for compatibility issues, and these last few months, I feel like I’ve stepped in a time machine. SQL Server Replication is a great technology, and deserves better. Let’s do something about it.