3

I need to move a live database with about 100 GB of data to a new machine. As I understand the easiest way to do that would be to take it offline, move the files and bring it back online.

But: The old server is connected via a 200 mbit uplink only, which means it would take me more than 1 hour to move the whole thing. That's too long to be down for my situation so I'm wondering if it might be suitable to just set up transactional replication and on day x take the old one offline, change my DNS records to the new server and make that one the new writeable master.

Can I be sure that all my table settings, views, etc. are properly copied to the new machine?

Or is there another suitable way to avoid longer downtimes when moving databases?

  • 7
    Log shipping would seem to be a more appropriate method than replication IMHO. – Aaron Bertrand Jan 15 '16 at 18:42
  • 1
    I agree with Aaron... Logshipping will be more straight forward and easy to implement. check my answer for Moving Databases to New Datacenters. This is sort of dupe to that. Let me know if you need more info. – Kin Shah Jan 15 '16 at 18:58
  • Only thing would be what is your max data loss SLA in case of a roll back. The business should be made aware of that in writing prior. – Ali Razeghi Jan 15 '16 at 21:06
1

Another option (similar to what happens in replication but more manual but less setup) is to do a FULL backup, restore over the network (with NORECOVERY) and continuously do Transactio Log backups with automatic restores. This would cut your downtime to only the last Tranlog Restore...

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.