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.
Best Answer
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.