Friday, November 16, 2012

2 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.


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


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.


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.



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:


  1. Welcome to cheap Soccer Jerseys Soccer Shop - The Best Wholesale cheap zlatan ibrahimovic Jerseys Jersey Suppliers on the Internet. We specialise in the best quality and cheap soccer jerseys.

    for Huge Savings on Soccer cheap Soccer Jerseys Replica Clothing. cheap yoshinori muto Jerseys Cheap Soccer Jerseys & Soccer Clothing with Next Day shipping!

  2. I really appreciate this great post that you have provided us. I guarantee this will benefit most people and myself. thank you very much!
    health magazine



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