SQL Server 2005
Set the Stage for a Smooth Upgrade
At a Glance:
- Preparing to upgrade
- Changes to SQL commands
- Security and permissions changes
At last! SQL Server 2005 has finally been released. Around the world, developers and database administrators like you are busy getting a handle on the broad changes and enhancements that are found in this new release. As you begin exploring
the changes, you’ll wonder what you need to do to upgrade your current environment to SQL Server 2005.
In this article, I’ll help you get ready for the switch and present solutions to some of the problems you may face as you upgrade. Of course, any upgrade plan will have a limited scope, and understanding that scope is important for success. Remember that upgrades and migrations are two different processes. An upgrade, in this case, is a move from one release of SQL Server™ to another. Upgrades do not mandate the migration of existing functionality to new functionality (unless the existing functionality is not in the new release), nor does an upgrade encompass the addition of new functionality to the environment. It is tempting to make use of the new functionality in SQL Server 2005, but doing so is probably outside the scope of an upgrade plan and could put the upgrade in jeopardy.
To develop an upgrade plan, you should list the issues you may encounter before, during, and after the upgrade and the ways you will handle those issues. To help you identify potential issues, Microsoft has created the SQL Server 2005 Upgrade Advisor, which you should execute against your databases before attempting an upgrade. To find out more about the SQL Server 2005 Upgrade Advisor, see: Microsoft SQL Server 2005 Upgrade Advisor
There are a few decisions you need to make before upgrading from SQL Server 7.0 or SQL Server 2000. Note that to upgrade from SQL Server 6.5 or older you must first upgrade to SQL Server 7.0 or SQL Server 2000, and then to SQL Server 2005.
Now on to the decisions. First on the list is choosing which edition of SQL Server 2005 will best serve your needs. The decision should be made by your team of database administrators, database developers, and business unit owners upon reviewing the functionality of the various editions. This process should entail an audit of your existing functionality, determination of immediate future functionality needs, and consultation with business units on features needed to support their goals over the next several years. You should match these requirements to the feature set of the correct SQL Server Edition. You can see more about the available editions of SQL Server 2005 at SQL Server 2005 Editions
Next, the plan should provide for known pre-upgrade issues. These will range from adding new columns to a system stored procedure call to adjusting the security on the database. You should specify how each of these issues will be addressed and identify the phase in which the issues will be tackled.
Changes to the base SQL Server database engine require database administrators to audit existing environments to identify any problems. The issues to be identified include those shown in Figure 1.
Figure 1 Red Flags
||If an object has the 0xFFFF Unicode character as part of its name, it will be invalid after the upgrade if the database compatibility level is set to 90.
|Database with ID of 32767
||This database ID is reserved in SQL Server 2005 for the resource database and any current database with that ID number must be detached before an upgrade can be attempted.
|Data Definition Language (DDL) statements
||These cannot be executed on the inserted and deleted tables inside DML triggers. SQL Server 2005 DML triggers must be modified to remove DDL statements before an upgrade can be attempted.
|Duplicate index names
||SQL Server 2005 does not allow duplicate index names on tables or views. Duplicate indexes must be renamed before attempting an upgrade.
|Compressed or READ-ONLY drives
||SQL Server 2005 cannot create or upgrade databases residing on compressed or READ-ONLY drives.
||Set the AUTO_UPDATE_STATISTICS option to ON before attempting an upgrade to SQL Server 2005.
|Master database rebuilds
||Modify scripts used to rebuild master database to utilize the REBUILDDATABASE option of Setup.exe.
|Northwind and Pubs
||No longer installed as sample databases. Database administrators can upgrade the databases for use in SQL Server 2005 if they desire or can modify scripts to use the new AdventureWorks sample database instead.
|Remote setup scripts
||Modify any remote setup scripts including the TARGETCOMPUTER parameter as this parameter is no longer supported.
||Review all uses of trace flags as the behavior of trace flags have changed in SQL Server 2005. Trace flags are no longer restricted to the session that calls them, they can affect all sessions opened after they are set.
||SQL Server 2005 contains many changes to system stored procedures (parameters, values returned, and columns returned), ANSI views (columns), and system tables (columns). Review the latest SQL Server 2005 Books Online articles before attempting a SQL Server upgrade and modify scripts that use the changed objects.
||These have been modified in SQL Server 2005 and scripts containing index view creation syntax may need to be modified before an upgrade.
||Review current backup scripts for use of named pipes as backups to named pipes are no longer valid.
||Check index definitions for references to functions or collations that have undergone changes in SQL Server 2005. These indexes may be disabled during an upgrade to SQL Server 2005 and may require rebuilding after the upgrade completes.
|ED and !! commands
||The OSQL utility no longer supports the ED or !! commands. To use these commands use the SQLCMD utility.
||Itwiz.exe has been replaced with the Database Tuning Advisor utility and references to this tool should be removed or modified.
|The ISQL utility
||Replaced by the SQLCMD utility and all references to ISQL should be removed or modified.
||SQL Mail no longer allows e-mail attachments and now requires either the Outlook XP or Outlook 2003 client.
||No longer supported and has no replacement.
|Metadata Services 3.0
||No longer available.
||The SQL-DMO WMI provider is no longer available.
In addition, note the SQL Server statements and commands that have changed so that any scripts containing these objects can be modified before the upgrade. The changes are shown in Figure 2.
Figure 2 Removed Commands
||No replacement as this is legacy behavior from SQL Server 6.x
||No replacement as this is legacy behavior from SQL Server 6.x
Security and Permissions Changes
SQL Server 2005 provides enhanced security thanks to the addition of new features and the fortification of existing features. The upgrade path for SQL Server security is not as complex a process as you may have thought, and many of the existing SQL Server security configurations will upgrade automatically without intervention on your part.
However, you must still review your databases and applications for the security upgrade issues and account for these issues in your upgrade plan. They include looking for SQL Server fixed server role names that are reserved in SQL Server 2005 and cannot be used for user-defined logins. Any user-defined login using a fixed server role name must be modified before the upgrade process. Also look for duplicate security identifiers (SIDs)—they are not allowed in SQL Server 2005. You must remove one of the logins and its associated users with a duplicate SID before you can upgrade.
Next, be aware that password hashes from SQL Server 6.5 are saved in a format that is not supported in SQL Server 2005. SQL Server logins with SQL Server 6.5 password hashes must have their passwords reset before the upgrade.
Another important change is that the database being upgraded cannot have a user with the name of sys. The sys name is reserved in SQL Server 2005 and any database user with this name must be renamed before the upgrade process.
Case-insensitive password comparisons are no longer supported. Applications attempting these comparisons will need to be modified before the upgrade process to ensure continued functionality.
When it comes to the Bulk Copy Program (BCP), scripts using the BCP utility should be reviewed since the permission requirements for the BCP utility have changed. Users will need to have the ALTER permission in addition to the INSERT and SELECT permissions to insert data into a table while disabling CHECK constraints on the target table, which is the default behavior of BCP.
Other permissions are changing as well. Database administrators can no longer use the ALL permission to grant all object or statement permissions to a user. In addition, this means that scripts assigning permissions to users will need to be reviewed and this grant modified.
Scripts and processes that view system metadata in virtual tables or system objects may need to be modified as access to virtual tables and system metadata is no longer available to guest users or members of the public role.
Execute permission has been strengthened for the sp_addtype system stored procedure and users attempting to execute this stored procedure must be a member of the db_ddladmin or the db_owner fixed database role.
Also, users who are executing the sp_changeobjectowner system stored procedure due to membership of the db_ddladmin or the db_securityadmin fixed database role must also have the CONTROL permission set for the target objects.
Finally, be advised that SQL Server 2005 no longer supports setting remote logins as trusted. Scripts using the sp_remotelogin system stored procedure to mark remote logins as trusted must be modified.
SQL Server maintenance plans will be upgraded during the SQL Server upgrade process but several changes will affect the functionality of the maintenance plan. The first change is that maintenance plans will no longer support log shipping. Log shipping must be configured outside of the maintenance plan. In addition, maintenance plans won’t attempt the repair of minor problems currently configured under the Database Integrity Check task of the maintenance plan wizard. And last but not least, maintenance plan metadata will be migrated to the new catalog views during the upgrade process. Any code that references the old maintenance plan system tables should be modified to reference the new catalog views. (For changes affecting replication, see Figure 3.)
There are a few items that need to be addressed to account for issues that take place during the actual upgrade process. The first concerns available disk space. Additional disk space is required for the system databases in SQL Server 2005 (due to changes in the system database schema), as well as for the PRIMARY filegroup of each user database, and the transaction log files of user databases (due to changes in how the transaction log handles database recovery).
If you plan to use DMO after the upgrade, you must install the component as a legacy component. If you’re upgrading from SQL Server 7.0 to SQL Server 2005, you must manually recreate the proxy accounts for job steps that utilize proxies.
After an upgrade is completed, you will probably have a checklist of action items to address. This checklist should accommodate processes that surround the database: disaster recovery, database maintenance, security, and external data movement. You may need to modify their environment after the upgrade process to account for the changes and default settings listed in the sidebar "Upgrade Gotchas
The behavior of the SQL Server statements and commands in Figure 4 have changed and scripts containing these objects should be modified before an upgrade.
Figure 4 Command Changes
|FOR LOAD option of CREATE DATABASE
||This should be modified to take advantage of the fact that RESTORE operations can create a database
||Use the DROP DATABASE command
||Use the DBCC CHECKALLOC command
||Use the DBCC CHECKDB command
||DBCC CHECKDB now includes DBCC CHECKCATALOG, making a second call to this DBCC command unnecessary in SQL Server 2005 maintenance scripts
||Use the DBCC CHECKTABLE command
As you can tell from this discussion of some of the issues you’ll encounter during a SQL Server 2005 upgrade, you must carefully audit your existing environments and plan for possible upgrade snags. To understand more, execute the SQL Server 2005 Upgrade Advisor and review the Upgrade Advisor’s Books Online as well. Whether you’re a database administrator or a developer, a thorough review of the following SQL Server 2005 Books Online articles will help you to understand many of the changes you’ll face during your upgrade to SQL Server 2005:
Randy Dyessis a mentor with Solid Quality Learning and is the founder of Dyess Consulting Inc. He specializes in SQL Server training and consulting with a focus on performance tuning. He is also the founder and principle author of www.TransactSQL.com.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited