This is a topic that comes up a lot for us as consultants and I realized recently that I have never blogged about it. Usually the question is something along the lines of:
- I have a multi-TB replicated database and want to upgrade to SQL Server XXXX.
- I have a multi-TB replicated database and want to upgrade hardware to newer but not upgrade SQL Server versions.
- How do I do either of the above without having to fully reinitialize the subscribers?
Depending on the environment and what is changing this is actually a relatively simple process to accomplish with a little bit of planning.
Know What is Being Migrated or Upgraded or Both
The first part of building any plan is to know what specifically is being migrated or upgraded. This is important because the steps will be slightly different if you are only migrating or upgrading one part of the environment vs. the entire environment. It is also important to know where the distributor is for the configuration and whether or not the distribution database is going to be affected by the changes being made. In most cases it is a full environment migration to newer hardware or upgrade to newer release of SQL Server and the steps below will work. This is not the only way of migrating/upgrading large replicated databases but this is the one I have used for over 15 years at the date this post was written and it has been flawless.
Start with Log Shipping
My general recommendation for any migration or upgrade is typically to start out by log shipping the database(s) to the new server(s) as a means of minimizing downtimes when performing the final cutover. I have assisted clients with upgrading SQL Server while also migrating to Availability Group configurations with multi-TB databases with minimal downtime and being able to initialize and bring up 5+ replica’s at go live in minutes by using log shipping to prime the environment. I love using log shipping because it is super simple; it’s just a backup, copy, restore. It doesn’t matter if it is the publisher, the subscriber, or both, log shipping can be used to keep the new server updated to the point of the final migration/upgrade.
Full Environment Upgrade Steps
- Stop all application access to publisher SQL Server – Firewall rules blocking 1433 traffic in the Windows Firewall will do this, so you can also allow log reader agent access between the publisher, distributor, and subscriber
- Open Replication Monitor – Verify log reader agent has processed all log records from publisher
- Take tail log backup (WITH NORECOVERY) of the publisher database – this will take it offline and set it into Restoring… mode
- Copy tail log backup to new publisher database and restore WITH RECOVERY – this will bring the database online and will also remove all of transaction replication configuration from it (DO NOT USE KEEP_REPLICATION here)
- Configure new distributor and configure distribution for new publisher server.
- Configure all transactional publications/articles with a script specifying @allow_initialize_from_backup = N’true’, @immediate_sync = N’true’ – this could take some time to complete but you can do steps 7-9 concurrently
- Check Replication Monitor for old server distribution agent – ensure all transactions have been delivered.
- Take tail log backup of Subscriber database
- Copy tail log backup to new subscriber database and restore with RECOVERY
- When step 6 is complete query syspublications table on publisher database and mspublications table on distribution database to validate the min_autonosync_lsn matches for each publication.
- Create subscriptions specifying @sync_type=’initialize from lsn’, @subscriptionlsn= <min_autonosync_lsn> with sp_addsubscription based on the results in step 10.
- Insert trace tokens in Replication Monitor to each publication and verify end to end delivery and timings.
- Open the application up to access new publication server
- Process complete.
Publisher Only Migration
If only the publisher database is migrating, essentially the same steps listed above can be followed with the only difference being that you need to remove the old subscriptions for the subscriber database after step 7. Depending on where the distribution database resides, usually it is on the publisher or the subscriber and is not a separate instance entirely, you may have different steps required to do this, but typically you can accomplish this using SSMS or by calling sp_subscription_cleanup in the subscriber database.
Subscriber Only Migration
If only the subscriber database is migrating, things get a little bit trickier, but not by much. First, the publications have to be configured with @allow_initialize_from_backup = N’true’, @immediate_sync = N’true’, which can be verified in the MSPublications table of the distribution database. If this is not configured properly then you can’t initialize the subscription by LSN. From the subscriber, we need the last sync LSN for the subscription to use for initialize by LSN which we can get after stopping the distribution agents from either the distribution database or from the subscriber database:
-- Inside Subscriber Database
FROM MSreplication_subscriptions AS s
ORDER BY publication;
-- Inside Distribution Database
SELECT a.publisher_db, a.publication, a.subscriber_db, t.xact_seqno, t.[time], a.name AS AgentName, a.job_id
FROM distribution.dbo.MSdistribution_agents AS a
INNER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY agent_id ORDER BY [time] DESC) AS rowid
FROM distribution.dbo.MSdistribution_history WITH(NOLOCK)
) AS t ON a.id = t.agent_id
WHERE rowid = 1
AND runstatus <> 0
ORDER BY publication;
Take the tail log backup of the subscriber, restore it with recovery on the new machine and then run the script to form the new subscriptions using initialize from LSN just as for a full migration, only you are using the LSN provided by where the old distribution agent was for the subscription when stopped/disabled.
Why Not Move Distribution Database and use KEEP_REPLICATION?
This is a really good question, and I figured someone would ask this in the comments so I’ll just address it as a part of the post. Why aren’t we keeping the existing distribution database for a publisher migration and using KEEP_REPLICATION for the database during the restore? First I have never seen this actually work for someone the way they might expect it to. Second, the server names are often being changed for the publisher/subscriber so the distribution database information is not going to be correct for the new servers and renaming the new servers during the cut-over adds steps and creates more potential failure points that could create other issues, though it is supported as a log shipped secondary for the replicated database per the Books Online:
“SQL Server does not support renaming computers that are involved in replication, except when you use log shipping with replication. The secondary computer in log shipping can be renamed if the primary computer is permanently lost. For more information, see Log Shipping and Replication (SQL Server).”