Use Backup/Restore to Minimize Upgrade Downtimes

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    Comments posted to this topic are about the item Use Backup/Restore to Minimize Upgrade Downtimes

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Pyay Nyein

    Mr or Mrs. 500

    Points: 565

    Thanks for the good article, Jonathan. 🙂

  • Jagadish Kumar Punnapu

    SSChasing Mays

    Points: 645

    I sincerely appreciate this idea Jonathan...

  • Amit W

    Ten Centuries

    Points: 1018

    Thanks for the article!

    Just a thought...steps 1-7 can be done using setting up log shipping and the rest can be done using step 8 onwards when moving on to the new server.

  • publicbutler@imapmail.org

    Old Hand

    Points: 345

    I have used this method for years. Even with busy and/or large databases, I can move a database server to a new piece of hardware with only about 10 minutes of downtime. I recently used the same method during migration of a number of legacy SQL 2000 boxes to SQL 2005.

    - Jay

  • Noel McKinney

    Hall of Fame

    Points: 3377

    Good article Jonathan, I've used these steps to move databases to new hardware and datacenter with just a few minutes of downtime. The key is making sure that the application(s) that use the databases are good to go. If possible you need to test such applications on the new hardware first to make sure the configurations are set properly... restore a backup of an actual database for such testing, using a "test" database might not reveal potential problems.

    The other thing is to make sure that logins have been propagated to the new server.

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    we're usually not as nice

    if we do a side by side upgrade we tell people the cut off for R/O access. then we backup and restore on the new hardware. critical databases are on the SAN and we just point people to a replicated copy of the data while we upgrade the hardware

  • GRE (Gethyn Ellis)

    SSCrazy Eights

    Points: 9486

    Noel McKinney (6/18/2009)


    Good article Jonathan, I've used these steps to move databases to new hardware and datacenter with just a few minutes of downtime. The key is making sure that the application(s) that use the databases are good to go. If possible you need to test such applications on the new hardware first to make sure the configurations are set properly... restore a backup of an actual database for such testing, using a "test" database might not reveal potential problems.

    The other thing is to make sure that logins have been propagated to the new server.

    The sp_help_revlogin stored proc could be useful for moving the logins.

    edited to add URL:

    http://support.microsoft.com/kb/246133

    Gethyn Ellis
    www.gethynellis.com

  • noeld

    SSC Guru

    Points: 96590

    Ermm... Isn't this just a description of LogShipping ?


    * Noel

  • GRE (Gethyn Ellis)

    SSCrazy Eights

    Points: 9486

    noeld (6/18/2009)


    Ermm... Isn't this just a description of LogShipping ?

    Yes it is, using a manual log shipping process to keep a large db up to date prior to the migration date. I used this many times and it does save alot time compared to 'doing it all the day'

    Gethyn Ellis
    www.gethynellis.com

  • SanjayAttray

    SSChampion

    Points: 13157

    noeld (6/18/2009)


    Ermm... Isn't this just a description of LogShipping ?

    Yep. it is.

    SQL DBA.

  • Adam Haines

    SSC-Insane

    Points: 23197

    Good job Jonathan 🙂

  • Ronzo

    Hall of Fame

    Points: 3730

    I'd probably add a step at the end.

    After the applications are up and you're sure they're all pointed at the new server and working fine, bring the old database back on-line (or restart the old SQL Server), and perform a final full backup. This is useful for ensuring you have a copy of the database as it existed in its old environment at the time of cutover. As you noted, sometimes the new database may not be restorable on the old server (different SQL versions). And depending on auditing requirements, you may need a restorable backup for off-site storage. It also gives you a picture of the data as it existed at the point of cutover.


    Have Fun!
    Ronzo

  • Cameron Mayfield

    SSC Enthusiast

    Points: 105

    Very good information. Thank you for sharing.

  • Robert Davis

    One Orange Chip

    Points: 28027

    If you are upgrading SQL 2005 to SQL 2008 or an earlier version of SQL 2005 to a newer version (such as SP1 to SP3), then you can do this with database mirroring. Then you don't need to do the tail backup or any of the manual steps when it comes time to switchover. You just need to perform a failover.

    Best part is, you can update the client conenction strings ahead of time to have the new server as the principal and the old server as the mirror and the client will automatically switch to the new machine.

    And if you want, you could then upgrade the old server and keep using it as a mirror for the new server.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply