Solved

Migration/upgrade backout plan

Posted on 2012-04-02
2
554 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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