Friday, November 16, 2012

0 Storage Design for the SQL Server

Despite that the SQLServer 2012 always on is allow to use the local storage instead of expensive SAN. I don’t think this trend would happen on our environment. 

Once upon time, the senior DBA told me, there is a good reason why our environment don’t use local storage and has to use SAN. I was not able to realize how important for the point until recently I spend sometime dig into the MSFT document for the storage design.

Generally, SAN offer the better performance ,scalability and stability.

Scalability

What does the scalability means is you can extend your disk without rebuild the entire disks. So when we see from OS perspective, it is still one single disk with mount point such as D:. However, the D drive may come from multiple different SAN allocation.

wmic partition get BlockSize, StartingOffset, Name, Index, size

image

The Disk 1 and 2 together actually made up my H Drive.  In later version of the Windows ( Win2008 ), we can event extent the drive without shutdown the server. I have not yet personally done that as I am not the SAN admin nor the OS admin.

Stability

Another key feature of SAN can offer is “Battery backup controller”. In the event the power outage, the controller would not shutdown right away and lost all the data in the cache but be able to write into the disk.

 

FILE_FLAG_WRITETHROUGH and FILE_FLAG_NO_BUFFERING

SQL Server engine manage its own cache does not rely on the OS or regular disk cache. This is to guarantee if SQL server can write the data/log to the stable media. However, not all storage vendor would honor this flag this is another reason if we want to perform any sort of storage test, the test file must large than the cache. 

 

Other important reference from MSFT for how SQL server interact with IO subsystem:

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates