Introduction

The objective of this white paper is to lay down a step-by-step guide for a SQL Server 2005 upgrade from SQL Server 2000. All the steps, explained in this document, are based on experience with the recent upgrade done for one of our Customer. The database was VLDB (very large database) having size of 470 GB and hosted on an active/passive cluster.

In this document, I will also explain about the tools used for planning the SQL 2005 upgrade, the upgrade process (In-place upgrade), and the rollback strategy. But the most important point is to achieve a smooth and trouble free transition, for which you must devote ample good time in planning and testing of the upgrade.

Why Upgrade to SQL Server 2005

SQL 2005 server contains many new features and improvements as compared to the old version. One or more can be the compelling reason for upgrade. A few of the important features are as below.

1. Online database mirroring
2. Maintaining stronger and more flexible security.
3. Greater manageability for VLDBs.
4. Better development features.
5. Better business development solutions.

You can download complete list of features from this URL: http://download.microsoft.com/download/2/4/5/2456889a-df87-4def-a553-91f15b4e8c00/SQLServer2005_WhyUpgrade_final.doc

Methods to Upgrade

There are two methods to upgrade SQL Server 2000 to SQL Server 2005.

1. In-place upgrade
2. Side by Side Upgrade

In-place upgrade: SQL Server 2005 gives you ability to automatically upgrade an instance of SQL Server 2000 or 7.0 to SQL Server 2005. The reason it is called in-place upgrade is because a target instance of SQL Server 2000 or 7.0 is actually replaced with a SQL Server 2005 instance. You 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 you want to upgrade only a single database.

Side-by-Side Upgrade : 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.

In this upgrade you will have to manually move the data files and other supporting objects (Jobs, DTS packages etc.) to the new instance.

Choosing an Upgrade Method

Lets discuss about the advantages and disadvantages of each method.

In-place upgrade advantages:

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

Disadvantages:

1. It's very complex to rollback.
2. Not applicable in scenarios where you want to upgrade a part of system like upgrading just one single databases.
3. You cannot run an upgrade comparison after doing the upgrade.

Side-by-Side Upgrade advantages

1. More control over the upgrade, as you can upgrade the components, which you want to.
2. You can keep you application running even when you are installing SQL 2005 as the old instance or server will be available.
3. Easy to do a rollback as the original database server is untouched.

Disadvantages:

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

Preparing for Upgrade Upgrade Advisor

Upgrade advisor is a tool available to find out your database compatibility and blocking issues while doing the migration. You can download this tool from this url: http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en

This tool requires .NET framework 2.0. Later in the document we will discuss, how to use this tool and take benefits from it.

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 databases and objects, the Upgrade advisor can also analyze T-SQL queries, by using SQL trace files.

When you start upgrade advisor, you will see the welcome screen below.



Fig 1.1

You can launch upgrade advisor by clicking on link, "Launch Upgrade Advisor Analysis Wizard". On clicking on this link, you will prompt with a window to select components, which can be analyzed by upgrade advisor.



Fig 1.2

You can select the components or you can click on the detect button. The detect functionality will automatically detect and select the components which need to be migrated. You cannot specify an instance name in the server name box. In the case you want to analyze a server with multiple instance, then you have to specify just the name of the server in the server name box.

Once you have chosen the server name, and have selected the components for analysis, you can click on next button to reach connection parameter screen.



Fig 1.3

In this screen, supply the instance name and login credentials. Click next, and the required details are supplied. If the login credentials supplied are correct, you will be re-directed to a new screen as below.

At this point you will be allowed to choose databases, which you want to run against Upgrade Advisor. If you choose to analyze all the databases, wizard will also analyze SQL Server configurations settings. It's always a good practice to choose all the databases in case you are planning to do an in-place upgrade.

You can also run trace files against upgrade advisor. This way you will be able to analyze any adhoc query getting executed from application. The recommended SQL profile template is SQLProfilerTSQL_Replay, as this will have unique number of queries.



Fig 1.4

On clicking next, the Upgrade Advisor will prompt you to analyze components you selected. Below is the screen shot. You can analyze either all the DTS packages on the server or any specific DTS packages saved in a file.



At the end of the wizard, you will have a chance to confirm all the options you have selected.



Once confirmed by clicking on "Run" button, Upgrade Advisor analyzes each selected database and components and when done, it saves the report on the default path of logged in user. The reports are saved in XML format, for each component. You can launch the Report viewer to view the reports generated.

Once done, you can launch the report viewer to check the report. You can also apply filter on the report to check analysis reports for a specific component.



Each report has got its own importance level. Red X symbol means that this error needs to be fixed

before upgrading. A yellow triangle indicates that additional action needs to be taken

once upgrade is done.

The "When to Fix" column indicates when you should address the error. Any error having "Before" as the value in "When to fix" need to be addressed immediately as this error can be blocker for the upgrade.

