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:
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:
Snapshot replication
This solution works but with few exceptions. For example, if there are User-Defined-Datatypes assigned to the table and have to be create
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
d 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:
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.
[TABLE]
