I generally on a drive to learn something new and if I can quickly learn from my friends who are expert in that field, then I just jump immediately to become a student. With the “teacher’s day” being celebrated last week, I was fortunate enough to get wishes from so many people. Feeling blessed, I thought I must become a student. In this blog, let me bring the notes on different disk system technologies that can be important to understand when working SQL Server database and transaction log files. More information about disk systems and considerations with SQL Server can be located here.
SCSI
SCSI stands for Small Computer System Interface and is simply a protocol for disk storage. The term is most commonly used when discussing what type of disk drive would be installed on a computer (IDE vs SCSI). The most important point here is that many SCSI disk drives work with disk controllers that support read and/or write caching. Therefore, it is important to discuss with DBAs whether their caching controller has an alternate power source (such as battery backed) so if system failures occur, writes are guaranteed to be flushed to disk (and in the order in which they were submitted).
IDE
IDE stands for Integrated Drive Electronics that later standardized under the name AT Attachment, or ATA. Many IDE drives can support large sizes (150Gb+) at inexpensive prices. One common characteristic of IDE drives is that the disk drive itself supports caching. A possible issue with some of these IDE drives that support disk caching is described in KB 234656. Many of these caches are not backed by a battery source and therefore cannot guarantee writes are flushed to disk. Furthermore, the use of the disk drive cache could result in writing reordering in which the transaction log writes may not be flushed to disk before page writes. This could potentially result in a violation of the WAL protocol.
Because of these issues, use caution when using IDE drives with SQL Server databases. Many of the drive manufactures do support disabling of the disk cache via Windows Disk Management. This is recommended for IDE drives, but could slow down expected performance.
SATA
SATA stands for Serial ATA. These are the most common drives used in personal computers today. The SATA data cable has one data pair for differential transmission of data to the device and one pair for differential receiving from the device. That requires that data be transmitted serially.
SAS
SAS stands for Serial Attached SCSI. SAS is a new generation serial communication protocol for devices designed to allow for much higher speed data transfers. SAS also includes features such as advanced queuing up to 256 levels and out of order queuing. The SAS backplane is designed in a way that enables the use of both SAS and SATA drives within the same system. The benefits of the SAS design are obvious.
SAN
SAN stands for Storage Area Network. SANs are designed to share large disk storage across multiple servers while not requiring close proximity between the disk storage and computer server. A good resource to understand the basics of SAN technologies comes from the IBM website.
A SAN is literally a network of disk storage (the actual disk media can be a different geographical location), but does not use standard network protocols to transmit data. From the perspective of the local computer accessing this disk storage, it is a local disk drive. In other words, the disk storage is not a network based mapped drive or UNC path.
SAN devices are connected using a technology called fiber channel. It is up to the SAN system to ensure read/write consistency just as though the disk was attached locally to the computer.
NAS
Network Attached Storage is simply a file server dedicated to storage that can be accessed across the network. Technically, this is not different than using a network share from another computer because any access to the NAS device is through the Windows network redirector. The difference is that NAS device is a dedicated disk storage that can reside on the network instead of disk attached to a computer. However, both a network share and NAS device must be accessed via a network mapped drive or UNC path. The best source of information on SQL Server’s support for NAS devices is in KB article 304261.
Use of NAS devices or network shares for database files can lead to some unexpected OS errors such as OS Error 64, “The specified network name is no longer available” when SQL Server fails during an I/O operation (Msg 823).
iSCSI
iSCSI stands for Internet Small Computer System Interface. The simplest way to think of iSCSI is that it is the implementation of the SCSI protocol across the TCP/IP network protocol. Some people are calling it “SAN over IP”. The concept is to try to cheaply implement a SAN without requiring all of the infrastructure that makes up a SAN system (including fibre channel). So effectively with iSCSI, NAS devices can be more reliable given that the SCSI protocol is used to transmit data now back and forth between the NAS device and the client computer vs using the network protocol to transmit data.
Since iSCSI devices can operate across the internet, there are of course performance concerns. Therefore, the issue is not a prevalent storage technology used in the marketplace, especially for SQL Server databases. Like SAN devices, iSCSI devices appear to the application as a local disk drive and therefore do not require trace flag 1807 to create databases on these devices. See KB article 304261 for more information about trace flag 1807 and network based database files.
Final words
I think I am exhausted by assimilating all this information and tons of learning that I got in one sitting of 30 minutes. I wrote this blog to make sure I use the notes from paper as an electronic topic for this as a reference for future.
Reference: Pinal Dave (https://blog.sqlauthority.com)
