Home / server / Questions / 290732
Accepted
crb
Asked: 2011-07-16 05:46:39 +0800 CST

Managing multiple storage volumes in Microsoft SQL Server

  • 772

I have a backup application backed by many databases (one per client) in Microsoft SQL Server, storing files in the database as BLOBs.

We are looking to move this application to Amazon EC2, and want to have 3TB of disk available. The largest EBS volume you can create and attach to an instance is 1TB.

(Assume that the 3TB could be about 500 databases which grow to 6GB each.)

I see two ways of doing this:

  • creating a striped RAID volume of these disks
  • manually manage the databases, storing ⅓ on each disk

In the case of a RAID volume, the failure of any volume in the stripe would lose all the data. Amazon don't recommend software RAID 1 across EBS volumes, suggesting you manage durability with regular EBS snapshots (as this is handled outside your instance). If you managed the databases manually, then a disk failure would only impact the databases on that volume.

Managing the volumes manually means a lot of administrative overhead.

Is there a method where I can say "these three disks are the primary storage pool for SQL Server" and have it sort it out itself?

sql-server amazon-ec2 storage database-administration

1 Answers

  1. Best Answer

    No you can't give SQL A pool of resources like that. I see three options.

    1. Stripe the volumes in Windows and create a single 3 Gig volume
    2. Split the databases into three pools and put 1/3 on each 1TB disk.
    3. Add two additional files to each database and put all the databases on all three disks. This would require rebuilding all the indexes so that the data is evenly spread across all the three files in the file group.
    • 0