For example in the previous screen shot, there is an error, which says, "Read-only Database

cannot be upgraded". This is because if the database is set to read only mode, SQL Server setup won't be able to upgrade it by running scripts on it.

You can expand each report as shown above screen shot to check the objects causing the error and how to resolve it. This can be done by clicking on link, "Show Affected objects"

Things To Do

I will break this section into three parts "Before upgrade", "During upgrade" and "After upgrade". Please carefully note all points in each part.

Before Upgrade

1. As discussed above, you should run upgrade advisor for all databases (including system databases) and fix any issue/blocker for upgrade.
2. Take proper down time before starting the upgrade. The outage required would depend upon the size of the databases and the environment in which database servers are hosted. In my case we took 48 hours of downtime.
3. Once all the issues and blockers are solved, and all applications are closed, take complete backup of all the databases (including master, model and msdb).
4. Also take transactional backup for all user databases and the disable all the jobs including jobs for taking full backups, jobs to defrag and other maintenance plans.
5. It is also recommended to take an image of your database server, this can be very handy in case you will have to rollback.
6. in case you are planning to use Reporting Services in the future, be sure to install IIS on all the nodes of SQL Server. For this you will require the Windows OS CD.
7. If the operating system of the server is Windows 2003, make sure that Windows service pack 1 is installed. This needs to be done on all the nodes of cluster in case the database server is on Failover Cluster environment.
8. You can save upgrade time by installing a few prerequisites in the database server. They are .Net 2.0 framework and SQL Native Client. in case you have servers on cluster, install these setups on each node.
9. Get ready with the new SQL 2005 DVD and the edition you want to install. Also download the latest service pack (SQL Server 2005 SP2) and some cumulative hotfixes. Keeping them ready will save time.
10. Make sure that you have enough space in the drive where SQL Server 2000 is installed in case of in-place upgrade. At least 2 GB will be required.
11. Fulltext indexes applied on tables in SQL 2000 are not compatible with SQL Server 2005. Therefore they should be removed from all the database in case there is any. Once they are removed, execute the below script. This will remove any fulltext index left. It is recommended that you execute the script under a login id having system administrator privileges.

       exec sp_configure 'allow updates',1
       reconfigure with override 
        
       update sysobjects set ftcatid = 0 where ftcatid <> 0
       declare @a int
       set @a = 1
       while @a > 0
        begin
         set rowcount 300000
         delete from sysfulltextnotify
         select @a = @@rowcount
        end
       update syscolumns set colstat = colstat & ~16 
       update sysindexes set status = status & ~33554432 
       update sysobjects set status = status & ~200, ftcatid = 0
       exec sp_configure 'allow updates',0
       reconfigure with override 
       

12. Once all the Fulltext indexes are removed from the database(s), detach the user databases, before starting the upgrade. This is a good practice, as you have your database untouched during the setup goes on. in case the upgrade fails because of any error you can reattach them on legacy database and continue the production.

During Upgrade

1. Make sure, all the points are taken care from "Before upgrade section".
2. Start installing by clicking SetUp.exe from SQL Server 2005 DVD.
3. The setup program is designed in such a way that it automatically updates the database binaries. It altogether create a different folder "90" to contain its new binaries. 90 specifies the compatibility of the database. It also upgrades the other SQL Servers on clusters automatically but only the database engine and not other components like reporting services or analysis services. This is very important. Therefore you will have to install other components separately in each node.

   You can see the progress of installation on installation screen. in case installation is done on cluster, setup will populate all the nodes in a drop down list.

4. Once all the nodes are upgraded with the database engine and all components are installed individually on servers, install the latest SQL Server service pack. This need to be done on all the cluster nodes
5. A reboot may be required to complete the installation due to open file locks during the installation process. If the installation log does not contain any 3010 error, then a reboot is not required.

After Upgrade

1. Once installation is successfully done on all the nodes, attached the databases.
2. Change the compatibility of all the databases attached to 90. If this step is not performed, you won't be allowed to use new features from SQL Server 2005. You can use the below script to achieve this:

      EXEC sp_dbcmptlevel @dbname ='<Database Name>', @new_cmptlevel = '90'
      GO
      use [Report]
      GO
      Alter authorization on database:: '<Database Name> to sa
      Alter database [Report] set parameterization forced
      GO
      

3. Recreate all the full text indexes removed from the databases.
4. Update the statistics for all the databases. This step may take time depending on the size of database. It took me 15 hours to update statistics for all the databases.
5. Once it is done, it's very important to test the application with the new database server 2005.

Conclusion

As discussed in the white paper, SQL Server 2005 is complex but can be made quite straight forward by doing lot of testing and study. I will recommend to test multiple times to get confidence and a

fair idea about entire process.