I have a large (1.2 terabyte) SQL Server database that I need to migrate to a new server. Most of the database lives on a single, 1.25 TB data file, and a little bit sits on a much-more-manageable 550 GB file (which is practically empty).

Now, the tricky bit: the server to which I'm migrating only has 3 700 GB volumes, meaning I need to somehow dissect this goliath into three equal chunks. Most advice I've found involves creating 3 target files and running DBCC SHRINKFILE EMPTYFILE on my main file to empty it into the targets, but that'd take ages with a database this large.

Is there a recommended method for splitting a database this large? I'm considering using the Sql Server Integration Services Data Export feature to dump the data into a clone database with the proper file structure, but I'm curious as to whether there's a better way.

  • 3
    You can add new filegroups & files and start transferring tables into them until you've split the data in thirds (see here), but why does the new server have 3x700 GB volumes? Does this layout make any sense for your database? This sounds like your database is being designed for your server, rather than your server being designed for your database. – James L Jul 22 '13 at 21:03
  • Unless I'm mistaken, adding new files and transferring tables is the same as the DBCC SHRINKFILE method I mentioned, which is woefully inefficient. I tried shrinking this database once: it spent days tied up with little-to-no effect. As for the volume split, it's mostly consideration for our IT folks (for whom allocating 1.5+TB of contiguous space on our SAN would be hellish). Besides, our database doesn't really need to be one giant file, it just grew that way. – Garrett Jul 22 '13 at 22:01
  • Yes I think we're talking about the same method, sorry. The shrink should be much faster once you're working with an empty or nearly empty file, but ultimately there's no efficient way to slice up a 1.25 TB MDF. Working with SSIS might get the job done faster, but with possibly more administrative effort, especially if you need to sync the data more than once. Personally I'd be questioning the need for the complex & unusual storage setup (which is not optimal for managing free space, and probably not for performance either), but that goes beyond your actual question. – James L Jul 22 '13 at 22:35

DBCC SHRINKFILE -- will be single-threaded – which will contribute to the long run-time.

Also, Sql Server Integration Services Data Export will be slower due to massive database size (1 TB) !

Instead, you should look for BCP OUT (in binary format) and BULK INSERT in the database.

  • BCP can read the SQL Server native format from text files. This is a very fast option that requires minimal parsing of the text file input.
  • The BULK INSERT command is the in-process method for bringing data from a text file into SQL Server. Because it runs in process with Sqlservr.exe, it is a very fast way to load data files into SQL Server.
  • Additionally, enable Trace Flag 610 to minimally log inserts into Indexed tables along with BATCHSIZE and ROWS_PER_BATCH parameters.
  • You can even disable Lock Escalation which on sql server 2005 generally occurs at 5000 locks allocated. This can be disabled on sql server 2005 by setting a batchsize to a number lower than this or enableing Trace flag 1211 (to completely disable lock esclation). In sql server 2008, you can do it using alter table <table_name> (LOCK_ESCLATION = DISABLE)
  • BCP has a -a switch to allow to specify packet size or if using SSIS it can be done in properties of the connection manager --> PacketSize = 32767
  • To avoid PFS contention, consider using -E startup parameter.

Also at hardware level, you can look into

  • Use fast NIC and switches.
  • Have the latest certified drivers for your NIC.
  • Enable full duplex.
  • Enable support for Jumbo frames.
  • Use TCP Chimney Offload.
  • Use Receive Side Scaling (RSS).

Check the BCP & BULK INSERT script that I provided at here

Note: Your hardware including disk partitioning, layout, number of CPU's, NUMA configuration, etc will also play a big role in performance when you load your data.

Excellent reading:


"…but I'm curious as to whether there's a better way."

Yes - use hardware or software RAID to create one RAID0 (or maybe RAID5, but write performance might suffer) array across the three 700 GB disks.

Better still (as the commenter below says), add more disks and create a RAID array with fault tolerance.

SQL Server databases are more manageable with fewer files, not more files.

Is it possible to physically remove the hard disks from the old server and install them in the new server? If so then restoring the database into the new instance of SQL Server is just a matter of attaching the data files.

Edit: The poster below makes an excellent comment about fault tolerance. A RAID0 array has no tolerance for errors. Then again, neither does splitting the database across three individual disks.

I am OK with SQL Server data files being on RAID arrays with no tolerance, if and only if (1) there is a great DR solution, and (2) the log file is on an array with fault tolerance, and (3) the database is in Full recovery Model (so the tail of the log can be backed up).

  • 3
    I can't think of a time that you would ever want to use a RAID 0 for a production database server. One RAID issue and you lost everything. – user1207758 Jul 25 '13 at 20:18

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.