Solved

Migration/upgrade backout plan

Posted on 2012-04-02
2
541 Views
Last Modified: 2012-04-17
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
Comment
Question by:Beth Kludt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 37814013
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
 
LVL 2

Author Closing Comment

by:Beth Kludt
ID: 37858695
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I find this data? 3 31
Need help with a query 14 40
Split string into 3 separate fields 5 22
Using datetime as triggers 2 27
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question