Introduction

It's widely recognized that database sizes are growing significantly, and that the growth is being forced by many factors, such as companies requiring more data to be available online for longer (e.g. to comply with government regulations) or an increasing amount of data being digitized for storage.

SQL Server 2008 has emerged as data platform not only for small and medium scale industries but for large scale industries which stores and manages several terabytes of data efficiently in a variety of different formats including XML, e-mail, time/calendar, file, document, geospatial, and so on. SQL Server 2008 is very much capable in handling data explosion while providing a rich set of services to interact with the data: search, query, data analysis, reporting, data integration, and robust synchronization.

The objective of this article is to lay down a step-by-step guide for a SQL Server 2008 upgrade from SQL Server 2000(SP4) and SQL Server 2005(SP2). In this document, I will talk of Upgrade Advisor tools used for planning the SQL 2008 upgrade, different upgrade processes (In-place upgrade and Side-By-Side), and the rollback strategy.

Why upgrade?

SQL Server 2008 delivers a powerful set of capabilities to solve the growing needs of managing data in the enterprise, on desktops, and on mobile devices, it also builds on the strong momentum in the business intelligence market by providing a scalable infrastructure that enables information technology to drive business intelligence throughout the organization and deliver intelligence where users want it. SQL Server 2008 also delivers improved performance in many areas, including data warehousing, reporting, and analytics. Some of the few compelling features of SQL Server 2008 are as follows:

• T-SQL Enhancements
• Change Data Capture
• Resource Governor
• Integration Services Enhancements
• Reporting Services Enhancements
• Analysis Services Enhancements
• Data and Backup compression
• Policy-Based Management Framework
• Transparent Data Encryption
• External/Extensible Key Management
• SQL Server Audit
• Database Mirroring Enhancements
• Performance Data Collection

For details on these features refer to the links below:

• https://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx
• http://technet.microsoft.com/en-us/magazine/cc434690.aspx

Note: I am writing separate articles on these above features, where I will talk more/granular details on it.

Methods to Upgrade

There are two methods to upgrade from SQL Server 2000(SP4)/ SQL Server 2005(SP2) to SQL Server 2008.

• In-place Upgrade – Organizations that do not have resources available to host multiple database environments commonly use an in-place upgrade. An in-place upgrade overwrites a previous installation of SQL Server 2000 or 2005 with an installation of SQL Server 2008. In other words, SQL Server 2008 gives us ability to automatically upgrade an instance of SQL Server 2000 or 2005 to SQL Server 2008. The reason it is called in-place upgrade is because a target instance of SQL Server 2000 or 2005 is actually replaced with a SQL Server 2008 instance. We do not have to worry about coping data from the old instance to new instance as the old data files are automatically converted to new format. This upgrade method is the easiest way to upgrade the database to newer version.

  It's because the files are automatically upgraded without any manual intervention. The number of instances and server involved in this type of upgrade is always one. This upgrade method cannot be used if we want to upgrade only a single database.

  Note: Database backups required

  Before we perform an in-place upgrade, we must back up all SQL Server databases and other objects associated with our previous SQL Server instances. In addition, we should be aware that the previous version of SQL Server Books Online will remain intact on the machine after the upgrade.

• Side by Side Upgrade – Database environments that have additional server resources can perform a side-by-side migration of their SQL Server 2000 or 2005 installations to SQL Server 2008. In this upgrade method, a new instance is created on the same server or in a new server. In this upgrade method the old database instance runs in parallel to the old legacy database. So as the old instance is untouched during this type of upgrade, the old legacy database is still available and online for the application. Having the old environment still active during the upgrade process allows for the continuous operation of the original database environment while we can install and test the upgraded environment. Side-by-side migrations can often minimize the amount of downtime for the SQL Server.

  A side-by-side migration does not overwrite the SQL Server files on our current installation, nor does it move the databases to new SQL Server 2008 installation. We/DBAs need to manually move databases to the new SQL Server 2008 installation and other supporting objects (Jobs, DTS/SSIS packages etc.) after a side-by-side installation by using one of the upgrade methods discussed below.

  • Detach/Attach – When disk storage and source database availability are not a consideration, one common method of moving a database from one server to another is to detach and then attach the database. This process requires that users not be accessing the database, but it has the safety advantage that if an unforeseen problem arises, the DBA can always reattach a copy of the database file to the original SQL Server instance. We can move a large database from one instance to another on the same server, which requires less disk space than some other methods because it reduces the number of database files that we must create for the upgrade process. However, this method can be unsafe because it doesn't follow the recommendation of creating a copy of the database files.
  • Backup/Restore – To avoid possible loss of the database files, making a database backup to use in the upgrade is a secure alternative method for moving a database from one SQL Server instance to another. This process does not interfere with the continuation of activity on the original database environment, nor does it jeopardize the usefulness of the source database files to the original SQL Server version.

