At first glance, SQL Server’s transactional replication seems like it’s useful for moving data around in all sorts of situations: it works in Standard Edition, it’s not synchronous, and you can have multiple subscribers.
Why People Want Replication to Test and Pre-Production Environments
Setting up a test environment sounds simple at first. But it’s pretty tricky. Here are typical requirements:
- Data in the test environment needs to be fresh, recent production data to support query tuning
- Data and schema in the test environment need to be writable for testing purposes
- Data and schema in the test environment need to be periodically reset back to production-like configuration
The first of those requirements are why replication sounds like a great idea. There’s a lot of gotchas, even with that issue, but the second two requirements are where replication just clearly doesn’t meet the bar.
Replication isn’t Friendly to Changing Subscriber Tables
Transactional replication won’t like it if you go around changing data and schema on its subscriber tables. If you change the data, updates and deletes flowing through replication may start to fail. Well, let’s just be realistic– they WILL fail, and fixing it will be manual and a major pain point.
Schema changes are even dicier. You can’t just drop or change the data type in a replicated column, you need to modify replication for that– and that means adding filters to the publication.
Resetting Replication isn’t Fun, Either
Re-initializing a replication subscriber is often very impactful on the publisher: running a replication snapshot is an intensive, time consuming process that causes CPU, IO, and lock overhead on the publisher. (Warning: this snapshot is a totally different thing from the database snapshot I talk about a bit later. This one basically copies out all the contents of the published articles on the publisher into flat files, copies everything, and then loads it into the subscriber.)
You can initialize a replication subscriber from a full backup, but that’s not lightweight or fast for large databases, and it’s very dependent on great custom scripting and timing. And usually people are looking at replication because they want to avoid big backups and restores.
What About a Creative Solution Involving Replication?
Refreshing test and staging environments is a tough problem. So tough that people often try to get creative, like this:
- ProductionDB in Denver is a transactional replication publisher on PROD01
- SubscriberDB in Portland is a transactional replication subscriber on STG01
- Periodically, some process is run against subscriber DB to reset other databases on STG01. Ideas usually involve database snapshots, backups, and custom scripts.
But when you dig into the details, this is never great. Here’s why.
Transactional Replication Impacts the Publisher, Your Developers, and Your DBAs
If there’s one thing that most DBAs and developers can agree on, it’s that replication has made them work late and curse a lot.
Replication requires that every table have a primary key. This may mean schema changes just to get it set up.
Replication slows down your software deployment, because it’s easy to mess up publishing tables when you make schema changes to them. The only way to not mess this up is to also implement replication in your development environment, which developers hate. And in our scenario, where the test database is the subscriber, what would that even mean? Recursive replication torture?
Replication impacts performance on the publisher, particularly under high load. It frequently requires tuning storage, setting up a scale out distributor ($$$ hardware and licensing $$$), fine tuning your publisher and subscriber properties, implementing custom monitoring, and basically having at least one team member obtain a Masters Degree in Replication at the School of Late Nights. I went to that school a long time ago, and I didn’t keep the yearbook, but I did write about it here.
Database Snapshots are Read-Only
There’s no such thing as a writable database snapshot in SQL Server, even though it’s an Enterprise Edition feature. (Fancy SANs have options for that, SQL Server itself does not.) That pretty much takes most of the uses out of it for a staging environment.
If You’re Writing a Custom Script in the Staging Environment, Why Use Replication?
If you have to basically write a custom ETL to read from the subscriber to refresh staging databases, transaction log shipping is much easier to manage than replication, and it allows a read only standby mode on the subscriber.
If You’re Using Backup and Restore, Replication is Also Overkill
While it’s technically possible to back up a replication subscriber and restore it, then remove replication from it, it’s not all that great. This is a lot of complexity without a lot of payoff.
- You’ve still got the time to do the backup and restore (which you were trying to avoid in the first place)
- You haven’t validated that the database you’ve restored has a schema that matches production (it could be very different on the replication subscriber)
You’re much better off basing the restore off production in this case. Even if the pre-production and testing databases are in a different datacenter, you can optimize the types of backups used, compression and network copy time, and restore schedule. Even setting up multiple test instances that restore “in rounds” and which can be used at different times a day is often operationally preferable to the performance, monitoring, and caretaking needs of replication.
There’s No Easy Answer to this Problem
Refreshing staging and development environments doesn’t have a single right answer. This is done many different ways based on application requirements. Just don’t pin all your hopes on replication and start investing in it without looking at the details of exactly how it will work — because it’s probably not what you think it is at first. Always make sure you factor in:
- Performance impact on the publisher and hardware/storage/licensing costs to mitigate this
- Any restrictions or requirements on schema changes at the publisher and impact on code development
- Operational cost of upkeep of the technology
Don’t give up! Having staging and development environments with recent data that works well for you is totally possible, you just need to consider more technologies than replication.
We’ve got lots of resources, for money and for love. Or just for free.
- Watch Brent’s free video on HA/DR basics
- Read more free articles about replication
- Buy our 6+ hour online course on High Availability and Disaster Recovery in SQL Server
- Get custom advice from us directly about your environment in our SQL Critical Care® Service
- Attend our in-person course for Senior DBAs to learn advanced tricks to wrangle your SQL Servers
Brent says: Yeah, replication isn’t what I call “refreshing.”