SQL Server Replication: The reports of my death are greatly exaggerated

I was reading posts in an email distribution list yesterday and someone asked if SQL Server Replication was deprecated. First up, let’s just confirm that it’s not. As Mark Twain said: “The reports of my death are greatly exaggerated”. There’s still a really strong need for it, and somewhat ironically, that need has been getting stronger lately.

Back when replication was introduced, it had a bit of a reputation for being hard to set up, and hard to look after. It was in the same category as features like clustering. If I was teaching a SQL Server 6.5 class, you could tell which students could follow instructions if they managed to get clustering working. Fortunately, it’s nothing like that today but you’ll still hear from people with “old pain”.

[caption id=“attachment_6841” align=“alignnone” width=“350”] Awesome image by Anh Nguyen[/caption]

Styles of Replication

SQL Server Replication is a general term, and it covers a number of features. That’s part of the reason that you get mixed messages.

Merge

I don’t use Merge Replication and I avoid it at all costs.

There, I’ve said it.

[caption id=“attachment_6842” align=“alignnone” width=“354”] Awesome image by Martin Katler[/caption]

Merge works by adding triggers to tables and capturing the changes, then periodically synching up with other machines and trying to apply each other’s changes. And no big surprise, conflicts are an issue. There are ways to resolve them, but they mostly involve overwriting someone else’s changes. Even though one database is the publisher, each system involved is pretty independent. It’s very similar in many ways to the form of replication that older Access systems used.

Where Merge falls down is in performance. It can be a real challenge, particularly if you have a complex topology. It’s easy to end up with what we call “merge storms” where systems are just endlessly moving data around. It works ok for small numbers of systems and small volumes of changes, particularly if they aren’t likely to conflict with each other.

Snapshot

If ever there was a word that is overloaded in SQL Server, it’s snapshot. (OK, DAC might be close to that claim). In the case of replication though, the basic idea is that you just periodically copy over a full copy of the data.

[caption id=“attachment_6843” align=“alignnone” width=“401”] Awesome image by Blake Wisz[/caption]

This is simple and reliable. Where it works well is when you have small amounts of data and it’s not worth tracking the changes, or if you have a relatively small amount of data and sending all the data is less work than sending the changes, because it’s endlessly being modified.

It’s an excellent option for pushing out reference data from a central database to many other databases.

Transactional

This is the most common form of replication today. The transaction log of the publishing database is read (by a log reader process), the interesting stuff is extracted from the log and sent to a location where it’s distributed. From there, it can be pushed to subscribers, or pulled by subscribers.

[caption id=“attachment_6844” align=“alignnone” width=“427”] Awesome image by Drew Farwell[/caption]

There’s a less-commonly used variant of it called Peer to Peer Transactional Replication. We’d rarely use that option.

As for “standard” transactional replication, nowadays it’s pretty easy to set up, and it’s not that hard to look after. The tooling has improved markedly over the years.

But why would you use it instead of other options like readable secondaries on availability groups? Well, for lots of reasons. Here are my main reasons:

On-Premises SQL Server to Azure SQL Database for BI

A few years back, we got the ability to replicate data from on-premises SQL Server databases into Azure SQL Database. (Note: I’m not talking about just Azure SQL Managed Instances or SQL Server in Azure VMs, but real Azure SQL Database). This is a wonderful option and is causing a resurgence in the use of transactional replication.

We have many customer sites where they have on-premises SQL Server systems and this lets them push a copy of the data, quite up to date, into Azure SQL Database. That’s used to either centralize the data, or to then perform analytics on it.

Let me give you an example: I’ve recently been dealing with a company that has locations across the country, all of which are running their own on-premises SQL Server based systems. We’re able to add replicas in Azure SQL Database, and now we can access all the data from the source systems directly in Azure.

No need for the days where you’d be setting up VPNs from sites to the head office to get this sort of data. And if any site’s link is down (including the head office), the others keep working. When the site comes back, the data just continues to flow.

And it’s a really great source of data for Azure Analysis Services, and/or Power BI.

Subset of Data Required for Analytics

A fellow MCM commented to me recently that if you needed to move 100 tables totalling 2GB for analytics, and the whole database was 4TB and had 30,000 tables, an availability group replica would be entirely the wrong choice.

With transactional replication, I can replicate just the required tables. In fact, I can replicate just the required columns and rows.

Indexing Strategies

When I have data replicated to another server, I can then use a different indexing strategy on that target server. This means that if I need indexes to run analytic queries and those indexes aren’t needed on the source system (or might hinder it), I can just add them on the target. You can’t do that with an availability group replica. You’d have to put the indexes on the primary database.

This is especially useful for  3rd party application databases where they’ll get all upset if you start adding indexes to their database. You still need to check if they’re ok with you replicating some of the tables in their database, but we’ve had good outcomes with this.

Schema and Data Type Differences

While we generally don’t do this, there’s nothing to stop you having different table definitions on the target systems. INSERT, UPDATE, and DELETE commands flow to the target and you can choose how they are applied. You can even write your own stored procedures to apply them in whatever way you want.

Large Numbers of Secondaries and No Domain Join Needed

With replication, you can have a large number of secondaries if all you’re trying to do is to distribute data so that it’s local to other systems.

And while availability groups have basic options for creation outside of domains, transactional replication works just fine in these situations.

Learning Curve

You do need to learn how to work with, and manage, replication. It’s easier today than it’s ever been but most people that I see having issues with it, just haven’t really understood something about how it works.

Given how useful it now is with the Azure SQL Database options, there’s never been a better time to learn about it.

If you want a quick way to learn, we have an online course that will probably take you a day or two to complete. You’ll find it  here:

https://training.sqldownunder.com/courses/sqlserver-replication

2020-10-15