Upgrading to SQL Server 2005
Every release of SQL Server is filled with data management innovations to meet business demands. SQL Server 2005 is no exception. It provides a highly secure, efficient, and highly available data management platform for relational data as well as data warehousing, analytics, and reporting for business intelligence to meet the needs of today's business applications. If your business is already running on earlier versions of SQL Server, exploiting the new SQL Server 2005 capabilities requires upgrading the existing system to SQL Server 2005. However, you may have concerns about the upgrade. Upgrades can be painful and messy. They can run into problems during the upgrade process and introduce backward compatibility issues. After an upgrade is complete, there is still the risk of existing applications not exhibiting the same behavior, resulting in customer dissatisfaction.
Upgrade issues can be avoided, or at least minimized, with proper education, planning, and testing. A successful upgrade also requires leveraging the right tools to facilitate the process.
In this section, we will briefly discuss upgrade planning, strategies for upgrading, preparing for upgrading by using the Upgrade Advisor tool, and how to actually perform the upgrade.
To ensure a smooth and successful upgrade, you must create an appropriate plan to meet your business requirements. The scope and number of tasks in your upgrade plan depends on the size and complexity of your application, but at a minimum, you need to divide tasks into three stages:
Preparing for a SQL Server 2005 upgrade requires understanding the two general upgrade strategies: In-place upgrade and Side-by-side migration.
In-place upgrade is an automated process where an old installation of SQL Server is updated to a SQL Server 2005 while the data, metadata, and configuration settings of the old instance are maintained. Performing an in-place upgrade is simple: You run the SQL Server 2005 setup program (the same program as the one you use for installing a new instance) and point it to the old instance name. The setup program shuts down the old service, updates the service with the new binary files, updates the data and metadata to the SQL Server 2005 format, and restarts the service. Notice the instance name remains the same during an in-place upgrade (see Figure 2-28), but the instance is not available during the upgrade.
Figure 2-28. Instance name remains unchanged during an in-place upgrade. Instance is down during upgrade.
Side-by-side migration is a manual process where you install a new instance of SQL Server 2005, and manually copy the metadata, data, and configuration settings of the old instance. During a side-by-side migration, both the old and new instance remain online until the migration is complete and client applications are pointed to the new instance (see Figure 2-29). At the end of the migration, you can remove the old instance if you choose. Performing a side-by-side migration is not as easy as performing an in-place upgrade, but you have full control over the migration. You can choose to bring all or part of the system and perform a checkpoint, testing to make sure the old and new instance exhibit the same behavior.
Figure 2-29. Instance or server names cannot be the same during a side-by-side migration. Both instances are available.
The main difference between an in-place upgrade and a side-by-side migration is how much granular control you need over the upgrade process. With the in-place upgrade, the old instance is replaced by a new instance, whereas in side-by-side migration you can migrate a single database, a table, or even a row from the old instance to the new instance. Another distinction between the two strategies is the amount of down time that your organization can incur during the upgrade. A side-by-side migration results in less down time if you can upgrade the system in phases. As soon as a phase is complete, you can switch the client applications from the old instance to the new instance. Side-by-side migration also provides access to both the new and old instances, allowing verification and comparison of the old and new environments. You should thoroughly study the characteristics of your application before choosing an in-place upgrade or side-by-side migration strategy.
Backward Compatibility Issues
One of the main issues in a successful upgrade is your level of preparedness for the backward compatibility issues. Although Upgrade Advisor does a great job of examining your server objects, scripts, and trace files for backward compatibility issues, there may be dynamic code in an application or a script that may still be affected by the compatibility issues. One of the best practices is to review the list of compatibility issues with developers and DBAs in your organization as part of your pre-upgrade tasks. Below is a list of general backward compatibility issues that may affect your environment:
Any object with Unicode character 0xFFFF as part of its name will be invalid after the upgrade if the database compatibility level is set to 90.
The database ID 32767 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 cannot be performed 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.
SQL Server 2005 does not allow duplicate index names on tables or views. Duplicate indexes must be renamed before an upgrade is attempted.
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.
The behavior of the following SQL Server statements and commands have changed, and scripts containing these objects should be modified before an upgrade:
DBCC PINTABLENo replacement.
DBCC UNPINTABLENo replacement.
DBCC ROWLOCKNo replacement.
DISK INITNo replacement as this is legacy behavior from SQL Server 6.x.
DISK RESIZENo replacement as this is legacy behavior from SQL Server 6.x.
The old rebuild master database utility, rebuildm.exe, is not available in SQL Server 2005. You can use the REBUILDDATABASE option of Setup.exe.
The Northwind and the pubs database are no longer installed as sample databases. Database administrators can upgrade the databases for use in SQL Server 2005 if they want to, or they can modify scripts to use the new AdventureWorks sample database instead.
Database administrators should modify any remote setup scripts, including the TARGETCOMPUTER parameter. This parameter is no longer supported.
The behavior of trace flags has 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). Database administrators need to review the latest SQL Server 2005 Books Online articles to understand changes to system objects before attempting a SQL Server upgrade to modify any scripts that utilize the changed objects.
Indexed views have been modified in SQL Server 2005 and scripts containing index view creation syntax may need to be modified before an upgrade.
Current backup scripts should be reviewed to see whether those scripts utilize named pipes. Backups to named pipes are no longer valid in SQL Server 2005.
Index definitions should also be checked to determine whether current indexes contain 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.
Presentation of some data fields displayed as query results in Management Studio may look different from those displayed with Query Analyzer. This is because under the hood Management Studio uses a different data provider (ADO.NET) as opposed to ODBC in Query Analyzer.
The OSQL utility no longer supports the ED or !! commands. To use these commands database administrators must 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 has been replaced with the SQLCMD utility and all references to ISQL should be removed or modified.
SQL Mail no longer allows mail attachments and now requires either the Outlook XP or Outlook 2003 client. SQL Mail is also deprecated in SQL Server 2005 and replaced with a new component called Database Mail.
English Query is no longer supported, and there is no replacement in SQL Server 2005.
Meta Data Services 3.0 is no longer available in SQL Server 2005 and there is no replacement for it.
The SQL-DMO WMI provider is not available in SQL Server 2005.
Although almost all the security settings from SQL Server 7.0 and SQL Server 2000 upgrade without a problem, you should be aware of the following security changes:
SQL Server fixed server role names 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.
SQL Server 2005 does not allow duplicate security identifiers (SID). You must remove one of the logins and its associated users with a duplicate SID before you begin the upgrade process.
Password hashes from SQL Server 6.5 are saved in a format that is no longer supported in SQL Server 2005. SQL Server logins with SQL Server 6.5 password hashes must have their passwords reset before you begin the upgrade process.
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 you begin the upgrade process.
Password comparisons that are not case sensitive are no longer supported, and applications attempting these comparisons need to be modified before the upgrade process to ensure continued functionality.
Scripts using the BCP utility should be reviewed because the permission requirements for the BCP utility have changed. Users 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.
Do not use the ALL permission to grant all object or statement permissions to a user. Scripts assigning permissions to users 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 because access to virtual tables and system metadata is no longer available to guest users or members of the public role.
Execute permission has been strengthen for the sp_addtype system stored procedure, and users attempting to execute this stored procedure must be members of the db_ddladmin or the db_owner fixed database roles.
Users executing the sp_changeobjectowner system stored procedure because they have membership in the db_ddladmin or the db_securityadmin fixed database role must also have the CONTROL permission set for target objects.
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 are upgraded during an in-place upgrade. However, you should be aware of several changes to in maintenance plans:
Maintenance plans no longer support log shipping. Log shipping must be configured outside the maintenance plan.
Maintenance plans no longer attempt the repair of minor problems currently configured under the Database Integrity Check task of the Maintenance Plan Wizard.
Maintenance plan metadata is 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.
The following SQL Server Agent upgrade issues may affect your environment:
The SQL Server Agent is available only for members of the sysadmin, SQLAgentUserRole, or MaintenanceUserRole roles.
The SQL Server Agent service account no longer allows SQL Server Authentication.
Upgrade all target servers (TSX) before you upgrade master servers (MSX).
You need to be aware of the following Replication and Log Shipping upgrade issues:
Log shipping does not upgrade and must be rebuilt after the upgrade process is complete.
Several discontinued functionalities affect all replication methods:
Creating push subscriptions without an active connection
Using file transfer protocol (FTP) to initialize subscribers running SQL Server version 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)
The following discontinued functionality affects transactional replication:
The following discontinued functionality affects merge replication:
You should perform a few additional checks in your pre-upgrade stage:
Ensure that you have additional disk size for the system databases in SQL Server 2005, which is necessary because of changes in system database schema.
Ensure that you have additional disk space in the PRIMARY file group of each user database.
Ensure that you have additional space required by transaction log files of user databases, which is necessary because of changes in how the transaction log handles database recovery.
If your application has a dependency on DMO, you must install the component as a legacy component.
If you upgrade from SQL Server 7.0 to SQL Server 2005, you must manually re-create the proxy accounts for job steps that utilize proxies.
The main upgrade task uses the Upgrade Advisor tool.
To help you find the upgrade issues prior to upgrading to SQL Server 2005, Microsoft has created the SQL Server 2005 Upgrade Advisor tool. SQL Server 2005 Upgrade Advisor is a tool for database system professionals and developers to analyze their current environments for upgrade blockers, compatibility issues, behavioral changes, deprecated features, and tasks that need to be done before and after an upgrade. The Upgrade Advisor also provides links to additional documentation that describes the recommended changes and the steps required to perform a workaround. We highly recommended that you run Upgrade Advisor against your system before performing an upgrade.
Upgrade Advisor supports analysis of the following components:
Upgrade Advisor Architecture
The architecture of Upgrade Advisor is simple (see Figure 2-30). Upgrade Advisor parses and extracts the objects in the upgrade target and verifies them against a predefined set of rules. Each upgrade rule has a detection logic and a number of attributes such as title, description, severity, and a link to a topic in the Upgrade Advisor documentation. Objects that match the upgrade rules are stored in an XML file. The report viewer of Upgrade Advisor performs a transformation on the XML file and generates a report.
Figure 2-30. Upgrade Advisor architecture.
Installing Upgrade Advisor
You can install Upgrade Advisor in either of two ways. You can install it from the redist folder of your SQL Server 2005 media or download it for free from the Microsoft SQL Server Web site: www.microsoft.com/downloads/details.aspx?familyid=451FBF81-AB07-4CCB-A18B-DA38F6BCF484&displaylang=en.
Upgrade Advisor has a dependency on the Microsoft .NET Framework 2.0, which is available on the SQL Server DVD, or it can be downloaded from Microsoft Web site: www.microsoft.com/downloads/details.aspx?FamilyID=0856eacb-4362-4b0d-8edd-aab15c5e04f5&DisplayLang=en.
Running Upgrade Advisor
Upgrade Advisor (see Figure 2-31) can connect to both local and remote servers. You can run it on the server you are planning to upgrade, or run it on a remote computer and point it to the target server. The remote functionality of Upgrade Advisor works well for situations in which the target server is not physically accessible or there is a restrict standard for installing software on production servers.
Figure 2-31. Welcome to SQL Server 2005 Upgrade Advisor screen.
A direct upgrade from SQL Server 6.5 or older to SQL Server 2005 is not supported and database administrators needing to upgrade their SQL Server 6.5 or earlier environments. They must perform a two-step upgrade and move the older environment to SQL Server 7.0 or 2000 before attempting an upgrade to SQL Server 2005.
Upgrade Advisor Analysis Wizard
To launch the upgrade Advisor Analysis Wizard, follow these steps:
On the Upgrade Advisor start page click Launch Upgrade Advisor Analysis Wizard
On the SQL Server Components
page (see Figure 2-32
), enter the name of the server to scan in the Server name
box and then click Detect
. Use the following guidelines for the server name:
To scan non-clustered instances, enter the computer name.
To scan clustered instances, enter the virtual SQL Server name.
To scan non-clustered components that are installed on a node of a cluster, enter the node name.
Figure 2-32. Upgrade Advisor Analysis Wizard provides an interface for analyzing all SQL Server 7.0 and 2000 components.
Review the list of components detected, modify the selections as necessary, and then click Next.
On the Connection Parameters page, select the instance of SQL Server you want to scan, select the authentication method, and, if necessary, enter the username and password information and then click Next.
The default instance name is MSSQLSERVER.
Click Run to analyze the instance of SQL Server.
When the analysis is finished, click Launch Report to view the detected upgrade issues.
Upgrade Advisor Analysis Command Line
You may run into situations in which you need to run the Upgrade Advisor Analysis tool against many servers (perhaps tens or hundreds), and the wizard does not scale because its interface allows analysis of only one server. The solution is the UpgradeAdvisorWizardCmd utility.
UpgradeAdvisorWizardCmd is a command line version of the Upgrade Advisor analysis tool that can run from a Windows command prompt. The command line switches of UpgradeAdvisorWizardCmd provide the same capabilities as the wizard.
UpgradeAdvisorWizardCmd [ -? ] |
[ -ConfigFile filename | <server_info> ]
[ -SqlUser login_id -SqlPassword password ]
[ -NsSqlUser login_id -NsSqlPassword password ]
[ -CSV ]
where <server_info> is any combination of the following:
For example, to analyze the Yukon instance of server1, server2, server3, and server4, you can include the following commands in a batch file and run it from a command prompt.
UpgradeAdvisorWizardCmd -Server server1 Instance Yukon
UpgradeAdvisorWizardCmd -Server server2 Instance Yukon
UpgradeAdvisorWizardCmd -Server server3 Instance Yukon
UpgradeAdvisorWizardCmd -Server server4 Instance Yukon
You can look up the full description of the command line parameters in the Upgrade Advisor documentation in Books Online.
Upgrade Advisor Report Viewer
To view an Upgrade Advisor report, click Launch Upgrade Advisor Report Viewer from the Upgrade Advisor start page. When Upgrade Advisor Report Viewer starts, the reports in the default directory are loaded. Reports are not displayed if Upgrade Advisor Report Viewer does not find any reports in the default directory. If there are no reports in the default directory, you can either run the Upgrade Advisor Analysis Wizard to create a report or load an existing report from another server or a sub-directory.
When XML files from the Upgrade Advisor Analysis Wizard are loaded into the Upgrade Advisor Report Viewer, a report for each component is displayed (see Figure 2-33). The report contains all the known issues, both detectable and undetectable, that you need to address. For each issue there is an icon indicating importance, a label informing you when the issue must be fixed, and a short description. When you expand an issue, you see a longer description, a link to issue details, and a link to the help file. The information for each issue is designed to provide enough information for you to fix the issue.
Figure 2-33. Upgrade Advisor report viewer.
Most components have issues that cannot be detected. To view these issues, expand the Other Upgrade Issues item for that component and then click the link to view additional information about the issues in the documentation.
Choosing an Upgrade or Migration Tool
SQL Server 2005 setup is the only tool for performing an in-place upgrade, but there are tools for performing a side-by-side migration. Table 2-4 shows the in-place upgrade and side-by-side migration tools and techniques for each of the SQL Server components.
Table 2-4. In-place Upgrade and Side-by-side Migration Tools for SQL Server 7.0/2000 Components
SQL Server Component
In-place Upgrade Tool
Side-by-side Tools and Technique
SQL Server 2005
Setup Backup/Restore, Detach/Attach, Copy Database Wizard
SQL Server 2005 Setup
Analysis Services Migration Wizard
SQL Server 2005 Setup (for default configuration only)
Manual deployment of reports
DTS Migration Wizard
Performing an In-place Upgrade
As mentioned earlier, performing an in-place upgrade is similar to installing a new instance of SQL Server. To upgrade an old instance of SQL Server, run the SQL Server 2005 Setup program on the same computer and follow the same installation instructions described in this chapter under the title "Installing SQL Server Database Engine." After you get to the Instance Name screen of the wizard (refer to Figure 2-8), specify the instance of the old installation. The installation wizard verifies the version information and applied service pack of the old instances, and examines the feasibility of upgrading the old instance by checking it against the upgrade blockers. If these conditions are verified, the wizard proceeds with the upgrade.
After the upgrade is completed, you should build a checklist of tasks to address. These tasks need to address changes to the maintenance, security, disaster recovery, and configuration options of SQL Server Database Engine. The following list provides you with issues and information that you will want to consider, check, or otherwise investigate in building your task checklist:
The model database is set to a database compatibility mode of 90 after an in-place upgrade. 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.
After an upgrade, all SQL Server Agent user-defined proxy accounts are changed to a temporary global proxy account named UpgradedProxyAccount. The UpgradedProxyAccount is granted access to only those subsystems that were explicitly used, and does not have access to all subsystems after upgrading.
Log into SQL Server using Windows Authentication and verify the maintenance plan tasks.
The value of max server memory is a hard limit for the buffer pool size in SQL Server 2005. SQL Server 2005 no longer allows the buffer pool to exceed this setting, even if additional memory is available. Queries fail with an insufficient system memory error if the value of max server memory is reached.
Changes in the query cost modeling may affect the successful execution of queries in SQL Server 2005 if the 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.
sp_configure [ [ @configname = ] 'option_name'
[ , [ @configvalue = ] 'value' ] ]
Direct system catalog updates are no not supported in SQL Server 2005. Review the allow updates option of sp_configure to determine whether 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 does not function. Review scripts and modify scripts, using this option.
The 'set working set size' option of sp_configure has been deactivated in SQL Server 2005. This option is present but it does not function. Modify scripts containing this option.
You must register each extended stored procedure using the full path for the DLL name. Extended stored procedures registered without the full path do not function after an upgrade to SQL Server 2005.
The behavior of the following SQL Server statements and commands have changed and scripts containing these objects should be modified:
FOR LOAD option of CREATE DATABASEShould be modified to take advantage of the fact that RESTORE operations can create a database.
DBCC DBREPAIRUse the DROP DATABASE command.
DBCC NEWALLOCUse the DBCC CHECKALLOC command.
DBCC TEXTALLUse the DBCC CHECKDB command.
DBCC CHECKDBDBCC CHECKDB now includes DBCC CHECKCATALOG, making a second call to this DBCC command unnecessary in SQL Server 2005 maintenance scripts.
DBCC TEXTALLOCUse the DBCC CHECKTABLE command.
You should review the use of trace flags in administration scripts to determine whether the trace flag still exists or whether the functionality of the trace flag has not changed in SQL Server 2005.
If upgrading target servers from SQL Server 7.0, you must manually reenlist them with the upgraded master server.
The syntax for calling tokens in the SQL Server Agent job steps has been changed and must be modified after an upgrade 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 an upgrade to SQL Server 2005.