We have a two node SQL 2008 Enterprise cluster in a data center in California, and are looking to move hosts from a leased environment to a colo environment much closer to our home office. Our cluster is quite busy with around 600GB of live data, so we're looking for a way to migrate with as little downtime as possible.

This is the only thing that I believe will be the challenge in the entire environment.

My initial thought was to add a passive node to the SQL Cluster and allow it to slowly replicate over to the other data center. Once caught up, it wouldn't be hard, in theory, to fail over to this node say overnight and be active, DNS propagated, and so on, by that morning.

Does this sound viable, and if not, are there some other suggestions?

Thanks in advance for your help.

Note that both environments are very beefy resource wise, and one has 100MBps unmetered, while the destination is 1GBps unmetered. Bandwidth, space, and resources are not a concern.


In SQL 2008 you're looking for minimum downtime vs no downtime in these cases unless you're willing to shell out a lot of money/time for a few seconds. This sounds like hopefully it's not too complex of an environment so you have several options.

How about SQL Mirroring? If your data isn't coming in too fast, it'll sync up and you can 'failover mirror' option. Also, it's a very easy rollback, you just hit failover mirror again and it'll fail right back to the old cluster. If you go with High Safety mode, all writes have to be hardened on the other side before committing, thus you will have some transactional delay while the mirror is up. The upside is that it's super fast to just fail over as the data is certainly up to date. High performance mode is available in Enterprise. Mirroring is much more simple than replication and requires a lot less configuration items/requirements. For example, in Replication, every replicated table must have a PK, this isn't required in Mirroring. You also have the option of a witness server which will help with it being transparent to the application.

Worst case, you can do log shipping with automated scripts to restore the tail of the log. You can take a log backup, restore, put the DB in read only mode, take another log backup, and restore again. Be sure to check your VLFS (DBCC LOGINFO;) because if you have hundreds upon hundreds, your log restores will be slow complicating things.

Ensure security matches on both sides. Are they different domains? Then you'll have different users you'll need to add to your DB as soon as they go live. SQL user? Copy the user and SID together. There's scripts to do this.

Prior to the failover, run a trace or profiler to see what apps are connecting from where. Make sure to account for them on the new cluster. This includes user security. Also run a trace or profiler looking for logins to the old server. Let the app owner know they are connecting to the wrong DB.

Whatever you do, make sure to test and stage the process multiple times and automate it to where it's a couple of key strokes. Test failure scenarios and go into this confident.


Couple of options:

Adding a third node might be possible but as you are running on SQL 2008 you have to figure out a way to replicate the storage between the two locations (https://msdn.microsoft.com/en-us/library/ff878716.aspx) In most cases this would be done with SAN replication. If you can do that you're good to go.

If SAN replication is not an option you can set up a new cluster in the new location and use log shipping/mirroring to ship the data to the new location and storage. When the server is ready, fail over and change server names or DNS records. I would prefer that way as it's easier to go back to the old location if the migration fails. We have done this with 600 GB sharepoint environment using logshipping, shipping the initial backup on a USB drive over 2000 miles, meanwhile taking log backups and copying those the new new site. After restoring the backups the actual downtime during failover was less than 10 minutes.



This link may help for people who require this informaiton

  • Avi, please don't provide link only answers as they become useless if the link dies. Check out the help center on how to write good answers. – LowlyDBA - John M Apr 15 '15 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.