Recently I was tasked with preparing and executing a database server migration. This was no small job. I was to move our live production database from an aging server running Microsoft SQL Server 2000 to a brand new Dual-Quad EM64 bad ass server running Microsoft SQL Server 2005. And I had to do it working against the clock.
Our database is really a gigantic string-buffer with some complicated counting bits. As words are said on TV or Radio they go into the database, and after a certain amount of time they are deleted to make room for new words. This system serves us very well, but we have to stay on top of things, because when we have a database server outage, all of the text that's meant to be coming in, just waits at the front door. When too much text is at the front door waiting we run the risk of never being able to catch up.
Oh, did I mention that this database is primarily responsible for putting food on my table?
So needless to say there was a lot of planning. I wanted to have a very good idea of how long our service would be unavailable to our users. So I came up with a plan:
- Test
- Stop the old database server
- Copy database files from old server to new server
- Attach the newly copied database files
- Test for sanity
- Swing DNS to point to the new server
Figuring out the time it takes to swing DNS is easy. You just look at the domains TTL and thats about the time it'll take for everything to make the change (give or take 50%).
Figuring out how to copy the files took a little bit of work. Our production database is somewhere in the neighborhood of 500GB. In the Microsoft SQL Server world, that means I have one gigantic file: My_Really_Big_Production_Database.mdf which has to get copied from the old server to the new server. Without getting too into the nitty-gritty I ended up using a Microsoft utility called eseutil to copy the files. This method seems to work quickly and didn't require me to install 3rd party software on my new database server.
The only other part of the process that had me nervous was performing the database "attach". Normally I wouldn't think twice about the performance of this operation, but in this case we were performing a rather significant upgrade. Not only did our database files have to be converted from SQL 2000 to SQL 2005, but it also had to go from 32bit to 64bit. What this means is that SQL Server performs a series of iterative upgrades on the database structure to get it up to date. So obviously I performed scaled down tests. Attaching and detaching little shell databases didn't get me any closer to an answer. The attach would either take 5seconds or happen instantaneously.
So obviously I turned to the internet. Oh no! No good answer! ACK! At the end of the day I had to cross my fingers and hope for the best as I executed the attach statement. Well, I got my answer. And now I'm giving it back to the internet. [drum roll...]
Question: How long does it take to attach a SQL 2000 database to a SQL 2005 server?
Answer: About 19 seconds for my 500GB database with about 300 tables. The truth is we moved a number of databases on that day. They all seemed to take between 3 and 19 seconds, and it didn't seem to matter how big the database was.
Phew... ok Internet, you can rest easy now.
Update
If I were writing this post in vi, now would be the time where I run this:
:s/I/Jared and I/g
Due to lateness of the the hour when I wrote this post I inadvertently left out the fact that without the help of, my friend, Jared, this migration would have most-likely crashed and burned. I was fortunate enough to benefit from Jared's encyclopedic knowledge of SQL server throughout the migration. Thanks bud!
Update #2
Success! Now the Internet people, with the same question that I had, can have an answer!
No comments:
Post a Comment