Tuesday, December 8, 2009

Migration of SQL Server 2000/2005 to SQL 2008

In this post, I will be discussing about the migration of SQL server 2000/2005 to SQL 2008 Engine and not about the analysis, integration and Reporting services migration.

Let me first discuss about the Upgrade Stratergy first. There are two ways of upgrading the SQL server 2000/2005 to SQL 2008.
1> In-place upgrade.
2> Side by Side upgrade.

Upgrade Pre-requisites
· When planning an upgrade to SQL Server 2008, we first have to make sure that the target servers meet the necessary hardware and software requirements for SQL Server 2008 Setup to be completed.
· .NET Framework 3.5 should be installed.
· Windows Installer 4.5 should also be installed.
· Need to back up all of the system databases and user data bases
· Power shell should be installed.
· Need to run DBCCCheckDb against all databases to see the integrity of all the databases are intact.
· Finally, Run the Upgrade Advisor. Need to go through all the errors/warnings one by one and fix the errors before going forward with upgrade. Priority will be set for the errors which needs to be mandatorily fixed before the upgrade can begin.

In-place Upgrade
There are few restrictions when we are going for an In-place upgrade.
  • SQL Server 2008 Setup requires that all SQL Server 2000 and SQL Server 2005 components be upgraded together. In other words, we cannot upgrade only an instance of the SQL Server 2005 Database Engine without also upgrading the Analysis Services component.
  • If our legacy instance of SQL Server 2000 or SQL Server 2005 is installed on Windows 2000 Server, we must upgrade to a new server by using a side-by-side upgrade; SQL Server 2008 is not supported on Windows 2000 Server.
  • A cross-platform, in-place upgrade from a 32-bit instance of SQL Server 2000 or SQL Server 2005 (x86) to a 64-bit instance of SQL Server 2008 (x64), or vice versa, is not supported.

An in-place upgrade is the fastest and easiest upgrade method because it upgrades all system and user databases and settings for us. We do not have to update client applications to connect them to a new instance of the Database Engine. In the unlikely event that an in-place upgrade of the relational Database Engine fails, we cannot quickly roll back to SQL Server 2000 or SQL Server 2005.
If the upgrade fails, we have to take the following steps:
1> From the installation media, we need to run the repair option in an attempt to fix the instance; if this does not work, we need to go for uninstall as mentioned in step 2.
2> Uninstall the corrupted SQL Server 2008 instance that was created during the failed upgrade attempt.
3> Reinstall the earlier version of SQL Server (SQL Server 2000 or SQL Server 2005).
4> Reinstall any required SQL Server service packs to your SQL Server 2000 or SQL Server 2005 instance.
5> Restore the system and user databases from database backups.
6> Review issues that prevented a successful upgrade in the previous attempt, resolve them, and restart the upgrade process.
We need to be aware that this process of in-place upgrading needs sufficient downtime, either when the upgrade is a success or when its a failure.

Activites to be done after In-place upgrade

1> Check if the Remote query timeout (s) server setting is set to 0, if not need to change.
2> Compatibility of master and User databases need to change to 100

Side by Side Upgrade

In this upgrade, we try to move all or some data from an instance of SQL 2000/2005 to SQL 2008. There can be one server move or two sever move.

In one server move, we install a new instance of SQL 2008 on the same server where SQL 2000/2005 is available.

In two server move, we install a new instance of SQL 2008 either in default or named instance on a new server. This upgrade is very time consuming in which the migration would be done manually or either by scripting. But seeing the positive side, there is no need for the application downtime, till the SQL 2008 databases are ready to be connected to the application.

Hope this helps someone...

No comments:

Post a Comment