• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

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.
0
Beth Kludt
Asked:
Beth Kludt
1 Solution
 
Ryan McCauleyCommented:
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
 
Beth KludtAuthor 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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now