We were recently working with a customer that had a SQL Server merge replication topology involving several servers. One of the servers was a fairly old physical server which was running out of resources and disk space. A new physical server was on the network waiting to replace it. The initial plan by the development was to allow for a couple of days of downtime while the 400 GB + merge replicated database was re-initialized from a snapshot. Adding to the complexity of the migration were 39 linked servers, a large number of SQL Logins, over 90 SQL Server Agent jobs and 16 other user databases. Rather than re-initialize the subscription and manually migrate everything over, we decided to use an alternative solution.

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