6

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?

Improve this question
2
  • Hmm... msdn.microsoft.com/en-us/library/cc917680.aspx
    – Rizwan Kassim
    Jul 21, 2010 at 1:26
  • 1
    Switching software versions, at least as far as the DB is concerned, is neither here-nor-there. Just attach and go. You can then further tune it (set the compatibilty level to 110, for example), but to get it off the ground it's not an unreasonable thing to do.
    – Mark Henderson
    Jul 21, 2010 at 1:31

6 Answers 6

Reset to default
8

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.

Improve this answer
4
  • 1
    Can you DB mirror across different versions of SQL Server?
    – gbn
    Jul 21, 2010 at 4:47
  • 2
    I would not call 65gb trivial. It is "small", agreed, but it is not "trivial" in that it requires NO care at all. Most people here would be challeneged to even think of it (says more about them, agreed). Otherwise +1.
    – TomTom
    Jul 21, 2010 at 5:03
  • 1
    not while the db's are at different versions. msdn.microsoft.com/en-us/library/bb677181.aspx but it's still a way to minimize down time
    – Nick Kavadias
    Jul 21, 2010 at 5:05
  • 2
    I found this to be a useful guide to plan the creation of the mirror and the steps after the mirror needs to be upgraded. blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/…
    – Rizwan Kassim
    Sep 14, 2010 at 0:33
3

You can detach your database from the old server, copy the physical files and attach it to the new one; it will happily attach to a different SQL Server version, and if you've already transferred logins (using one of the suggested methods), everything will work instantly.

Taking a full database backup and a log backup (and optionally shrinking the database) before the transfer will help reduce the size of the files to be copied.

Improve this answer
1
  • this is probably the another good approach for Rizwan, which is restoring logs without having to go through the trouble of setting up logshipping.
    – Nick Kavadias
    Jul 21, 2010 at 5:07
2

This article includes a couple of stored procedures that make login transfer very easy.

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

It maintains the original sid of the login as well, so your database users won't require sp_change_users_login to match up the new login's sid to what is stored in the user db.

Improve this answer
2

I'd backup/restore with a FULL backup the night before or so, then do a differential backup/restore on the day. I wouldn't copy the MDFs and LDFs. I've migrated databases that around 400Gb on disk this way before.

Instant File initialisation for MDFs makes this quite quick

Logins I'd have already scripted and applied, jobs setup and disabled etc already.

Improve this answer
1

Database Mirroring will work across different versions of SQL. I did exactly what you are attempting by using database mirroring and having the logins migrated ahead of time. The catch is you have to setup DB mirroring with t-sql, the gui won't accept it, also you can't go back so take a full backup of your DB just before you want to cut over, fail the mirror over, do sp_change_users_logins 'Update_One' 'user' 'user' to sync your new logins with the users existing in the db and you're done. DB migration of any size in less than 10 seconds. Even less if you have it all scripted.

Improve this answer
0

Why would you not copy the MDF's and LDF's when you are performing the restore? Our database is approx. 350GB in

Improve this answer
1
  • 1
    You could copy the MDFs and LDFs, but that would lead to a longer outage. Something like Log shipping would be a better option as the outage window would be reduced from hours to minutes.
    – mrdenny
    Jan 21, 2011 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.