Another advantage of using backup and restore is that the backup files are usually smaller than the original database files because the backup process captures only database data, not reserved, unused database space. The decrease in file size usually makes any file transfer faster than transferring the original database file. However, we must take into account the disk space needed for the original database files, the backup files, and the new database files during the upgrade. We can use SQL LiteSpeed tool to take compressed backup.

• Copy Database Wizard – If we want to automate the task of moving a database from one server to another during the upgrade process, we can use SQL Server's Copy Database Wizard. The Copy Database Wizard gives us a way to move one or more SQL Server 2000 or 2005 databases, with their associated objects, while the source database is either online or available for use or offline to SQL Server 2008. This direct copy makes efficient use of disk space while preserving database uptime.
• Manual Schema Rebuild and Data Export/Import – A method not commonly used for database upgrades is the manual method of scripting out the database, scripting the logins associated with that database, scripting all objects associated with that database, and scripting out any other supporting SQL Server objects associated with that database. After executing the script or scripts in the new instance, then we must manually move the data from the original database to the new database using Transact-SQL scripts, SQL Server Integration Services (SSIS), BCP, or other methods available for moving data from one database to another.

Note: Make a copy of the files

It is a best practice to create a copy of the database file for recovery purposes before attaching it to a new instance. After we attach a database file to SQL Server 2008, we can no longer use that file in an earlier version of SQL Server.

Choosing upgrade method

The two upgrade methods discussed above have several different advantages and disadvantages with it as given below, we have to evaluate it in terms of our requirements and current circumstances before moving for upgrade.

In-place Upgrade Advantages:

• It's easier and faster, especially in small systems.
• It's mostly an automated process.
• The instance will be offline for a minimum amount of time.
• The resulting instance after upgrade will have the same name as the original, as the new setup will replace the older version.
• No additional hardware is required in many cases.

o Disadvantages:

• It's very complex to rollback.
• Not applicable in scenarios where we want to upgrade a part of system like upgrading just one single databases.
• We cannot run an upgrade comparison after doing the upgrade.
• Side by Side Upgrade

o Advantages:

• More control over the upgrade, as we can upgrade the components, which we want to.
• We can keep our application running even when we are installing SQL 2008 as the old instance or server will be available.
• Easy to do a rollback as the original database server is untouched.

In-place Upgrade Disadvantages:

• We might need additional hardware resources in terms of disk space, CPU and RAM.
• Manual intervention is required to migrate databases, Jobs, logins etc.
• There will be change in configuration settings, which are used by the application to connect to the database.
• More time is required while moving VLDB to the new version of database.

Risk and Mitigation plan

We need to define the criteria that determine the success of our database upgrade. The success test might be as simple as manually verifying the existence of an object through the graphical user interface (GUI); or it might be a very complex procedure that executes a set of predetermined queries and scripts to verify that all objects exist, that data has successfully imported, that surrounding database objects such as backup jobs operate normally, and that users have full access to the database. However simple or complex the success criteria, we must define the criteria before upgrading to help ensure a successful upgrade.

To prepare the success criteria, we should review each phase and step of the overall database upgrade plan and ask ourselves several questions. The following questions will help us understand what we need to do to declare that the phase or step is successful:

• How can I measure whether this step is successful?
• How can I test that measurement?
• How can I compare my test results against what they would have been in the old database?

Although creating an upgrade plan reduces the likelihood of problems occurring during the upgrade process, problems do arise that can prevent the upgrade process from completing. Most organizations rely heavily on the data contained in their databases, and having that data unavailable due to an upgrade might cause problems in business operations and even have financial implications. We should create a plan to recover from each phase and step of the upgrade process to help minimize data loss and reduce the time that data might be unavailable. This recovery plan might involve backing up a set of files, creating scripts to move database connections from the upgraded instance back to the original instance, or anything else we feel is necessary to get the old database instance back up and meet business uptime needs while you fix the upgrade problems.

The following tips can help us perform a secure and successful upgrade:

