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?

Improve this question
3
  • 7
    Log shipping would seem to be a more appropriate method than replication IMHO.
    – Aaron Bertrand
    Jan 15, 2016 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, 2016 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 - AWS
    Jan 15, 2016 at 21:06

1 Answer 1

Reset to default
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...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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