Moving a large number of databases from one server to another?

Question

Moving a large number of databases from one server to another?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Wayne West SSC-Insane Points: 22586 More actions · Answer 1

xsevensinzx (5/6/2016)
Being no one has mentioned it yet, if you do transfer, note the bandwidth usage if applicable. We had multiple lines and we dedicated a full line/circuit to the transfer with help of the network admins.

There's an old joke that the fastest bandwidth transfer is a station wagon filled with tapes. And it holds true today. At my wife's observatory, one program that ran on the 2.5 meter telescope collected so much data that they striped the write across 5 DLTs, then every day shipped those tapes to the university that compiled and analyzed the data.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

TheSQLGuru (5/3/2016)
Backup and restore works fine.
Detach and reattach works fine (beware the stats updates though - make sure you tell the script to not do those).
If this is a migration and you need to minimize downtime on the application(s) then you can set up tlog shipping or database mirroring to help facilitate quick switch-over. Note that mirroring can have a significant overhead.

I cheated like hell on my migration. The server had about 1.5 TB of databases on it. I did restores from the previous night's backups with both servers up. About 45 minutes before the switch, I turned off the log file backups and did DIFs across all the databases and restored those. That didn't take much time at all. When it was "show time", I took Tail Log backups (leaving the databases in the un-modifiable restoring state), restored the Tail Logs with recovery, renamed the servers, and was done. It worked a whole lot more quickly than even I expected and keep downtime to a minimum without having to mirror or setup replication, etc, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

Wayne West (5/6/2016)
xsevensinzx (5/6/2016)
Being no one has mentioned it yet, if you do transfer, note the bandwidth usage if applicable. We had multiple lines and we dedicated a full line/circuit to the transfer with help of the network admins.
There's an old joke that the fastest bandwidth transfer is a station wagon filled with tapes. And it holds true today. At my wife's observatory, one program that ran on the 2.5 meter telescope collected so much data that they striped the write across 5 DLTs, then every day shipped those tapes to the university that compiled and analyzed the data.

I remember doing a data center migration project a few years ago, and, while most of the databases could come up the line relatively quickly, it was still quicker to do the main system by backing it up locally, copying the backup to a USB drive, and shifting that drive by car.

Fortunately, we had a guy on-site shifting other stuff for us as part of the move, so it wasn't a dedicated trip, but it's still a valid point.

Obviously, when transferring backups this way, make sure they're encrypted and the password *isn't* on the same media... 😉

Thomas Rushton
blog: https://thelonedba.wordpress.com

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

ThomasRushton (5/9/2016)
Wayne West (5/6/2016)
xsevensinzx (5/6/2016)
Being no one has mentioned it yet, if you do transfer, note the bandwidth usage if applicable. We had multiple lines and we dedicated a full line/circuit to the transfer with help of the network admins.
There's an old joke that the fastest bandwidth transfer is a station wagon filled with tapes. And it holds true today. At my wife's observatory, one program that ran on the 2.5 meter telescope collected so much data that they striped the write across 5 DLTs, then every day shipped those tapes to the university that compiled and analyzed the data.
I remember doing a data center migration project a few years ago, and, while most of the databases could come up the line relatively quickly, it was still quicker to do the main system by backing it up locally, copying the backup to a USB drive, and shifting that drive by car.
Fortunately, we had a guy on-site shifting other stuff for us as part of the move, so it wasn't a dedicated trip, but it's still a valid point.
Obviously, when transferring backups this way, make sure they're encrypted and the password *isn't* on the same media... 😉

I had one where two sets of physical media were set out. One on an airplane and another in a vehicle driving just in case the plane didn't make it for some reason.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service