SQL Server 2005

Set the Stage for a Smooth Upgrade

Randy Dyess

At a Glance:

• Preparing to upgrade
• Changes to SQL commands
• Security and permissions changes

SQL Server 2005

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

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

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.

Maintenance Plans

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.)

Figure 3 Discontinued Functions

Affecting all replication:

• Creating push subscriptions without an active connection
• Using file transfer protocol (FTP) to initialize Subscribers running SQL Server 7.0
• Creating subscriptions in Windows Synchronization Manager
• Subscribing to a publication by locating it in Active Directory
• Embedding the Snapshot Agent in applications
• Remote agent activation
• Subscriptions using Microsoft Access (Jet 4.0)

Affecting transactional replication:

• Microsoft Message Queuing (MSMQ) option for queued updating subscriptions no longer available

Affecting merge replication:

• Publishing from SQL Server 2005 Express Edition
• Log shipping will not upgrade and must be rebuilt after the upgrade process is complete

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

Conclusion

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:

• Discontinued Database Engine Functionality in SQL Server 2005
• Breaking Changes to Database Engine Features in SQL Server 2005
• Behavior Changes to Database Engine Features in SQL Server 2005
• Deprecated Database Engine Features in SQL Server 2005

Upgrade Gotchas

Model Databases

• The model database will be set to a database compatibility mode of 90. This may affect the behavior of scripts in or against this database.
• The PAGE_VERIFY database option of the model database will be set to CHECKSUM.

Target Servers

• If upgrading target servers from SQL Server 7.0, you must manually reenlist them with the upgraded master server.
• You must upgrade all target servers (TSX) before you upgrade master servers (MSX).

Scripts and Stored Procedures

• Database administrators must register each extended stored procedure using the full path for the DLL name. Extended stored procedures registered without the full path will not function after upgrading to SQL Server 2005.
• You should review the use of trace flags in administration scripts to determine if the trace flag still exists or if the functionality of the trace flag has not changed in SQL Server 2005.

User Accounts

• After upgrading from SQL Server 2000 to SQL Server 2005, all user proxy accounts that existed before upgrading are changed to the temporary global proxy account UpgradedProxyAccount. The UpgradedProxyAccount is only granted access to those subsystems that were explicitly used, and does not have access to all subsystems after upgrading.
• Administrators must log into SQL Server using Windows Authentication in order to view maintenance plan tasks in SQL Server 2005.

Configuration and Memory

• The max server memory option is a hard limit for the buffer pool size in SQL Server 2005. SQL Server 2005 will no longer allow the buffer pool to exceed this setting even if additional memory is available. Queries will fail with an "insufficient system memory" error if the max server memory value is reached.
• Changes in the query cost modeling may affect the successful execution of queries in SQL Server 2005 if the sp_configure query governor cost limit option has been set in the upgraded installation. Review the value of this option and reset to a higher value or set to 0 to specify no time limit.
• Direct system catalog updates are not supported in SQL Server 2005. Review the allow updates option of sp_configure to determine if direct updates are allowed before upgrading to SQL Server 2005. Scripts updating system tables must be modified to use documented commands instead of direct updates.
• The open objects option of sp_configure has been deactivated in SQL Server 2005. This option is present but it will not function. Review modify scripts utilizing this option before upgrading to SQL Server 2005.
• The set working set size option of sp_configure has been deactivated in SQL Server 2005. This option is present but it will not function. Modify scripts containing this option before upgrading to SQL Server 2005.

SQL Server Agent

• The syntax for calling tokens in the SQL Server Agent job steps has been changed and must be modified after upgrading to SQL Server 2005.
• SQL Server Agent 2005 uses a new format for error messages written to the job step log files; you must modify any custom or third-party applications that parse the new format.
• Scripts using the xp_sqlagent_proxy_account extended stored procedure must be modified to remove references to this extended stored procedure after upgrading to SQL Server 2005.
• The SQL Server Agent is now only available for members of the sysadmin, SQLAgentUserRole, or MaintenanceUserRole roles.
• The SQL Server Agent service account no longer allows SQL Server Authentication.

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.