Graphics and Text parsing SQL Server 2005 and 2000 compatibility

It seems that you have decided to upgrade from SQL Server 2000 to SQL 2005.

You may have installed an instance of SQL Server 2000 in your desktop and test environment and want to upgrade it appropriately. Or you might just want to install a new instance of SQL Server 2005. This article will focus on the problems you may encounter when installing a new instance, and this article will analyze the options for interacting with two versions (such as connecting servers, multiple server management, log delivery).

Install SQL Server 2005 on the SQL 2000 host

If you want to install SQL Server 2005 on your SQL Server 2000 host, you should have at least a service Pack 3a installed on your host, preferably to install SP4.

When you install SQL Server 2005 on a SQL 2000 host, you may experience the following problems:

The Setup Wizard does not recognize the default instance of SQL Server 2000. Because the Setup wizard may not recognize the default instance of SQL Server 2000, he may ask you to install the default instance of SQL Server 2005. Although the installation was completely successful, the new 2005 instances were not actually installed. To do this, you must uninstall and reinstall the 2005 instances, and in the new installation process, enter a named instance.

The SQL Server 2000 agent cannot be restarted. After a full installation, the agent for SQL Server 2000 may not be able to reboot, and in general, it is possible to re-enter the password on the proxy server to resolve the problem.

After 2005 is installed, 2000 of Enterprise Manager cannot work while the database server is running. If Enterprise Manager does not work after installing 2005, refer to the method described by Microsoft in SQL Server co-exist and SQL 2005.

1. Click Start, click Run, enter regedit and click OK.

2. Find the following in the registry

　　

Hkey_local_machinesoftwaremicrosoftmicrosoftsqlserver80toolsclientsetup

3. Right-click the client Installation subkey, select New, and then click on the string value.

4. Name the subkey you created as SQLPath, and then press ENTER.

5. Double-click the SQLPath subkey, and then enter C:Program FilesMicrosoft SQL server80tools in the Numeric Data dialog box.

6. Click OK, and then close the Registry Editor.

7. Click Start, click Run, enter regsvr32 "C:Program filesmicrosoft SQL Server80ToolsBinnsqlmmc.dll", then click OK.

SQL Server Management Studio is not installed. Before you install SQL Server Management Studio, you need to first install the. Net Framework 2.0.

SQL 2000 and 2005 can coexist on the same host, but for the interaction between two versions, I have been asked the following question:

　　

	Problem	Error message when using the 2005 tool	Error message when using the 2000 tool	Possibility?
1	Registering a new SQL Server	Success	Connection to server 2005 cannot be established. Cause: [SQL-DMO] You must use SQL Server 2005 's administrative tools to establish a connection to this server.	Only from 2005 to 2000
2	Multi-Server Management	2005– primary server, 2000– target server: The primary server <2005 server> version 9.00.2047 is incompatible with the target server <2000 server>.	2000– primary server, 2005– target server: SQL Server registration failed because the connection shown below failed [SQL-DMO] You must use SQL Server 2005 's administrative tools to establish a connection to this server.	Can't coexist
3	Copy	Success	There is no error message, and the SQL Server 2005 instance does not appear in the list of possible read servers.	Only from 2005 to 2000
4	Connecting to a server	Success	Success	Can coexist
5	Database restore	Backup –2005, Restore – 2000: Backup Database version 611 has a ON-DISK structure, but the server supports version 539 and cannot restore or upgrade the database. RESTORE database does not exit normally.	Backup –2000, Restore – 2005: Success	Only from 2000 to 2005
6	Log delivery	Because the version is incorrect, ' second server name ' is not a valid server instance. The second server instance must be running on SQL Server 2005 or later.	The SQL Server 2005 instance does not appear in the list of possible second server instances.	Can't coexist

SQL Server 2000 and 2005: six compatibility issues

SQL Server 2000 and 2005 can coexist on the same host. In the first section: Install SQL Server 2005 on the SQL 2000 host, I list some compatibility issues by using a table. Below, I will explain the interaction between the upgraded SQL Server 2000 and 2005 listed in the table.

Registering a new SQL Server

Multi-Server Management

Copy

Connecting to a server

Backup and restore

Log delivery

1. Register a new SQL Server

In Enterprise Manager:

　　

The following illustration shows an error message when you try to add an instance of SQL 2005:

　　

But in Management Studio, the connection was successful.

　　

2. Multi-Server Management

I have a new instance that I want to add to the maintenance plan, can we implement it through the multi-server management option? Let's take a look at the specific actions:

In Enterprise Manager:

　　

When I tried to use SQL Server 2000 as the primary server and SQL Server 2005 as the target server, I got the following error message:

　　

In Management Studio:

　　

The error message is as follows:

　　

3. Copy

In the publishing properties of Enterprise Manager, I try to launch a new read-only server:

　　

As we expected, the SQL Server 2005 instance does not appear, so it cannot be used as a subscriber.

In Management Studio:

　　

To add a new subscriber:

　　

Success.

Note: Although some commands have a change in syntax. For example: Inclue,noexpand hints in the index, pivot and Unpivot, but surprisingly, the data was replicated successfully.

4. Connecting to the server

In Enterprise Manager:

　　

I entered the instance name and security context for SQL 2005:

　　

The connection server was configured successfully. I also tried to run the command on the connection server, working properly, and I got the same result when I used Management Studio.

5. Backup and restore

Backups of SQL Server 2000 databases can of course be restored to 2005. However, once you have used SQL 2005, the database can no longer be restored to SQL 2000 even if the compatibility level is still 80. This is an upgrade, a very painful question to ask because you cannot backup and restore "rollback" to the old version.

After backing up a SQL 2005 database (compatibility level =80), if you try to restore it to SQL 2000, you get the following error message:

　　

6. Log Delivery

When you try to add a target database for log delivery in Enterprise Manager, the SQL Server 2005 instance name does not appear in the list of server names:

　　

When you try to add a second SQL 2000 instance to Management Studio, you get the following information:

　　

Conclusion

SQL Server 2000 and version 2005 can coexist well on the same host. Some of the problems that you may encounter during installation can be resolved. But if you try to interact with the two versions through the management tools, not all of the work can be done successfully.