Storage Systems - SQL Server 2012 Internals and Troubleshooting - page 85

Block-Based Storage vs. File-Based Storage
The operating system, in this case Windows, uses NTFS to create a structure that enables it to use
one or more blocks to store i les. When a server accesses a physical disk directly, it is called block-
based access. When data is accessed over a server network, such as TCP/IP, it is called i le data.
Devices that provide i le access are called network-attached storage (NAS).
Disk drives store data in blocks. Each block contains 512 bytes of data (some storage arrays use
520-byte blocks — the extra 8 bits dei ne a checksum used to guarantee data integrity).
NOTE Disk technology changes over time. In order to facilitate larger disk drive
sizes, disk manufacturers are now implementing a larger 4KB data block. SQL
Server is designed to utilize this new block size. See this Microsoft customer
support blog for more information: http://blogs.msdn.com/b/psssql/
archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx
Disk drive data blocks are individually numbered by the disk i rmware in a scheme using what are
called logical block numbers (LBNs) .
NOTE SQL Server best practices recommend that NTFS partitions used for data
and log i les be formatted using 64K allocation clusters. This setting maximizes
efi ciency by minimizing wasted space. NTFS reserves space for the MFT$ based
on the partition's size. Each i le needs a 1KB allocation record in the MFT$.
Because only a few data and log i les are stored on a partition, and 64KB clus-
ters align with 64KB data extents used by SQL Server, it makes perfect sense to
minimize the size of an MFT$. If the partition is used for storing many smaller
i les then it should be formatted using the standard 4KB cluster size. Running out
of allocation units will result in a fragmented MFT$, thus signii cantly harming
i le access performance on that partition.
Starting with SQL Server 2008 R2, storage administrators have the option of using Server Message
Block (SMB) networks to access data i les. Technet offers a detailed overview of the advantages of
SMB here: http://technet.microsoft.com/en-us/library/ff625695(WS.10).aspx .
SQL Server 2012 supports SMB version 3.0 which offers improved performance over earlier
versions. For more information on coni guring SQL Server 2012 with SMB 3.0 visit: http://msdn
.microsoft.com/en-us/library/hh759341.aspx .
Setting up an SMB network enables you to connect to your i le over a UNC path (\\ server_name \
share ). This access can greatly simplify the setup of network-based storage, although you should use
caution and specii cally check to ensure that your particular system is supported, as NAS devices
often are not supported for use in this coni guration.