NAS
Definitely not NAS for SQL Server. SMB/CIFS does not have adequate support for file locking to support a DBMS (at least it didn’t a few years ago, ca. 2002-2003). Note that NFS does and you can actually do this with Oracle on an NFS server. However, SQL Server on a CIFS share is not reliable due to limitations of the protocol. It may not even let you put files on CIFS mounted shares.
SAN
This is good for transactional applications as the cache on the RAID controllers can absorb quite large working sets. SAN RAID controllers will typically support more cache than host-based RAID controllers, particularly on high-end kit where a RAID controller might be a multiprocessor box that’s just as powerful as a server.
SANs with dual controllers also have an architecture with no single point of failure and offer many options for hot back-up. This makes them a win from a manageability and reliability perspective. However they are expensive and constrained for streaming data volumes, although the latter is unlikely to be an issue on a transactional system.
For operational systems, SANs are almost always the best choice if available. They can also be shared between multiple servers running low-mid volume systems. However they come with a price tag that puts quite a substantial lower bound on the smallest system that the technology can be used with.
Direct Attach
In some cases, direct attach storage is best. One possibility is bandwidth constrained streaming applications, where the limited number of fibre channel connections will constrain the available bandwidth to less than might be possible with a high-end SAS controller. However, these are likely to be fairly specialised applications such as very large data warehouses where a shared-nothing architecture may provide the best throughput.
In fact, direct attach storage often better than a SAN for data warehouse systems for a number of reasons:
-
Data warehouses put large transient load spikes on disk subsystems. This makes them quite anti-social on SANs as they can affect the performance of other systems on the SAN.
-
The aforementioned streaming bottleneck.
-
Direct attach storage is quite a lot cheaper than SAN storage.
Another market for direct-attach storage is when you are selling to a market that will not pay enough money for a SAN. This is often true of applications sold to SMB customers. For a point-of-sale system or practice management system that will have six users a SAN is probably overkill. In this type of situation a small stand-alone tower server with some internal disks is a far more appropriate solution.