1 / 49

Upgrading to SQL Server 2000

Upgrading to SQL Server 2000. Lesson 1: Preparing to Upgrade After this lesson, you will be able to Work with multiple versions of SQL Server Choose the appropriate upgrade process and method Determine whether any hardware or software upgrades are necessary

etta
Télécharger la présentation

Upgrading to SQL Server 2000

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Upgrading to SQL Server 2000

  2. Lesson 1: Preparing to Upgrade • After this lesson, you will be able to • Work with multiple versions of SQL Server • Choose the appropriate upgrade process and method • Determine whether any hardware or software upgrades are necessary • Prepare your existing installation for the actual upgrade

  3. Working with Multiple Versions of SQL Server on the Same Computer • SQL Server 2000 is designed to support multiple versions of SQL Server simultaneously on the same computer. • It accomplishes this in two different ways. • The first method is through the use of version switching, and the second method is through the use of multiple instances. Through the use of these two methods, SQL Server 6.5, SQL Server 7.0, and SQL Server 2000 can coexist on the same computer, although only two versions may be running at any given time (one of which must be SQL Server 2000).

  4. Version Switching • Version switching allows SQL Server 7.0 or SQL Server 2000 to be installed as the default instance on a computer on which SQL Server 6.5 is already installed. • You can then use the Vswitch.exe utility to switch between SQL Server 6.5 and either SQL Server 7.0 or SQL Server 2000 (but not both). • Using version switching allows you to switch between default instances, controlling which version of SQL Server (including any version-specific tool) is running as the default instance at any given point in time. • It does not allow multiple instances or versions to run simultaneously. The Vswitch utility is available from the Start menu, in the Microsoft SQL Server - Switch program group. • It is also available in the \Program Files\Microsoft SQL Server\Mssql\Binn folder.

  5. Named Instances • Using a named instance allows you to install and run SQL Server 2000 as a named instance on a computer on which either SQL Server 6.5 or SQL Server 7.0 is installed without performing a version upgrade of that installation. • This allows you to keep your existing version of SQL Server intact and running while also running SQL Server 2000 on the same computer. Installing SQL Server 2000 as a named instance on a computer on which SQL Server 7.0 is installed is the only way to maintain the default instance of SQL Server 7.0 on that computer. • When you install SQL Server 2000 as a named instance on a computer on which SQL Server 7.0 is also installed, all SQL Server 7.0 client tools are upgraded to SQL Server 2000 client tools for all instances. • Note You can install SQL Server 6.5 or 7.0 only as default instances. Only SQL Server 2000 can be installed as a named instance.

  6. Choosing the Appropriate Upgrade Process and Method • When you decide to upgrade SQL Server 6.5, you have only one upgrade process available. • However, you have several methods to choose from. Your need to have SQL Server 6.5 running simultaneously with SQL Server 2000 after the upgrade might determine this decision. • When you decide to upgrade SQL Server 7.0, you have two separate upgrade processes to choose between. Your need to migrate server and replication settings, as well as the need to minimize server downtime, might determine this choice. • You must also determine whether to use a default or a named instance for your SQL Server 2000 installation. Your need to have multiple versions of SQL Server running simultaneously on the same computer might determine this choice.

  7. SQL Server 6.5 • If you are using SQL Server 6.5 and you want to upgrade your installation to SQL Server 2000, you must perform a version upgrade using the SQL Server Upgrade Wizard (this requires SQL Server 6.5 SP5). • When you perform a version upgrade, you can choose to upgrade some or all of your user databases along with the system databases. The SQL Server Upgrade Wizard can transfer replication settings, SQL Executive settings, and most server configuration settings. • The upgrade process includes built-in recovery methods to restart and resume an upgrade if it fails during the upgrade process. During this upgrade process, SQL Server 6.5 must be offline and unavailable to users. To perform an upgrade from SQL Server 6.5, you must also install SQL Server 6.5 SP5 • Note If you merely want to migrate your data to SQL Server 2000 rather than upgrade your installation, you can use the DTS graphical tool, use the bulk copy program (Bcp) command-prompt utility, or perform a query between linked servers. These data transfer methods are covered in Chapter 7.

  8. The SQL Server Upgrade Wizard can upgrade a computer running SQL Server 6.5 to SQL Server 2000 using either the local hard drive or a local tape drive. • Using the local hard drive gives the best performance and is the most reliable. Use a tape drive only when you do not have sufficient hard drive space. • The SQL Server Upgrade Wizard can also upgrade from one computer running SQL Server 6.5 to another computer running SQL Server 2000. Regardless of the method you choose, the SQL Server Upgrade Wizard can only upgrade to the default instance; it cannot upgrade SQL Server 6.5 to a named instance. • After the SQL Server Upgrade Wizard finishes the upgrade, you will have two independent installations of SQL Server with two independent sets of identical data (at least initially). SQL Server 6.5 is not removed during the upgrade process (although all client tools are upgraded). You should verify the success of the upgrade before you remove the SQL Server 6.5 installation. • Note If you want to have SQL Server 6.5 running simultaneously with the SQL Server 2000 installation after the upgrade is complete (in order to verify the upgrade), you must upgrade SQL Server 6.5 from one computer to another.

  9. SQL Server 7.0 • If you are using SQL Server 7.0 and you want to upgrade your installation to SQL Server 2000, you have a choice of two processes. • Your first choice is to perform a version upgrade of SQL Server 7.0 to SQL Server 2000 using the SQL Server 2000 Setup program. • Version switching back to SQL Server 7.0 after the upgrade is not an option. During the version upgrade process, the Setup program replaces and overwrites your SQL Server 7.0 installation. • All program files (including all tools and utilities) are upgraded and all databases are converted to SQL Server 2000. Replication settings, SQL Server Agent settings, and most server configuration settings are retained. • However, SQL Server 7.0 Profiler traces and servers registered with SQL Server Enterprise Manager are not retained. During (and after) this upgrade process, SQL Server 7.0 must be offline and unavailable to users.

  10. Your second choice is to use the Copy Database Wizard to perform an online database and associated meta data upgrade of selected SQL Server 7.0 user databases. • Meta data information includes such things as logon information and user-specific objects associated with user databases. Performing an online database upgrade allows you to upgrade any or all of your user databases without having to shut down SQL Server 7.0 during the upgrade. • In addition, your SQL Server 7.0 installation remains intact after the upgrade. If you plan to use this process, you can install SQL Server 2000 as a named instance on the same computer that is running SQL Server 7.0 as the default instance. • In addition, the Copy Database Wizard can upgrade databases from remote servers. Finally, you can also schedule the online database upgrade to occur at a specified time because the Copy Database Wizard creates a DTS package that is scheduled as a job. DTS packages and jobs are covered in Chapter 7. • Note An online database upgrade does not upgrade SQL Server Agent or server configuration settings, and cannot be used with databases involved in replication.

  11. Replication Issues • When upgrading servers involved in replication, you must upgrade the server functioning as the Distributor first, followed by the Publisher and finishing with the Subscribers. • If you update servers in this sequence, you can continue to publish and replicate data during this process even though servers are running different versions of SQL Server. • If you are using the immediate updating functionality or are using File Transfer Protocol (FTP), additional upgrade steps are required. Refer to "Replication and Upgrading" in SQL Server Books Online for more detail. • Note For databases involved in replication, you must perform a version upgrade; on SQL Server 6.5, it must be a single computer version upgrade.

  12. Determining Hardware and Software Requirements • The process of upgrading an existing SQL Server installation to SQL Server 2000 has certain hardware and software requirements, in addition to the hardware and software requirements for installing SQL Server 2000. These requirements differ depending on the version of SQL Server being upgraded, and on the type of upgrade process being performed.

  13. SQL Server 6.5 • If you are upgrading a SQL Server 6.5 installation running on Windows NT 4.0 and plan to upgrade on the same computer, you must apply Service Pack 5 or later and Internet Explorer 5.0 or later to Windows NT 4.0 before upgrading to SQL Server 2000. • You must also apply SQL Server 6.5 Service Pack 5 or later to your SQL Server 6.5 installation. However, if you are performing a computer-to-computer upgrade, you need only apply SQL Server 6.5 Service Pack 3 or later to your SQL Server 6.5 installation. • Regardless of the upgrade method, the SQL Server Upgrade Wizard uses named pipes. SQL Server 6.5 and SQL Server 2000 must be set to use the default pipe (\\.\pipe\sql\query). Finally, upgrading SQL Server 6.5 to SQL Server 2000 requires available hard drive space equal to approximately 1.5 times the size of the SQL Server 6.5 user databases. This additional hard drive space is required only during the upgrade process. You can use the SQL Server Upgrade Wizard to estimate the amount of space required to complete the upgrade.

  14. SQL Server 7.0 • If you are upgrading a SQL Server 7.0 installation running on Windows NT 4.0 and plan to perform a version upgrade, you must apply Service Pack 5 or later and Internet Explorer 5.0 or later to Windows NT 4.0 before upgrading to SQL Server 2000. • You do not need to apply any service packs to SQL Server 7.0 prior to upgrading to SQL Server 2000. Performing a version upgrade of SQL Server 7.0 requires the use of named pipes. • SQL Server 7.0 and SQL Server 2000 must be set to use the default pipe (\\.\pipe\sql\query). However, an online database upgrade (using the Copy Database Wizard) does not require named pipes; rather, it will use any available Net-Library. • Finally, an upgrade of SQL Server 7.0 to SQL Server 2000 does not require any additional hard drive space, although the Copy Database Wizard might require additional space if the database is copied rather than moved.

  15. Preparing for the Actual Upgrade • Terminate all user activity in the database and obtain exclusive use of all files in the database. • Back up all system and user databases (including master) to ensure recoverability. • Run the appropriate Database Console Commands (DBCC) to ensure database consistency (such as DBCC CHECKDB). • For SQL Server 6.5, set the tempdb system database size to at least 10 MB (25 MB is recommended). • For SQL Server 6.5, verify that the master database has at least 3 MB of free space. • For SQL Server 6.5, verify that the master database contains logon information for all users. • For SQL Server 6.5, disable any startup stored procedures. If you do not disable them, the upgrade process might stop responding. • Disable all jobs. • Close all open applications, particularly all that are dependent on SQL Server.

  16. Stop replication and ensure that the replication log is empty. • Ensure that there is enough hard disk space available to perform the upgrade. • Make sure that you upgrade all databases that have cross-database dependencies at the same time. This will ensure that, for example, logon information for owners of objects with cross-database dependencies will be created, which, in turn, will ensure that their objects can also be created. • For SQL Server 6.5, disable any startup stored procedures. If you do not disable them, the upgrade process might stop responding. • Disable all jobs. • Close all open applications, particularly all that are dependent on SQL Server. • Stop replication and ensure that the replication log is empty. • Ensure that there is enough hard disk space available to perform the upgrade. • Make sure that you upgrade all databases that have cross-database dependencies at the same time. This will ensure that, for example, logon information for owners of objects with cross-database dependencies will be created, which, in turn, will ensure that their objects can also be created.

  17. Performing a Version Upgrade from SQL Server 7.0 • When you install SQL Server 2000 on a computer running SQL Server 7.0, you are given the option during setup to upgrade your SQL Server 7.0 installation to SQL Server 2000. After installation, there are several tasks that you should perform to ensure maximum performance from your upgraded installation. Finally, you need to understand that certain items are not upgraded at all and that other items must be upgraded separately • After this lesson, you will be able to • Perform a version upgrade of SQL Server 7.0 to SQL Server 2000 • Understand the tasks you should perform after the version upgrade is complete • Manually upgrade the Meta data Services Information Models and repository database

  18. Performing a Version Upgrade • You perform a version upgrade by running the SQL Server 2000 Setup program. When the Setup program detects an installed version of SQL Server, you are given the option to upgrade, remove, or add components to an existing instance of SQL Server on your computer. See Figure 4.1. • To upgrade your SQL Server 7.0 installation, choose the default instance on your computer and then choose to upgrade your existing installation. See Figure 4.2.

  19. After you choose to upgrade your SQL Server 7.0 installation, you define the authentication mode for the Setup program to use to connect to SQL Server 7.0. • The Setup program verifies that it can successfully connect using this connection information (starting SQL Server 7.0 if necessary). Next, you must choose the licensing mode (see Chapter 2). • Thereafter, SQL Server 7.0 is upgraded to SQL Server 2000. The system databases are upgraded using a series of scripts and the registry is updated. • MDAC 2.6 and client tools are also upgraded at this point, unless a previously installed SQL Server 2000 named instance already performed this task.

  20. Performing Post-Upgrade Tasks • After the Setup program completes the upgrade process, there are a number of tasks that the database administrator should perform. These are: • Review the SQL Server error logs and the Sqlstp.log file if troubleshooting is necessary. • Repopulate all full-text catalogs if full-text search is being used. The upgrade process disables all full-text catalogs because of a format change that occurs during the upgrade. Repopulation can be time-consuming, so planning an appropriate amount of time is important. Maintaining full-text catalogs is covered in Chapter 12. • Update statistics. This process can also be time-consuming on large databases, but using SQL Server 7.0 statistics with SQL Server 2000 could result in poor query performance. Updating statistics is covered in Chapter 12. • Registering servers. Servers registered with SQL Server Enterprise Manager for SQL Server 7.0 are not registered with the new SQL Server Enterprise Manager for SQL Server 2000 (other than the local instance that was upgraded). Registering servers is covered in Chapter 12.

  21. Manually Upgrading Meta Data Services Tables and the Repository Database • When you upgrade your SQL Server 7.0 installation to SQL Server 2000, the Meta Data Services Information Models required by DTS are not updated as part of this process. You must perform a manual update of the information to save and retrieve DTS package versions to and from Meta Data Services. This update modifies the Meta Data Services table structure to support the new functionality and features available with SQL Server 2000 and preserves existing repository data in the new table structure. The precise commands and syntax for manually upgrading the information model are available in the DTS Information model section of Books Online. • In addition, the repository database used by Meta Data Services must also be upgraded manually to take advantage of the new repository engine 3.0 installed during the upgrade to SQL Server 2000. The precise commands and syntax for manually upgrading the repository database are available in the "Upgrading and Migrating a Repository Database" section of Books Online. DTS packages, Meta Data Services, and the Meta Data Services repository are covered in Chapter 7.

  22. Performing an Online Database Upgrade from SQL Server 7.0 • When you choose to perform an online database upgrade, you use the Copy Database Wizard to upgrade one or more SQL Server 7.0 databases to an instance of SQL Server 2000. You can upgrade databases from instances on the local computer or from a remote computer. The database being upgraded can be copied or moved. After installation, there are several tasks that you should perform to ensure maximum performance from your upgraded installation. • After this lesson, you will be able to • Perform an online database upgrade from SQL Server 7.0 to SQL Server 2000 • Understand the tasks you should perform after the database upgrade is complete

  23. Performing an Online Database Upgrade • The Copy Database Wizard is used to perform an online database upgrade. It is available from several locations within SQL Server Enterprise Manager. You can launch it from the Tools/Wizards menu, by right-clicking an instance of SQL Server 2000 and clicking All Tasks, or by clicking the Wizards tab from the taskpad (if you have enabled the taskpad). See Figure 4.3. • When you run the Copy Database Wizard, follow these steps: • Launch the Copy Database Wizard, using one of the methods described previously. When the Welcome To The Copy Database Wizard appears, click Next. • From the Select A Source Server page, select a source server from which you want to move or copy a database. • Note Because the database upgrade process is transparent and the Copy Database Wizard is also used to copy and move SQL Server 2000 databases, terminology in the wizard only makes reference to copying and moving databases

  24. On the Select A Source Server page, you can connect to any SQL Server instance on the network (including any local instance). To list the currently active instances of SQL Server, click its associated ellipse button. When the Select Server dialog box is displayed, select the desired active server. Finally, you must connect using either a Windows or SQL Server login account that has system administrator privileges on the source server. See Figure 4.4.

  25. After you have selected the source server, you must select the destination server from the Select A Destination Server page. • Again, to list the currently active instances, click its associated ellipse button. The destination server does not have to be the instance of the server from which you are running the Copy Database Wizard. • However, when you are copying or moving a database between servers, the service account used by the SQL Server service on the destination server must be a domain user account to have the rights to copy files over the network. • The local system account has no such rights and therefore can only be used when the source and destination servers are on the same computer. Finally, you must connect using either a Windows or SQL Server login account that has system administrator privileges on the destination server. See Figure 4.5.

  26. Next, you must select a user database to move or copy on the Select The Databases To Move Or Copy page. You can move or copy multiple databases in one operation, by selecting the associated check boxes under the Move or Copy columns. • However, you cannot move or copy a database if a database with the same name exists on the destination server. You must resolve any name conflicts prior to running the Copy Database Wizard, because database names cannot be renamed during a move or copy operation. In addition, you cannot move system databases (only user databases are available to be moved).

  27. After you have selected the database you want to move or copy, you can change the default location for the data and log files on the Database File Location page. • You select a new location by clicking the Modify button. When the Database Files dialog box appears, you click the ellipse button next to the destination you want to change. When the CDW dialog box appears, select the new default location for the files. • You are also given the option, in the Destination Files column, to change the filename for the destination database files (but not the name of the database itself). The default location for the files is the Data folder for the instance to which the database is being copied or moved. • The Database Files dialog box also informs you regarding available disk space on the destination drive. See Figure 4.7. • By default, all logins for the databases being copied or moved, along with all logins for stored procedures, jobs, and user-defined error messages, are copied along with the database. However, you can modify this default to selected specific logins only on the Select Related Objects page. See Figure 4.8.

  28. Finally, on the Schedule The DTS Package page, you can schedule the copy or move operation to occur immediately, to run once at a specified date and time, or as a scheduled DTS package at a later time. • Be aware that the DTS package must be able to place the source database in single-user mode prior to copying or moving it, or it will terminate the processing of the DTS package with an error message. • If SQL Server Enterprise Manager (or any other client) is connected to the source server at the time the package runs, this open connection will prevent the package from running. See Figure 4.9.

  29. Performing a Version Upgrade from SQL Server 6.5 • You upgrade a SQL Server 6.5 installation by running the SQL Server Upgrade Wizard on a computer that has SQL Server 2000 installed as the default instance. An upgrade from SQL Server 6.5 to SQL Server 2000 is more involved than an upgrade from SQL Server 7.0 and is therefore more likely to have objects that could not be upgraded properly. This might require some troubleshooting. Finally, you need to be aware that because of changes in features, you might need to use backward compatibility levels to make the transition to SQL Server 2000 smooth. • After this lesson, you will be able to • Perform a version upgrade of SQL Server 6.5 to SQL Server 2000 • Troubleshoot a SQL Server 6.5 upgrade • Specify backward compatibility levels for upgraded databases

  30. Performing a Version Upgrade • To perform a version upgrade of SQL Server 6.5 to SQL Server 2000, you must run the SQL Server Upgrade Wizard from a computer on which you have installed SQL Server 2000 as the default instance. • You start the SQL Server Upgrade Wizard from the Microsoft SQL Server - Switch program group. This wizard is available on any computer that has SQL Server 2000 installed as the default instance. • This wizard (Upgrade.exe) is also in the Upgrade folder of your SQL Server 2000 installation. The upgrade folder location is \Program Files\Microsoft SQL Server\Mssql\Upgrade. See Figure 4.12. • NoteYou must use SQL Server authentication to connect to the SQL Server 2000 server. Because the default authentication mode of SQL Server 2000 allows only Windows authentication, you might need to change the SQL Server 2000 configuration to permit SQL Server authentication. This requires a restart of the SQL Server service. Changing authentication modes is covered in Chapter 10.

  31. Next, on the Data And Object Transfer page, the wizard prompts you to select the upgrade method you will use. You can perform a direct upgrade on the same computer using either named pipes or a tape device for the transfer of data (the tape device option will be available only if a tape device is detected on the local computer). • You can also choose to perform additional verification options. If you choose to have the SQL Server Upgrade Wizard validate the successful transfer of data objects, the wizard prepares a list of all objects in the SQL Server 6.5 databases and the number of rows in each table before the upgrade and then compares this list to a similar list after the upgrade. • The wizard reports any discrepancies. This verification is in addition to the reporting of any problem in the creation of database objects that is recorded by default in the output logs. Selecting this initial level of verification is highly recommended. The next level of verification is to perform an exhaustive byte-by-byte checksum verification on each column in each table to verify that no data values have changed. This level of verification substantially increases the time required for the upgrade. Errors occur only rarely, but if the time is available, this additional level of verification is also recommended. See Figure 4.14.

  32. Next, on the Logon page, you specify the name of the SQL Server 6.5 computer you want to upgrade (called the export server). The default is the computer on which you are running the wizard, but you can specify any other computer in the same domain. The server you are upgrading to must be the server on which you are running the wizard (called the import server). • Note If you are upgrading a database involved in replication, you must perform a single computer upgrade, and the database compatibility level must be set to 70 during the upgrade. • In addition, you must specify the password for the SQL Server administrator account (sa) for both the export and the import server. You can also provide optional startup arguments for the export and the import server. See Figure 4.15.

  33. After you provide the name of the server being upgraded and provide the authentication information and optional startup arguments for both the servers, the wizard displays a SQL Server Upgrade Wizard dialog box warning that both of the SQL Server installations need to be stopped and then restarted, using these authentication and startup parameters. • During this verification process, the wizard also obtains the code page used by SQL Server 6.5 from the master database. • Next, the Code Page Selection page appears. The Upgrade Wizard requires the selection of a scripting code page, which is used to create the upgrade scripts. Most users can accept the default code page. • The code page used in the upgrade scripts must match the code page of the database being upgraded. See Figure 4.16.

  34. You then select databases to upgrade on the Upgrade Databases To SQL Server 2000 page. You can choose to upgrade some or all user databases. Notice that the master, msdb, publication, pubs, and Northwind databases are not available for selection. Only the model database and any user-created user databases can be selected for upgrading. It is recommended that you upgrade all databases at the same time. See Figure 4.17.

  35. After you select the databases to upgrade, the wizard examines the layout of SQL Server 6.5 devices. It uses this information to create database files in SQL Server 2000.

  36. Next, the Database Creation page appears. The wizard creates data and log files for the databases being upgraded. The data files are sized to hold all transferred objects and data, with no allowance for additional free space. • The log files are sized based on the size of the SQL Server 6.5 log files. The locations of the data and log files are the same as for the first device for data and logs in SQL Server 6.5. • If multiple devices were used in SQL Server 6.5, multiple files will be created in SQL Server 2000, but the initial file is sized to contain the objects and data with additional files sized minimally. • On the Database Creation page, you can specify a custom configuration of database files and logs by editing this default configuration, using databases previously created for this purpose in SQL Server 2000, or by using Transact-SQL scripts. • See Figure 4.18. When you click the Edit button on the Database Creation page, the SQL Server Upgrade Wizard dialog box appears. In this dialog box, you can modify the name, file path, and initial size of the file, as well as the autogrow increment. See Figure 4.19.

  37. Next, on the System Configuration page, you select system objects to transfer. These include server configuration information (such as local information and remote logon registrations), replication settings (including all articles, publications, and subscriptions) and SQL Executive settings (including all tasks and schedules). • In addition, you must set the ANSI_NULLS and the QUOTED_IDENTIFIER settings. The wizard uses these settings for all database objects it creates. • Refer to the topic "System Configuration" in Books Online for more information regarding these advanced settings. The choices you make for these settings will vary based on your existing SQL Server 6.5 databases and how you created objects within them. See Figure 4.20.

  38. Finally, the Completing The SQL Server Upgrade Wizard page appears. A summary of your choices and any warning messages are listed here. Click the Finish button to begin the upgrade. See Figure 4.21.

  39. While the upgrade is in process, the wizard displays each step in the process in the SQL Server Upgrade Script Interpreter dialog box and then notifies you when the upgrade is complete. If an error occurs, details of the error are displayed. See Figure 4.22.

  40. Troubleshooting a SQL Server 6.5 Upgrade • If the SQL Server Upgrade Wizard encounters problems during the upgrade, either you are notified during the upgrade of the problem or you must look in the upgrade logs created by the wizard specifying any problems it encounters. • Typical problems are an inability to create objects and tables (you will have to create these manually after the upgrade). The possible reasons for this include: • Text is missing from the syscomments table. • Objects were renamed using sp_rename (the syscomments entry is not updated when the object is renamed). • Stored procedures were embedded within other stored procedures (no entry exists in syscomments for these stored procedures).

  41. Table and views have NULL column names (the wizard cannot script these objects). • Tables were created on behalf of a user that does not have CREATE permissions. • A stored procedure modifies a system table or references a system table that does not exist in SQL Server 2000. • Another problem that can occur is having a computer name that does not match the server name returned by @@SERVERNAME. Use the sp_dropserver and sp_addserver system stored procedures to change the server name returned by @@SERVERNAME to match the computer name.

  42. Specifying a Backward Compatibility Level for Upgraded Databases • When you upgrade databases from SQL Server 6.5 to SQL Server 2000, it is likely that you will have objects in the upgraded databases that use features that have changed. Most applications are not affected by the changes in behavior. • However, in some cases, applications will need to be upgraded. SQL Server 2000 allows you to set a backward compatibility level to retain the earlier behavior while retaining almost all of the performance enhancements of SQL Server 2000. • This allows time for applications to be upgraded. The backward compatibility setting affects only a small number of Transact-SQL statements. As always, test your applications thoroughly after the upgrade. If you need to set a backward compatibility level for an upgraded database, use the sp_dbcmptlevel system stored procedure. Valid levels are 60, 65, 70, and 80. You can also set the compatibility level using SQL Server Enterprise Manager by right-clicking the desired database, clicking Properties, clicking the Options tab, and then selecting the desired compatibility level. • Note Microsoft might drop the 60 and 65 backward compatibility levels in future versions of SQL Server.

More Related