Migration/upgrade backout plan
Posted on 2012-04-02
We will use database mirroring to migrate some very large and critical databases from SQL2005 to SQL2008R2 and also to new hardware and new OS with as little downtime as possible. I'm trying to develop a backout plan in case we need to revert back to the old SQL2005 environment days or even weeks after the migration. While I expect this scenario is highly unlikely, I need to have a fully developed plan for such an event.
At this point I've already rejected:
* reversing the mirror - can't mirror to an older version
* Transactional replication - many tables still don't have a primary key (vendor app)
* Snapshot replication - data is very large
* log shipping - not to an older version
At this point I'm thinking CDC may be a viable option. It looks like it captures the changes and places them into tables which we can use with a scheduled SSIS job to apply the changes periodically to the old databases.
Can anyone advise me regarding this idea? Is it viable? Is it advisable? Is there a better way that I'm missing?
Thanks for any help you can provide.