SQL Server Migration 2008 and Rollback plan RRS feed

  • Question

  • Hi,

    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.

    1. Take full backups of all users and system databases of 2000.
    2. Run the upgrade advisor on each 2000 server which has to be eventually migrated to 2005.
    3. backup-restore method /dettach - attach method of all application databases.
    4. Fixing of Orphan users
    5. Script jobs from 2000 server and excute the script on 2005 server.
    6. Setting up linked servers
    7. migrate the dts packages
    8. Perform unit testing at each level
    9. Replication setup
    10. Change the compatibility levels to 90
    11. rebuild all indexes
    12. Run dbcc checkdb, pagetorn....,DBCC UpdateUsage,update stats commands (health checks)

    and so on...

    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?
    I would like to know how do they normally plan for that?

    Thanks in Advance

    Wednesday, May 12, 2010 5:18 AM


  • First off, check out www.scalabilityexperts.com/ssua for another tool that may be helpful in your testing efforts. It was jointly developed by SE and MS to help customers test for application compatibility issues before upgrade.

    Reverting to the previous version of the system is always a tricky thing and it involves both DB and application - remember that you may have had to make some application code changes to run on SQL Server 2008 and those changes may not work on Shiloh.

    As for how you would actually make the switchback, well, depends on how much time  you can spare. That means the size of your database and available hardware/infrastructure/people comes into play also. There used to be quite a few vendors that provided log readers for SQL Server so you could run both versions of SQL Server in parallel until you're comfortable with the new system but most of them stopped development after 2005. I think APEX SQL is the only one left (if they're still around) that has a log reader for SQL Server. That allows you to apply transactions from 2008 to 2000/2005.

    Alternatively, you'd have to do some type of data export/import to get the database back in sync or implement replication. Neither are trivial approaches. If you use SSIS/BCP, you have to figure out where/when to stop the data movement. Same thing with replication. All that while dealing with DRI and potentially other constraints. See why I ask about time? :-)

    One little thing that helps significantly reduce risk is scripting. Don't depend on chasing the mouse around to do anything. Too big a risk of missing a step or accidentally clicking the wrong button (even if you documented everything in detail, down to how many inches to move the mouse, left-click, etc...).

    Btw, another popular approach is hire a vendor to do it. That way, most (not all) of the arrows will be pointed at the vendor's back. :-D

    I hope other folks on the board have smarter/easier ways of doing this. We have to deal with this quite bit a too.



    No great genius has ever existed without some touch of madness. - Aristotle
    Thursday, May 13, 2010 8:52 AM