Upgrading to SQL Server 2000
White Paper
Table of Contents............................................................................................................ 1
Introduction.................................................................................................................... 1
Upgrading to SQL Server 2000: Overview........................................................................ 2
Hardware and Software Requirements........................................................................ 3
Upgrading from SQL Server 7.0 to SQL Server 2000....................................................... 4
Upgrading During Installation...................................................................................... 4
After Upgrading........................................................................................................... 5
Online Database Upgrades from SQL Server 7.0 – Using the Copy Database Wizard... 6
After Upgrading........................................................................................................... 7
Upgrading Databases from SQL Server 6.5 to SQL Server 2000...................................... 9
Preparing to Upgrade from SQL Server 6.5.................................................................. 9
Upgrading SQL Server 6.5 to SQL Server 2000 Using a Direct Pipeline...................... 13
Upgrading SQL Server 6.5 to SQL Server 2000 Using a Tape Drive............................ 15
Using SQL Server Upgrade Wizard (for SQL Server 6.5) - Details............................... 17
Replication and Upgrading............................................................................................. 26
Upgrading and Immediate Updating......................................................................... 26
Upgrading and File Transfer Protocol......................................................................... 27
Troubleshooting and Replication Upgrades............................................................... 27
Backward Compatibility.................................................................................................. 29
Backward Compatibility: SQL Server 2000 and SQL Server 7.0.................................. 29
Backward Compatibility: SQL Server 2000 and SQL Server 6.5.................................. 33
Failover Clustering......................................................................................................... 37
Upgrading to a SQL Server 2000 Failover Cluster...................................................... 37
Failover Cluster Installation....................................................................................... 39
Conclusion..................................................................................................................... 40
Appendixes................................................................................................................... 41
Appendix 1: SQL Server 6.5 Backward Compatibility—Level 1................................... 41
Appendix 2: SQL Server 6.5 Backward Compatibility—Level 2................................... 46
Appendix 3: SQL Server 6.5 Backward Compatibility—Level 3................................... 48
Appendix 4: SQL Server 6.5 Backward Compatibility—Level 4................................... 51
Upgrading
to SQL Server 2000
White Paper
Published: September 2000
For the latest information, please see http://www.microsoft.com/sql/ and the Books Online on the SQL Server 2000 Evaluation Edition CD.
Microsoft® SQL Server™ 2000, the data management and analysis backbone for Microsoft’s .NET enterprise applications and servers, improves on the performance, reliability, scalability, quality, and ease of use of its predecessors, SQL Server 7.0 and SQL Server 6.5. SQL Server 2000 offers rich Extensible Markup Language (XML) support, comprehensive analysis services, and simplified database administration—features that combine to produce a solution able to rapidly deliver reliable, scalable e-commerce, data warehousing and line of business applications. Together, these features make SQL Server 2000 a compelling upgrade from SQL Server 6.5 and SQL Server 7.0.
For e-commerce applications, SQL Server 2000 offers the tools needed for companies to Web-enable existing systems and build new applications. Integrated XML support allows database and application developers to easily store XML in the database or retrieve data in XML format. XML support speeds application integration, enhances the flexibility of Web sites, and facilitates the exchange of information across platforms and between companies.
The data warehousing and business intelligence features of SQL Server 2000 include improved data transformation tools, advanced data analysis and highly scalable online analytical processing (OLAP). Data mining—a new feature in SQL Server 2000—is used to detect trends that would otherwise go unseen in large quantities of data. Moreover, with the ability to expose analytical information over the Internet, true Web-based access to analysis solutions will be possible, even through firewalls.
SQL Server 2000 also provides the reliability and scalability needed for line of business applications. To ensure scalability, SQL Server 2000 includes support for 32 CPUs and 64 gigabytes (GB) of RAM. SQL Server 2000 includes log shipping, online backups, and failover clusters to improve reliability. And SQL Server 2000 has been thoroughly tested to ensure its reliability—the new release underwent 96,000 functional tests and 474 stress tests, compared to 65,000 functional tests and 158 stress tests for SQL Server 7.0.
Recent benchmarks show that SQL Server 2000 has the performance needed to handle the most demanding applications.
· SQL Server 2000 running on the Microsoft Windows® 2000 operating system achieved nearly double the performance of Oracle running on UNIX at half the total system cost in a TPC-C benchmark study. SQL Server achieved 262,243 tpmC on a Compaq ProLiant 8500-700-96P (12-node) with a price/tpmC of U.S.$20.24 and total system cost of $5.3 million.
· SQL Server 2000 on Windows 2000 provides the best performance on the SAP R/3 Sales and Distribution Standard Benchmark on Windows, supporting 7,500 concurrent sales and distribution users with 53 percent more scalability than Oracle on Windows 2000.
· SQL Server 2000 Enterprise Edition and Windows 2000 Advanced Server on Compaq set the world record for the standard J.D. Edwards OneWorld Benchmark, with 3,442 concurrent users. It beat the Oracle/Sun previous record by more than 37 percent.
These and other advances make SQL Server 2000 an invaluable upgrade from SQL Server 7.0 and SQL Server 6.5. And with proper planning and a little patience, these upgrades can go smoothly.
The SQL Server Upgrade Wizard was designed to make most upgrades as easy as possible for most users. Users can often upgrade using the default values and options. However, proper preparation and a familiarity with the upgrade process can make it easier to prevent or solve any problems that do arise. This document contains the information you will need to prepare for and understand the upgrade process.
This white paper details the steps involved in upgrading from SQL Server 6.5 and SQL Server 7.0 to SQL Server 2000. It covers:
· Preparing for an upgrade.
· Using the Database Copy Wizard to move SQL Server 7.0 databases to SQL Server 2000
· Using the SQL Server Upgrade Wizard to upgrade from SQL Server 6.5 to SQL Server 2000.
· Understanding backward compatibility as it applies to SQL Server 7.0 and SQL Server 6.5.
· Upgrading, installing, and troubleshooting failover clustering.
Upgrading from Microsoft SQL Server 7.0 to SQL Server 2000 is one of the basic choices offered by the SQL Server Setup program on the initial Installation Selection screen. When you select the option to Upgrade, remove, or add components to an existing installation of SQL Server, Setup detects your current installation and initiates the correct sequence of setup screens for the upgrade selected. Upgrade variations include:
· A complete installation upgrade from SQL Server 7.0 to SQL Server 2000 (installing over SQL Server 7.0).
· Adding components to an installation of SQL Server 2000.
· An upgrade to the feature set of an existing installation of SQL Server 2000 (edition and component upgrade).
· An upgrade to SQL Server 2000 from SQL Server version 6.5 using the SQL Server Upgrade Wizard.
· An online database upgrade of SQL Server 7.0 databases to SQL Server 2000 database format using the Copy Database Wizard.
During the upgrade from SQL Server 7.0, external packages, such as Microsoft Management Console and the Microsoft Distributed Transaction Coordinator, must be installed for each upgrade, and the registry must be updated. The master database and other system databases are upgraded in various ways involving a series of scripts run on the server with specific options. If the upgrade process fails, built-in recovery mechanisms restart and resume the upgrade.
To upgrade from SQL Server 6.5 or SQL Server 7.0 to SQL Server 2000, the computer must meet the hardware and software requirements for SQL Server 2000 as well as the following upgrade requirements:
|
Hardware/Software |
Upgrade Requirements |
|
Operating system |
· Microsoft Windows NT® Server 4.0 Enterprise Edition with Service Pack 5 (SP5) or later · Windows NT Server 4.0 with SP5 or later · Windows NT Workstation 4.0 with SP5 or later · Internet Explorer 5.0 or later · Windows 2000 |
|
SQL Server 6.5 |
When upgrading SQL Server 6.5 to an instance of SQL Server 2000 on the same computer, you must have applied SQL Server 6.5 Service Pack 5 (SP5) or later. When upgrading SQL Server 6.5 to an instance of SQL Server 2000 on a different computer, you must have applied SQL Server 6.5 Service Pack 3 (SP3) or later. |
|
SQL Server 7.0 |
SQL Server 7.0 (at any Service Pack level) |
|
Network protocols |
Named Pipes. SQL Server 6.5, SQL Server 7.0, and SQL Server 2000 all must be set to listen to the default pipe, \\.\pipe\sql\query. Named Pipes is required even for a tape backup upgrade. |
|
Hard-disk space |
No additional hard-disk space is required when upgrading from SQL Server 7.0 to SQL Server 2000. When upgrading from SQL Server 6.5 to SQL Server 2000, however, you need approximately 1.5 times the size of the SQL Server 6.5 databases. |
You can overwrite an installation of SQL Server 7.0 with a version upgrade to SQL Server 2000. If SQL Server 7.0 is detected as an existing installation when you run Setup, you can choose the option to upgrade. In this process, all the SQL Server 7.0 program files are upgraded, and all data stored in SQL Server 7.0 databases is preserved. In addition, SQL Server Books Online for SQL Server 7.0 remains on your computer.
Note: SQL Server 7.0 profiler traces and registered servers are not upgraded when SQL Server 7.0 tools are upgraded to SQL Server 2000. Similarly, information models that were installed with Microsoft Repository 2.0 are not upgraded automatically. SQL Server 2000 supports newer versions of information models for both Data Transformation Services (DTS) and the Open Information Model (OIM).
You can also upgrade from one edition of SQL Server to another edition during the version upgrade to SQL Server 2000.
Caution: After you perform this version upgrade, the SQL Server 7.0 installation no longer exists on your computer. The only way to restore an installation of SQL Server 7.0 is to first uninstall SQL Server 2000, perform a complete reinstall of SQL Server 7.0 files, and then restore your backed-up SQL Server 7.0 databases.
1.
Insert the SQL Server 2000 compact disc for the
edition to which you want to upgrade into your CD-ROM drive. If the compact
disc does not autorun, double-click Autorun.exe in the root directory of
the compact disc.
Note: If you have purchased an edition of SQL Server with more features
than your current SQL Server 7.0 installation, the upgrade process will perform
both the version and edition upgrade at the same time.
2. Select SQL Server 2000 Components, and then select Install Database Server.Setup then prepares the SQL Server Installation Wizard. At the Welcome screen, click Next.
3. In the Computer Name dialog box, Local Computer is the default option and the local computer name appears in the edit box. Click Next.
4. In the Installation Selection dialog box, click Upgrade, remove, or add components to an existing instance of SQL Server, and then click Next.
5.
In the Instance Name dialog box, Default
will be selected. Click Next.
Note: When upgrading, SQL Server 7.0 automatically becomes the default
instance of SQL Server 2000.
6. In the Existing Installation dialog box, click Upgrade your existing installation, and then click Next.
7. In the Upgrade dialog box, you are prompted as to whether you want to proceed with the requested upgrade. Click Yes, upgrade my <text specific to the upgrade> to start the upgrade process, and then click Next. The upgrade runs until finished.
8. In the Connect to Server dialog box, select an authentication mode, and then click Next.
9. If you are not sure which mode to use, accept the default: The Windows account information I use to log on to my computer with (Windows).
10. In Start Copying Files dialog box, click Next.
11. In the Setup Complete dialog box, click Yes, I want to restart my computer now, and then click Finish.
Caution: This version upgrade procedure overwrites your Microsoft SQL Server 7.0 installation; the installation no longer exists on your computer. In addition, previous registry settings are removed. For example, after upgrading you will need to re-register your servers.
After you upgrade from SQL Server 7.0 to SQL Server 2000, it is recommended that you repopulate full-text catalogs and update statistics. Both operations can be time-consuming, but will enhance the performance of SQL Server 2000.
3. Check the Backward Compatibility section of this document, or SQL Server 2000 Books Online, for more information about SQL Server 2000 backward compatibility with SQL Server 7.0.
4. Check the Replication and Upgrading section of this document, or SQL Server 2000 Books Online, for more information about SQL Server 2000 backward compatibility with SQL Server 7.0.
5. Check the Failover Clustering section of this document, or SQL Server 2000 Books Online, for more information about SQL Server 2000 backward compatibility with SQL Server 7.0.
Using the Copy Database Wizard, you can move or copy a database and associated meta data from SQL Server 7.0 to an instance of SQL Server 2000, without having to shut down any servers in the process. Advantages of an online database upgrade include:
· No downtime for servers during the upgrade.
· Custom selection of databases to upgrade, leaving other databases available to the original (SQL Server 7.0) server.
· Inclusion of related meta data in the upgrade procedure. For example, logon information, jobs, and user-specific objects associated with user databases can be included.
· The process can be run at a convenient time.
The Database Copy Wizard is based on detach and attach functionality that allows user databases to be moved or copied from a source to a destination server. A Data Transformation Services (DTS) package performs the actual move or copy operation. You can schedule the package to run at a specified time or rerun the package if required.
Database administrators can move or copy one or more databases from an instance of SQL Server 7.0 to the default instance of SQL Server 2000 on your local computer, or to either a default or a named instance on a remote computer. This upgrade feature does not support SQL Server 6.5 databases.
Note: You can have only one active default instance of SQL Server on a computer at one time, either a default instance of SQL Server 7.0 or a default instance of SQL Server 2000. SQL Server 6.5 can also be a default instance.
· A database with the identical name on both source and destination servers cannot be moved or copied. On the database selection screen, it will be noted as “Already exists.”
· For databases involved in replication, a regular server upgrade is required.
At the start of a database move or copy operation, one administrator must have exclusive use of all files to prevent any changes to the file set during the process. Two connections are required to copy database files: sysadmin privileges on both installations of SQL Server and administrator privileges on the server/network.
To prevent any chance of data corruption, the SQL Server 7.0 databases must be in read-only condition and cannot be renamed during this operation. Any name conflicts between source and destination servers must be resolved manually prior to upgrading databases. Nothing on the destination server is overwritten.
If you move or copy multiple databases in one operation, each database is actually moved one at a time; that is, one database at a time is detached, files are copied and then reattached. To avoid any problems, the DTS package writes a message to the error log indicating that the database is about to be detached from its source server. At the same time, a script is prepared to attach the database to its destination. After the database is successfully attached to the destination, another entry is written to the log indicating successful completion.
When upgrading to a destination that is a clustered server, the Copy Database Wizard will ensure you select only shared drives on a clustered destination server. The source server may also be clustered.
Note: Unrelated to this upgrade process, you can also use the Copy Database Wizard to move or copy user databases from one instance of SQL Server 2000 to another instance of SQL Server 2000.
· Expand a server group, and then expand a server.
· Right-click the server, point to All Tasks, and then click Copy Database Wizard.
· Complete the steps in the wizard.
After you upgrade from SQL Server 7.0 to SQL Server 2000, it is recommended that you repopulate full-text catalogs and update statistics. Both operations can be time-consuming, but will enhance the performance of SQL Server 2000.
3. Check the Backward Compatibility section of this document, or SQL Server 2000 Books Online, for more information about SQL Server 2000 backward compatibility with SQL Server 7.0.
4. Check the Replication and Upgrading section of this document, or SQL Server 2000 Books Online, for more information about SQL Server 2000 backward compatibility with SQL Server 7.0.
5. Check the Failover Clustering section of this document, or SQL Server 2000 Books Online, for more information about SQL Server 2000 backward compatibility with SQL Server 7.0.
You can convert data from SQL Server 6.5 to the formats for SQL Server 2000 using the SQL Server Upgrade Wizard. The wizard upgrades any or all of your databases, transferring all catalog data, objects, and user data. It also transfers replication settings, SQL Executive settings, and most of the SQL Server 6.5 configuration options. Be sure to review all aspects of this upgrade, as noted in the “Preparing to Upgrade from SQL Server 6.5” section below.
Note: To run the SQL Server Upgrade Wizard, you must have a default instance of Microsoft SQL Server 2000 installed on your computer.
The SQL Server Upgrade Wizard does not support consolidation of databases from multiple SQL Server 6.5 installations. If you must upgrade SQL Server 6.5 databases from multiple servers, consolidate all of the SQL Server 6.5 databases onto one server, and then run the wizard to upgrade the consolidated server.
The SQL Server Upgrade Wizard does not remove SQL Server 6.5 from your computer. If you are using a tape backup to perform the upgrade, you have the option of removing the SQL Server 6.5 devices to save disk space.
When the upgrade process is complete, two separate installations of SQL Server exist, including two separate sets of the same data. The SQL Server 6.5 and the SQL Server 2000 installations become independent of each other.
If you are performing the upgrade on a single computer, additional disk space is required. You can also upgrade from one computer to another.
Note: You can leave the installation of SQL Server 6.5 on a computer indefinitely. In addition to installations of SQL Server 6.5 and a default instance of SQL Server 2000 on the same computer, you also can install multiple named instances of SQL Server 2000 on the same computer.
Follow these steps before using the SQL Server Upgrade Wizard to move from SQL Server 6.5 to SQL Server 2000:
1. Back up the SQL Server 6.5 database files (all .dat files, including master) so you can completely restore them if necessary.
2. Run the appropriate Database Console Commands (DBCC) on the SQL Server 6.5 databases to ensure they are in a consistent state.
3. Estimate the disk space required. In addition to the hard disk space used by Microsoft SQL Server 2000, you need approximately 1.5 times the size of the SQL Server 6.5 databases. See below for more information on estimating required disk space.
4. Set tempdb to at least 10 megabytes (MB) in the SQL Server 6.5 installation; 25 MB is recommended. See below for more information on setting tempdb.
5. Ensure the master database has at least 3 MB of free space.
6.
Ensure that all database users have logon
information in the master database.
This is important for restoring a database because system logon information
resides in the master database.
7.
Ensure the @@SERVERNAME is defined on SQL Server
2000. If @@SERVERNAME is NULL, you can use the sp_addserver system stored
procedure. For example, if your computer is named “production,” the command
would be sp_addserver 'production1',local. Changes do not take affect until the
MSSQLServer service is restarted.
Note: Because SQL Server 6.5 does not recognize the hyphen (-) in a
computer name, replace a hyphen with an underscore (_).
8.
Disable any startup stored procedures.
The SQL Server Upgrade Wizard starts and stops the SQL Server 6.5 server during
the upgrade process. Stored procedures processed at startup may cause the
upgrade process to stop responding.
9.
Ensure that you upgrade all databases with
cross-database dependencies at the same time.
For example, you want to upgrade three databases, database1, database2,
and database4, and there is logon information in SQL Server 6.5 master..sysdatabases
for USER1 that defaults to database3 (not one of the databases
you are upgrading). The SQL Server Upgrade Wizard does not create the logon
information because the database is not upgraded, and therefore does not exist
in SQL Server 2000. If USER1 is listed as the owner for objects in any
of the databases upgraded, those objects cannot be created because the logon
information for USER1 does not exist.
10. If performing a two-computer upgrade, assign a domain user name and password to the MSSQLServer service for SQL Server 6.5 and SQL Server 2000 instead of using the local system account or a local user account. The domain user account should belong to the Administrators group of both the computers involved in the upgrade. (The local system account is sufficient for a one-computer upgrade.)
11. Stop replication and ensure that the log is empty.
12. Quit all applications, including all services dependent on SQL Server.
If you copied the SQL Server 6.5 databases to a new computer to perform the upgrade, you may need to update the new SQL Server 6.5 master database as follows:
1. Change references from the earlier server name to the current server name in the SQL Server 6.5 master database. See below for more information.
2. Update the device file locations in the SQL Server 6.5 master database. See below for more information.
3. Ensure that all users have corresponding logon information.
Before you perform an upgrade of SQL Server 6.5 to SQL Server 2000, ensure that there is enough available disk space. This is important if you intend to perform either a one-computer or a two-computer upgrade.
The SQL Server Upgrade Wizard estimates the disk space necessary to upgrade the SQL Server 6.5 server to SQL Server 2000. The wizard examines the current SQL Server 6.5 installation and estimates the amount of disk space the SQL Server 6.5 data will occupy in SQL Server 2000.
You can estimate:
· The size of SQL Server 2000 databases.
· The size of SQL Server 2000 logs.
· The amount of disk space required for tempdb.
Note: The SQL Server Upgrade Wizard estimates the disk space required; it cannot give an exact requirement.
Note: To run the SQL Server Upgrade Wizard, you must have an instance of SQL Server 2000 already installed on your computer.
1. On the Start menu, point to Programs/Microsoft SQL Server-Switch, click SQL Server Upgrade Wizard, and then click Next.
2. Select Named pipe; then click Next.
3. In Export server (6.5), in the Server name box, enter the name of the local or remote computer on which SQL Server 6.5 resides.
4.
In the Administrator password ('sa') box,
enter the sa password for SQL Server 6.5, and then click Next.
Unless you have changed it, the system administrator (sa) password for SQL
Server 2000 is blank.
5. Include the databases to upgrade. Move any database not to include in the disk space estimation to the Exclude list, and then click Next.
6.
Select Use the default configuration or edit
the default; then click Edit.
The SQL Server Upgrade Wizard layout utility appears, showing the proposed
layout of the SQL Server 2000 data files.
7. Click Advanced.
8. Click an object in the Proposed database layout box to view details in the Object details box.
9. The Drive summary box shows the estimated size of all SQL Server 2000 data files and the free disk space left on all of the local fixed disks. On the Options menu, select Freespace includes 6.5 files to view the free space that would exist if the SQL Server 6.5 data files were deleted.
10. Click Accept to return to the Database Creation dialog box.
11. Click Cancel to quit the SQL Server Upgrade Wizard.
1. On the Start menu, point to Programs/Microsoft SQL Server 6.5, and then click ISQL/w.
2. Enter the sa password, and then click Connect.
3. Execute a DISK INIT command to increase the size of the tempdb device to at least 25 MB.
4. Execute an ALTER DATABASE command to increase the size of the tempdb database to at least 25 MB.
--Increase
the size of the tempdb device:
DISK
INIT name = 'tempdb1',physname = 'c:\mssql\data\tempdb1.DAT',vdevno = 100, size
= 12800
GO
--Increase
the size of tempdb:
ALTER
DATABASE tempdb ON tempdb1 = 25
1. Start SQL Server 6.5 in minimal configuration mode.
2. In a command prompt window, from the \Mssql\Binn directory, run: sqlservr -f
3. On the Start menu, point to Programs /Microsoft SQL Server 6.5, and then click ISQL/w.
4. Enter the sa password, and then click Connect.
5. Execute SELECT @@SERVERNAME to retrieve the former server name.
6. Execute sp_dropserver to drop the former server.
7. Execute sp_addserver to add the current server.
8. Stop SQL Server. In the command prompt window, press Ctrl+C.
9. Restart SQL Server.
10. Execute SELECT @@SERVERNAME to verify the current server name.
--Start
SQL Server in minimal configuration mode.
--Retrieve
the former server name:
SELECT
@@SERVERNAME
--Drop
the server returned from the previous select:
sp_dropserver
'SERVER6X'
--Add
the current server:
sp_addserver
'SERVER70', local
--Stop
SQL Server.
--Restart
SQL Server in minimal configuration mode.
--Verify
the current server name:
SELECT
@@SERVERNAME
1. On the Start menu, point to Programs/Microsoft SQL Server 6.5, and then click ISQL/w.
2. Enter the sa password, and then click Connect.
3. Select from sysdevices in the master database to view the old device file locations.
4. Execute sp_configure to allow updates to the system tables, and then reconfigure with override.
5. Update the device file locations that have changed.
6. Execute sp_configure to disallow updates to the system tables, and then reconfigure with override.
--View
the old device file locations:
SELECT
phyname FROM sysdevices
--Allow
updates to the system tables:
sp_configure
'allow updates',1
GO
RECONFIGURE
WITH OVERRIDE
GO
--Update
device file locations that have changed:
UPDATE
sysdevices
SET
phyname = "E:\Data\HR\HR1.dat"
WHERE
name = "HumanResources1"
GO
UPDATE
sysdevices
SET
phyname = "E:\Data\HR\HR1Log.dat"
WHERE
name = "HumanResources1Log"
GO
--Disallow
updates to the system tables:
sp_configure
'allow updates',0
GO
RECONFIGURE
WITH OVERRIDE
GO
Note: To run the SQL Server Upgrade Wizard, you must have an instance of SQL Server 2000 already installed on your computer.
1. On the Start menu, point to Programs/Microsoft SQL Server-Switch, click SQL Server Upgrade Wizard, and then click Next.
2. In the Data and Object Transfer screen, accept the default selections, including Named pipe, and then click Next. Verification options are recommended, but not required. Click Help for information.
3.
On the Logon screen, in the Server
name box in the Export server (6.5) group box, enter the name of the
local or remote computer on which Microsoft SQL Server 6.5 is installed.
In the Administrator password ('sa') box, enter the sa password for SQL
Server 6.5, and then click Next. Unless you have changed it, the system
administrator password for SQL Server 2000 is blank.
For Import Server, the server name is filled in. Enter the optional startup
arguments, if you want. Click Help for information. When you are
finished setting options, click Next.
4. In the message box asking if you want to continue, click Yes if you are ready to upgrade. The SQL Server Upgrade Wizard shuts down SQL Server 6.5 and starts SQL Server 2000.
5. In the Code Page Selection screen, accept or change the default settings, and then click Next.
6.
In the Database Selection screen, include
the databases to upgrade. Move any databases you do not want upgraded at this
time to the Exclude list, and then click Next.
Converting all databases is recommended.
7.
In the Database Creation dialog box,
select Use the default configuration or edit the default, and then click
Next.
Click Edit to examine and make changes to the proposed disk
configuration within the layout utility. In the Proposed Database Layout
box, make changes as needed. Click Advanced to view Object Details and
Drive Summary. When you are finished, click Accept to return to the SQL
Server Upgrade Wizard.
8. In the System Configuration screen, in System objects to transfer, select the object types to transfer from SQL Server 6.5 to SQL Server 2000:
·
Server configuration
Login and remote login registrations and server configuration options relevant
to SQL Server 2000 are transferred as part of the version upgrade.
·
Replication settings
All articles, subscriptions, and publications of each selected database, plus
the distribution database, if any, are transferred and upgraded.
·
SQL Executive settings
All tasks scheduled by SQL Executive are transferred and upgraded so that SQL
Server 2000 can schedule and run those tasks in SQL Server Agent.
9. In the System Configuration screen, in Advanced settings, for ANSI Nulls, select:
· Off, if ANSI nulls should not be used when stored procedures are created. This is the default.
· On, if ANSI nulls should be used when stored procedures are created.
10. In Quoted identifiers, select one of these options, and then click Next:
· Mixed (or don't know), if some of your objects were created with QUOTED_IDENTIFIER set to ON and others with it set to OFF, or if you are not sure how they were created.
· Off, if all objects should be compiled with QUOTED_IDENTIFIER set to OFF.
· On, if all objects should be compiled with QUOTED_IDENTIFIER set to ON.
11. In the Completing the SQL Server Wizard screen, view the summary of choices you have made. Click View warnings and choices in notepad to open a text version of the upgrade script. If all options are correct, click Finish.
The SQL Server Upgrade Script Interpreter screen appears, with information on the progress of the upgrade.
Note: To run the SQL Server Upgrade Wizard, you must have an instance of SQL Server 2000 already installed on your computer.
1. On the Start menu, point to Programs/Microsoft SQL Server-Switch, click SQL Server Upgrade Wizard, and then click Next.
2. In the Data and Object Transfer screen, click Tape, and then click Next. Verification options are recommended, but not required. Click Help for information.
3.
On the Logon screen, in the Server
name box in the Export server (6.5) group box, enter the name of the
computer on which Microsoft SQL Server version 6.5 is installed.
In the Administrator password ('sa') box, enter the sa password for SQL Server
6.5, and then click Next.
Unless you have changed it, the sa password for SQL Server 2000 is blank.
For Import Server (2000), the server name is filled in. Enter optional
startup arguments, if you want. Click Help for information. When you are
finished setting options, click Next.
4. In the message box asking if you want to continue, click Yes if you are ready to upgrade. The SQL Server Upgrade Wizard switches to the SQL Server 2000 server.
5. In the Code Page Selection screen, accept or change the default settings, and then click Next.
6. In the Database Selection screen, include the databases to upgrade. Move any database not to be upgraded at this time to the Exclude list, and then click Next.
7. In Device for data transfer, specify the location of the tape drive.
8.
In 6.5 device backup options, select Backup
6.5 devices before exporting data if you have not backed up the databases
already.
Prior to creating the SQL Server 2000 databases, the SQL Server Upgrade Wizard
either prompts you to back up the SQL Server 6.5 devices or copies the devices
for you automatically.
9.
Select Delete 6.5 devices before importing
data if necessary due to lack of disk space, and then click Next.
After objects and data are exported, and before creating databases in SQL Server
2000, the SQL Server Upgrade Wizard deletes the SQL Server 6.5 devices to
reclaim disk space.
10.
Select Use the default configuration or edit
the default, and then click Next.
Click Edit to examine and make changes to the proposed disk
configuration within the layout utility. In the Proposed Database Layout
box, make changes as needed. Click Advanced to view Object Details and
Drive Summary. When you are finished, click Accept to return to the SQL
Server Upgrade Wizard.
11. In System objects to transfer, select the object types to transfer from SQL Server 6.5 to SQL Server 2000:
·
Server configuration
Login and remote login registrations and server
configuration options relevant to SQL Server 2000 are transferred as part of
the version upgrade.
·
Replication settings
All articles, subscriptions, and publications of
each selected database, plus the distribution database, if any, are transferred
and upgraded.
·
SQL Executive settings
All tasks scheduled by SQL Executive are
transferred and upgraded so that SQL Server 2000 can schedule and run those
tasks in SQL Server Agent.
12. In ANSI Nulls, select:
· Off, if ANSI nulls should not be used when stored procedures are created. This is the default.
· On, if ANSI nulls should be used when stored procedures are created.
13. In Quoted Identifiers, select one of these options, and then click Next:
· Mixed (or don't know), if some of your objects were created with QUOTED_IDENTIFIER set to ON and others with it set to OFF, or if you are not sure how they were created.
· Off, if all objects should be compiled with QUOTED_IDENTIFIER set to OFF.
· On, if all objects should be compiled with QUOTED_IDENTIFIER set to ON.
14. In the Completing the SQL Server Wizard screen, view the summary of choices you have made. Click View warnings and choices in notepad to open a text version of the upgrade script. If all options are correct, click Finish.
The SQL Server Upgrade Script Interpreter screen appears with information about the progress of the upgrade.
This section describes in detail the options in the SQL Server Upgrade Wizard.
The Data and Object Transfer screen allows you to choose upgrade options.
The objects and data check boxes indicate that the SQL Server Upgrade Wizard exports catalog data, objects, and user data from selected SQL Server 6.5 databases and imports them into newly created SQL Server 2000 databases.
You can perform an upgrade using either of the following data transfer methods:
·
Named pipe
(simultaneous import/export)
A direct pipeline enables the SQL Server Upgrade Wizard to transfer data in
memory from SQL Server 6.5. This data transfer method is the most reliable and
provides the best performance. However, when performing a one-computer upgrade,
you cannot reuse the disk space occupied by the SQL Server 6.5 devices until
the version upgrade process is complete, so use this option only if you have
disk space available.
·
Tape (requires a
Windows NT tape driver to be installed)
The SQL Server Upgrade Wizard backs up to tape all of the SQL Server 6.5
databases you have selected to upgrade. The SQL Server Upgrade Wizard then
optionally deletes all of the SQL Server 6.5 devices, freeing disk space before
new data files are created.
Important: The SQL Server Upgrade Wizard deletes all of the SQL Server
6.5 devices, not only those upgraded. You should upgrade all databases if you
choose to delete the SQL Server 6.5 devices. The tape backup option should be
used only when you want to upgrade on a single computer but there is not enough
space on the hard disk to install SQL Server 2000 alongside SQL Server 6.5 and
perform the version upgrade.
Note: The SQL Server Upgrade Wizard uses a named pipe, even when
performing a tape backup upgrade. SQL Server 6.5 and SQL Server 2000 must be
set to listen to the default named pipe, \\.\pipe\sql\query.
The transfer of objects and data by the SQL Server Upgrade Wizard is a very reliable process. If any objects could not be imported due to errors in those objects or compatibility problems with Microsoft SQL Server, they are noted in the output logs of the SQL Server Upgrade Wizard.
The SQL Server Upgrade Wizard also offers the following optional verification measures:
·
Validate successful object data transfer
The SQL Server Upgrade Wizard examines the SQL Server 6.5 databases before the
upgrade process and SQL Server 2000 databases after the upgrade. For each, the
wizard prepares a list of all objects, including schema and stored procedures,
and the number of rows in each table. The wizard then compares the two lists
and reports any discrepancies.
·
Exhaustive data integrity verification
The SQL Server Upgrade Wizard performs a checksum for each column of each table
before and after the upgrade to verify that data values have not changed.
Note: The SQL Server Upgrade Wizard does not report as errors any
intentional differences in objects. If some objects, typically stored
procedures, could not import due to errors in the objects or compatibility
problems with SQL Server 2000, they are reported twice: once in the SQL scripts
that show the source code of the objects and the error messages received from
SQL Server 2000 when trying to create them, and again in the output of the
verification processes.
The SQL Server Upgrade Wizard performs a version upgrade using the options specified. The SQL Server 6.5 server and data used by SQL Server 6.5 databases are left intact throughout the version upgrade process. At this time, the SQL Server 6.5 catalog data, objects, and databases are converted so that they are compatible with SQL Server 2000. After the version upgrade is complete, SQL Server 2000 becomes your production system.
The order of upgrade is basically the same for both a direct pipeline and a tape drive upgrade. The one difference is in how data is exported and imported. When using a tape drive, data is exported to the tape drive after shutting down SQL Server 6.5 and before starting SQL Server 2000. This data is then imported from the tape drive later to SQL Server 2000. When using a direct pipeline, the export and import steps are combined in one step, simultaneously.
The following list shows the order in which the SQL Server Upgrade Wizard performs the upgrade from SQL Server 6.5 to SQL Server 2000. The differences between the direct pipeline and tape drive methods are noted.
1. Starts SQL Server 6.5
2. Updates ODBC and SQL-DMO components on SQL Server 6.5
3. Examines SQL Server 6.5 databases
4. Exports replication settings
5. Exports server configuration settings from the master database
6. Exports logon information
7. Exports database owners
8. Exports SQL Executive objects and settings from the msdb database
9. Exports database objects for all databases chosen
10. Shuts down SQL Server 6.5
· Tape Drive only: Exports data to tape
· Tape Drive only: Backs up and then deletes SQL Server 6.5 devices
11. Starts SQL Server 2000
12. Creates databases
13. Modifies SQL Executive objects and settings to SQL Server 2000 formats
14. Imports logon information
15. Imports database objects
· Tape Drive only: Imports data from tape into SQL Server 2000
· Direct Pipeline only: Simultaneously exports data from SQL Server 6.5 and imports it into SQL Server 2000
16. Imports modified SQL Executive objects and settings into SQL Server 2000
17. Imports replication settings
18. Examines SQL Server 2000 databases
19. Verifies that the upgrade is successful
20. Sets database options in SQL Server 2000
21. Marks server and databases as moved
22. Drops temporary tempdb files
The upgrade process can take place on a single computer or from one computer to another, depending on where SQL Server 6.5 and SQL Server 2000 are installed. The SQL Server Upgrade Wizard identifies the two servers as the export server and import server.
· For a one-computer upgrade, leave the import and export servers at their default values.
· For a two-computer upgrade, select the name of the computer with your SQL Server 6.5 server as the export server. To upgrade SQL Server from one computer to another, the two computers must be in the same network domain.
Important: The one-computer upgrade is the only method supported when upgrading a server used in replication. A two-computer upgrade is not supported for replication servers.
Export server (6.5) is the name of the SQL Server 6.5 server. This defaults to the name of the computer on which the SQL Server Upgrade Wizard is run, but may be changed if your SQL Server 6.5 server is on another computer.
·
Server name
Server name is the name of your SQL Server version 6.5 server. This defaults to
the name of the computer on which the SQL Server Upgrade Wizard is run, but may
be changed if your SQL Server 6.5 server is on another computer.
·
Administrator password ('sa')
Enter the system administrator (sa) password for the SQL Server 6.5 server.
·
Optional startup arguments
Enter any trace flags or other startup parameters to be used when the SQL
Server Upgrade Wizard starts the SQL Server 6.5 server.
The import server is the name of the SQL Server 2000 server. This is always the name of the computer on which the SQL Server Upgrade Wizard is run.
·
Server name
Server name is the name of your SQL Server 2000 server computer. This is always
the name of the computer on which the SQL Server Upgrade Wizard is run.
·
Administrator password ('sa')
Enter the system administrator (sa) password for the SQL Server 2000 server.
Unless you have changed it since installing SQL Server 2000, the default sa
password is blank.
·
Optional startup arguments
Enter any trace flags or other startup parameters to be used when the SQL
Server Upgrade Wizard starts the SQL Server 2000 server.
The SQL Server Upgrade Wizard requires the selection of a scripting code page, which is used to create the upgrade scripts. When the Code Page Selection screen appears in the Upgrade Wizard, most users can accept the default code page, which is the code page recorded in the master database.
In some cases, the actual code page used for a SQL Server 6.5 installation differs from the code page recorded in the master database. If you know that the actual code page is different from the recorded code page, select the actual code page in the list on the Code Page Selection screen.
Caution: If you choose a scripting code page other than the default, do not upgrade replication settings. If the server is involved in replication, reconfigure the replication settings after the upgrade is complete.
The enhancements to collation settings in SQL Server 2000 do not apply directly to this selection of a code page for the SQL Server 6.5 upgrade.
When running the SQL Server Upgrade Wizard, you can choose to upgrade some or all SQL Server 6.5 databases. The master, msdb, and publication system databases, as well as the pubs and Northwind sample databases, are not explicitly available for selection. However, the master, msdb, and publication databases can be selected for upgrading (the default) in the Server Configuration dialog box of the SQL Server Upgrade Wizard.
Note: If you run the SQL Server Upgrade Wizard again after databases have been upgraded, previously updated databases will default to the excluded list. If you want to upgrade a database again, drop the database in SQL Server 2000 and move it to the included list in the wizard.
Before any data is transferred, the SQL Server Upgrade Wizard creates, if necessary, database and log files large enough to contain the upgraded database data. On the Database Creation screen there are several options for creating the SQL Server 2000 database and log files.
The SQL Server Upgrade Wizard estimates how much disk space is necessary to hold transferred objects and data for each selected database and creates database files of the estimated sizes. The wizard makes no allowance for free space beyond the loaded data. By default, the data file for a database is placed in the same location as the first device used by that database in SQL Server 6.5.
The SQL Server Upgrade Wizard also creates a log file for each database using the SQL Server 6.5 log size. By default, the log file is placed in the same location as the first device used for log space in SQL Server 6.5.
You can view and edit the default database configuration in the SQL Server Upgrade Wizard. For each database and log file, you can modify:
· The name and file path.
· The initial size of the file.
· The autogrow increment.
If using multiple devices in a SQL Server 6.5 database, multiple database files are created in the same location. However, the first database file is sized to accommodate the bulk of the data, and the other files are minimally sized. If you want to remove these files, you must do so before they are created. All files are set to grow automatically if extra space is required.
You can specify a custom configuration in two ways:
·
Using databases and logs that you created in
SQL Server 2000.
The SQL Server Upgrade Wizard does not create any
user databases. You must create the necessary databases and logs in SQL Server
2000 before you start the SQL Server Upgrade Wizard. Use this option only if
necessary.
·
Using an SQL script file that you provide.
The SQL Server Upgrade Wizard uses an SQL script
file that you provide to create the necessary user databases and logs. Use this
option only if you are familiar with the new CREATE DATABASE statement in SQL Server
2000.
If you create the user databases or an SQL script file, the SQL Server 2000 databases must have the same names as in SQL Server 6.5. Also, remember that data may take up more disk space in SQL Server 2000 than in SQL Server 6.5. The SQL Server Upgrade Wizard estimates this growth. You can view the proposed layout of the SQL Server 2000 data files to see the estimated initial size of the SQL Server 2000 database, and edit the default configuration, if necessary.
It is recommended that you leave the autogrow feature on for each database. You may also want to set a backward compatibility level for each database.
Note: To run the SQL Server Upgrade Wizard, you must have an instance of SQL Server 2000 already installed on your computer.
1. In the Database Creation dialog box of the SQL Server Upgrade Wizard, click Edit.
2. Click Advanced to view object details and drive summaries.
3. In the Proposed database layout box, double-click a database file.
4. Change any database file attributes, and then click OK.
5. View the changes to the drive summary.
6. When all changes have been made, click Accept to save the database configuration.
The Proposed Database Layout dialog box lists the databases, file groups, and data files that the SQL Server Upgrade Wizard will create. You can create or remove file groups and data files from the File menu. Double-click a data file to edit the file name, initial size, or file growth details.
Click on a file group or data file in the proposed database layout to view details. Click a database in the proposed database layout to view summary information.
The drive summary lists all local fixed-disk drives. For each drive, the existing SQL Server 6.5 data file size, proposed SQL Server 2000 data file size, and free space are listed. On the Options menu, select Freespace includes 6.5 files to view the free space that would exist if the SQL Server 6.5 data files were deleted. This option shows the disk space available if the upgrade is performed using tape and the SQL Server 6.5 devices are deleted.
When you perform a tape backup, you must select a tape drive and choose how the SQL Server Upgrade Wizard handles backing up and deleting objects in the SQL Server 6.5 databases.
The SQL Server Upgrade Wizard transfers all of the data you are upgrading to this tape drive before the SQL Server 2000 databases are created.
You may also choose to back up the SQL Server 6.5 devices. This is separate from the transfer to tape that the SQL Server Upgrade Wizard uses to complete the upgrade. There are two options for backing up the devices:
·
Prompt me to backup my devices manually
Before data is exported, the SQL Server Upgrade Wizard pauses and prompts you
to perform a backup. The SQL Server Upgrade Wizard does not perform a backup
for you. You must use a backup utility such as Windows NT Backup.
·
Automatically copy device files to the
following location
Before data is exported, the SQL Server Upgrade Wizard copies the device files
to a shared network directory.
Warning: If you back up the devices to tape, remove the tape backup and insert a blank tape before continuing. Before the SQL Server Upgrade Wizard begins transferring data to the tape drive, it formats the tape in the drive. If you do not remove your tape backup, the SQL Server Upgrade Wizard overwrites it.
If you decide to delete your SQL Server 6.5 devices before creating the SQL Server 2000 databases, you can choose whether to be prompted before the devices are deleted. All of the SQL Server 6.5 device files will be deleted if you choose to delete devices, even if you are upgrading only one database. This will render the SQL Server 6.5 server unusable until the files are restored.
Note: If you choose not to delete the devices, you must have enough disk space for both the SQL Server 6.5 and SQL Server 2000 databases. If sufficient space is available, you should use a Named Pipe upgrade instead of a Tape upgrade.
On the System Configuration screen, you can set options for system objects to transfer, ANSI Nulls, and quoted identifiers.
When the SQL Server Upgrade Wizard upgrades the master database, it can upgrade several configuration options:
·
Server configuration
Logon information and remote logon registrations and server configuration
options relevant to SQL Server 2000 are transferred as part of the version
upgrade process. The SQL Server 6.5 configuration options not used in SQL
Server 2000 are not transferred.
·
Replication settings
All articles, subscriptions, and publications of
each selected database, including the distribution database, if any, are
transferred and upgraded.
·
SQL Executive settings
All tasks scheduled by SQL Executive are
transferred and upgraded so that the SQL Server 2000 can schedule and run the
tasks in SQL Server Agent.
Note: Upgrading replication or SQL Executive settings causes existing modifications made to the SQL Server 2000 replication or SQL Server Agent settings to be overwritten.
The ANSI_NULLS option controls both database default nullability and comparisons against null values. When upgrading SQL Server 6.5 to the SQL Server 2000, set the ANSI_NULLS option to ON or OFF.
When the SQL Server Upgrade Wizard creates the SQL Server 2000 database tables, the database default nullability determined by the ANSI_NULLS option is not an issue. All columns are explicitly qualified as NULL or NOT NULL based on their status in SQL Server 6.5.
The ANSI_NULLS option is important with regard to comparisons against null values, when the SQL Server Upgrade Wizard creates the SQL Server 2000 database objects. With ANSI_NULLS set to ON, the comparison operators EQUAL (=) and NOT EQUAL (<>) always return NULL when one of its arguments is NULL. With ANSI_NULLS set to OFF, these operators return TRUE or FALSE, depending on whether both arguments are NULL.
In SQL Server 6.5, the ANSI_NULLS option in objects, such as stored procedures and triggers, is resolved during query execution time. In SQL Server 2000, the ANSI_NULLS option is resolved when the object is created. You must choose the ANSI_NULLS option setting you want for all objects in the databases you are upgrading. The SQL Server Upgrade Wizard then creates all database objects using this ANSI_NULLS setting.
Note: Quoted identifiers are used by default in SQL Server 2000; that is, they are set to ON. This is different from SQL Server 7.0 where they were set to OFF by default.
The QUOTED_IDENTIFIER setting determines what meaning Microsoft SQL Server gives to double quotation marks ("). When QUOTED_IDENTIFIER is set to OFF, double quotation marks delimit a character string, just as single quotation marks do. When QUOTED_IDENTIFIER is set to ON, double quotation marks delimit an identifier, such as a column name. An identifier must be enclosed in double quotation marks; for example, if its name contains characters that are otherwise not allowed in an identifier, including spaces and punctuation, or if the name conflicts with a reserved word in Transact-SQL. Regardless of the QUOTED_IDENTIFIER setting, an identifier can also be delimited by square brackets.
The meaning of the following statement, for example, depends on whether QUOTED_IDENTIFIER is set to ON or OFF:
SELECT "x" FROM T
If QUOTED_IDENTIFIER is set to ON, "x" is interpreted to mean the column named x. If it is set to OFF, "x" is the constant string x and is equivalent to the letter x.
If the previous SELECT statement example were part of a stored procedure created when QUOTED_IDENTIFIER was set to ON, then "x" would always mean the column named x. Even if the QUOTED_IDENTIFIER setting was later switched, and set to OFF, the stored procedure would respond as if it were set to ON and treat "x" as the column named x.
When the SQL Server Upgrade Wizard re-creates database objects in SQL Server 2000, the QUOTED_IDENTIFIER setting determines how all of these objects behave. If all database objects were created in SQL Server 6.5 with the same QUOTED_IDENTIFIER setting, click that setting, either On or Off. If objects were created in SQL Server 6.5 with a mix of the two settings, or if you are unsure of the settings used, click Mixed.
With the Mixed option, the SQL Server Upgrade Wizard first converts all objects containing double quotation marks with QUOTED_IDENTIFIER set ON. The SQL Server Upgrade Wizard then converts any objects that failed to be created with QUOTED_IDENTIFIER set OFF.
Use this screen to view the summary of choices you have made.
Click View warnings and choices in notepad to open a text version of the upgrade script. If all options are correct, click Finish.
After you click Finish, this screen displays the progress of the upgrade.
Displays information about the current task and its progress toward completion. The information presented varies according to the type of task.
The SQL Server Upgrade Wizard adds each upgrade task to the list as it is started.
The SQL Server Upgrade Wizard displays the status (Running, Done, or Error) for each task.
The SQL Server Upgrade Wizard displays the time and date on which the task began.
The SQL Server Upgrade Wizard displays the time and date on which a completed or terminated task is finished.
Temporarily suspends the version upgrade process until you click Resume.
Cancels the currently running task and proceeds to the next task. Do not cancel a task unless you are certain the current task does not need to be completed before subsequent tasks are run.
Retries the current upgrade task. If a task ended in an error and you corrected the problem, the SQL Server Upgrade Wizard retries the current task.
Allows you to participate interactively in the version upgrade process and track the progress of the SQL Server Upgrade Wizard. The SQL Server Upgrade Wizard asks for confirmation between each step of the version upgrade process.
When upgrading to SQL Server 2000, you can upgrade servers in your organization one at a time; however, when servers are used for replication, you must upgrade the Distributor first, the Publisher second, and then Subscribers. Upgrading servers one at a time following this sequence is recommended when a large number of Publishers and Subscribers exist because you can continue to replicate data even though servers are running different versions of SQL Server. You can create new publications and subscriptions with servers running instances of SQL Server 2000, and still maintain subscriptions created in SQL Server 6.5 or SQL Server 7.0.
When using transactional replication, you can upgrade Subscribers before the Publisher.
You can upgrade replication servers running SQL Server 6.5 or SQL Server 7.0 to SQL Server 2000. If the server is running SQL Server 6.5, you do not need to upgrade it to SQL Server 7.0 before upgrading to SQL Server 2000.
Important: When upgrading servers configured for replication to SQL Server 2000, the database compatibility level must be set to 70 (version 7.0 compatibility) or later. If you have servers running in 65 (version 6.5) or an earlier compatibility level, temporarily change them to 70 or later during the upgrade process.
When the Publisher or Subscriber is running in 65 or an earlier compatibility level during upgrade to SQL Server 2000, error 15048 will be raised stating that the operation is supported only on SQL Server version 7.0 or SQL Server 2000.
If you are upgrading replication on a failover cluster, you must uncluster the previous installation before upgrading. Unclustering the previous installation means that you must delete all publications, remove replication, and reconfigure it after upgrading to SQL Server 2000. This will not be a requirement when upgrading SQL Server 2000 to future releases.
If you are using immediate updating with snapshot replication or transactional replication, changes to that feature in SQL Server 2000 will affect how you upgrade. Rows in immediate updating articles now use a uniqueidentifier column to identify versions, whereas in SQL Server 7.0, a timestamp column was used. In addition, the triggers generated for immediate updating have been changed, and the trigger generation code has been modified to accommodate queued updating. Because of these changes, additional upgrade steps are necessary.
If using immediate updating:
· Upgrade both the Publisher and Subscriber before replicating data.
· Drop the publication and all subscriptions to the publication.
· Use an ALTER TABLE DROP COLUMN Transact-SQL statement to drop the timestamp column from the tables on the Publisher and from the tables on the Subscriber that allow Subscriber updates.
· Re-create the publication and subscriptions. The system adds a uniqueidentifier column to the published table. That column is used for row versioning (to detect conflicts when receiving updates from the Subscriber).
Although it is recommended you upgrade both the Publisher and the Subscriber and then drop and re-create the existing publications, the Publisher and Subscribers can be upgraded in any order. If you need to reinitialize a Subscriber or add a new Subscriber, you need to drop and re-create the publication.
If using File Transfer Protocol (FTP), you should follow the recommended upgrade path, which ensures that Subscribers are able to obtain the necessary FTP information from the Distributor.
SQL Server 2000 stores FTP parameters as Publication Properties; you no longer need to administer them at the Subscriber for each subscription. When upgrading to SQL Server 2000, the FTP option in the Publication Properties is turned off, and you need to open the properties for each publication that uses FTP, and then reset the FTP parameters.
SQL Server 7.0 Subscribers will continue to locate FTP files using the FTP parameters stored in the Subscription Properties when using a Distributor running an instance of SQL Server 2000. However, Subscribers running an instance of SQL Server 2000 will not be able to obtain FTP information from Distributors running earlier versions of SQL Server.
Existing subscriptions using merge replication or transactional replication will be unaffected by this change unless you need to reinitialize or connect to the FTP site. The FTP parameters need to be specified before snapshot replication occurs, or replication agents will not be able to locate the snapshot files.
If errors occur while upgrading replication servers, they might be related to the database being offline or unavailable or a script may have failed.
It is recommended that you stop all data modifications at the replication server while it is being upgraded. When upgrading from SQL Server 6.5, you must run the Log Reader Agent and Distribution Agent before upgrading to make sure there are no replicated commands pending delivery to Subscribers.
Because you can upgrade servers running instances of SQL Server 2000 one at a time, you may have circumstances where servers in your replication topology are running different versions of SQL Server. You can replicate between different versions of SQL Server, but you are often limited to the functionality of the earliest version used.
Important: When upgrading from SQL Server 6.5 or 7.0 to SQL Server 2000, SQL Server Setup runs several *.sql replication scripts. Although the upgrade process can take several minutes and does not display progress notifications, you can view error messages in the *.out and *.err files located in the SQL Server Install directory.
SQL Server 2000 has built-in backward compatibility with SQL Server 7.0 and SQL Server 6.5. Backward compatibility modes enable your database applications to function in SQL Server 2000 as they did in SQL Server 6.5 and SQL Server 7.0.
SQL Server 2000 is compatible with SQL Server 7.0 in most ways. This section describes backward compatibility issues when upgrading from SQL Server 7.0 to SQL Server 2000.
When using the SQL Server client connectivity components from SQL Server 7.0 or earlier, you must set up an alias using the Client Network Utility before you connect to a named instance of SQL Server 2000. For example, on a SQL Server 7.0 client, to connect to a named instance of SQL Server 2000, you must add an alias that points to \\computername\pipe\MSSQL$instancename\sql\query. If you use an alias name of computername\instancename, clients can connect by specifying this name in the same way as SQL Server 2000 clients do. For the TCP/IP Sockets and NWLink IPX/SPX Net-Libraries, you must use the Client Network Utility to define an alias on the client that specifies the port address on which the named instance is listening.
When using Master Servers and Target Servers, SQL Server 7.0 cannot interoperate with named instances of SQL Server 2000. To use an instance of SQL Server 7.0 with an instance of SQL Server 2000 for MSX/TSX operations, you must use a default instance, not a named instance, of SQL Server 2000.
When running an instance of SQL Server version 6.5 on a server, you should be aware of the following issue: If you are upgrading from SQL Server 6.5 client software to SQL Server 2000 client software (and you have an application that uses the default Net-Library), you must use the Client Network Utility to make either Named Pipes or Multiprotocol the default Net-Library to make Windows Authentication connections.
SQL Server 2000 can operate in one of two security (authentication) modes:
· Windows Authentication Mode (Windows Authentication)
· Mixed Mode (Windows Authentication and SQL Server Authentication)
Mixed Mode allows users to connect using Windows Authentication or SQL Server Authentication. Users who connect through a Windows NT 4.0 or Windows 2000 user account can make use of trusted connections (connections validated by Windows NT 4.0 or Windows 2000) in either Windows Authentication Mode or Mixed Mode.
SQL Server Authentication is provided for backward compatibility. An example of SQL Server Authentication would be if you create a single Windows 2000 group, add all necessary users to that group, and then grant the Windows 2000 group login rights to SQL Server and access to any necessary databases.
ROWCOUNT is not supported for INSERT statements against remote tables in SQL Server 2000 when the database compatibility level is set to 80. For these INSERT operations, the SET ROWCOUNT option is ignored.
The ROWCOUNT setting for INSERT statements against remote tables was supported in SQL Server 7.0.
These server configuration options are not supported in SQL Server 2000.
|
default sortorder id |
resource timeout |
|
extended memory size |
spin counter |
|
language in cache |
time slice |
|
language neutral full-text |
unicode comparison style |
|
max async IO |
unicode locale id |
SQL Server 2000 provides the following recovery models to simplify recovery planning, simplify backup and recovery procedures, and to clarify tradeoffs between system operational requirements:
· Simple Recovery
· Full Recovery
· Bulk-Logged Recovery
Each model addresses different needs for performance, disk and tape space, and protection against data loss.
In SQL Server 7.0 and earlier, similar functionality was provided through the combined settings of the trunc. log on chkpt and select into/bulkcopy database options, which could be set using the sp_dboption stored procedure.
This table maps the settings of trunc. log on chkpt and select into/bulkcopy to the new recovery models.
|
|
And select into/bulkcopy is: |
|
|
FALSE |
FALSE |
FULL |
|
FALSE |
TRUE |
BULK-LOGGED |
|
TRUE |
TRUE |
SIMPLE |
|
TRUE |
FALSE |
SIMPLE |
Note: If you upgrade a database in which the trunc. log on chkpt and select into/bulkcopy options are set to TRUE, select into/bulkcopy is set to FALSE, forcing the database into the simple recovery model.
The trunc. log on chkpt and select into/bulkcopy database options are supported in SQL Server 2000 for backward compatibility purposes, but may not be supported in future releases.
In SQL Server 2000, the ALTER DATABASE Transact-SQL statement provides a SET clause for specifying database options, including recovery models.
These words are no longer reserved keywords in SQL Server 2000: AVG, COMMITTED, CONFIRM, CONTROLROW, COUNT, ERROREXIT, FLOPPY, ISOLATION, LEVEL, MAX, MIN, MIRROREXIT, ONCE, ONLY, PERM, PERMANENT, PIPE, PREPARE, PRIVILEGES, REPEATABLE, SERIALIZABLE, SUM, TAPE, TEMP, TEMPORARY, UNCOMMITTED, WORK.
These words are reserved keywords in SQL Server 2000: COLLATE, FUNCTION, OPENXML.
SQL Profiler extended stored procedures, such as xp_trace_addnewqueue and xp_trace_generate_event, are not supported in SQL Server 2000. They have been replaced by a set of new stored procedures and system user-defined functions.
In SQL Server version 7.0 and earlier, the default setting for SET QUOTED_IDENTIFIER in SQL Query Analyzer was OFF. In SQL Server 2000, the default setting in SQL Query Analyzer is ON, which is also the default setting for ODBC and OLE DB. Moreover, several new features in SQL Server 2000, such as indexed views and indexes on computed columns, require this option to be ON.
Note: If you use double quotation marks for strings when QUOTED_IDENTIFIER is ON, you will receive a syntax error.
To read character files created by earlier versions of DB-Library bcp in SQL Server 2000, use the -V switch.
For users who have database diagrams created with earlier versions of the visual database design tools:
If the first visual database tool that was used against a SQL Server 2000 database is a version earlier than the tools in SQL Server 2000, SQL Server Enterprise Manager will not be able to open or create a database diagram in that database. Any attempt to do so results in the error:
ODBC
error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'dbo.dt_getobjwithprop_u'.
Running the following script on the database allows SQL Server Enterprise
Manager to work with the database diagrams in that database:
alter table
dbo.dtproperties add uvalue nvarchar(255) nullAfter this script has been run, both the SQL Server Enterprise
Manager in SQL Server 2000 and the earlier versions of the visual database
tools can jointly access the database diagrams in the database. There are
additional issues to consider when using the earlier versions of the database
tools against a SQL Server 2000 database.
go
if exists(select * from dbo.dtproperties) exec('update dbo.dtproperties set
uvalue = convert(nvarchar(255), value)')
go
These are the backward compatibility issues for Data Transformation Services (DTS).
Some objects in DTS are extended in SQL Server 2000.
There are restrictions on using the Copy SQL Server Objects task (Transfer SQL Server Objects task in SQL Server version 7.0) when copying database objects between an instance of SQL Server 2000 and SQL Server 7.0.
DTS packages created on an instance of SQL Server 2000 cannot be loaded or run on an instance of SQL Server version 7.0 or earlier. If you attempt to do this, you may receive one of the following messages:
"Invalid class string."
"Parameter is incorrect."
Both messages indicate that the current server does not contain all the components necessary to load the package and cannot support objects defined in the DTS package, such as tasks and transformations.
However, if you receive one of these messages, you can still open and run the package on an instance of SQL Server 2000.
When using the Copy SQL Server Objects task and Copy Column transformation to copy non-Unicode data between an instance of SQL Server 2000 and SQL Server 7.0, issues arise when using different code pages and collations.
In SQL Server 7.0, you did not have to code "trusted_connection=yes" in your connection strings for ADO, OLE DB, or ODBC to obtain a trusted connection. If you did not specify a UID and PASSWORD, SQL Server would default to trying a trusted connection. In SQL Server 2000, you must code "trusted_connection=yes" to obtain trusted connection.
Some objects in SQL-DMO are extended in SQL Server 2000.
The SQL-SCM (Service Control Manager) API has been removed and is no longer supported.
For users of OLAP Services for SQL Server 7.0 who want to install or uninstall English Query, these issues apply:
· OLAP Services for SQL Server 7.0 must not be running during installation. Shut down the OLAP Services service before installing English Query. (See the Services application in Control Panel.)
· If you have installed OLAP Services for SQL Server 7.0 and you uninstall English Query, you must reinstall OLAP Services. Conversely, if you have installed English Query and you uninstall OLAP Services, you must reinstall English Query to maintain OLAP connectivity.
These issues do not occur with SQL Server 2000 Analysis Services (formerly OLAP Services).
SQL Server 2000 is compatible with SQL Server 6.5 in many respects. Most product functionality of SQL Server version 6.5 remains in SQL Server 2000. Most applications for SQL Server 6.5 work unchanged after the SQL Server Upgrade Wizard upgrades the database server to SQL Server 2000.
The SQL Server 2000 upgrade process:
· Adds functionality, either new to SQL Server 2000 or changed from earlier versions, which makes tasks easier to accomplish.
· Minimizes the time and effort needed to upgrade.
In some cases, compatibility issues can arise:
·
Configuration Options
Some server configuration options have changed.
·
SQL-DMO, Tasks, and Replication
Task, replication, and device objects have changed.
SQL Server 2000 uses jobs instead of tasks, and provides new system tables and
system stored procedures.
·
Replication and Triggers
Replication types that allow data modifications at
the Subscriber use triggers to track changes to published tables. If there are
triggers on your application that modify published tables, the sp_configure
server option nested triggers should be enabled. This option affects
tables used in merge replication or tables used in snapshot replication or
transactional replication with the immediate updating or queued updating
option. Before adding these types of replication to an existing database that
uses triggers, be sure your application works correctly with the nested
triggers option enabled. The nested triggers option is enabled by
default; however, if this option was disabled previously, you will need to
enable it again.
·
Segments and Devices
SQL Server 7.0 and SQL Server 2000 use files and
filegroups instead of segments and devices for storing indexes or tables.
Unless your application depends upon the physical layout of segments within
devices, this does not create compatibility problems for your application.
·
System Tables
If your applications depend upon accessing system
tables directly, the applications may need to be revised. It is recommended
that you use system stored procedures or information schema views.
Here are the SQL Server 6.x system tables that are not included with SQL
Server 2000.
|
master.dbo.spt_datatype_info |
sysprocedures |
|
sysbackupdetail |
sysrestoredetail |
|
sysbackuphistory |
sysrestorehistory |
|
syshistory |
syssegments |
|
syskeys |
systasks |
|
syslocks |
sysusages |
·
Backup and Restore
SQL Server 2000 uses BACKUP and RESTORE statements in place of DUMP and LOAD.
DUMP and LOAD are supported for backward compatibility, but with some
limitations.
·
System Stored Procedures
Some system stored procedures are no longer supported.
For more information, see the discussion of specific backward compatibility issues.
When running at its default settings, Microsoft SQL Server 2000 implements SQL-92 behaviors for some Transact-SQL statements whose behaviors differed from the standard in earlier versions of SQL Server. SQL Server 2000 also enforces reserved keywords that were not keywords in earlier versions of SQL Server. If upgrading existing systems with existing applications, you can use the database compatibility level settings to retain the earlier behaviors if your existing applications depend on those behaviors. This gives you time to upgrade applications in an orderly fashion. Most applications, however, are not affected by the changes in behavior and work at the SQL Server 2000 compatibility level.
The compatibility level is specified for each database using the sp_dbcmptlevel system stored procedure. The database compatibility level can be set to 60 (version 6.0 compatibility), 65 (version 6.5 compatibility), 70 (version 7.0 compatibility), and the default 80 (SQL Server 2000 compatibility). The effects of the compatibility level settings are generally limited to the behaviors of a small number of Transact-SQL statements that also existed in earlier versions of SQL Server. Even when the database compatibility level is set to 60 or 65, applications gain almost all of the benefits of the new performance enhancements of SQL Server 2000. Applications still benefit from features such as the improved query processor.
For installations of all instances of SQL Server 2000, the default level for all databases is 80. For upgrades from SQL Server 7.0 to SQL Server 2000, the default level for all databases is 80. For upgrades from SQL Server 6.5 and SQL Server 6.0 to SQL Server 2000, the existing default compatibility level is retained.
Important: The compatibility level for the master database is 80 and cannot be changed. If you have added any user-defined objects to master, you must ensure they work correctly at the 80 compatibility level.
The model database is set automatically to the SQL Server 2000 compatibility level during an upgrade. All new user-defined databases are created with the same compatibility level setting as model. If you do not want to use any SQL Server 2000 behavior in new databases created after an upgrade, use sp_dbcmptlevel to change the compatibility level setting in model.
Certain behaviors are not enabled at lower compatibility levels. For example, the keywords LEFT, OUTER, and JOIN are not keywords at compatibility level 60. This means the database compatibility level must be set to 65 or higher before the LEFT OUTER JOIN clause becomes valid. Before any applications can take advantage of features only available at a higher compatibility level, all applications using the database must be upgraded to work correctly at the higher compatibility level.
Likewise, setting the compatibility level of a database to 65 makes the database version-6.5 compatible, but does not necessarily provide version 6.5 behaviors. For example, when SET ANSI_PADDING is ON and you attempt to insert the strings 'abc' and 'abc ' into a primary key column, SQL Server 2000 considers the strings to be duplicates and does not violate the primary key constraint. In SQL Server 6.5, the two strings are considered to be unique and both insertions succeed. Setting the compatibility level to 65 does not force SQL Server 2000 to treat the strings as unique values.
Note: While running at compatibility level 60 or 65 preserves legacy behaviors on SQL Server 2000, support for these behaviors may be dropped in future versions of SQL Server. It is recommended that you plan to upgrade your applications to work correctly with the compatibility level set to 80 as soon as is practicable.
SQL Server 2000 adds many new features. Most of the changes are internal and will not affect your database scripts or applications. All Transact-SQL statements are compatible. However, administration tools or scripts should be updated to work with SQL Server 2000.
The backward compatibility topics in this section contain a detailed list of features and behaviors supported in SQL Server 6.5 that have changed and could possibly affect your administration tools or scripts. The backward compatibility level does not control these changes.
To indicate their potential effect on administration tools or scripts, feature changes have been grouped into four levels:
|
Level |
Consists of |
|
1 |
Administrative statements, stored procedures, or SQL Server items that have been removed from, or are no longer supported in, SQL Server 2000. Administrative tools or scripts using these items must be fixed prior to using SQL Server 2000. |
|
2 |
Important changes that produce different behavior from earlier versions of SQL Server. For example, items in this category are those that have changed behavior in data type conversion or usage of selected functions, changed behavior of clauses in selected Transact-SQL statements and stored procedures, changed column names in selected system tables, and changed behavior due to the database compatibility setting. |
|
3 |
Items supported for backward compatibility only. Any item included in this category is fully supported, but may be removed or unsupported in a future release. SQL Server 2000 provides features that accomplish these tasks more efficiently and have ongoing support. |
|
4 |
Minor changes that produce different behavior from earlier versions of SQL Server. For example, items in this category are either ignored or have one or more ignored parameters, changed byte lengths, added parameters or columns, or changed data type columns. |
For more details on these four levels of SQL Server backward compatibility, see the appendixes.
When using SQL Server 6.5 integrated security, SQL Server 6.5 did not report the Windows NT account used by a connection unless the system administrator activated the SET HOSTNAME TO USERNAME option in SQL Enterprise Manager.
The setting could also be activated through the SQL-DMO SetHostName property. With this setting in effect, these functions and columns returned the user's Windows NT account name instead of the network name of the client computer:
· Transact-SQL HOST_NAME() function
· hostname column in the result set returned by sp_who
· hostname column in sysprocesses
In SQL Server 2000, the loginame column in the sp_who result set contains the Windows NT account name for connections made using Windows NT Authentication. Applications needing the Windows NT account associated with a connection using Windows NT Authentication should reference this column.
SQL Server 2000 Enterprise Manager no longer presents the SET HOSTNAME TO USERNAME option. SQL Server 2000 ignores the setting of the SQL-DMO SetHostName property.
With SQL Server 2000 Enterprise Edition, SQL Server 2000 failover clustering provides high-availability support. For example, during an operating system failure or a planned upgrade, you can configure one failover cluster to fail over to any other node in the failover cluster configuration. In this way, you minimize system downtime, thus providing high server availability. To install, configure, and maintain a failover cluster, use SQL Server Setup, and be sure to first read the detailed information in SQL Server 2000 Books Online. This document is only intended to provide an introduction.
When you are upgrading to a SQL Server 2000 failover cluster, only one default instance is allowed. Use the Cluster Wizard in SQL Server 6.5 or SQL Server 7.0 to uncluster any existing SQL Server 6.5 or SQL Server 7.0 clustered instances before upgrading to SQL Server 2000. Then run SQL Server Setup on SQL Server 2000.
SQL Server 6.5 or SQL Server 7.0 failover clusters cannot exist on the same computer as a SQL Server 2000 failover cluster. In SQL Server 6.5 or SQL Server 7.0, in an active/active configuration or in an active/passive configuration where one server contains an unclustered SQL Server, there is a name conflict. Both servers are default instances.
Important: You cannot run the Cluster Wizard in SQL Server 6.5 or SQL Server 7.0 after SQL Server 2000 has been installed.
For SQL Server 2000, you must use a domain account for the services (SQL Server, SQL Server Agent, and all services in the clustered group). That domain account must be an administrator on all computers in the cluster if those computers are running on Windows NT Server 4.0, Enterprise Edition.
Note: If you are using replication on a SQL Server 6.5 or SQL Server 7.0 failover cluster and upgrading to a SQL Server 2000 failover cluster, you must uncluster the previous installation. Delete all publications, remove replication, and then reconfigure replication after upgrading. This will not be a requirement when upgrading from SQL Server 2000 in future releases.
1. Uncluster SQL Server version 6.5.
2.
Install a default instance of SQL Server 2000.
You must install the binaries to a local drive and use a cluster disk for the
data. This local drive is a path, which is a non-clustered disk valid on all
nodes of the cluster. On all nodes of the cluster, this drive must have at
least 300 MB of available space.
3. Run the SQL Server Upgrade Wizard to migrate your data into SQL Server 2000.
4. Uninstall SQL Server 6.5.
5. Run SQL Server Setup to upgrade your default instance of SQL Server 2000 to a SQL Server 2000 failover cluster.
Note: To upgrade from a SQL Server 6.5 active/active failover cluster (or any configuration where SQL Server exists on the second node), you must first convert one side of the failover cluster to a named instance of SQL Server 2000.
1. On Node 1, uncluster SQL Server 6.5. On Node 2, uncluster SQL Server 6.5.
2.
On Node 1, install a default (non-clustered)
instance of SQL Server 2000.
You must install the binaries to a local drive and use a cluster disk for the
data. This local drive is a path, which is a non-clustered disk valid on all
nodes of the cluster. This drive on all nodes of the cluster must have at least
300 MB of available space.
3. On Node 1, run the SQL Server 2000 Upgrade Wizard to migrate your data into SQL Server 2000.
4. On Node 1, uninstall the instance of SQL Server 6.5.
5. On Node 1, install a named, clustered instance of SQL Server 2000.
6. Run the Copy Database Wizard (CDW.exe) to migrate your SQL Server data (originally from SQL Server 6.5) to a named instance in a SQL Server 2000 failover cluster.
7. On Node 1, uninstall the default instance of SQL Server 2000.
8. On Node 2, install a default instance of SQL Server 2000.
9.
Run the SQL Server 2000 Upgrade Wizard to
migrate your data into SQL Server 2000.
You must install the binaries to a local drive and use a cluster disk for the
data. This local drive is a path, which is a non-clustered disk valid on all
nodes of the cluster. On all nodes of the cluster, this drive must have at
least 300 MB of available space.
10. On Node 2, uninstall the instance of SQL Server 6.5.
11. On Node 2, upgrade the default instance of SQL Server to a clustered instance.
Note: To upgrade from a SQL Server version 7.0 active/active failover cluster (or any configuration where SQL Server exists on the second node), you must first convert one side of the failover cluster to a named instance of SQL Server 2000.
1. On Node 1, uncluster SQL Server version 7.0. Reboot Node 1.
2. On Node 2, uncluster SQL Server 7.0. Reboot Node 2.
3. On Node 1, install a clustered, named instance of SQL Server 2000 as a virtual server. This is not an upgrade process, but a side-by-side installation of SQL Server 7.0 and SQL Server 2000. Do not install the data to the same location/disk as Node 2. If you do, when you attempt to upgrade Node 2 from a SQL Server 7.0 to a SQL Server 2000 installation, Setup will fail.
4. On Node 1, run the Copy Database Wizard (CDW.exe) to move all databases and related information from the SQL Server 7.0 installation into the clustered, named instance of SQL Server 2000.
5. On Node 1, uninstall SQL Server 7.0.
6.
On Node 2, upgrade SQL Server 7.0 to SQL Server
2000 as the default instance.
You must install the binaries to a local drive and use a cluster disk for the data.
This local drive is a path, which is a non-clustered disk valid on all nodes of
the cluster. This drive on all nodes of the cluster must have at least 300 MB
of available space.
7. On Node 2, upgrade the default instance of SQL Server 2000 to a clustered instance.
Note: Optionally, you could create two named instances of SQL Server 2000 and use the Copy Database Wizard to upgrade both SQL Server 7.0 installations to a clustered, named instance of SQL Server 2000. This will provide better consistency, because all references to clustered installations of SQL Server 2000 will be in the form of VirtualServer\Instance, rather than sometimes being just the servername, and sometimes both the servername and instancename.
1. On Node 1, uncluster SQL Server 7.0. Reboot Node 1.
2.
On Node 1, upgrade SQL Server 7.0 to SQL Server
2000 as the default instance.
You must install the binaries to a local drive and use a cluster disk for the
data. This local drive is a path, which is a non-clustered disk valid on all
nodes of the cluster. This drive on all nodes of the cluster must have at least
300 MB of available space.
3. On Node 1, upgrade the default instance of SQL Server 2000 to a clustered instance of SQL Server 2000.
When you install a SQL Server 2000 failover cluster, you must:
· Ensure that the operating system is installed properly and designed to support failover clustering.
· Consider whether the SQL Server tools, features, and components you want to use are supported with failover clustering.
· Consider whether failover clustering is dependent on the products you want to use.
· Consider how to create a new failover cluster, using the information in Books Online.
· Review the instructions for upgrading from a SQL Server 6.5 or SQL Server 7.0 cluster to a SQL Server 2000 failover cluster.
The upgrade from SQL Server 6.5 or SQL Server 7.0 to SQL Server 2000 requires time and planning. However, the improvements in SQL Server 2000 make the upgrade well worth the effort. By following the procedures outlined in this white paper, companies can deploy SQL Server 2000, including failover clustering, smoothly and efficiently.
For more information: http://www.microsoft.com/sql/ and SQL Server Books Online.
Backward Compatibility Level 1 consists of administrative statements, stored procedures, or Microsoft SQL Server items that were supported in SQL Server 6.5 but have been removed from, or are no longer supported in, SQL Server 2000. Administrative tools or scripts using these items must be fixed prior to using SQL Server 2000.
|
This subheading |
Relates to |
|
Backup and Restore |
BACKUP |
|
Configuration Options |
sp_configure
(backup buffer size,
backup threads, database size, free buffers, hash
buckets, LE threshold maximum, LE threshold minimum, LE
threshold percent, logwrite sleep, max lazywrite IO, memory,
open databases, procedure cache, RA cache hit limit, RA
cache miss limit, RA delay, RA pre-fetches, RA slots per
thread, RA worker threads, recovery flags, remote conn
timeout, SMP concurrency, sort pages, min memory per
query, index create memory, tempdb in ram, and user
connections options) |
|
Custom Sort Orders |
Character sets, sort orders, and Unicode collations |
|
Databases |
ALTER DATABASE |
|
Database Options |
sp_dboption (subscribe and no chkpt. on
recovery options) |
|
Data Access Objects (DAO) |
odbccmpt utility |
|
DBCC |
DBCC DBREINDEX |
|
DB-Library |
Two-Phase
Commit |
|
DECnet Network Library |
DECnet Sockets Net-Library |
|
Disk Commands |
DISK REINIT |
|
Disk Mirroring |
DISK MIRROR |
|
Indexes |
CREATE INDEX |
|
Open Data Services |
Windows NT
Component Services |
|
Program Group Tools and Utilities |
Client Network
Utility |
|
Replication |
Restricted
publications |
|
Security |
DENY |
|
Segments |
CREATE INDEX |
|
Services |
SQL Executive |
|
SET DISABLE_DEF_CNST_CHK |
SET DISABLE_DEF_CNST_CHK |
|
SET SHOWPLAN |
SET SHOWPLAN |
|
SQL Alerter |
SQLALRTR.exe |
|
SQL-DMO |
sqlole.dll |
|
System Stored Procedures (General Extended Procedures) |
xp_snmp_getstate |
|
System Stored Procedures (Replication) |
sp_replica |
|
System Stored Procedures (System) |
ALTER TABLE |
|
System Stored Procedures (Tasks) |
sp_addalert |
|
System Tables |
Information
Schema Views |
|
Transactions |
Data type conversions |
|
Utilities |
probe login |
Backward Compatibility Level 2 consists of important changes in Microsoft SQL Server 2000 that produce different behavior from earlier versions of SQL Server. For example, items in this category are those that have changed behavior in data type conversion or usage of selected functions, changed behavior of clauses in selected Transact-SQL statements and stored procedures, changed column names in selected system tables, and changed behavior due to the database compatibility setting. This topic covers backward compatibility details for these items.
|
This subheading |
Relates to |
|
Backup and Restore |
BACKUP |
|
Bulk Copy |
bcp Utility |
|
Configuration Options |
Setting
Configuration Options |
|
Database Pages and Extents |
Pages and Extents |
|
Data Types |
CAST and
CONVERT |
|
DB-Library |
dbcursorfetchex |
|
Empty Strings |
sp_dbcmptlevel |
|
Indexes |
CREATE INDEX |
|
INSERT |
sp_dbcmptlevel |
|
Keyset Cursors |
Keyset cursors |
|
LTRIM and RTRIM Trimming Functions |
LTRIM |
|
ODBC |
SQLGetDiagRec |
|
RIGHT |
Using
Identifiers |
|
Security |
GRANT |
|
SELECT |
SELECT |
|
SET SHOWPLAN |
SET
SHOWPLAN_ALL |
|
System Tables |
Information
Schema Views |
|
Table Hints |
DELETE |
|
Transactions |
SET TRANSACTION
ISOLATION LEVEL |
|
Triggers and System Stored Procedures |
sp_dbcmptlevel |
|
UPDATE |
@@ERROR |
|
UPDATETEXT |
UPDATETEXT |
|
Views |
DELETE |
Backward Compatibility Level 3 consists of items that were supported in Microsoft SQL Server 6.5 but are supported in SQL Server 2000 (and SQL Server 7.0) for backward compatibility only. Any item included in this category is fully supported, but may be removed or unsupported in a future release. It is recommended that, as time allows, the backward compatible item be replaced with the recommended item. SQL Server 2000 provides features that accomplish these tasks more efficiently and have ongoing support.
This topic covers backward compatibility details for these items.
|
This subheading |
Relates to |
|
Backup and Restore |
BACKUP |
|
Database Options |
sp_dboption
(publish option) |
|
DBCC |
DBCC NEWALLOC |
|
Devices |
Overview of SQL
Server Architecture |
|
Open Data Services |
srv_config |
|
Query Performance |
SUSER_ID |
|
Security |
GRANT |
|
SELECT |
FASTFIRSTROW |
|
SET SHOWPLAN |
SET
SHOWPLAN_TEXT |
|
System Stored Procedures (Extended) |
xp_grantlogin |
|
System Stored Procedures (System) |
sp_add_job |
Backward Compatibility Level 4 consists of minor changes in Microsoft SQL Server 2000 that produce different behavior from earlier versions of SQL Server. For example, items in this level are either ignored or have one or more ignored parameters, changes to byte lengths, added parameters or columns, or changed data type columns.
This topic covers backward compatibility details for these items.
|
This subheading |
Relates to these items |
|
Aliases |
Roles |
|
Backup and Restore |
RESTORE
HEADERONLY |
|
Configuration |
sp_configure
(media retention option) |
|
CREATE PROCEDURE |
CREATE TABLE |
|
Data Types |
decimal and numeric |
|
DATEPART and SET DATEFIRST |
SET DATEFIRST |
|
DBCC |
DBCC |
|
DBCS String Comparisons |
Unicode space characters |
|
DELETE and SELECT |
FROM |
|
Devices |
ALTER DATABASE |
|
Functions |
@@DBTS |
|
Global Variables |
Functions |
|
ODBC |
SQL_COPT_SS_PERF_QUERY_INTERVAL |
|
Rebuilding the master Database |
Rebuild Master Utility |
|
Rebuilding the Registry (Level 4) |
setup/t RegistryRebuild = On |
|
Replication |
Replication
Between Different Versions of SQL Server |
|
Security |
SYSTEM_USER |
|
SELECT |
SELECT |
|
Triggers and System Stored Procedures (System) |
CREATE TRIGGER |
|
UPDATE |
UPDATE |
|
Utilities |
SQL Query
Analyzer |
uuuu
This is a preliminary document and may be changed substantially prior to final commercial release. This document is provided for informational purposes only and Microsoft makes no warranties, either express or implied, in this document. Information in this document is subject to change without notice. The entire risk of the use or the results of the use of this document remains with the user. The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unpublished work. Ó 2000 Microsoft Corporation. All rights reserved.
Microsoft, SQL Server, Visual Studio, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.