Upgrading to SQL Server 2008 R2

Most businesses will implement SQL Server 2008 R2 by upgrading existing instances of SQL Server. Microsoft provides a setup program called the SQL Server Installation Center to install SQL Server 2008 R2. In addition to creating new installations, the SQL Server Installation Center can be used to upgrade existing SQL Server 2008, SQL Server 2005, and SQL Server 2000 installations to SQL Server 2008 R2. However, it cannot be used to upgrade older versions of SQL Server such as SQL Server 7.0 or SQL Server 6.5. In this article you’ll see how to upgrade a SQL Server 2005 instance to SQL Server 2008 R2.

Upgrading from SQL Server 2005 or SQL Server 2000

The SQL Server Installation Center supports direct upgrades to SQL Server 2008 R2 from SQL Server 2008, SQL Server 2005, and SQL Server 2000 installations. SQL Server 2008 R2’s essential on-disk structures are the same and the setup program can successfully perform an in-place migration of all of these versions of SQL Server.

To initiate the upgrade to SQL Server 2008 R2 you can execute the Autorun program on the SQL Server 2008 installation media or you can launch the setup.exe program found in the installation media’s root directory. These options will both start the SQL Server Installation Center. As with the clean installation process, the setup program first checks for the presence of the Microsoft .NET Framework 3.5 SP1. If it isn’t found, the setup program will download and install the .NET Framework 3.5 SP1, as you can see in Figure 1.

Figure 1: Installing the .NET Framework 3.5 SP1

Clicking the OK button will begin the web installation for the .NET Framework 3.5 SP1. The setup program will display the Welcome dialog box shown in Figure 2.

Figure 2: Accepting the .NET Framework License Agreement

Selecting the checkbox that indicates that I have read and accept the terms of the License Agreement and then clicking the Install button will start the installation of the .NET Framework 3.5 SP1.

After installing the .NET Framework 3.5 SP1, the SQL Server 2008 R2 installation process updates the system with the Windows Installer 4.5. This displays the Windows Installer, version 4.5 License Agreement dialog box that you can see in Figure 3.

Figure 3: Installing the Microsoft Windows Installer 4.5

To install the Windows Installer 4.5, select the I Agree radio button and click Next. After the Windows Installer 4.5 setup process has finished you’ll be prompted to restart the system. Then you need to relaunch the SQL Server 2008 R2 setup program. The SQL Server 2008 R2 portion of the upgrade process begins when the setup program displays the SQL Server Installation Center shown in Figure 4.

Figure 4: SQL Server 2008 Installation Center

Before performing the upgrade, it’s a good idea to install and run the Upgrade Advisor. Selecting the Install Upgrade Advisor option from the Planning page will install the Upgrade Advisor on your system. After completing the installation you can run the Upgrade Advisor can check your SQL Server system configuration and code. The Upgrade Advisor will warn you about changes or depreciated features that might affect your system upgrade. You can optionally generate a report from the Upgrade Advisor that you can use to systematically address any potential upgrade problems. When you’re ready to perform the actual upgrade, you can click Installation and then select the Upgrade from the SQL Server 2000, SQL Server 2005 or SQL Server 2008 option shown in Figure 5.

Figure 5: Upgrade from SQL Server 2000, SQL Server 2005 or SQL Server 2008

Selecting the Upgrade from SQL Server 2000, SQL Server 2005 or SQL Server 2008 option launches the SQL Server 2008 installation program. As with a clean installation, the first thing the SQL Server 2008 R2 setup program does is run the Setup Support Rules to check if there are any system problems that would interfere with the upgrade. You can see the Setup Support Rules dialog box shown in Figure 6.

Figure 6: Setup Support Rules

If there are any issues found that will prevent the installation from running, they will be displayed and you must correct them before you can continue the upgrade. If the system passes all of the setup rules, then the OK button will be enabled. Clicking OK from the Setup Support Rules screen displays the Product Key dialog box, allowing you to either choose to install the free Evaluation Edition or to enter your SQL Server 2008 R2 product key. You can see the Product Key dialog box shown in Figure 7.

Figure 7: Product Key

After entering the product key, click the Next dialog box to display the License Terms dialog box that’s shown in Figure 8.

Figure 8: License Terms

To proceed with the upgrade, select the I accept the license terms box and then click Next. This will display the Setup Support Files dialog box that you can see in Figure 9.

Figure 9: Setup Support Files

Clicking Install will copy the files needed to upgrade to SQL Server 2008 R2 to your SQL Server 2008, SQL Server 2005 or SQL Server 2000 system. Next the Setup Support Rules dialog box is automatically run to check for any problems that might have occurred from installing the setup support files. This is the same dialog box that was shown earlier in Figure 6. Click Next to display the Select Instance dialog box that’s shown in Figure 10.

