It was a late night migration, but since you're reading this today, it was a success. Actually it was a success on Saturday morning, but I'm still amazed on how smoothly it went. A lot of credit to the IT team at Red Gate, who are supposed to be writing up more details for you, but here's my take.

Preparation

It helps to be prepared for any move and I know that people were digging in from the emails and questions I got from the testers. We initially setup new servers in a virtual environment, putting up 4 VMs on Virtual Server that mimicked the current environment. We scripted out everything, copied over backups of the databases, and configured everything in a closed network. This was to prevent any problems with mail going out to users or alerts, etc.

We had a few bugs, but once we worked through everything, it was time to move forward to real servers. Four new servers were set up at a new hosting facility and the firewalls set to deny all traffic except from the internal domains. Again this was a safety precaution to prevent problems with mail. Once we were fairly confident that things were working, we started the migration with our mail servers.

We have a distributed process that drops all email into a table in a database. This is essentially a Q of mail that needs to go out and we have standalone applications that run every minute on various server to pick up the mail and send it to you. This allows us to scale out the mail process, something that's needed when you're sending 200,000+ messages in half a day.

Our first step was to add the new mailers to our DNS and network configuration. Once those changes were propagated, we opened the firewall and configured the new mailers to point to our old database server. Slowly over 3 days we ramped up the new servers and ramped down the old ones until we were confident that everything was working well.

Now things were set for the migration of the database and web site. We built configuration files on both sets of servers (new and old), each pointing to one of the databases. Since we wanted to minimize downtime, our plan was to test the new servers against the old database, then configure them for the new database. We also planned on pointing the old servers to the new database after the migration. All of this was done by renaming files, moving from _old or _new to the live names.

An Upgrade and a Move

When we started planning this move in February, we decided to move to SQL Server 2005. As well as SQL Server 2000 has worked for us, it didn't make sense to install the older version and have to deal with the upgrade later. It would also allow our developers (hard at work right now) to work on .NET 2.0 and SQL Server 2005 from the beginning.

That violates my "one change at a time" rule, but since we were in test phase, I agreed it was worth a try. We installed SQL Server 2005, restored SQL Server 2000 backups, and have not looked back. All of our issues have been in terms of configuration and rights, nothing wrong with running a SQL Server 2000 database restore, in SQL Server 2000 mode, on a SQL Server 2005 server.

The move occurred last Friday. Everything was all set up and running in a test environment, meaning logins were created and synched, databases existed in the proper places, etc. Friday afternoon we performed a full backup of our six databases and copied them to the new servers. A restore was performed with the NORECOVERY option in preparation for the final move. The full backup jobs were disabled to prevent them from running Friday night and delaying the move. Actually we shut down a number of maintenance jobs that afternoon.

After putting kids to bed, I watched a little of the NCAA tournament and grabbed a Corona before heading down to the office. One of the nice perks of living in the same building in which I work.

It's important to do things in some type of regular order. So the first step was to shut down the current SQLServerCentral.com and DatabaseDaily.com sites on the server. We'd configured a separate site that consisted of a single maintenance page with host headers for those sites, so I started that and tested it to be sure it was working.

As soon as things were shut down, I started differential backups of all the databases. These were pretty small since they were only about 10 hours old. We could have run those and then futzed with log backups, but I declined since it was 11pm MST. Our largest was 200MB and everything else was less than 20MB, so I started those FTP'ing to the new servers. That took a little over a half hour, so I enjoyed my beer and watched an old episode of Friday Night Lights.

Just before the transfers finished, my counterpart had arrived and started the restores of the smaller dbs. We communicated by IM, which worked well and was easier than holding a phone to my ear. Once the databases were restored, we fixed the logins, opened the firewall and got ready to test.

The site was still live on the old servers according to our DNS records. As a note, we could fool our workstations into using the new servers by editing our "hosts" files and entering the host and IP. This is great when you use host headers on your servers and IP alone won't work. Your hosts file is in %system root%\system32\drivers\etc.

Testing showed a few minor config issues that required a little editing of the various files to fix issues. About the time my eyes started to burn at 2:00am, we were pretty sure everything was working. Obviously we had the time display issue in the forums, but everything else seemed ok. I signed off to get some rest after my 20 hour day and left the rest of the crew to do some testing.

Conclusion

Further testing over Saturday and Sunday turned up no other issues. The mail was delivered faster than ever and things have run smoothly over the next few days. Overall it was one of the smoothest server moves I've had.

There are still some things to get used to. I have been used to using Visual Studio to access the site and a firewall route for database access and we have things more locked down now. I have to sign in and out of a Cisco VPN, which messes up my Internet access a little, but it's working so far.

I'm looking forward to getting a write up from the other side of the team that performed most of the work and helping you in your next server migration.