Accidental DBA
Posts
SQL Server Replication on Steroids!

SQL Server Replication on Steroids!

Kevin Hill
September 27, 2024

We Turned a 2-day Replication Re-initialization Into a 1-hour Process

I’ve been working with and supporting SQL Server Replication since SQL 2000. It is not a perfect technology, but it does work well when used for its intended purpose: distributed data processing.

Years ago, that meant a central office and remote offices. Or field reps with limited connectivity that used Merge replication to synch up the data at night.

The most common use case I see now is a Publisher for transactional activity and a Subscriber for reporting and analytics. Replication is NOT a HA or DR solution.

Re-initializing replication can be very time consuming

The standard way to start or re-initialize replication is by generating a new snapshot of the publication and letting replication agents push the data out to the subscriber(s). Depending on the size of the database and other factors, this can take minutes, hours or days.

Here is how I turned a 2-day replication re-initialization for client into a 1-hour process:

1. Optimize the SQL Instance:

We found that the Publisher and Subscriber were both running Standard Edition SQL Server, no patches, default installation settings. We patched and made the basic changes for Max Server Memory, Cost Threshold and MAXDOP on both instances.

2. Optimize tempdb:

TempDB was split into 8 files, thanks to a current SQL Server version, but it was on the same drive as the database data files. We moved them to a new, fast, dedicated drive.

3. Analyze the Published Database:

The database came from a 3rd party vendor and has hundreds of indexes that were unused or barely used. These indexes were being replicated, which happens after the Snapshot is delivered and clustered indexes are created. In some cases, this did not complete for almost 48 hours.

4. Purge Unnecessary Data:

One table had data in it going back over 5 years, when only 90 days was actually useful to the business. There were also many unused indexes on this table.

5. Replication Agent Profile Settings:

We created a custom profile for the Distribution agent, which involved repeated tweaking and testing of the settings for batches and commands. We also learned that in this case, the SubscriptionStreams parameter would sometimes deadlock on itself.

6: The BIG WINNER! Backup and Restore Instead of Snapshot:

Even with all of the tweaks, we were still seeing up to 24 hours to re-initialize. Trying the Backup and Restore option became our last hope. We had never been able to get this to work properly before, so a lot of testing and Googling was involved until we found the right combination of parameters to make it work.

The Result:

A process that used to paralyze reporting operations for 48 hours now completes in about 60 minutes.

For one client in the car insurance industry, this improvement allowed them to maintain 24/7 operations even during necessary re-initializations.

Bottom Line:

This isn't just about saving time. It's about business continuity.

With faster re-initialization, our clients can maintain data consistency and availability, crucial for industries like banking and insurance where every minute of downtime can cost thousands. Email me for the code we used to make this happen. [email protected], subject: ‘Replication’