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.
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.
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:
- SQL Server 2000 I/O Basics: http://technet.microsoft.com/en-us/library/cc966500.aspx and http://technet.microsoft.com/library/Cc917726
- SQL Server 7.0, SQL Server 2000, and SQL Server 2005 logging and data storage algorithms extend data reliability: http://support.microsoft.com/kb/230785/en-us
- Description of caching disk controllers in SQL Server: http://support.microsoft.com/kb/86903
- Key factors to consider when evaluating third-party file cache systems with SQL Server: http://support.microsoft.com/default.aspx?scid=kb;en-us;917043&sd=rss&spid=2852
- Physical Database Storage Design: http://technet.microsoft.com/en-us/library/cc966414.aspx
- Microsoft SQL Server Database Engine Input/Output Requirements: http://support.microsoft.com/kb/967576
- Description of using disk drive caches with SQL Server that every database administrator should know: http://support.microsoft.com/kb/234656