4

I have a database that's about 3TB in size and want to upgrade to SSDs. The SSDs I found that are reasonably priced are only 512GB so I'll need to purchase multiple drives.

What are some ways to split the database file so that it can be accessed from multiple drives without much code changes or needing a RAID controller.

The database software in question in sql server 2014 enterprise edition, but it would be best to find a solution without requiring database engine specific features.

6

In SQL Server, you can create file groups that consist of 1 or more files. You can then migrate data into those file groups individually by rebuilding the clustered index for each table on that file group.

You can also do the same for indexes by rebuilding the index by specifying the desired file group. You can use file size caps to ensure the files do not grow larger than 512 GB.

You will want to do this prior to migrating to the new drive array so that you can do a backup/restore or a detach/attach to the new array with no issues. You will also want to shrink the PRIMARY file group after you're finished migrating data to the new file group(s). (I'm assuming everything is in PRIMARY currently).

This will be your easiest solution that will not require any coding changes but it will incur some significant performance hits while the data is being moved.

Your performance will likely go up markedly by splitting the data into multiple files and moving to SSDs. I would suggest doing a backup first, and don't do detach-attach if you need to physically move files around - backup/restore is FAR safer.

Multiple filegroups are superior to multiple PRIMARY files as they allow piece-meal restores, which can help get the database up and running far more quickly in a DR event.

3

If you use a RAID partition, you will get fault tolerance in case of drive failure. If you instead stripe your database across N drives you are N times more likely to suffer a fault causing you downtime.

If you are paying for Enterprise Edition, why on earth are you trying to save money on drives? Spend a few thousand dollars and build a decent RAID. Or use software RAID. Every OS has it built-in. It'd certainly be more real-world tested than most hardware RAID software.

2

On a storage system that offers RAID data protection, I highly recommend splitting the database into several equally sized data files with one file on each LUN/drive/mount point. That gives increased aggregate service queue depth to reach the data at both the logical & physical drive levels.

Without RAID protection provided by the underlying storage, using storage spaces is an option if available on your OS to get RAID5. When creating a striped volume, the default interleaved is 256KB. I prefer a larger interleave of 1MB, 2MB, or even 4MB because my workloads can leverage the larger reads heavily. Your mileage may vary with respect to optimal interleave size.

See also Storage Spaces Overview

Using storage spaces to create a logical volume made of several physical volumes will give greater aggregate service queue depth at the physical volume level, but the limit at the logical volume level may become a performance hindrance (in comparison to multiple files on multiple LUNs, one file per LUN.

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.