Syncing Database for Server Transition

Question

I'm transitioning from an old web/sql box combined to a much more robust solution on a completely different host (800hosting to RackSpace).

Our application has very high uptime requirements. It runs 24x7x365 and impacts literally thousands of sites, and I want to ensure the minimum possible downtime during the transition. (Don't ask how we're pulling this off with a single box right now.)

My plan for the web server is a little bit of DNS work to point a new subdomain to the new server setup, and forward requests from the existing server. That'll take care of flipping everyone to the new web box fast enough. The problem is the database - how do I keep it in sync until I'm ready to flip the switch on the website (or is it worth the effort vs. just accepting the downtime to stop the app, backup, compress, transfer, and restore?).

A few details. We're running Sql2k5 on the old system, 2k8r2 on the new. The database itself is ~30 gigs for the primary, ~60 gigs for the warehouse. I can live with downtime on the warehouse, but really want to minimize the impact on the main database.

Any suggestions for the best way to migrate the database across from the old setup to the new?

Answer 1

S1 = old server, S2 = new server

1. Full Backup S1
2. Restore to S2 and with NOrecovery
3. Block ALL data changing connections to S1 (e.g. change web connection password)
4. Diff backup on S1
5. Restore diff to S2 with recovery - S2 will now be ready
6. Enable connections to S2 ( don't forget to reset any changed passwords back to the originals)

Answer 2

I've upvoted Jimbo's answer, because I think in an ideal world, it would have worked perfectly.

Unfortunately, due to network transmission constraints, database size, disk I/O limits, CPU usage restrictions, etc, I couldn't do the restore/diff/restore fast enough to maintain the proper downtime requirements without impacting the old server. If I tried transferring the data at full speed, it pegged the CPU too hard and started causing issues with the site, and if I throttled it, it took too long.

So, I ended up doing log shipping. Log shipping is nice enough to automatically play catch up, so I let the original backup take it's time transferring, and then applied all the transaction logs via the shipping config. It also made the whole thing less stressful, since when I was done, I simply turned off access to the old server, ran the scheduled log ship task manually on it, ran the copy/restore log ship methods on the new server, and turned things back on. No manual file-copying, etc, was needed, which made it nice and easy to do the final transition.

Thanks for all the suggestions.

Answer 3

You should use database mirroring to replicate the database to the new server, then fail it over (alter database mydb set partner failover) and break the mirror (alter database mydb set partner off) when you are ready to make your DNS change since mirroring FROM 2008 TO 2005 will not work. The fail over for database takes around 5-30 seconds and the roll back plan would be to recover the database on the 2005 server (restore database mydb with recovery) and then re-point DNS back to the original server.

Answer 4

If you have full access to the SQL Server at the current facility (which it sounds like you do) then Log Shipping will be the way to go. I've done this many times moving people into/out of/between managed hosting facilities.

1. Setup a VPN tunnel between the sites.
2. Create a Windows account on the new SQL Server that has the same username and password as the account which is currently running SQL Server.
3. Setup two new jobs on the current SQL Server. The first copies all the t-log backups (I'm assuming you are already taking t-log backups) to another folder on the local server. The second job copies those files to the new SQL Server at Rackspace.
4. Setup a job on the Rackspace SQL Server to restore the logs in order. If you are naming them correctly you can simply get a directory listing with xp_dirtree and they will be in the correct order.

Once all this is setup and running, when it comes to the night of the migration you simply shut down the front end site, run the backup logs job, run the copy jobs in order, then run the restore job. Once everything is done you can bring the databases online at the new site. Then just point the web servers at Rackspace to the new database server and you are done.

A consultant who's done this before (disclaimer I'm a consultant) can be helpful and make the project less stressful, but it isn't required if you are familiar with SQL Server and doing backups and restores. Everything here is pretty straight forward. Done correctly the total downtime for SQL Server can be as small as a few minutes or a couple of hours depending on how much data needs to be restored in that last set of log backups.