SQL Server Mirroring and Replication - Replace the Primary

This is a follow on from my previous post, about how I replaced the mirror in a SQL Mirror set, all the while keeping replication flowing.

In this episode, we'll explore how to move the primary server in the mirror, which is also the publisher of a bunch of publications, onto new hardware.

Unlike the mirror, because the primary is the publisher, its replacement must use the same NetBIOS name as the machine being replaced. Note, it doesn't need the same IP - just the same NetBIOS/hostname. This can be understandably difficult if both machines need go on the same domain. In this instance, don't join the new hardware to the domain just yet and uncheck the boxes about automatically registering itself in DNS, under the network hardware settings.

The first steps are to backup both the service master key and the database master keys. Remember to snag the master databases master key as well as the published/mirrored databases master key.

You must also make sure the disk layout on the replacement machine matches the existing server, as we will be doing a straight filecopy of the relevant data files and the restored master database will expect them to be in the same place as they originally were.

At this point you should stop the SQL Server Agent on the original machine, and stop both the SQL Server service and Agent service on the new destination. It's about to get real up in here. Here's a handy step by step check list of what happens next.

1. Failover to your mirror. Ensure that the log reader still runs and that replication still flows
2. Backup master and msdb (and model if you have made changes) on the original machine
3. Stop the SQL Server service on the original machine. Note that this will pause the log reader agent, so replication will pause.
4. Copy the mirrored database files from the original machine to the new machine, maintaining their file paths

Once this is done, you can un-join and shutdown the old source machine. Disable the SQL services to make sure they don't affect things if the machine is accidentally spun up with network in the future. Before proceeding, join the new machine to the domain if necessary.

The next steps are your pretty basic disaster recovery steps.

1. Use host hacks if necessary, but make sure that the mirror server and distributor have the new IP for your new machine of the same name
2. Either start the new machines SQL service in single user mode and restore the master DB, or just copy master and msdb to the paths specified by the SQL service
3. Restore the service master key - you will probably need to force regenerate to get this to work
4. Restore the master DB master key if applicable
5. Once the master database is back, it should immediately recover your mirrored database, because you copied the data files over to same paths.
6. The mirror should move from disconnected, to synchronizing and eventually synchronized
7. When you are ready, fail back to your new and fresh primary node
8. Restart the SQL Agent service when ready and let the jobs flow

That's it. It's surprisingly easy. I did this with a ~1TB database over about 3 hours (over 10GbE interconnect between 2 datacenters) and it worked flawlessly. There is no need to break and re-establish the mirror. And using this method keeps your replication configuration intact.

Hope this saves you a bunch of time and heartache.