Many organizations still use SQL Server 2000. But at some point, those organizations will need to upgrade. They can’t put off the task indefinitely, either, because the next version of SQL Server isn’t likely to support a direct upgrade from SQL Server 2000.

SQL Server 2008 supports direct, in-place upgrades from SQL Server 2005 and SQL Server 2000. However, because you can’t use SQL Server 2000 on Windows Server 2008 and you can’t use SQL Server 2008 on Windows 2000 Server, only Windows Server 2003–hosted SQL Server 2000 databases can be upgraded. (If your SQL Server 2000 database is installed on Win2K, you should consider performing a migration, which SQL Server 2008 does support, instead of an upgrade. For information about migrating to SQL Server 2008, see the Microsoft article “Migrating to SQL Server 2008,” msdn.microsoft.com/en-us/ library/bb677619.aspx.) For readers who will be upgrading, whether sooner or later, I explain how to upgrade from SQL Server 2000 to SQL Server 2008.

SQL Server 2000 Prerequisites
Before you start your upgrade, make sure that Windows 2003 has SP2 or later applied and that your SQL Server 2000 instance has SP4 applied. The Windows Installer service must be running. Also be aware that you can’t perform an upgrade if a restart is pending (e.g., due to the installation of updates) or if performance counters are corrupt. The SQL Server 2008 installation routine checks for these problems before the upgrade begins.

Microsoft supports only certain upgrade paths from SQL Server 2000 to SQL Server 2008; Table 1 shows those paths. The rule of thumb is that you can upgrade SQL Server 2000 to an equivalent or higher edition of SQL Server 2008, but you can’t upgrade SQL Server 2000 to a lower edition of SQL Server 2008. For example, you can upgrade SQL Server 2000 Standard Edition to SQL Server 2008 Enterprise Edition, but you can’t upgrade SQL Server 2000 Enterprise to SQL Server 2008 Standard. Neither can you upgrade a 32-bit instance to a 64-bit instance, although it’s possible to perform a detach-and-restore upgrade if the databases aren’t published in replication. The drawback of a detach-and-restore upgrade is that you need to recreate all logins and user objects in the master, msdb, and model system databases on the 64-bit SQL Server 2008 instance.

When upgrading replicated databases, upgrade the distributor database before the publisher database because the distributor’s edition must either be the same as the publisher’s edition or it must be a more advanced edition. Be aware that upgrades of IA64 failover clusters aren’t supported and that you can’t upgrade SQL Server Analysis Services (SSAS) 2000 to SQL Server 2008 with a failover cluster.

SQL Server 2008 Upgrade Advisor
SQL Server 2008 Upgrade Advisor, which you can install from the SQL Server 2008 installation media, lets you 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 you 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 or the Microsoft Download Center (www.microsoft.com/downloads/ details.aspx?FamilyId=F5A6C5E9-4CD9-4E42- A21C-7291E7F0F852). You can run Upgrade Advisor on Server 2008, Windows Vista, Windows 2003 SP1, and Windows XP SP2. If necessary, you can run Upgrade Advisor remotely. If you want Upgrade Advisor to scan SSAS 2000, SQL Server 2000 Decision Support Objects must be installed on the scanning computer. SQL Server 2000 client components must be installed on the scanning computer to scan SQL Server 2000 DTS packages. Because you need to install SQL Server 2000 components, it’s often easiest to run Upgrade Advisor on the SQL Server 2000 computer.

2. Launch the Upgrade Advisor Analysis Wizard, enter the name of the target server, and select the SQL Server components that you want to analyze, as in Figure 1. You can click Detect to determine which components are installed on the target computer.

3. Provide the credentials and authentication method you’ll 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. You should use an account that’s assigned the system administrator role on the target SQL Server 2000 server. If you want to examine SSAS, the account should also be a member of the target SQL Server 2000 server’s local OLAP Administrators group.

