Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Migration/upgrade backout plan

Posted on 2012-04-02
2
Medium Priority
?
579 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 1500 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

610 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