Backward Compatibility | Professional SQL Server 2005 Administration (Wrox Professional Guides)

This section of the chapter covers major changes in the product that are classified in one of three categories. These behavior changes are categorized as unsupported, discontinued, or affecting the way SQL Server 2000 behaves today. Although, the Upgrade Advisor tool will highlight these conditions if they are relevant to your environment, you should read this section to familiarize yourself with these changes.

Unsupported and Discontinued Features

From time to time, to move a technology forward, choices have to be made. For this release of SQL Server 2005, the following features are no longer available.

English Query is no longer included, so don't look for it on your setup CD.
isql.exe and rbuildm.exe are no longer present. You will have to use sqlcmd.exe and setup.exe instead if you need to rebuild the master database or rebuild the registry.
The Northwind and Pubs databases have been replaced by the AdventureWorks and AdventureWorksDW databases. These new sample databases are intended to be more like reallife databases. If you really want to get your hands on Northwind or Pubs databases, they are available on the Microsoft download site.
Allow updates option of sp_configure is still present but not functional.
Metadata component of SQL Server 2000 DTS repository is deprecated as a feature. As part of the upgrade, the DTS packages will be migrated to the filesystem, and the specific tables will be present in msdb after upgrade.

SQL Server 2005 Deprecated Database Features

These features are no longer available as of the SQL Server 2005 release or the next scheduled release of the product. The following are some of the features scheduled for deprecation; it is recommended that you try to change these features over time with the recommended items:

Use Backup Database and Restore Database instead of the Dump Database or Dump Transaction and LOAD Database or Load Transaction (Load Headeronly) statements. These statements are available today for backward compatibility only.

In the area of security, use "Execute As" instead of SETUSER in statements. SETUSER statements are available today for backward compatibility. The following stored procedures are also scheduled for deprecation: sp_addalias, sp_dropalias, sp_addgroup, sp_changegroup, sp_dropgroup, sp_helpgroup. It is recommended that you familiarize yourself with the new security model and leverage role-based security.

For a very long time, Microsoft has told us not to use system tables, as they are subject to change. With this release, most known (documented or undocumented) system tables have been omitted. References to a few popular system tables have been preserved through the use of views such as sysusers (sys.sysusers). The remainder of them, including syslocks, have been discontinued. Therefore, this instruction:

 Select * from syslocks

Will return the following results:

 Msg 208, Level16, Stat 1, Line 1 Invalid object name 'syslocks'. Refer to books online section "Mapping SQL Server 2000 System Tables to SQL Server 2005 system views" for list of discontinued system table and mapping to system views.

Other SQL 2005 impacting Behavior Changes

The behavior changes in these features could adversely affect SQL Server migration to SQL Server 2005.

Ensure that you have set AUTO_UPDATE_STATISTICS ON before you upgrade any databases. If not, the database statistics will not be updated as part of the upgrade to SQL Server 2005. This will most likely cause suboptimal query plans.
Unlike the SQL Server 2000 behavior, the max server memory option in SQL Server 2005 will not take advantage of the available memory beyond its limit. If your setting is set too low, you will simply get an "insufficient system memory" error. This will happen despite memory availability beyond max server memory setting.
In SQL Server 2005, the query governor cost limits option works. In SQL Server 2005, setting it to a value other than "0" means that you have enabled a server-wide query timeout value measured in seconds.
SQL Server 2005 no longer supports Banyan VINES, Multiprotocol, AppleTalk, or NWLink IPX/SPX. MDAC versions 2.6 or earlier are not supported, because they do not support named instances.
SQL Server 2005 does not support the creation or upgrade of databases on compressed drives. Once updated, read-only databases or filegroups can be placed on NTFS compressed drives. They must, however, be set to READ_WRITE permission prior to going through the upgrade process.

Note
With the exception of the Model database, after the upgrade process, all databases remain in 8.0 backward-compatibility mode.
Additional space will be required for data and log files. Extra space is required to maintain user objects and permissions. Additional space is required for metadata information for LOB objects on a column basis. If your database also contains full-text catalog, additional space is needed for the full-text document ID map to be stored in the data file.

Note
Set database log and data files to autogrow during the upgrade process.
Before upgrading to SQL Server 2005, use the sp_dropextendedproc and sp_addextended-proc stored procedures to reregister any extended stored procedure that was not registered with full pathname.
It is a good idea to allocate additional space or to have plenty of space for tempdb to grow during the upgrade process. Overall guidance for tempdb is covered in greater detail in Chapter 14.

Note
tempdb is responsible for managing temporary objects, row versioning, and online index rebuilds.
You should disable all trace flags before upgrading to SQL Server 2005. The possibility exists that either the trace-flag functionality will be different in SQL server 2005 or will not exist. After the upgrade process, you should work with PSS to determine which (if any) of your trace flags are still required.

Note
In SQL Server 2005, trace flags are now deterministic and can be set at the local or global level based on additional arguments (default behavior is local).
Migrate to database mail. If you happen to like SQL mail, the good news is that as long as you have Outlook 2002 (or greater) as a mail client, you will be able to upgrade to SQL Server 2005.

Note
SQL Server 2005 database mail does not have a requirement for Outlook mail client.