Pages

Monday, November 5, 2012

SQL hardware configuration

As a DBA as much as knowledge of query tuning and indexing and maintenance is important, so is important the other aspect i.e. Disk I/O configuration. Because sometimes how much ever you tune the query the problem lies elsewhere.

Raid 0: should generally be never used for SQL server
Raid 1: Mirrors data: provides protection from loss of data
Raid 5: stripping with Parity
Raid 6:stripping with double distributed Parity
Raid 10: stripped pair of mirrors
Raid 01: mirrorred pair of strips

For read-only data raid 5 or 6 are good

For heavy writes or OLTPs raid 1+0 is a good choice though expensive

Raid 1 is a good choice for transaction log which are mostly written to continuously

tempdb since is mostly writes Raid 1 or raid 1+0 is good option. Or else the RAM or any other specialized hardware is too.
Direct attached storage (DAS): disk are attached/built into server chasis and hence dedicated to each server. Easy assembly as well as performace troubleshooting. No support for failover clustering, disk array snapshots, cross data center or array based replication.

Storage Area networks (SAN):  Shared storage usage btween multiple servers. Ensures maximum use of storage space.
Since the sytem is complex, performance troubleshooting becomes difficult