Welcome to WebmasterWorld Guest from 34.204.189.171

Forum Moderators: open

What's the best way to migrate to a new MS SQL server?

Minimal downtime preferred.

     
4:06 am on Jul 14, 2019 (gmt 0)

Junior Member from US 

10+ Year Member

joined:July 14, 2006
posts: 155
votes: 1


Changing hosts, I need to move 80GB server with minimal downtime to a new server at a different datacenter.
My programmer predicts a very long shutdown. He's planning on backing up the entire database and moving it all at once.
Is this the preferred way? What kind of downtime am I looking at?
6:52 am on July 14, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:10458
votes: 1091


Most times this will be subject to the speeds of transfer/writing of data and then uploading again. There's no real cut and dried answer to that. However, even in worst case scenarios, should be accomplished within a 24 hour period!
6:54 am on July 14, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:10458
votes: 1091


Let me qualify that estimate with "best case data pipes and servers" involved. on TB pipes this is nothing, on GB pipes, pretty quick, on anything else it will depend on your speeds.
9:06 am on July 14, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member Top Contributors Of The Month

joined:Nov 13, 2016
posts:1194
votes: 285


To handle with caution.

- do not stop your SQL server on your actual host,

- do not start your SQL server at the new host,

- rsync your database folder from the current host to the new one,

At this point, at your new host you will have a "partially corrupted" data base. Because new transactions, writings, deleting would have occurred during the rsync transfer.

- stop your SQL server at your actual host, (=> downtime)

- rsync again your database,

This second rsync should be very fast, because rsync will just copy the differences, which will fix the data which were "partially corrupted". Since it's going to be very fast, it minimizes the downtime.

- start your SQL server on the new host,

Finished.

edit: some will certainly say it's not good practice, so I makes no warranties, I am just saying this is what I am doing when I change server, with a 300 GB MariaDB database (before MySQL), without problem since 15 years, and when changing server, I have less than 5 minutes downtime of the site.
5:41 pm on Aug 28, 2019 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5848
votes: 198


rsync is not going to work with MS SQL data files.

You *could* setup replication and accomplish something similar to what Dimiti posted, although it gets kind of messy.

What type of writes are being done to the DB? (Is it mostly being used for reads, or do you have a lot of writes as well?)
7:22 pm on Aug 28, 2019 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator mack is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:June 15, 2001
posts:7845
votes: 95


You can use Rsync to copy the binary files from server to server? This is something I had to do some time ago to back up a 100G+ DB.

Mack.
7:40 pm on Aug 28, 2019 (gmt 0)

Junior Member from US 

10+ Year Member

joined:July 14, 2006
posts: 155
votes: 1


We ended up just shutting down at 11PM and transferred a complete database.
I feared compromising the integrity of the database.