SQL Server Merge Replication – Server Migration
These are the steps we took.
- Install SQL Server on new server Ensure that the installation directory and the system databases directory match what is on the old server.
- On the new server, shutdown SQL Server and do offline backups of the system databases (MDF and LDF files) then once complete start SQL Server
- Backup the merge subscription database and copy the backup file over to new server
- Restore merge subscription database on new server with NORECOVERY so that further transaction logs can be applied. Don’t use the KEEP_REPLICATION option at this time as you can’t use this option with NORECOVERY.
- Backup transaction logs for merge subscription database and restore them on new server, again using the NORECOVERY option without the KEEP_REPLICATION
- Stop SQL Server Agent on the old server and check that there are no connections to the subscription database before running the next step. If there are connections the next step will error.
- Carry out final transaction log backup of the merge subscription database but use the NORECOVERY option when performing the backup. This will put the database on the old server into a RESTORING state.
- Restore the final transaction log onto the new server, and again use NORECOVERY without the KEEP_REPLICATION option. At this point both the databases will be identical and both in RESTORING state.
- Shutdown SQL Server on both servers.
- Set the SQL Server service to be manual on both servers
- Copy over all of the MDF and LDF files from old server to new server (with the exception of the merge subscription database). The drive letters and directory structures that they are copied to must be identical to the ones they were in on the old server. The system database files have to be copied over as well, they will replace the ones on the new server. (They have been backed up in a previous step).
- Shutdown the old server. Optionally rename it just in case it ever gets started and brought back onto the network.
- Rename the new server so that the hostname matches the old servers host name
- Restart new server
- Start up SQL Server on new server
- Set SQL Server services back to Automatic startup
- Verify SQL Server is online and that the SQL Error log contains no issues
- Bring the subscription database back online with the following command
restore database dbname with recovery, keep_replication
Because the version of SQL Server had changed slightly when SQL Server was first brought online couldn’t access it as it said it was running upgrade scripts. Once they’d been run it was necessary to run a couple of procedures manually due to the subscriber being offline
sp_vupgrade_replication
sp_vupgrade_replsecurity_metadata
Total downtime was around 20 minutes and replication picked up with no issues as soon as the database was back online