Tips for Upgrading SQL Server Versions
At my previous employer I took part in several projects that involved migrating databases not only to new hardware but also to new versions of SQL Server. We upgraded machines running the 2000 and 2005 versions of SQL Server to 2008. Here's how we went about doing it, and some helpful hints we figured out along the way.
Disclaimers
I'm fully aware there are lots of ways to migrate hardware and data between machines and database versions, I'll just be talking about the way we did it. We were fortunate enough to not have to worry about downtime, as this was carried out during scheduled maintenance windows and at other times when bringing affected products out of service was deemed acceptable.
How We Did It
We started out by taking full backups of all databases on the source instance and restoring them (WITH NORECOVERY) on the target instance. All source databases were then placed in read-only mode, and differential backups were taken and restored to the target (this time WITH RECOVERY). Target databases were then taken out of read-only mode and scripts were run on each database to perform a laundry-list of tasks such as:
- DBCC CHECKDB to make sure our newly-restored DB is consistent
- Change the database owner
- Update the compatibility level
- Ensure PAGE_VERIFY is set to CHECKSUM
- Run sp_updatestats
After the scripts completed successfully, the application servers were pointed to the new instances either by editing configuration files or redirecting connections via DNS. Applications were checked to make sure nothing broke, and the migration was complete.
Tips & Tricks
Do as much as you can in advance. The migration process I described above looks rather simple because it is, but a lot of work was done ahead of time to allow that to happen. Scheduled Tasks and Maintenance Plans were all migrated in advance by scripting them out in Management Studio and creating them in a disabled state on the target instance (they were enabled once migration was complete). SQL Server logins were transferred in a similar matter using the Microsoft-provided sp_help_revlogin stored procedure.
Practice makes perfect. Script out everything that you can in advance and "rehearse" the migration several times before it actually takes place, preferably on the new hardware before it is placed into service. Scripting it all out and testing repeatedly will ensure that nothing is forgotten and should reduce the chance of surprises at launch time. If the launch is happening in the wee hours of the morning, scripts will make sure you have much less to remember.
Change the database owner. Whatever SQL Server login or Windows User restores a database becomes the owner of that database. If that's not desired, be sure to change the database owner using sp_changedbowner (deprecated in SQL 2008) or ALTER AUTHORIZATION syntax instead (2005/2008 only).
Update the COMPATIBILITY_LEVEL. When you restore a database from an older version of SQL Server to a newer one, COMPATIBILITY_LEVEL doesn't change. If you want to take advantage of the syntax & functionality offered by the newer version, you'll need to up this value to 90 (SQL 2005) or 100 (2008) using either sp_dbcmptlevel (deprecated in SQL 2008) or ALTER DATABASE (2008 only).
Ensure PAGE_VERIFY is set to CHECKSUM. SQL 2000 doesn't support CHECKSUM, so any database upgraded from 2000 will probably be set to TORN_PAGE_DETECTION. CHECKSUM is slightly more cpu-intensive than TORN_PAGE_DETECTION, but it provides much more functionality and also isn't deprecated. Use it! PAGE_VERIFY can be changed using the ALTER DATABASE syntax in both 2005 and 2008.
Run sp_updatestats. You'll want to do this because each version of SQL Server keeps different statistics. Running this will ensure you have all the necessary stats for your version.
Like I said before I'm sure there are much more elegant ways to migrate databases depending on your requirements, this is just how we did it. Comments are most welcome!