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.
The next steps are your pretty basic disaster recovery steps.
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.
- Failover to your mirror. Ensure that the log reader still runs and that replication still flows
- Backup master and msdb (and model if you have made changes) on the original machine
- Stop the SQL Server service on the original machine. Note that this will pause the log reader agent, so replication will pause.
- Copy the mirrored database files from the original machine to the new machine, maintaining their file paths
The next steps are your pretty basic disaster recovery steps.
- 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
- 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
- Restore the service master key - you will probably need to force regenerate to get this to work
- Restore the master DB master key if applicable
- Once the master database is back, it should immediately recover your mirrored database, because you copied the data files over to same paths.
- The mirror should move from disconnected, to synchronizing and eventually synchronized
- When you are ready, fail back to your new and fresh primary node
- 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.
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
ReplyDeleteI 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.
ReplyDeleteI admire this article for well-researched content and excellent wording. Thank you for providing such a unique information here. residential locksmith san antonio
ReplyDeleteExcellent information, Thanks for publishing such essential information. You are doing such a good job. This information is very helpful for everyone. Keep it up. Thanks. Emergency Locksmith Baltimore
ReplyDeleteSuch an amazing information.
ReplyDeleteRestore Master Database in SQL Server
Thank you so much for sharing such an intresting blog with us.
ReplyDeleteKanarya Adaları yurtdışı kargo
ReplyDeleteKanada yurtdışı kargo
Kamerun yurtdışı kargo
Kamboçya yurtdışı kargo
Jersey yurtdışı kargo
M2D08