Destination: SQL Server 2008 or SQL Server 2008 R2
Now that you have become familiar with how to use the helpful Upgrade Advisor, you’re ready to begin your extensive pre-upgrade testing phase. After you resolve all the issues you can, it’s time to take the next step: install SQL Server 2008 (in your test and development environments first, of course).
Two different paths lead from SQL Server 2000 or 2005 to SQL Server2008:
The same upgrade paths exist for upgrading from SQL Server 2008 to SQL Server 2008 R2. The path you choose depends primarily on two factors: your comfort level with the new platform and the scope of
If the server environment where your current SQL Server installation resides is not a supported platform for performing an in-place upgrade, a side-by-side migration may be your only option. For example, if you are upgrading from SQL Server 7 or running in a Windows 2000 server environment, an in-place upgrade is not supported. For a list of supported in-place upgrade paths, see the “Upgrading In-Place” section, later in this website.
SQL Server 2008 can coexist without a problem on the same servers as any existing SQL Server 2000 or 2005 instances. SQL Server 2008 R2 or later version can coexist on the same servers as any existing SQL Server 2000, 2005, or 2008 instances. This means you can install one or more instances of SQL Server 2008 or 2008 R2 without performing an in-place upgrade of any pre-2008 instances. You don’t have to worry about whether you’re breaking existing functionality. Side-by-side migration is therefore an easy option to investigate. If you are doing a side-by-side installation, be sure your server has sufficient resources (CPU, memory, disk space) to support running multiple instances of SQL Server. Many administrators favor the side-by-side approach to upgrading because it gives everyone on the development team (including eager software folks) a chance to get comfortable with the new features in the new SQL Server release before committing to it in production environments.
In addition, it is far easier to roll back to your previous-version SQL Server components because installing side by side leaves them intact (unlike upgrading in-place, which replaces them). When you are reasonably comfortable with the new SQL Server release, you can go forward confidently in migrating all your objects (presuming that, if you’re leaving previous versions intact, you’re also ready to perform necessary tasks, such as changing connection strings, server aliases, and so on).
Avoiding an Unintentional In-Place Upgrade During Setup
If you do intend to go ahead with a side-by-side installation, there’s a small gotcha you need to watch out for when installing a new instance of SQL Server 2008. When you run the Setup program, the Instance Name screen is somewhat lengthy in its header’s verbiage, and if you don’t take the time to read it closely, you might unintentionally upgrade all your components. This is the lowdown:
Database Compatibility Level Migrating pre-2008 databases into SQL Server 2008 brings up the question of compatibility issues and database compatibility levels. The compatibility level is a per-database setting that controls T-SQL execution behavior with regard to SQL Server’s versioning system.
The T-SQL execution engine is flexible insofar as it has the capacity to switch between varying, version-dependent behaviors according to the current compatibility-level setting. When a database is upgraded to SQL Server 2008 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 80 (SQL Server 2000). Upgrading a database with a compatibility level below 80 sets the database to compatibility level 80. Compatibility level affects behaviors only for the specified database, not for the entire server. An important point to understand about database compatibility levels, however, is that the database compatibility-level setting is intended to provide only partial backward compatibility with earlier versions of SQL Server. It does not prevent the use of new T-SQL features available in SQL Server 2008 such as new data types and statements.
The compatibility-level setting is provided primarily as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. Essentially, it allows T-SQL code that may be using deprecated features or expects pre-100 level behaviors for certain commands to continue operating as it did in the prior version of SQL Server. Using the compatibility-level setting should not be viewed as a permanent solution. It should be used only until the T-SQL code affected by behavioral differences in SQL Server 2008 can be converted to work properly in SQL Server 2008. Then you can use ALTER DATABASE to change the compatibility level to 100. You can find a full list of the behavioral differences between the compatibility-level settings in the Books Online article associated with the “ALTER DATABASE Compatibility Level” topic.
This option can be used to set the compatibility level for a particular database. To view the current compatibility level of a database, query the compatibility_level column in the
Now that you’ve seen how to migrate your databases to SQL Server 2008 by following the side-by-side migration path above, let’s look at the alternative: upgrading in-place. Unlike a sideby-side install, an in-place upgrade permanently modifies the SQL Server components, data, and metadata objects, and there is no going back. You will likely be more comfortable taking the side-by-side migration path than doing an in-place upgrade, unless a side by-side migration is not possible because of disk space limitations, you have very few SQL Server features in use, or you are fairly confident about the potential success of the upgrade process because you’ve done extensive issue resolution with the assistance of the Upgrade Assistant.
If you are performing an in-place upgrade of the Database Engine, it is strongly recommended that you first do the following:
After you perform all these actions, you are ready to begin the upgrade process.
Upgrading the Database Engine
You perform an in-place upgrade by running the SQL Server Installation Center. On the Installation page, you can invoke the Upgrade Wizard to upgrade from SQL Server 2000, 2005, or 2008 or later version (see the pictures below). After first running the Setup Rules check and installing the Setup Support Files, the Upgrade Wizard essentially runs the installation process. (The installation process and all its screens are described in another Chapter under the heading, “Install Screens, Step by Step.”)
The key differences between running a new install versus an upgrade is that during the upgrade process, you choose an existing default or named instance on the Select Instance screen (see picture below as well in 2012
To add features, you need to run the Installation Center again after the upgrade operation is complete. After making choices on the Features Selection page, step through the Instance Configuration, Disk Space Requirements, and Server Configuration screens, making changes as necessary. For example, authentication and login information are carried forward from the previous instance of SQL Server. You can assign the same login account
You can also specify whether services start automatically, are started manually, or are disabled. Next, you are presented with options for upgrading your full-text catalogs. In SQL Server 2005 and earlier versions, each full-text index resided in a full-text catalog that belonged to its own
Package screen, which allows you to run the transfer immediately or schedule it to run at a specific time. You are also given an opportunity to specify an SSIS proxy account to use to run the transfer (you should make sure it’s an account that has appropriate permissions on both the source and destination servers to ensure a successful transfer). After you make your scheduling choices, click Next to display the Complete the Wizard screen (see the below picture). Here, you have an opportunity to review the choices you’ve made on the prior screens. If everything looks okay, click Finish to complete the wizard and start or schedule the Copy Database package.
Now it’s time for the most important task: migrating your databases to SQL Server 2008. One method of migrating to SQL Server 2008 or 2008 R2 is by backing up your SQL Server 2000 and 2005 databases and restoring them to SQL Server 2008. Another method is to attach or restore a database from a prior version of SQL Server to SQL Server 2008. When you migrate using either of these methods, the database is upgraded automatically during the attach/restore process.
Database backups created by using SQL Server 7.0 or earlier are in an incompatible format and cannot be restored in SQL Server 2008 or 2008 R2. For information on how to migrate a database from SQL Server 6.5 or 7.0 to SQL Server 2008, see the section “Upgrading from SQL Server 7 or SQL Server 6.5,” later on this website.
When you use backup and restore to copy a database to another instance of SQL Server, the source and destination computers can be any platform on which SQL Server runs. The general steps to upgrade using backup and restore are as follows:
When restoring the database, you might need to use the MOVE option to relocate the database files because SQL Server 2008 and SQL Server 2008 R2 or later version use a different default path than earlier versions. For more information on using backup and restore, see Chapter saying, “Database Backup and Restore.” In SQL Server 2008 R2, you can also use the detach and attach operations to migrate a user database from SQL Server 2000 or SQL Server 2005. After you attach a SQL Server 2005 or SQL Server 2000 or SQL Server 2008 database to SQL Server 2008 R2, the database is upgraded automatically and becomes available immediately.
For more information on the syntax and options for detaching and attaching databases, see Chapter saying, “Creating and Managing Databases.” Another method of migrating an existing database is by using the SQL Server Copy Database Wizard to copy databases between multiple instances of SQL Server.
Before you use any of the methods described here, Microsoft recommends you run the appropriate DBCC consistency checks to make sure there is no data corruption within the databases to be migrated.
Using the Copy Database Wizard
Using the Copy Database Wizard is probably the easiest approach to use to migrate your databases to SQL Server 2008 or 2008 R2. To run the Copy Database Wizard, using SQL Server Management Studio (SSMS), connect the Object Explorer to your previous SQL Server version’s instance. Expand Databases and then select and right-click the database you want to copy (or move) into SQL Server 2008. Then select Tasks, Copy Database.
When the wizard’s initial Welcome page is displayed, click Next and then select your source server (the 2000 or 2005 instance). Click Next again and select your destination server (your newly installed SQL Server 2008 or SQL Server 2008 R2 instance). Click Next again to bring up the Select the Transfer Method screen. This screen provides two options for copying or moving your databases to SQL Server 2008:
When you use the detach and attach method, SSIS uses the service account of SQL Server Agent that is running on the 2008 (destination) or later version instance. This account must be able to access the file systems of both servers; otherwise, the wizard will fail.
Select the option that works best for you and then click Next. The Select Databases screen appears, and, as the below picture shows, you should check the Copy (not Move) check boxes for the databases you want to migrate.
full-text catalog, a new filegroup is still created on the same disk to maintain the preupgrade disk I/O behavior. If the old full-text catalog path is invalid, though, the upgrade places the full-text index in the same filegroup as the base table or in the primary filegroup if the table is partitioned.
Three options are available for upgrading your existing full-text catalogs:
After choosing your full-text upgrade option, you next choose your Error Reporting options, and then the Upgrade Rules check is run to validate your system configuration with the options and features chosen during the upgrade process. If all the rules pass, you can review the upgrade operation on the Ready to Upgrade page, which also displays the path to the upgrade configuration file (this is useful for setting up and performing unattended upgrades from the command line, as discussed later in this chapter). If everything looks okay, click Upgrade to begin the upgrade process. The upgrade process automatically upgrades all objects that are common to all databases, including the following:
You can monitor the upgrade progress on the Upgrade Progress screen. Depending on your hardware configuration and the features to be upgraded, the upgrade operation can take from approximately 30 minutes to several hours. The databases on the instance being upgraded remain unavailable until the upgrade is complete.
When the upgrade finishes, it displays the upgrade status of each component and also provides the location of the upgrade log. A system restart may be required in some cases if any upgraded components were in use during the upgrade process.
When your upgrade of the Database Engine is complete, it is recommended that you perform the following on all databases (also recommended for side-by-side migration):
One caution you have to take, After a pre-2008 database is upgraded (in case you choose the Move Database option or you perform an attach or restore and delete the original), it cannot be downgraded back to its former version—not even if you attempt to detach/attach or restore it to SQL 2000 or 2005. Thus, it is especially important to create full backup copies of all your databases before you upgrade. It’s also a good idea to back up the entire Program Files/Microsoft SQL Server directory tree.
After you make your database selections, click Next, and the Configure Destination Database screen appears for each database you selected in the previous step. This screen allows you to rename the database on the destination server if you so desire (see picture below). It also provides options to overwrite any existing databases or MDF (data) and LDF (log) files on the destination server or to create new ones in the folders of your choice. Make your selections and click Next.
The Select Database Objects screen that appears next (see below picture) provides some real power because it allows the server-wide objects (those stored in the system databases and source database) to be imported. These objects include stored procedures residing in master, SQL Server Agent jobs, custom user-defined error messages, SSIS packages, and SQL Server logins.
You need to click the ellipsis button to choose the specific ones you want to import (rather than choosing them all, which is the default). When you’re finished selecting the objects you want brought over, click Next again. The Configure the Package screen that appears next provides the opportunity to name and save the SSIS package created for migrating the database, and to specify how you want to log the messages generated during the transfer. Click Next to present the Schedule the
"An education equips a mind. A degree demonstrates its potential."
- IGNATIUS J. REILLY
"SQL DBA School presented me with all of the opportunities I needed to succeed."
- GEORGE BOWLING
"The sense of accomplishment I felt after completing my SQL DBA School courses was great!"
- TIM DOYLE
SQL DBA School is strongly committed to providing complete practical training exclusively on Microsoft SQL Server and Datawarehousing technologies. SQL DBA School Training Institute established in February 2013 is now one of the best training institute offering SQL Server and T-SQL (SQL Server) Training, SQL Database Administration (SQL DBA) Training and Business Intelligence (MSBI / SQL BI) Training.
We have been working with numerous consultancies in USA & UK. We undertake training on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 versions. We make sure that all our sessions are very much interactive and well structured with doubts clarifications during and after each course.