Help me move a large database to new storage

Question

question

bobmcc asked Mar 12, '13

Help me move a large database to new storage

I'm part of a company that was acquired by another company. We're moving it to their SAN. We are now part of the new company and on the same network, we're in a different domain that will be going away sometime in the future. Please Help me with the pros & cons of the various methods of moving this large db to new server and storage. Source: SQL 2008 database (on Windows Server 2003) and I need to move to new SAN Storage, and to a new Server (Windows Server 2008 R2). Here are the db details: Database: 3589175.50 MB Recovery=SIMPLE, Version=655.
Yes, it's almost 3.5 TB; spread across 6 data files. Data1 655360000 KB
Data2 551936000 KB
Data3 552448000 KB
Data4 747877504 KB
Data5 548104448 KB
Data6 586821760 KB
Log1 32768000 KB
1) Detach database; copy mdf and ndf files to new SAN, reattach them onto the new SQL Server
2) Backup database (currently being backed up using TSM) and restore to new server "with move" option.
3) Using copy database wizard in SQL Server Management Studio.
4) Set up replication to the new server & SAN
Because of the size of the db, I think 3 is definitely out. I'm in favor of at least TRYING the backup/restore. Additionally, couldn't I get some sort of portable disk pack to back the database onto (as opposed to network back, which I presume is a lot slower), then attach the disk pack to the new server and restore it? Regarding the copy data files method: Using Robocopy on a test file that's about 280 GB in size: Pull Speed : 1081.357 MegaBytes/min. Push Speed : 727.859 MegaBytes/min So we're talking a couple of days to copy the db. Not too good of a thing. So... let's open a conversation about this if you please. Thanks, Bob McC

database backup restore move san

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bobmcc commented · Mar 13, 2013 at 01:53 PM

Downtime is definitely a concern. With the file transfer speeds mentioned in the OP, I'm looking at 2-3 days for a file copy... if there are no glitches. We might get around some of the downtime concerns by transferring on Memorial Day week. It's not two locations; both servers are two of many stored in our data center.

0 ·

ThomasRushton ♦♦ commented · Mar 13, 2013 at 02:20 PM

Just a thought - do you get better transfer speed if you split the file into, say, 10 chunks and shift them all at once?

0 ·

Grant Fritchey ♦♦ commented · Mar 17, 2013 at 09:11 AM

If an answer was helpful to you, please show that by clicking on the thumbs up next to it. If an answer solved your problem, show that by clicking on the check box next to it.

0 ·

Answer 1 · 2013-03-12T22:10:19Z

Grant Fritchey answered Mar 12, '13

Any chance of a direct SAN transfer? Connect one to the other & copy the files across. Personally, if you can do that, I'd feel more comfortable, for once, with detach, copy, attach. Yeah, the database is down during the process, but it's a single step, clean and pretty safe. Otherwise, if you want real safety, get ready for some work and prep a bunch of disk space. You can backup the database. Then restore it to it's new home. You can do this without taking the database offline. Then backup the logs and restore them to the new location. You can more or less keep doing this until some arbitrary point where you take the database offline, backup the tail of the log, restore that to the new server, then bring it online through recovery. This is the most work of all the viable choices, but it'll be the safest AND it gives you maximum uptime (not something you referred to). It's basically log shipping. I wouldn't even consider #3. I'm pretty sure I wouldn't pick #4 either, mainly because setting up replication is a bear and you can do the log-shipping approach with less persnickety work around the distribution server, etc.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM commented · Mar 13, 2013 at 01:31 PM

+100 for using the word "persnickety" in a technical response. ;-)

3 ·

Answer 2 · 2013-03-13T08:45:00Z

ThomasRushton answered Mar 13, '13

I go along with what @Grant Fritchey said, but would prioritise option 2 (the backup/restore) option over the SAN copy to minimise downtime. Yes, transferring the backup via locally-connected portable storage is an option - I've done that before because it was quicker. However, you need to bear in mind your transaction logs - make sure you don't delete any T-Logs (or DIFFs) taken after the FULL backup that you have transferred before you get them restored on the new server, in order to maintain the log chain and the ability to successfully restore. This shouldn't be a problem if the two locations are close (are they close?), but if you're going intercontinental, then you may have more problems. options 3 & 4 - definite no-nos.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2013-03-13T13:53:31Z

