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.