5

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.

Garrett
  • 151
  • 1
  • 2

2 Answers2

4

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:

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
-3

"…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).

Greenstone Walker
  • 4,389
  • 1
  • 17
  • 23