• Create a series of checklists: DBAs and developers should prepare a series of checklists that need to be performed before, during, and after a database upgrade.
• Back up all important files: Back up all SQL Server database files from the instance to be upgraded, as well as any application files, script files, extract files, and so on so that you can completely restore them if necessary.
• Ensure database consistency: Run DBCC CHECKDB on databases to be upgraded to ensure that they are in a consistent state before performing the upgrade.
• Reserve enough disk space:Estimate the disk space required to upgrade SQL Server components, user databases, and any database files that might need to be created during the upgrade process. We might need two to four times the amount of disk space during the upgrade process as we will need after the upgrade is finished.
• Ensure space for system databases: Configure system databases (master, model, msdb, and tempdb) to autogrow during the upgrade process, and make sure that they have enough disk space for this growth.
• Transfer login information: Ensure that all database servers have login information in the master database before upgrading the database. This step is important for restoring a database because system login information resides in the master database and must be re-created in the new instance.
• Disable all startup stored procedures: The upgrade process will usually stop and start services multiple times on the SQL Server instance being upgraded. Stored procedures set to execute on startup might block the upgrade process.
• Stop replication:Stop replication and make sure that the replication log is empty for starting the upgrade process.
• Quit all applications:Certain applications, including all services with SQL Server dependencies, might cause the upgrade process to fail if local applications are connected to the instance being upgraded.
• Register your servers after the upgrade: The upgrade process removes registry settings for the previous SQL Server instance. After upgrading, we must reregister our servers.
• Repopulate full-text catalogs: The upgrade process marks your databases as full-text disabled. Catalogs must be repopulated, but Setup doesn't run this operation automatically because it can be time-consuming. Because this operation enhances the performance of your SQL Server 2008 installation, we should plan to repopulate full-text catalogs at a convenient time after the upgrade.(Full Text Search has been integrated in SQL Server 2008, please refer to the section given above for more details)
• Update statistics:To help optimize query performance, update statistics on all databases following the upgrade.
• Update usage counters:In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, run DBCC UPDATEUSAGEon all databases following the upgrade.

Note:

• Before we perform an in-place upgrade, we must back up all SQL Server databases and other objects associated with our previous SQL Server instances. In addition, we should be aware that the previous version of SQL Server Books Online will remain intact on the machine after the upgrade.
• It is a best practice to create a copy of the database file for recovery purposes before attaching it to a new instance. After we attach a database file to SQL Server 2008, we can no longer use that file in an earlier version of SQL Server.

Things to Do

Prerequisites

• Participants must have real-world SQL Server 2000/2005 administration experience
• Participants must have basic operational familiarity with SQL Server Management Studio and SQL Server Profiler
• Participants must have basic operational familiarity with Windows administration and Networking
• Participants are encouraged to have real-world experience installing and supporting SQL Server applications

Preparing for Upgrade – SSUA

The SQL Server Upgrade Advisor (SSUA) is a tool available to find out database compatibility and blocking issues while doing the migration. Upgrade advisor smoothes the transition from an older version to the newer version by anticipating issues/blocking with legacy databases. The Upgrade Advisor generates reports explaining the upgrade issues and also guidance on how to resolve them.

In addition to analyzing (analysis of both local and remote instances) databases and objects, the Upgrade advisor can also analyze T-SQL queries, by using SQL trace files and BI components excluding Reporting Services (Though I noticed it was not also analyzing Integration Service as well remotely and giving error, “Upgrade Advisor cannot locate the SQL Server 2005 Integration Services files that it requires to analyze packages. The installation of Integration Services on this computer has already been upgraded to SQL Server 2008. To upgrade existing Integration Services packages without analyzing them, use the SSIS Package Upgrade Wizard”, same was working fine on local machine). Reporting Services instances will require the SSUA to be installed on the report server itself. The SSUA utility supports analyzing both SQL Server 2000(SP4) and SQL Server 2005(SP2) installations (which are the supported platforms for upgrade to SQL Server 2008).

The SSUA utility provides both analysis and reports. SSUA reports provide the alerts/warnings that we should consider or take action on prior to upgrading our SQL Server environments to the 2008 platform. A nice feature of the SSUA utility is its ability to provide the user with links directly to its alert documentation.

The output is a report that tells us how severe the problem is and when it should be fixed. It can be run from any client with .NET 2.0 framework installed and does not affect the targeted databases when it is run. It can take a while to run as it has to check each object in the database so the more there are the longer we have to wait.