Skip to main content

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.

Comments

  1. Great Article. Thank you for providing such a unique and valuable information to your readers. I really appreciate your work. Master Key to Unlock Cars in Madison Tn

    ReplyDelete
  2. I wish more authors of this type of content would take the time you did to research and write so well. get the best Locksmith Plymouth MA at affordable charges. to know more visit our website.

    ReplyDelete

Post a comment

Popular posts from this blog

Writing to the event log in .NET - the right way

As a DevOper, I spend a lot of time instrumenting code. This involves adding stuff like performance counters, tracing and logging. One thing that constantly irks me when I go looking for ways or means to improve how I do my logging, is the complete and utter misinformation and misuse of the Windows Event Log. The problem relates to the canonical implementation of logging in .NET, log4net. Look at this method signature: void Info(object message); Note the lack of structure, the lack of context. Note the ability for a developer to put absolutely anything into the message object (which will be outputted as a string). When these types of messages are hooked up to an Event Log sink, the result is a generic message, usually in the Application log with a bit of free form text. Fairly useless for anyone to be able to filter and report on. The above style of logging is really more suited towards tracing and debug logging than actual operational logging, Why you should write operatio

SCCM 2012 - SMSPXE Error

I write this to help all you technical warriors out there who get shitty advice from the Microsoft Forums and other profound idiots about the 0x80092002 error in SCCM - usually to do with PXE booting but can manifest in other ways around DP -> MP communications. The tricky one about this error is that it happens even if you are only using HTTP communication and not HTTPS/TLS. So to get an error about certificates is somewhat confusing. What the uneducated masses of the internet will tell you is to re-install the PXE role over an over. This will do nothing. Then they will tell you to re-install your management point and effectively re-build your entire infrastructure. This will  work, but only because it is the nuclear option and will run the step that actually makes a difference as a matter of course. So, here it is folks. If you have these kinds of errors in your logs: PXE::MP_InitializeTransport failed; 0x80092002 SMSPXE 22/03/2017 2:45:58 PM 4144 (0x1030) PXE::MP_

Log Reader Error On Adding Subscription

Ever tried to add a new subscription to an existing publication, using the "replication support only" or "initialize from backup" methods and go the following error? The process could not execute 'sp_MSadd_replcmds' on <'Distribution Server>'. (Source: MSSQLServer, Error number: 1007) Conversion failed when converting the nvarchar value '.' to data type int. (Source: MSSQLServer, Error number: 1007) Batches were not committed to the Distributor. (Source: MSSQL_REPL, Error number: MSSQL_REPL22020) The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) If so, you will know the internet is absolutely useless, filled with worthless workarounds or advice to just "rebuild replication" Well, if you want an actual solution, just read on. Between SQL 2012 RTM and SQL 2012 SP4 there was a change made to the Distributor proc sp_MSadd_replcmds. This change was to facilitate a change in the format