SQLTeam.com | Weblogs | Forums

Cluster Storage Allocation

sql2012

#1

Hello Everyone!!

I wanted to field a question to the masses to return a best-practices of the community.

If storage is allocated as the following :

  • Y: for user database data files (.mdf)
  • B: for user database log files (.ldf)
  • X: For SQL Backups
  • O: Quorum Disk

What percentage would everyone apply to the different enclaves?

Example:

  • Y: 30% (.mdf)
  • B: 20% (.ldf)
  • X: 40% For SQL Backups
  • O: 10% Quorum Disk

#2

The quorum shouldn't require 10%.

I'd keep the backups as small as possible while allowing all backups to always succeed, of course. For example, if you can, start moving backups from that storage to its more permanent location asap after a db backup, rather than waiting for all backups to complete first.

I think you should mix data and logs (although that is not common), because it allows better balancing of size and i/o usage, but with this ABSOLUTE condition: any single db must have its log file on a separate disk set from its data files. For example: dbA's data files on Y, dbA's log file on B; dbB's data file on B, dbB's log file on Y; etc.


#3

You also need a separate drive for the system files - including system databases and a separate volume (at a minimum) for tempdb. Could potentially have 2 separate volumes for tempdb - one for the data files and one for the log files depending on activity (I have not found a compelling reason to do this).

I would lean more towards mount points instead of drives - that way you can separate out databases to their own volumes if those cause any contention issues.


#4

Since I often don't have the luxury of enough drives to get an optimal system, I'll put tempdb log, at least, on the backup drive. That drive is mostly idle when backups aren't being done anyway. Similarly, if necessary I'll often put large db files that are static and/or not often read on the backup drive as well if I need to utilize the space.


#5

Thanks everyone!!! Lots of good stuff to go track down for a better understanding!!! Winning :slight_smile: