How to upgrade from SQL Server2000 to 2005

If you plan to upgrade your database from SQL Server 2000 to SQL Server 2005. You must test everything before you upgrade and prove that the application is stable. Even so, if any problems occur after the upgrade, you will still want to make sure you can still go back to the original environment and ensure that no data modifications are lost.

This article lists the methods that keep the data in the original data (SQL Server 2000) up-to-date until the new environment is proven to be the best.

The newest way to keep the original SQL Server environment:

In SQL Server, there are methods that can be used to replicate data modifications to another database:

1. Log Shipping

2. Copy Database task

3. Copy (transaction, snapshot)

4, SQL Tracking

5. Programming (triggers, DTS,BCP, etc.)

6. Third-party tools

Here are three ways to discuss this:

Log shipping

Can we transfer logs between a SQL Server 2005 database (the primary database) and a SQL Server 2000 database (from a database)?

I tried to find a positive answer to the question on the Internet, but I was unlucky. Then I try to creatively find a solution that uses the standard tools of the product itself. There's no door, God ... I can only use the WITH NORECOVERY in the second database to transfer logs from SQL Server 2000 to SQL Server 2005, there is no other way. Therefore, the answer is "no", using log shipping is unrealistic.

Copy Database

Unfortunately, when the Copy Database Wizard is turned on, when the source and target versions are different, you receive an error message and cannot continue.

Copy

Transactional replication

Transactional replication works between two versions. There are two issues with this solution:

There are some versions of SQL Server that cannot participate in the replication model as primary or distributor, as described in the book SQL Server Features Comparison.

A table that does not have a unique key defined cannot participate in this model.

snapshot replication

This solution works, but there are several exceptions. For example, if a table has a user-defined data type and must be created before the table is created, it will fail because there is no create type command in SQL Server2000.

SQL Tracking

Use SQL Server Profiler or SQL Trace to capture the workload and export it to a SQL script. Scripts can be run again from the database.

The problem with this solution includes:

1, the execution of the order is in a certain order. If a transaction is opened or closed in a separate execution, and the operation is not one of the series of commands, then the script cannot relate to it because the session cannot be identified by traces.

2. If there are differences in command syntax between two versions, execution from the database must fail.

Programming

If you have a small batch of databases to migrate, you might consider writing a database component to transfer data modifications.

Example:

· Use triggers-this can affect performance because triggers are part of a transaction.

· Use DTS or bcp to transfer data--a method that relies heavily on the size of the data.

Third-party tools

You can use Third-party tools such as log readers to perform business logs, read SQL commands in scripts, and then execute them from the database. Also, although I can't find one of these tools myself, there's definitely a tool in SQL Server 2005 that can back up the transaction logs and store them successfully in SQL Server2000.

Other

You can also innovate ...

For example, in some cases, you can transfer the log to a SQL Server 2005 database, change its compatibility level to 80, and then back up and store it again in a third database.

Conclusion

For critical databases, it is a good idea to keep the old version of the database to be upgraded, as well as the latest data modifications so that you can use it when you need to roll back.

But...... There is no "best solution" for everything. You have to analyze the characteristics and structure of your database and then decide on the best solution for your needs. Personally, I tend to think of replication as the quickest and most reliable solution.