Tim answered Mar 13, '13

If the two servers can talk to one another then why not just setup log shipping? I have done this hundreds of times for migrations to new servers. If the new server can see TSM then there is no reason why you couldn't restore the last full and diff from TSM to the new server, then setup log shipping. When the time comes to fail over it is a very simple process. Your question didn't mention allowable downtime. Anything you can do to stage this ahead of time the better, you don't want to wait until go live weekend to attempt this restore only to find you have a flawed process and or invalid backup.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Mar 13, 2013 at 01:56 PM

Amen to testing!

1 ·

Tim commented · Mar 13, 2013 at 02:01 PM

Your world changes when dealing with TB's of data verses single digit GB's. Minutes become HOURS. :)

1 ·

Tim commented · Mar 13, 2013 at 02:40 PM

Based on them being in the same data center and downtime a concern, log shipping is a very solid route to go. You may also consider a dedicated connection (private network) between to two servers to speed up transfer speed and lessen the impact to production traffic. I have done that from time to time as well. Very simple and cost effective.

1 ·

Answer 4 · 2013-03-13T18:46:06Z

Usman Butt answered Mar 13, '13

I guess we are overlooking a key feature now a days available so easily i.e. backup compression ;) Seems like the biggest hurdle is the time to copy whether the files OR the full backup (full backup would be almost the same size as are the files). Not to mention the storage size (the most expensive resource) for the backup that would be restored which would mean that you would need atleast double the size of the database. Which to me might not be feasable and in most cases very well be the main reasons to migrate to the other bigger servers. So go for the backup compression tools which would save you time and storage. Hence Log shipping setup would be benefitted as well. Considering Log shipping is the best idea, the first third party tool comes to mind is Red Gate's SQL Backup Pro. Upto to 95% smaller and hence faster backups, Self healing Log Shipping (GUI environement is very helful), Multiple threads to increase backup performance, Network resilience for fault-tolerant backup transfer, including automatic resuming of interrupted transfers, DBCC CHECKDB etc. are some of the key features of this wonderful tool. I can left rest of the features to our own @Grant Fritchey :) Just my two cents.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt commented · Mar 14, 2013 at 04:37 PM

> Backup database (currently being backed up using TSM) and restore to new server "with move" option Sorry, I took the above statement differently and I may be wrong in assuming things. One of the few things on my mind were 1. "Move" option is not native to TSM? 2. Since we are dealing with SQL 2008. So SQL compression would only be possible if the OP has Enterprise addition to work with. 3. As one would have to deal with Legacy backups, SQL compression is generally faster and more effective than Only TSM compression OR both compressions together. 4. SQL Compression is not ON by default. One has to set the compression flag in config file OR through GUI 5. Third party tools showed better compression throughput than native SQL compression. And as you said, when you are dealing with such huge amount of data, any kind of savings is precious. Since the OP did not provide such detailed information about the TSM, I thought backup compression option should be added explicitly...Continued

2 ·

Tim commented · Mar 13, 2013 at 06:50 PM

The OP mentioned TSM which uses compression from the client to the server.

0 ·

Usman Butt commented · Mar 14, 2013 at 04:43 PM

So the OP does know each and every option available (No means to offense the OP, as he may already knows things better than me. But it is always better to share than not). I guess I should have better started this as a general inquiry with OP to confirm. Last but not least, sorry for the late response. We are suffering with scheduled power outages with atleast one hour duration. At then, It was 12:00 a.m. in our part of the world and we suffered a blackout again and I was in no condition to wait till 1:00 a.m. to respond :) This is one of the reasons, nowadays, I am not able to follow up the posts the way I should have. Many Thanks to @KenJ for backing me up on another post.

0 ·

Answer 5 · 2013-03-14T10:58:34Z

tanglesoft answered Mar 14, '13

I would not use the copy option from SSMS in offline / online copy mode it's only automating something that can be done with more control manually and it usually makes a mess of the copy. If you carry out daily backup consider a tool like Hyperbac which will intercept the backups and compress them. Some of our databases reduce down to 4% of the MDF+LDF size so the network copy is far easier. You can get a trial version and mark the backups as zip compatible then unzip on new server. Restore on new server in recovery mode then do a differential backup on source and restore the new database into operation mode.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question