Figure 10: Select Instance dialog box

The Select Instance dialog box lets you choose the SQL Server instance that you want to upgrade. It’s possible to have multiple instances installed on the same system and to upgrade them separately. SQL Server supports up to 50 instances on the same system and each named instance is essentially a different installation of the SQL Server code. As you can see in Figure 10, a default instance named MSSQLSERVER has been selected to be upgraded. Clicking Next displays the Select Features dialog box shown in Figure 11.

Figure 11: Select Features dialog box

The Select Features dialog box shows you the installed features that will be upgraded. All of the installed options are checked by default. In case you were wondering, you can’t choose to just update some features. When performing an upgrade to SQL Server 2008 R2 you must update all of the installed features in the selected instance at the same time. Clicking Next displays the Instance Configuration screen, shown in Figure 12.

Figure 12: Instance Configuration screen

The upgrade process will detect the installed SQL Server instances and all of the installed instances will be listed in the Installed instance box at the bottom of the screen. You can choose to keep the installed instance name or you can opt to change it. In most cases, you’ll want the upgrade process to use the same SQL Server instance name. In Figure 12, you can see that upgrade process will update the default MSSQLSERVER instance. Clicking Next displays the Disk Space Requirements dialog box, shown in Figure 13.

Figure 13: Disk Space Requirements dialog box

The Disk Space Requirements screen shows the disk space that will be required by the upgrade process as well as the available disk space for the volume. As you can see in Figure 13, it also displays the required disk storage for each installation directory. If there’s adequate space to proceed with the installation, a green check mark will be displayed at the top of the screen. If there isn’t enough disk space to perform the upgrade, a red X will be displayed. Click Next to display the Server Configuration screen shown in Figure 14.

Figure 14: Server Configuration screen

The Server Configuration dialog box enables you to change the default startup type and authentication information for SQL Server Integration Services, the SQL Full-text Filter Daemon Launcher, and the SQL Server Browser services. You can set the authentication to a domain user account or you can accept the default values, which were pulled from the existing configurations. Click Next to proceed through the upgrade installation process. If you installed the Full-text search feature on the SQL Server instance that you selected to upgrade, then you’ll see the Full-text upgrade dialog box, shown in Figure 15.

Figure 15: Full-text Upgrade screen

You use the Full-text Upgrade screen to specify how you want the upgrade process to deal with any existing full-text search catalogs. You can choose to import the existing catalogs without any changes, or you can choose to rebuild or reset the full-text catalogs. Importing the existing catalogs is the default option, and it leaves the full-text catalogs unchanged but that means that they might not be able to take advantage of some of the enhanced SQL Server 2008 R2 full-text search functionality. The rebuild option will rebuild the catalogs, adding the new functionality, but using this option can considerably lengthen the upgrade process. The reset option clears the existing full-text catalog data but doesn’t rebuild it during the upgrade. If you select the reset option, you can rebuild the catalog data manually after the installation. If you choose the default Import option, you can optionally rebuild the full-text catalogs at some point after the upgrade has completed. Clicking Next displays the Error Reporting dialog box that’s shown in Figure 16.

Figure 16: Error Reporting

The Error Reporting dialog box lets you optionally send SQL Server error reports and usage information to Microsoft. This is optional and you can elect to skip it. By default, the upgrade process won’t send error reports to Microsoft. Clicking Next displays the Upgrade Rules dialog box, shown in Figure 17.

Figure 17: Upgrade Rules dialog box

The Upgrade Rules dialog box performs a final check for any conditions that might cause the upgrade process to fail. If any error conditions are found, they will be shown in the Rule listing, preceded with a red X. If all the rules have green check marks, as you can see in Figure 17, then you can proceed with the upgrade. Clicking Next displays the Ready to Upgrade dialog box that’s shown in Figure 18.

Figure 18: Ready to Upgrade dialog box

The Ready to Upgrade dialog box enables you to see a summary of the upgrade actions that will be performed. If you need to change anything, you can use the Back button to page back through the previous upgrade dialog boxes. Click the Upgrade button to launch the SQL Server 2008 R2 upgrade process. The Upgrade Progress dialog box will then be displayed, showing the current action and status of the upgrade process. After the SQL Server 2008 R2 upgrade operation is finished, the Complete dialog box, shown in Figure 19, will be displayed. At that point, your SQL Server instance will have been upgrade to SQL Server 2008 R2 and you can begin using the SQL Server instance.

Figure 19: Complete dialog box

Verifying the Installation

You can verify the installation of SQL Server 2008 R2 by checking to see if the necessary services are running. You can see these services using the Start, Administrative Tools, Services menu option. Table 1 lists the services used by SQL Server 2008 R2.

Table 1: SQL Server 2008 R2 Services