SQL Server 2005 - Upgrading from earlier versions of SQL Server

SQL Server 2005 - Upgrading from earlier versions of SQL Server

In this tutorial you will learn about SQL Server 2005 - Upgrading from earlier versions of SQL Server. SQL Server 2005 permits the upgrading from version 7.0, 2000 (with service pack 3) and 2003. SQL Server 6.5 will have to be upgraded to SQL Server 7.0 with service pack 7.0 and then to SQL Server 2005.

The process of upgrading to these various versions requires a lot of careful planning. Data, Data Transformation Services packages, agent jobs, replication setup, failover clustering setup, Analysis Services objects, Full text search objects, Reporting Services configuration, Notification Services configuration etc will have to be upgraded. The Upgrade Advisor helps the user analyze the system and generates a report identifying issues that needs to be addressed pre and post upgrading.

Planning for an upgrade

Before beginning an upgrade check whether the required hardware and software are available. Read the backward compatibility notes and make the necessary changes to the application and scripts. The Upgrade advisor tool will have to be run and all issues highlighted for resolution will have to be addressed. All SQL Server database files will have to be backed up. Appropriate DBCC commands on databases to be upgraded will have to be run to ensure consistent state. SQL Server System databases will have to be configured with autogrow setting to ensure that they will have adequate disk space. All startup procedures will have to be disabled; else they will block the process of upgrading. Replication log should be emptied and Replication will have to be stopped.

Once all the above has been done, SQL Server 2005 setup has to be run and the instance installed. Thereafter, move user databases to the instance by using backup and restore or detach and attach functionalities in SQL Server 2005. Then register the server, repopulate full text catalogs, update the statistics and run Surface Area Configuration tool.

Another factor that needs to be taken into account while preparing for an upgrade, are the features that have been deprecated in SQL Server 2005. The features that are going to be deprecated are as under:

1. The DUMP and LOAD statements.
2. The SETUSER statement
3. sp_addtype.
4. sp_attach, Sp_attach_single_file_db, sp_rename, sp_dboption and sp_resetstatus.
5. sp_fulltext_catalog, sp_addlogin, sp_adduser.
6. System table are being replaced with backward compatibility views.

Some of the features of earlier releases are no longer supported. The features that are not being supported are as under:

1. English Query
2. isql.exe and rbuildm.exe
3. Northwind database
4. allow updates option of sp_configure.
5. Metadata component of SQL Server 2000

A number of breaking and behavior changes to the Database engine to support the new features that have been introduced in SQL Server 2005. The trigger_schema is added as the last column in the result set returned by the sp_helptrigger system stored procedure. WITH has been made mandatory for table hints. Qualified column names and aliases in the ORDER BY clause are resolved to columns of tables listed in the FROM clause unlike in SQL Server 2000.

SQL Server 2005 Upgrade Advisor

The Upgrade Advisor tool is used to analyze the system and installed SQL Server instance and identify issues that should be addressed before or after upgrading to SQL Server 2005. The Upgrade advisor has two components—the Analysis Wizard and the Report viewer. The former analyzes the system and saves the results into an XML file. The Report viewer reads the results and displays it. It allows sort and filter operations to be performed on the results. Each item in the Report viewer has a Tell Me More About This Issue and How to Resolve it links. The Upgrade Advisor documentation also provides information on various upgrade issues and steps required to resolve them.

The upgrade Advisor does not get installed automatically when SQL Server is installed. It has to be installed separately.

The process of upgrading each of the components is beyond the scope of this tutorial and will require a separate chapter for each process. Therefore, this section has been confined to a definition of the common issues relating to upgrading and touched upon the new features of SQL Server 2005 and the deprecated features have been listed to make the user aware of them. It has also briefly examined the features of the Upgrade Advisor.