Database Server Upgrades

There are two upgrade paths available to move from SQL Server 2000 or SQL Server 2005 to SQL Server 2008.

• In-place upgrade
• Side-by-side migration

In-place upgrade is fully supported. In an in-place upgrade, the SQL Server 2008 binaries are installed on an existing SQL Server installation. We stop the older SQL service, point the services to the new SQL Server 2008 binaries, and then upgrade the databases. We must upgrade to the same architecture version of SQL Server and can't move from 32-bit to 64-bit. The in-place upgrade is generally simpler; we have less control and granularity of database upgrade scope and very little rollback capability in the event of a problem.

Side-by-side migration, which is a more manual process where a second instance of SQL Server is created running SQL Server 2008. This second instance can be on the same box as the SQL Server 2000 or 2005 instance, or we can use a new server. The data is copied from the legacy SQL Server instance using the copy database wizard, a backup/restore, or a detach/attach operation. Once testing is completed on the new server, we can move applications and services to the new instance and retire the old one. If we use side-by-side migration, we can change the SQL Server's architecture as part of the migration. A side-by-side migration gives us a lot of control and testing capability, but is more complex and we need additional hardware.

Whichever approach we take, we need to make sure to use the SQL Server 2008 Upgrade Advisor to identify any potential problems before doing the upgrade.

SQL Server 2008 Upgrade Advisor:

SQL Server 2008 Upgrade Advisor, which can be installed from the SQL Server 2008 installation media, lets us check the upgrade state of the database engine, SSAS, SQL Server 2005 Reporting Services, SQL Server Integration Services (SSIS), and DTS. Components such as .NET Framework 2.0 and Windows Installer 4.5 are automatically installed before we run Upgrade Advisor if they aren’t present on Windows 2003.

There are seven steps to using Upgrade Advisor.

1. Install Upgrade Advisor from the SQL Server 2008 installation media. We can run Upgrade Advisor on Server 2008, Windows Vista, Windows 2003 SP1, and Windows XP SP2.
2. Launch the Upgrade Advisor Analysis Wizard, enter the name of the target server, and select the SQL Server components that we want to analyze, as in the screenshot. We can click Detect to determine which components are installed on the target computer.
3. Provide the credentials and authentication method we will use to connect to the SQL Server instance and perform the check. If both computers are members of the same Active Directory domain, the check will by default use the credentials of the user who’s logged on. We should use an account that’s assigned the system administrator role on the SQL Server 2000 server.
4. On the wizard’s SQL Server Parameters page, select the server databases that we want to analyze. If we want to analyze trace files or SQL batch files, select the appropriate check box and provide the paths to the files.
5. Enter parameters for other services, such as SSAS and DTS. When we select DTS, we can choose to analyze all DTS packages or specify them by path.
6. Begin the check. The time taken to perform the check will depend on the number of items the Upgrade Advisor needs to check.
7. After the analysis is complete, click Launch Report.       

1. Note: Microsoft recommends that if SQL Server 2000 deployment includes SSAS, we should not upgrade SSAS when performing the rest of the upgrade. Instead, we should install SSAS 2008 side by side with SSAS 2000 on the same server and then, update the SSAS 2000 databases to the SSAS 2008 format and remove SSAS 2000. Only then we need to upgrade the database engine to SQL Server 2008.