locked
How to minimize downtime migrating databases RRS feed

  • Question

  • I am engaged in a project to migrate the databases from an existing server to a newer one. The existing server runs SQL Server 2005 Standard 32bit on Windows Server Enterprise 2003. The new server runs SQL Server 2005 64bit on Windows Server Enterprise 2008 64bit. We also run Idera's SQL safe in the current server, and will be moving it to the new one, for our compressed backups. There are 6 production databases that need to be moved to the new server with minimal downtime. The largest one is close to 500 Gb while the others are less than 34 Gb, one of them very tiny. My question is whether I should use log shipping or database mirroring to perform this process. I would also appreciate any detailed description of this process since I am not very familiar with either technology.

    Thanks,

    Eduardo

    Friday, January 14, 2011 1:11 PM

Answers

  • Ted Krueger wrote a couple of articles on using mirroring to upgrade a SQL Server.  They can be found in the links below.  As you can see by the titles, the second article goes into the actual migration.

    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/move-databases-to-new-server-with-little-1
    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/using-mirroring-to-reduce-db-migration-d-2

    Mirroring, once set up, has fewer moving parts than log shipping, so it should make your actual switchover point quicker.  Quite a few people are using this approach to limit downtime for an upgrade.  Some report system down times for the upgrade in the 10 minutes to 1 hour time frame.  (Depends on how many things you want to double check.)

    Since you have never done this, I suggest testing it out using the new server as a sandbox.  Then have a set of people try to use the sandbox and see what (if anything) you missed.  Some things outside of the user databases to care about include:

    • Logins and Credentials
    • Linked servers
    • Code signed certificates
    • Service broker queues
    • SQL Agent Proxies
    • SQL Agent Jobs
    • Server level permissions

    All the best,
    RLF

    • Marked as answer by Olivera58 Tuesday, January 18, 2011 4:23 PM
    Tuesday, January 18, 2011 4:04 PM

All replies

  • Ted Krueger wrote a couple of articles on using mirroring to upgrade a SQL Server.  They can be found in the links below.  As you can see by the titles, the second article goes into the actual migration.

    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/move-databases-to-new-server-with-little-1
    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/using-mirroring-to-reduce-db-migration-d-2

    Mirroring, once set up, has fewer moving parts than log shipping, so it should make your actual switchover point quicker.  Quite a few people are using this approach to limit downtime for an upgrade.  Some report system down times for the upgrade in the 10 minutes to 1 hour time frame.  (Depends on how many things you want to double check.)

    Since you have never done this, I suggest testing it out using the new server as a sandbox.  Then have a set of people try to use the sandbox and see what (if anything) you missed.  Some things outside of the user databases to care about include:

    • Logins and Credentials
    • Linked servers
    • Code signed certificates
    • Service broker queues
    • SQL Agent Proxies
    • SQL Agent Jobs
    • Server level permissions

    All the best,
    RLF

    • Marked as answer by Olivera58 Tuesday, January 18, 2011 4:23 PM
    Tuesday, January 18, 2011 4:04 PM
  • Thanks, Russell
    Eduardo
    Tuesday, January 18, 2011 4:23 PM