May 082015 Tagged with , 0 Responses

SQL Server Cluster P2P migration

Motto: “it should be out there”

Recently, my team was involved in several SQL Failover Cluster Instances migration. All nodes were physical boxes and migration was done between two Data Centers.

Due to business constraints, we had no option but to rebuild the clusters in the new Data Center, keeping everything as before and with as minimum downtime as possible.

Migration should be transparent to all applications/users that connect to the instances.

Nice challenge, we said.

Single instance migration was documented and you may find it at:

How about cluster migration? The steps we took to ensure all migrations were successfully are listed below.

I know.. most of the steps are only backups/information and may be skipped. A backup may never suffice. When downtime is important, any piece of backup/information is valuable.

Always do a test before, check whatever steps apply to your environment, add others, document the procedure and go live.

Build Destination Cluster: OS & SQL

  • different node names/IPs, different OS cluster name, different SQL Cluster Network Name
  • make sure service account is local administrator on both nodes
  • same settings
  • check instance configuration parameters
  • compare Cluster 1 with Cluster 2 and match on Cluster 2 any custom values
  • make sure service properties are identical on all nodes, both clusters
  • make sure service accounts are identical on all nodes, both clusters
  • make sure Data Folder, Log Folder, Backup Folder are identical on both SQL clusters
  • make a backup of the system databases and mssqlsystemresource files from Cluster 2
  • match physical path for system databases
  • perform full backup on master and msdb on Cluster 1
  • backup service master key from Cluster 1
  • stop SQL Cluster Role on Cluster
  • backup all files from cluster volumes (step can be skipped if volumes are synced later at storage level)
  • create a registry full backup from Cluster 1, all nodes
  • copy master & msdb backup files, copy master key backup file from Cluster 1 to Cluster 2
  • shutdown all nodes from Cluster 1

Active Directory Changes

  • go to AD, Computers, and delete Failover cluster virtual network name account for Cluster 1
  • go to AD, Computers, and re-add the cluster name; grant full permissions to OS cluster computer object on the new entry
  • eventually change DNS entry (IP address) and grant permissions to OS cluster computer object (do this for both SQL Network Name and DTC Network Name, if applicable)
  • go to Failover Cluster Manager on the Cluster 2 and make sure all cluster resources except cluster volumes are offline
  • go to Failover Cluster Manager on the Cluster 2 and rename SQL Network Name to match the name from Cluster 1
  • use same name and IP for DNS name as it was on Cluster 1 (if previously not changed)

Databases migration / Storage synchronization

  • sync cluster volumes from Cluster 1 with Cluster 2
  • either storage synchronization or manual restore of the previous copied files
  • or sync SQL Server data and log files (make sure you retain file & folder permissions)

Complete migration

  • from Failover Cluster Manager, bring online only Server name and its network
  • on the cluster active node, start SQL in single user mode and check messages
  • stop SQL from single user mode and bring the SQL Server cluster role online
  • open a SSMS window, connect to cluster (the new one but with old name) and run:
  • restore service master key from the backup


  • If you try to restore the key that is the same as the current key, you will receive the following message:
  • The old and new master keys are identical. No data re-encryption is required.
  • You can regenerate the Service Master Key using this code:
  • go to Failover Cluster Manager on the Cluster 2 and start all cluster resources that are still offline
  • cluster migration done!
  • run DBCC CHECKDB on all user/system databases
  • backup master database and service master key

Take a deep breath and grab a beer!

Lessons learned after performing 4 such migrations:

  • have all involved teams informed on timeline and actions to be made
  • we have managed to complete a P2P migration with only 30 minutes SQL Server service downtime
  • some of the steps are not mandatory
  • with proper preparations and coordination, downtime may be limited to 15 minutes or even less

Leave a Reply

Your email address will not be published. Please enter your name, email and a comment.