4. On the wizard’s SQL Server Parameters page, select the target-server databases that you want to analyze. If you want to analyze trace files or SQL batch files, select the appropriate check box(es) and provide the paths to the files.

5. Enter parameters for other services, such as SSAS and DTS. When you select DTS, you can choose to analyze all DTS packages or specify them by path.

6. Begin the check. How long the check will take depends on the number of items the Upgrade Advisor needs to check.

7. After the analysis is complete, click Launch Report. Figure 2 shows a sample report.

After Update Advisor is installed, the system configuration checker determines whether your server meets the prerequisites for a successful SQL Server 2008 installation. It performs this check again when you start the upgrade process.

Upgrade SSAS
Microsoft recommends that if your SQL Server 2000 deployment includes SSAS, you not upgrade SSAS when performing the rest of your upgrade. Instead, you should install SSAS 2008 side by side with SSAS 2000 on the same server. Then, follow the steps I provide here to update the SSAS 2000 databases to the SSAS 2008 format and remove SSAS 2000. Only then should you upgrade the database engine to SQL Server 2008.

1. Open the SQL Server Installation Center by double- clicking setup.exe on the installation media.

2. Click Installation, then New Installation or Add Features to an Existing Installation.

3. Enter the product key, review the license terms, and install the setup files.

4. On the Feature Selection page, select only Analysis Services and the SQL Server Management Studio (SSMS) tools, as in Figure 3. Remember that you’ll be performing an upgrade for the other SQL Server 2000 components.

5. On the Instance Configuration page, create a named instance rather than using the default instance. If you don’t use a named instance, you’ll have problems migrating existing SSAS databases to SQL Server 2008.

Continue to page 2

6. For SSAS, specify a service account—preferably a domain account—and the users who should have administrative permissions for SSAS. You can add the user account that’s performing the installation on this page. After the installation routine performs a final check, you can install SSAS 2008.

7. After the SSAS installation is complete, start SSMS, click Connect to Analysis Services, rightclick the instance you created, then click Migrate Database to launch the Analysis Services Migration Wizard. Specify the source and destination servers, as in Figure 4.

8. Select the SSAS databases to migrate. The wizard validates each database’s metadata, then migrates the databases to SSAS 2008.

9. After the databases have been migrated, remove SSAS 2000 using the Control Panel Add or Remove Programs applet.

Upgrade Walk-Through
Once you’ve run the Upgrade Advisor and upgraded SSAS, you can upgrade to SQL Server 2008. The following instructions and examples are from upgrading a SQL Server 2000 SP4 Enterprise Edition instance with the AdventureWorks database installed to SQL Server 2008 Enterprise Edition. The instructions should apply to other upgrades from SQL Server 2000 to SQL Server 2008.

1. Launch the SQL Server Installation Center, shown in Figure 5, either by running setup.exe or double-clicking the SQL Server 2008 installation media to launch the autorun routine. If you haven’t already run Upgrade Advisor, you might be prompted to install .NET Framework 2.0 and Windows Installer 4.5.

2. Click Installation, then Upgrade from SQL Server 2000 or SQL Server 2005. The installation routine checks whether the SQL Server 2000 host server meets the minimum requirements for setup. If the routine finds a problem at this stage, you should quit the installer, solve the problem, and then restart the upgrade.

3. Enter the license key and accept the license terms. Before you continue the setup, install the setup support files.

4. On the Select Instance page, select the instance of SQL Server 2000 that you want to upgrade.

5. By default, the SQL Server 2000 Database Engine, SQL Server Replication, Full-Text Search, SSAS, and Management Tools will be upgraded. It’s not possible to deselect any of these features if you’ve chosen the full upgrade option. If you’ve chosen to upgrade shared features, you can upgrade only the shared features. Review the automatic selections and then click Next.

6. On the Instance Configuration page, select the instance ID and click Next.

