Migration/upgrade backout plan

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.
LVL 2
Beth KludtIndependent Business OwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan McCauleyEnterprise Analytics ManagerCommented:
You're essentially tying to perform replication back to your old system without any of the drawbacks or caveats of actual replication - you can't use any of the native versions of SQL Server replication for the reasons you've mentioned (though perhaps snapshot is an option - it's a lot of data, but in absence of a better option, it's something to consider).

CDC would definitely work for what you're describing, though isn't without it's own problems in your situation (you'd have to apply the changes by hand, which would be a huge pain, very slow, and is prone to errors or problems applying them). It's really designed to be used to track changes for auditing, not for re-applying the changes elsewhere, though it can be used for that if you'd like. I'd advise against of it in favor of snapshot replication. Not sure how much data you're looking at, but you might want to consider a tool like Redgate's SQL Data Compare - it can be used to compare the contents of two databases and synchronize the data in the tables, generating the SQL statements needed to apply changes from one database to the other. It's not instant or ongoing, but you can easily run it a few times a day on your databases to keep them generally in sync, and the tool is only a few hundred dollars (and they offer fully-functional trial periods).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Beth KludtIndependent Business OwnerAuthor Commented:
Sorry for the delayed response.  I think my email must have sent the notification to junk mail.

The databases are very large but some of the largest tables can be "disconnected" so we can continue running the application while loading those tables.  Also, some of the large tables are not going to change and some of the data is batch loaded so we may be able to rerun the load process.  Bottom line: I think we're going to go with a piecemeal approach using a combination of SSIS, redoing batches and CDC as a last resort.

Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.