With the improvements in the Windows Server 2012 implementation it’s now feasible to install SQL Server on SMB network shares. This applies to both standalone SQL server installations as well as clustered SQL Server installation. Before that, it was possible with the use of trace flag 1807 but this was never a desired configuration. However, Microsoft didn’t support this because of the risks of network errors compromising database integrity. There were several reasons for this. Before Windows Server 2012, SMB file shares were really too slow to support I/O intensive SQL Server workloads. Plus, the SMB protocol was never designed for applications like SQL Server which keeps files open for long periods of time. Instead, the SMB protocol was really designed to support file servers which have many frequent file opens and closes. In contrast SQL Server typically holds its MDF and LDF files open for as long as the server is active. Windows Server 2012 implemented a number of significant improvements in the SMB file sharing protocol. As a part of Windows Server 2012,Microsoft released SMB 3.0 which provided a number of new performance and availability enhancements including:
- SMB Transparent Failover — Enables SMB clients transparently reconnect to another cluster node without interrupting server applications that are storing data on these file shares. This enables administrators to perform hardware or software maintenance without interrupting server applications, it also can protect applications from hardware or software failures.
- SMB Scale Out — Using Cluster Shared Volumes (CSV) version 2, administrators can create file shares that provide simultaneous direct access to data files through all nodes in a file server cluster. This provides better network bandwidth utilization and load balancing for file server clients.
- SMB Multichannel — Enables the aggregation of network bandwidth as well as network fault tolerance if there are multiple paths that are available between the SMB 3.0 clients and the SMB 3.0 server.
- SMB Direct – Provides high performance network transport with the use of network adapters that have RDMA (Remote Direct Memory Access) capability. This enables the network adapter to function at full speed using minimal CPU resources. SMB Direct enables a remote file server to resemble local storage when used with workloads like Microsoft SQL Server.
Leveraging these new features starting with Windows Server 2012 and SQL Server 2012 both system databases (Master, Model, MSDB, and tempdb) and user databases can be installed on SMB file shares. This applies to both SQL Server stand-alone and SQL Server failover cluster installations (FCI).
Requirements and Limitations
There are a few requirements that you need to meet in order to implement SQL Server on SMB. First you need to be using the SQL Server 2012 version or higher. Next , the SQL Server service and SQL Agent service accounts must have FULL CONTROL share permissions and NTFS permissions on the SMB shares where you intend to store your databases.
There are also some limitations about running SQL Server on SMB file shares. For instance, the Filestream data type is not currently supported on an SMB file share. In addition,you need to use the Universal Naming Convention (UNC) Path formats when you specify your database storage locations. SQL Server supports using either \\ServerName\ShareName\ or \\ServerName\ShareName.
The following path formats are not supported:
- Loopback path, e.g., \\localhost\..\ or \\127.0.0.1\…\
- Administrative shares, e.g., \\servername\x$
- Other UNC path formats like \\?\x:\
- Mapped network drives
In part 1, I covered some of the background and requirements for installing SQL Server on SMB file shares. In part 2, I’ll dive into more of the implementation details.
Leave a Reply