2

I've got 5 databases that run on separate servers using SQL Express. Some of these include a Citrix Datastore, Citrix Edgesite Server, an AV db, Time and Billing App, and a document management program. These all currently run on separate servers using SQL Express as mentioned.

I'm considering consolidating to one central SQL database to provide larger DB sizes and other benefits to the full SQL 2008 R2 platform. To save money I want to use a server with one quad core CPU using a per socket SQL license. The server I have is stocked with SATA drives.

How exactly should I partition/RAID everything to provide the best performance? Should all the DBs be on a single RAID set or all separated? Most configs I see seem to show recommendations for partitioning a server running only a single database.

Any guidance would be greatly appreciated.

Untalented
  • 21
  • 1
  • get as much RAM as you can afford to hold the dbs in memory to try and avoid having to go to those slow SATA disks as much – August May 15 '12 at 16:27
  • More RAM gives you the most bang for your buck, but don't forget having many fast disks. More spindles = more IOPS. – Bigbio2002 May 15 '12 at 17:28

3 Answers3

2

Database storage configuration is more of an art than a science. Some simple guidelines:

  • Keep your logs and data on separate raid groups
  • Choose your hard drives carefully: if you expect a lot of IO, pick smaller drives with faster spindle speeds
  • Raid is not a backup. Just because you can hotspare a drive doesn't mean your database doesn't need to be backed up :P

What I see a lot is the log file on a small raid 1 or raid 10, and the data on a larger raid 10. Raid 5 and 6 will give you a performance loss (in terms of IOs per second, or IOPS) compared to some non-parity based types of raid (like raid 1 and 10). The tradeoff is that you get to use more than half the capacity you bought. If you have a decent hardware raid controller, the performance loss is manageable, however you'd want to use raid 6 for any large drives (900GB and up).

Basil
  • 8,851
  • 3
  • 38
  • 73
1

From a 50,000 ft view you want to have a separate mirrored pair for logs and a RAID-10 for data. The cost of backup media will far outweigh the difference in cost of storage media between a RAID-5 or RAID-10 setup.

Chances are that with modern storage the disks are so big you will be more constrained by bandwidth than capacity. If you are worried about the data volumes then you may well find that a RAID-10 provides the same I/O bandwidth with fewer disks. This is quite likely to become an issue before storage space does, particularly if you're using SATA disks.

If you have more log traffic than a single RAID-1 can handle you can expand it into multiple RAID-1 log volumes or a RAID-10 volume.

Unless one database is a much bigger resource hog than the others they can all reside on the same data and log volumes, providing the volumes have enough disk bandwidth. If you have one database with particularly heavy usage you may with to move it onto its own disk volume.

ConcernedOfTunbridgeWells
  • 8,820
  • 2
  • 32
  • 52
0

Smaller Drives + more spindles = faster read / write times for any Raid 5. Separate info by types of data. Launch the OS from a totally separate Physical and Logical volume.

Chris S
  • 77,945
  • 11
  • 124
  • 216
Dave
  • 42
  • 2