Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2010-05-12 : 01:10:47
|
| I would like to know, how do DBA's plan for Rollback plan for migration.Assuming for the first time am going for SQL 2000 to SQL 2008 migration. I have taken all the sql 2000 system and app backups. Run the upgrade advisor. Assuming that we have fixed all the issues which will not support in SQL 2008 and whatever which is breaking the Application.Suppose, we have tested everything and we planned to GO LIVE!. Till that time , we will keep the SQL 2000 instances/databases in READ_ONLY mode and point our Application to the new SQL 2008 instances. Assuming that we have migrated to SQL 2008 and customers started using the Application. Assuming, a scenario wherein after 5 days something has broke the Application. In such a scenario how to have a Rollback plan for safe side.As per my knowlege we cannot restore the SQL 2008 backups on to SQL 2000 instances nor we can apply the SQL 2008 Txn'l backup on SQL 2008.How to recover or make the customer in safer zone without loosing the 5 days data??How does a DBA can plan or prepared for such a scenario?????This was a question normally asked by the management, and if that is the case how can a DBA can come up with a solution?Thanks in Advance |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 03:25:26
|
| We would not have a rollback plan. Testing has proven that we can do forwards. If there is an error during the actual upgrade (something unforeseen) then we would revert to the original server, but otherwise we are going forward and will fix whatever problems arise.If I did need a rollback plan I would be copying changed data from NEW back to OLD server on an ongoing basis, so that old server mirrored New server. I think I would do this as a batch job (rather than setting up replication) as we have scripts that will transfer only changed rows between two database (but you could use RedGate Compare or somesuch)We always upgrade hardware with SQL major versions, so have the old server "available" (and typically we migrate the databases one-by-one) - not to say you couldn't do that on a single machine, but we prefer to start-over. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-05-12 : 04:33:05
|
| I'd say that if you for some reason need to go back after 5 days you've done a really poor job planning the upgrade! Something might go wrong during the actual upgrade and then you will for sure need a fallback plan but after 5 days you keep going forward. A part of your migration plan should include a point of no return, where you say that whatever goes on from here on we fix on the new system instead of reverting to the old. If management do require a fallback after 5 days then I guess Kristens with some sort of replication scheme is the only option. I'd probably go for BCP'ing out changed data to flat files and importing it to the old server on a frequent basis...every 15 mins or something.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 04:46:57
|
BCP out changed data is not entirely straightforward. Deleted rows for example ...We have a "changed data XFER script" (mechcanically generated, but like I say RedGate Compare would do similar job) which does:DELETE DFROM DestinationTable AS D LEFT JOIN SourceTable AS S ON S.ID = D.IDWHERE S.ID IS NULLUPDATE USET Col1 = S.Col1, ...FROM DestinationTable AS D JOIN SourceTable AS S ON S.ID = D.IDWHERE (D.Col1 <> S.Col1 COLLATE SomeBinaryCollation OR (D.Col1 IS NULL AND S.Col1 IS NOT NULL) OR (D.Col1 IS NOT NULL AND S.Col1 IS NULL)) AND ....-- SET IDENTITY_INSERT DestinationTable ON (if this table has IDENTITY column)INSERT INTO DestinationTable( Col1, Col2, ...)SELECT * -- Requires complete column list if table has an IDENTITY columnFROM SourceTable AS S LEFT OUTER JOIN DestinationTable AS D ON D.ID = S.IDWHERE D.ID IS NULL-- SET IDENTITY_INSERT DestinationTable OFF (if this table has IDENTITY column) Deletes are done in FKey order, and inserts in opposite FKey order. Even so there are often some chicken-and-egg FKey scenarios - might be better to DROP FKeys during the "stand-by-fallback" period, and reinstate them just before Fallback goes live. Non-clustered non-PK indexes too (for speed) maybe |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-05-12 : 04:52:21
|
| Good point about deletion Kristen, didn't think about that! Now another thought...could a sql server trace do the trick? I just got it off the top of my head, but I guess you could save all queries to a file or something and replay it at the old server. It could potentially mess up identity-columns and such but this is more or less how replication in MySQL works and it is actually quite efficient. Hmmm..- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 05:03:16
|
For a hopefully-never-can-happen scenario I would go for light-weight and try to avoid using anything "hybrid" - i.e. at variance from normal working. If the darn thing fails you want to be 100% sure you have a migration path back again.Clients ask about rollback for upgrades where we have added . changed column type significantly. Sure we can remove the new columns/data, but users will have restructured the way they have entered data, embracing the new fields, such that the record with those new fields removed will no longer be "sensible" when standalone. That's my "hybrid" scenario, which we avoid in such situations (usually by saying "Its go forwards only"  If you are going to have a fall back then it needs to be tested too, during QA phase. Depends on the critical-ness of the data, but its a lot of extra testing work to be 100% you have a reverse-direction route (particularly if structure of database has changed too, rather than just SQL Version) |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-05-14 : 02:14:30
|
| Thanks for the open suggestions. |
|
|
|
|
|
|
|
|
SQL Migration