How To Upgrade To SQL Server 2008

Now that SQL Server 2008 has released to manufacturing (RTM), you need to start planning how you will upgrade from a previous version of SQL Server to SQL Server 2008.  This will depend on which version of SQL Server you are currently running, your availability requirements, and your hardware budget.

The "best" approach, (assuming you have available hardware and budget) is to install a fresh copy of Windows Server 2008 on new hardware, with SQL Server 2008 installed as a default instance. Then, you can use one of the methods below to get your database(s) upgraded to SQL Server 2008 format.

  1. Take a full backup in SQL Server 2000\2005. Copy the backup files to the new server and restore from the backup. Change the compatibility level to 10.0 and run sp_updatestats.
  2. Detach your SQL Server 2000\2005 database. Copy the data files and transaction log files to the new server and attach the database. Change the compatibility level to 10.0 and run sp_updatestats.
  3. If you are running SQL Server 2005, you can use database mirroring to move the data with one brief 15-20 second outage. You must be using the full recovery model to do this. Take a full backup in SQL Server 2005. Copy the backup files to the new server and restore from the backup with no recovery. Replay any subsequent transaction log backups on the new server with no recovery. Enable database mirroring and let the mirror get synchronized. Modify your connection strings to include a failover partner. Failover from SQL Server 2005 to SQL Server 2008, and then remove the mirror and change your connection strings. Change the compatibility level to 10.0 and run sp_updatestats.

Methods 1 and 2 are easier, but require some downtime. Method 3 is quite a bit more complicated, but can be used to migrate with virtually no downtime. Of course with all of these methods, you will have to migrate your logins and SQL Agent jobs to the new server. Even though Microsoft puts a lot of effort and testing into various "upgrade in place" scenarios, I would personally never go that route.

Technorati Tags:
This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s