Sql-server – Fastest way to move a SQL Server database between systems and SQL Server versions

sql server

We're moving our non-trivial SQL Server database [65Gib] to a new server.

Added in the mix, we're moving to SQL Server 2008 R2 from SQL Server 2005, and ideally need to take some time doing ALTERs to move various tables into different filegroups.

The traditional knife over would be :

  • Down OLD Server
  • Copy off DB files (Just realized I'll have to find some other way of copying over user logins from masterdb)
  • Copy them over GigE to new DB
  • Import DBs into NEW Server and allow any upgrades to process.
  • Complete ALTERs
  • Enable NEW Server

I'd love to pre-load as much of the transfer as possible; is there a good [simple] way to copy over a live version of the database and continue updating the target server until all the prep work is done? It occured to me that I could use replication, but I wasn't aware if I could then promote the Replication Slave to Master…

Or am I making a mistake trying to switch servers and software versions at the same time?

Best Answer

65 GB really is trivial, but may not be over in copying over a slow LAN, or if you can't afford a few minutes of down-time.

The fastest way to cut-over (and keep it simple) is to do logshipping (no recovery mode) between your your existing db and your new db.

You should be able to copy logins & agent jobs before the cut-over & leave them disabled till you move your live traffic over.

Not really a mistake to switch servers and version of sql server at the same time, depends on your requirements. Just remember that bringing the db online on sql server 2008 R2 is a one way process that will upgrade the data files the first time the db does a recovery & there is no going back!

Your other not so simple option is to do the following:

  • on your new server, install the same version of sql server as your existing server
  • setup database mirroring btwn existing & new server
  • do the same prep in copying logins/agent jobs/ other dependencies
  • failover the mirror to new server making it the primary (assuming you have support from your app to do this)
  • update existing server to 2008 r2, then fail back the mirror upgrade new server to 2008 r2 then failover again remove the mirror, shutdown old server

There is probably many variations of the above approaches. The moral of the story is that you have to consider the cost of the cutover vs. the cost of down-time. It's a trade-off.

Related Topic