You are about to upgrade your databases from SQL Server 2000 to SQL Server 2005. You have tested everything before the upgrade and the applications have proven stable. But, you would still like to be sure that you can fall back to your old environment if any problems arise after the upgrade -- without losing any data modification.
This article highlights methods to keep databases in the old (SQL Server 2000) databases up to date until the new environment is proven to be robust.
Methods of keeping old SQL Server environment up-to-date:
In SQL Server, there are number of methods for duplicating data modifications to another database:
- Log shipping
- Copy database task
- Replication (Transactional, Snapshot)
- SQL traces
- Programming (Triggers, DTS, BCP, etc.)
- Third-party tools
Let's discuss these methods:
Log shipping
Can we log ship between a SQL Server 2005 database (primary) to a SQL Server 2000 database (secondary)...?
I tried to dig up a positive answer to this question on the Internet and had no luck. Then I tried to be creative and find a solution by myself using the standard tools that are shipped with the product. No way, Jose…I was only able to log ship from SQL Server 2000 to SQL 2005 using the WITH NORECOVERY state in the secondary database, but not the other way around.
So the answer is "No," using log shipping for that is not feasible.
Copy Database
Unfortunately, when starting the Copy Database Wizard, when the Source and Destination versions are different, you will get an error message and will not be able to proceed.
Replication
Transactional replication
Transactional replication is working between the two versions.
There are two problems with this solution:
- There are SQL Server editions that cannot participate in the replication model as PRIMARY or DISTRIBUTOR, as described in this article on SQL Server 2005 Features Comparison
- Tables without a defined Unique Key cannot participate in this model.
Snapshot ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
replication
This solution works but with few exceptions. For example, if there are User-Defined-Datatypes assigned to the table and have to be created before the table is created, this will fail because there is no CREATE TYPE command in SQL 2000.
SQL traces
The workload can be captured with the SQL Server Profiler or SQL Trace and exported to SQL script. The script can run against the secondary database.
Problems with this solution include the following:
- Commands are executed in a serial order. If a transaction was opened or closed in a separate execution than the commands it contains, the script will not relate them since "session" is not known by the Traces.
- >If there are syntax differences between the versions, the execution in the secondary database will fail.
Programming
If you have a small amount of migrated databases, you may think about programming database components to transfer data modifications.
Examples:
Using triggers – this may affect performance since triggers are part of the transaction.
Using DTS or BCP to transfer data – this method can be heavy depending on the data size.
Third-party tools
You can use third-party tools, such as Log Readers to read the SQL commands from the transaction log, script and then execute them in the secondary database. Also, although I couldn't find such a tool myself, there might be a tool that can backup transaction logs in SQL Server 2005 and restore them in SQL Server 2000 without problems.
Others
You can also be creative…
For example, in some cases you can log ship to a secondary SQL Server 2005 database, change its compatibility level to 80 and then backup and restore it to a third database.
Conclusion
For critical databases, it might be a good idea to keep the old version of an upgraded database with the latest data modifications in case there's need to fallback.
But…There is no "best solution" for that matter. You must analyze your database characteristics and structure and decide the best solution for your needs. Personally, I prefer replication for fast and reliable solution.
ABOUT THE AUTHOR:
Michelle Gutzait works as a senior databases 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 database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.
Copyright 2006 TechTarget
