Migrate a SQL Server environment with complex replication without reinitializing or rebuilding replication

Michelle Gutzait

April 4, 2016

Tags: On-Premises, SQL Server, Azure, Microsoft Sql Server, Technical Track, Migrate

When you have a SQL Server environment where a very complex replication setup is in place, and you need to migrate/move (without upgrading), some or all the servers involved in the replication topology to new servers/Virtual Machines or to a new Data Center/Cloud, this Blog post is for you! Let’s assume you also have Transactional and/or Merge publications and subscriptions in place, and you need to move the publisher(s) and/or distributor(s) to a new environment. You also have one or more of the following restrictions:

You are not sure if the schema at the subscribers is identical to the publisher (i.e.: different indexes, different columns, etc).
You cannot afford downtime to reinitialize the subscriber(s)
There are too many subscribers to reinitialize and you cannot afford the downtime if anything goes wrong.

Here are the general steps for this migration: Prior the migration date:

New instance has to have same SQL Server version and edition plus patch level as old instance. Windows version and edition can be different but you need to ensure the version of Windows supports the version of SQL Server.
The directory structure for the SQL Server files should be identical in the new server as old server and same permissions:
- Same path for SQL Server binaries
- Same path and database files names in both servers for system databases
- Same directories where user database files and T-logs reside
- Same path for the replication directories (when applies)
Copy over any instance-level objects (Logins, Linked Servers and jobs) to new instance; leave jobs disabled if applies or stop SQL Server Agent on new server

On migration date:

Disable any jobs, backups and maintenance that should run during the migration window on old server
Stop all database activity on old instance or disable logins
Restart old instance and verify there is no activity
Synchronize all replication agents that are related to the server being migrated
Stop and disable replication agents related to the server being migrated
Stop both instances
Copy over all system database files from old to new server
Copy over all user database files from old server to new one
- Alternatively, backup all user databases on old server before stopping service and copy the files to new server
Shutdown old server
Rename new server to the name of old server and change the IP of new server to old server’s IP
Start the new server
Verify that the name of the new instance is like the old server and it’s local
If you backed up the user databases previously, you need to restore them to same location and file names as in old server with RECOVERY and KEEP_REPLICATION
Verify that all user databases are online and publications + subscribers are there
Start all replication agents related to the migrated server and verify replication is working properly
Verify that applications are able to connect to the new instance (no need to modify instance name as it is the same as before and same IP)

At any case, it is strongly recommended to test the migration prior to the real cutover, even if the test environment is not identical to Production, just to get a feel for it. Ensure that you are including most replication scenarios you have in Production during your test phase. The more scripts you have handy for the cutover date, the less downtime you may have. It is extremely important to also have a good and tested rollback plan. In future Blog posts I will discuss more complex replication scenarios to be migrated and rollback plans. If you would like to make suggestions for future blogs, please feel free to add a comment and I will try to include your request in future posts.