Tip

SQL Server 2000 and 2005: Six compatibility concerns

SQL Server 2000 and 2005 can co-exist on the same machine. In Part 1: Installing SQL Server 2005 on a SQL 2000 machine, I listed some compatibility issues in a quick reference table. Below, I've mapped

    Requires Free Membership to View

out the specifics from that table as to interaction concerns between SQL Server 2000 and SQL Server 2005 upon upgrading.

  1. New SQL Server registration
  2. Multi Server Administration
  3. Replication
  4. Linked Server
  5. Backup and restore
  6. Log Shipping

1. New SQL Server registration

In Enterprise Manager:

Here's the error message when trying to add a SQL 2005 instance:

In Management Studio, however, the connection was successful.

2. Multi Server Administration

I have a new instance that I want to add to the maintenance plans. Can we do that via the Multi Server Administration option? Let's check out:

From Enterprise Manager:

When I try to define SQL Server 2000 as the master, SQL Server 2005 as the target, I get the following error message:

In Management Studio:

The error message:

3. Replication

From Enterprise Manager, in the publication's properties, I tried to push new subscription:

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

In Management Studio:

Add new subscriber:

Success.

Note: I tested few commands where the syntax has been changed. For example: INCLUDE in indexes, NOEXPAND hint, PIVOT and UNPIVOT, and to my surprise the data was perfectly replicated.

4. Linked Server

From Enterprise Manager:

I entered my SQL 2005 instance name and a security context:

The linked server was successfully configured. I also tried to run commands in the new linked server and they worked. I did the same thing from Management Studio and it works.

5. Backup and restore

Of course when backing up a SQL Server 2000 database, it can be restored in 2005. But after being "touched" once by SQL 2005 – even if the compatibility level stays as 80 – the database cannot be restored again in SQL 2000. This is a painful subject for the upgrade process because it does not allow to "roll back" to the old version using backups and restores.

When backing up a SQL 2005 database (compatibility=80), this is the error message you get when you try to restore it on SQL 2000:

6. Log Shipping

From Enterprise Manager, when trying to add destination databases for log shipping, the SQL Server 2005 instance names do not appear in the server name list:

From Management Studio, when trying to add a secondary SQL 2000 instance:

Conclusion

Both SQL Server 2000 and 2005 versions can beautifully co-exist on the same machine. There might be few issues at installation time, but they can be resolved. When trying to work between the two versions via the management tools, not everything works.

Intro: Compatibility of SQL Server 2005 and 2000 coexisting
Part 1: Installing SQL Server 2005 on a SQL 2000 machine
Part 2: SQL Server 2000 and 2005: Six compatibility concerns


ABOUT THE AUTHOR:   
Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.
Copyright 2007 TechTarget


This was first published in July 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.