This chapter is from the book
This chapter is from the book
This chapter is from the book
Upgrading to SQL Server 2005
SQL Server 2005 allows you to upgrade from SQL Server 7.0 Service Pack 4 and SQL Server 2000 Service Pack 3 and above. Direct upgrade from SQL Server 6.5 is not supported; you need to first upgrade from SQL Server 6.5 to SQL Server 7.0 Service Pack 4 and then use the SQL Server 2005 setup to upgrade to SQL Server 2005.
Upgrading requires careful planning and execution. You have to consider upgrading your data, Data Transformation Services (DTS) packages, agent jobs, logins, replication setup, failover clustering setup, Analysis Services objects, Full-Text Search objects, Reporting Services configuration, Notification Services configuration, and so on. To help you with the upgrade process, SQL Server 2005 provides a tool called Upgrade Advisor that analyzes the system and installed SQL Server 2000 or SQL Server 7.0 components and then generates a report that identifies issues you must or should address before or after upgrading to SQL Server 2005.
SQL Server 2005 setup has been enhanced to simplify the upgrade process and to reduce the server downtime during the upgrade. Before upgrading the database engine, you need to make sure to do the following:
Review hardware and software requirements for installing SQL Server 2005.
Review SQL Server 2005 backward compatibility notes and make appropriate changes to your application and scripts.
Run the Upgrade Advisor tool to prepare for upgrades and resolve any upgrade blocking issues.
Back up all SQL Server database files from the instance to be upgraded, so you can completely restore them, if necessary.
Run the appropriate DBCC commands on databases to be upgraded to ensure that they are in a consistent state.
Ensure that existing SQL Server system databases—master, model, msdb, and tempdb—are configured with the autogrow setting and ensure that they have adequate hard disk space.
Disable all startup stored procedures because the upgrade process will stop and start services on the SQL Server instance being upgraded. Stored procedures processed at startup time may block the upgrade process.
Stop replication and make sure that the replication log is empty.
After you have gone through this pre-upgrade checklist, you can run the setup and install an instance of SQL Server 2005. When SQL Server 2005 is installed, you can move user databases to a SQL Server 2005 instance by using the backup and restore or the detach and attach functionalities in SQL Server 2005. You can then register your server, repopulate any full-text catalogs, update the statistics, and run the Surface Area Configuration tool to reduce the attackable surface.
Before we look at Upgrade Advisor, let’s review some changes in SQL Server 2005 that you should be aware of to better prepare for the upgrade process.
Deprecated and Discontinued Database Engine Functionality
Deprecated features in SQL Server 2005 include features that will be removed in the future product releases. Some of the deprecated features will be removed in the next product release, while others will be removed in the releases following that one. The following are some of the deprecated features, which you should try to replace with the recommended items:
The DUMP and LOAD statements are being deprecated, and the recommended approach is to instead use the BACKUP and RESTORE statements, respectively.
The SETUSER statement is being deprecated, and the recommended approach is to instead use the EXECUTE AS statement.
sp_addtype is being deprecated; instead, you should use CREATE TYPE. You should also avoid using the sp_attach_db and sp_attach_single_file_db stored procedures; instead, you should use the CREATE DATABASE statement with the FOR ATTACH or FOR ATTACH_REBUILD_LOG option.
You should avoid using sp_renamedb, sp_dboption, and sp_resetstatus stored procedures; instead, you should use the ALTER DATABASE statement.
You should avoid full-text search stored procedures, such as sp_fulltext_catalog; instead, you should use the CREATE/ALTER/DROP FULLTEXT CATALOG and CREATE/ALTER/DROP FULLTEXT INDEX statements.
You should avoid using security stored procedures such as sp_addlogin, sp_adduser, and so on; instead, you should use statements such as CREATE LOGIN, CREATE USER, CREATE ROLE, and so on.
System tables are being replaced with backward compatibility views. The recommended approach is to use the new system catalog views. Direct updates to system tables are no longer allowed.
SQL Server 2005 does not support some of the features present in the earlier releases:
SQL Server 2005 no longer includes English Query.
Tools such as isql.exe and rebuildm.exe are also not included; you should instead use sqlcmd.exe and setup.exe. If you need to rebuild the master database or rebuild the registry, you should run SQL Server 2005’s setup.exe from the command prompt and provide the appropriate parameters.
The Northwind and Pubs sample databases are being replaced with a new sample database, AdventureWorks.
The allow updates option of sp_configure is present but because direct updates to system tables are not supported, this option is meaningless.
Meta Data Services 3.0 is a component of Microsoft SQL Server 2000 that is no longer available in SQL Server 2005. Upgrading does not delete the existing repository tables in the msdb database. However, the repository engine that reads and updates the tables will not be available after you upgrade, and the repository tables will no longer be accessible. To remove the unused tables, you can manually delete them. Repository tables have the Rtbl prefix.
Breaking and Behavior Changes to the Database Engine
Wherever possible, SQL Server 2005 provides backward compatibility so that applications can be easily migrated to SQL Server 2005. However, in order to better support some new features and to better position the product, Microsoft has introduced some breaking and behavioral changes in SQL Server 2005 that might require changes to your applications:
SQL Server 2005 adds trigger_schema as the last column in the result set returned by the sp_helptrigger system stored procedure.
In SQL Server 2000, the WITH keyword to specify table hints is optional. However, in SQL Server 2005, WITH is mandatory for table hints, except for single-word hints. For instance, the query SELECT * FROM master.dbo.sysobjects (TABLOCK, HOLDLOCK) works in SQL Server 2000 but not in SQL Server 2005. To make this query work in SQL Server 2005, you need to either specify the WITH clause or just keep a single table hint.
In SQL Server 2000, column names in the ORDER BY clause are resolved to columns listed in the SELECT list, regardless of whether they are qualified. However, in SQL Server 2005, qualified column names and aliases in the ORDER BY clause are resolved to columns of tables listed in the FROM clause. Consider the following Transact-SQL script:
USE [Tempdb]; GO IF OBJECT_ID(‘dbo.dblTest’) IS NOT NULL DROP TABLE dbo.dblTest; GO CREATE TABLE dbo.dblTest (col1 int, col2 varchar(20)); GO INSERT INTO dbo.dblTest SELECT 1, ‘X’; INSERT INTO dbo.dblTest SELECT 2, ‘U’; INSERT INTO dbo.dblTest SELECT 3, ‘A’; GO SELECT col1 AS ‘col2’ FROM dbo.dblTest a ORDER BY a.col2; GO
If you run this script in SQL Server 2000 and SQL Server 2005, you will get different output with SQL Server 2000 than with SQL Server 2005. SQL Server 2000 ignores the table alias and maps the a.col2 specified in the ORDER BY clause to the column alias in the SELECT list and hence returns 1, 2, and 3. On the other hand, SQL Server 2005 honors the table alias and sorts the results on col2 in the dbo.tblTest table and hence returns 3, 2, and 1.
SQL Server 2005 Upgrade Advisor
As mentioned earlier, you can use the Upgrade Advisor tool to analyze the system and installed SQL Server 2000 or SQL Server 7.0 components and identify issues that you must or should address before or after upgrading to SQL Server 2005. The Upgrade Advisor tool consists of two components:
Analysis Wizard—Analysis Wizard analyzes a system and saves the results into an XML file. Analysis Wizard does not modify your data or change settings on your computer.
Report Viewer—Report Viewer reads the results XML file generated by Analysis Wizard and displays the results of the analysis. It also allows you to sort and filter the results. Each upgrade issue listed on the Report Viewer screen contains a Tell Me More About This Issue and How to Resolve It link that you can click to obtain more information about the upgrade issue and the steps you need to take to resolve it.
The Upgrade Advisor documentation consists of a section on resolving upgrade issues, with categories such as Analysis Services Upgrade Issues, DTS Upgrade Issues, and Database Engine Upgrade Issues.
Note that the Upgrade Advisor tool is not installed as part of SQL Server 2005 setup. You have to separately install it from the setup splash screen (splash.hta) by clicking the Install SQL Server Upgrade Advisor link. Figure 3.4 shows the Microsoft SQL Server Upgrade Advisor tool in the background, with Analysis Wizard in the foreground.
)
Figure 3.4 The Upgrade Advisor tool is designed to analyze the existing SQL Server environment and identify and resolve any issues that might block the upgrade process.