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:

  • You can upgrade your existing SQL Server 2000 SP4 or later and SQL Server 2005 SP2 or later instances in-place, using the SQL Server Installer.
  • You can install SQL Server 2008 side by side with your current SQL Server instances and then migrate your data and other content to SQL Server 2008. 


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 feature use in your current environment. When you have become familiar with what it takes to travel either path, you’ll find it much easier to make your decision. The first approach we explore in this chapter is feature use in your current environment. When you have become familiar with what it takes to travel either path, you’ll find it much easier to make your decision The first approach we explore in this chapter  is the more conservative sideby-side migration path.


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.


Side-by-Side Migration

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:

  • If you choose the Default Instance radio button and you already have a SQL Server default instance, that default instance is upgraded.
  •  If you the choose the Named Instance radio button, you need to make sure to enter a name that you know is not in use as an instance name; otherwise, the existing named instance is upgraded. Below picture shows an example of how to make the right choice and use an instance name, SQL2008R2, that makes it abundantly clear you are installing a new instance.

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 sys.databases catalog view: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 sys.databases catalog view:


select compatibility_level

fromsys.databases

where name = db_name()

go
compatibility_level

------------------

90


Upgrading In-Place

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:


  • Create full, verified backups of your existing SQL Server databases.
  • Run the appropriate DBCC consistency checks (for example, DBCC CHECKDB and DBCC CHECKFILEGROUP).
  • Make sure the system databases on your pre-2008 instances (for example, master, msdb, tempdb, and model) are all set to auto-grow.
  • Disable any startup stored procedures that get kicked off when the SQL Server service starts.
  • Disable database replication and empty the replication log.


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 sql version, most features are the same with 2008). After selecting the instance to upgrade, you see the Feature Selection page. The features to be upgraded are preselected. You cannot change the features to be upgraded, and you cannot add features during an upgrade operation.


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 to all SQL Server services, or you can configure each service account individually.


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 filegroup and was treated as a database file. In SQL Server 2008, a full-text catalog is a logical concept that refers to a group of full-text indexes and is no longer treated as a separate database file with a physical path. However, during upgrade of any

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.

Migrating Databases

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: 


  1. Back up the source database that resides on an instance of SQL Server 2000, SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2.
  2.  Restore the backup of the source database on the destination SQL Server. Restoring the database automatically creates all the database files and upgrades the database. 


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: 


  • Detach and Attach—This option is the same as the detach/attach method described previously. It’s fast, but it takes the database offline during the migration process.
  • Use the SQL Server Management Objects (SMO) to Import the Database—This option is slower, but it keeps the source database online during the process.


 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.

Upgrading SQL Server SQL Server continued

 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:

  • Import—Typically, import is the fastest method of upgrading, but an imported full text catalog does not use the new and enhanced word breakers introduced in SQL Server 2008, so you might want to rebuild your full-text catalogs eventually if not during the upgrade.
  • Rebuild—This method uses the new SQL Server 2008 word breakers, but rebuilding indexes can take awhile.
  • Reset—When you use this method, SQL Server 2005 full-text catalog files are removed, but the metadata for full-text catalogs and full-text indexes is retained. The catalog remains empty until you manually issue a full population after the upgrade completes.


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:

  • Tables, views, indexes, and constraints
  • Stored procedures, functions, and triggers
  • User-defined types, rules, and defaults
  • Logins, users, and permissions
  • Database diagrams

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):

  • Repopulate your full-text catalogs if you chose not to rebuild them during the upgrade.
  • Run the sp_updatestats system stored procedure to update statistics.
  •  Reregister your server in SSMS.


 

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
 

Online learning

​fREE

SQL DBA SCHOOL

Founder Abubeker Refaw