7. When you reach the Disk Space Requirements page, the upgrade routine checks whether enough space is available for the upgrade. The upgrade needs about 2GB: approximately 700MB on the system drive, 600MB on the volume hosting the program files, and another 700MB on the volume hosting the instance you’re upgrading. Click Next.

8. On the Service Accounts page, specify an account that has low privileges to assign to the SQL Full-text Filter Daemon Launcher service. The SQL Server Browser service will default to NT AUTHORITY\LOCAL SYSTEM. In general, you should use a separate, specially named, lowprivilege account for each service.

9. On the Full-text Upgrade page, you can choose to import, rebuild, or reset full-text catalogs. Importing is the quickest, but that option doesn’t use the new and enhanced SQL Server 2008 word breakers, which determine where boundaries between words in text exist. The Rebuild option uses the enhanced word breakers but might incur a performance hit. The best option in many cases is Reset, which removes the catalog files but keeps metadata for catalogs and indexes. The catalog will remain empty when the upgrade is completed until you issue a full population.

10. On the Error and Usage Reporting page, specify whether you want to send Windows and SQL Server error reports to Microsoft or to your corporate reports server. You can also allow feature and usage data to be sent to Microsoft. These options are disabled by default.

11. The Upgrade Rules page shows the results of 29 tests that the installation routine performs. This check is less thorough than the one performed by Upgrade Advisor.

12. The routine then provides a summary of your upgrade information and displays the path to the upgrade configuration file. Click Upgrade to start upgrading to SQL Server 2008. Depending on your hardware configuration, the upgrade process can take from 30 minutes to several hours. The database is unavailable to clients during the upgrade process.

13. When the upgrade is finished, the wizard tells you the upgrade status of each component. The final page of the upgrade wizard shows the location of the upgrade log.

Continue to page 3

Upgrade DTS
Once you’ve upgraded SQL Server 2000 to SQL Server 2008, you should use the DTS Package Migration Wizard to move packages from DTS to SSIS format. Package migration will usually succeed unless the packages contain unregistered objects or use scripting. Packages that contain only tasks and features that are present in SSIS will migrate successfully. You can preserve packages that contain non-SSIS DTS tasks and features by encapsulating them in an Execute DTS 2000 Package task, and those packages will often run without error. However, you must eventually replace those DTS functions with SSIS equivalents.For more information about migrating DTS packages to SSIS format, see the Learning Path and the SQL Server 2008 Books Online.

Gotchas
There are a couple of things to watch out for when you’re doing your upgrade. A post in a Microsoft blog acknowledged that problems occur if you attempt to upgrade to SQL Server 2008 and you’ve changed the name of the the systems administrator (sa) account on the database you’re upgrading. Apparently, the sa username is hard-coded into at least one call in the sqlagent 100_msdb_upgrade.sql script, causing the script to fail if the account has a different name. You can avoid the problem by renaming the account to sa or by creating a temporary domain user account with the name sa and adding it to the Database Administrators group.

Also, if you intend to use APPLY, PIVOT, UNPIVOT, or TABLESAMPLE against upgraded databases, use the sp_dbcmptlevel stored procedure to set the database compatibility level to 100, or you may encounter unexpected results.

Other Notes
It should go without saying that before you attempt the upgrade, you need to back up everything so that you have an adequate fallback position should you need one. I also recommend doing an upgrade of a development server that mirrors your production configuration before you upgrade your production instance. Virtualization software simplifies testing whether upgrades will be successful and helps you find upgrade problems that tools such as Upgrade Advisor might miss. Upgrade Advisor is an excellent tool, but it doesn’t catch everything, especially if you have a highly customized configuration. Completing a successful upgrade of a virtualized configuration that mirrors your production configuration will make upgrading your production system less stressful.

If you find that you’re unable to upgrade successfully in a development environment even though the upgrade tools indicate there should be no problems, consider removing SQL Server 2000 components, such as SSAS, and trying again. You can also look into performing a migration instead of an upgrade.