Install SQL Server with SMB Fileshare as a Storage Option
Starting with SQL Server 2012, system databases (Master, Model, MSDB, and TempDB), and Database Engine user databases can be installed with Server Message Block (SMB) file server as a storage option. This applies to both SQL Server stand-alone and SQL Server failover cluster installations (FCI).
Filestream is currently not supported on an SMB file share.
SMB File share Formats:
While specifying the SMB file share, the following are supported Universal Naming Convention (UNC) Path formats for standalone and FCI databases:
For more information about Universal Naming Convention, see UNC (http://go.microsoft.com/fwlink/?LinkId=245534).
The loopback UNC path (a UNC path whose server name is localhost, 127.0.0.1, or the local machine name) is not supported. As a special case, SQL Server using File Server Cluster which is hosted on the same node SQL Server is running is also not supported. To prevent this situation, it is recommended that SQL Server and File Server Cluster to be created on separated Windows Clusters.
The below UNC 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.
Supported Data Definition Language (DDL) statements
The following Transact-SQL DDL statements and database engine stored procedures support SMB file shares:
In setup UI “Database Engine Configuration” page, “Data Directories” tab, set the parameter “Data root directory as “\\fileserver1\share1\”.
In command prompt installation, specify the “/INSTALLSQLDATADIR” as “\\fileserver1\share1\”.
Here is the sample syntax to install SQL Server on a Standalone server using SMB file share option:
Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="<DomainName\UserName>" /AGTSVCPASSWORD="<StrongPassword>" /INSTALLSQLDATADIR="\\FileServer\Share1\" /IACCEPTSQLSERVERLICENSETERMS
To install a single-node SQL Server failover cluster instance with the Database Engine and Analysis Services, default instance:
setup.exe /q /ACTION=InstallFailoverCluster /InstanceName=MSSQLSERVER /INDICATEPROGRESS /ASSYSADMINACCOUNTS="<DomainName\UserName>" /ASDATADIR=<Drive>:\OLAP\Data /ASLOGDIR=<Drive>:\OLAP\Log /ASBACKUPDIR=<Drive>:\OLAP\Backup /ASCONFIGDIR=<Drive>:\OLAP\Config /ASTEMPDIR=<Drive>:\OLAP\Temp /FAILOVERCLUSTERDISKS="<Cluster Disk Resource Name - for example, 'Disk S:'" /FAILOVERCLUSTERNETWORKNAME="<Insert Network Name>" /FAILOVERCLUSTERIPADDRESSES="IPv4;xx.xxx.xx.xx;Cluster Network;xxx.xxx.xxx.x" /FAILOVERCLUSTERGROUP="MSSQLSERVER" /Features=AS,SQL /ASSVCACCOUNT="<DomainName\UserName>" /ASSVCPASSWORD="xxxxxxxxxxx" /AGTSVCACCOUNT="<DomainName\UserName>" /AGTSVCPASSWORD="xxxxxxxxxxx" /INSTALLSQLDATADIR="\\FileServer\Share1\" /SQLCOLLATION="SQL_Latin1_General_CP1_CS_AS" /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="xxxxxxxxxxx" /SQLSYSADMINACCOUNTS="<DomainName\UserName> /IACCEPTSQLSERVERLICENSETERMS
For more information about the usage of various command line parameter options in SQL Server 2016, see Install SQL Server 2016 from the Command Prompt.
Different Windows operating systems has different SMB protocol versions, and the SMB protocol version is transparent to SQL Server. You can find the benefits of different SMB protocol versions with respect to SQL Server 2016.
SMB2 protocol version
Benefits to SQL Server
Windows Server 2008 SP 2
Windows Server 2008 R2 SP 1, including Server Core
Windows Server 2012, including Server Core
Windows Server 2012 R2, including Server Core
The SQL Server service account and SQL Server agent service account should have FULL CONTROL share permissions and NTFS permissions on the SMB share folders. The SQL Server service account can be a domain account or a system account if an SMB file server is used. For more information about share and NTFS permissions, see Share and NTFS Permissions on a File Server (http://go.microsoft.com/fwlink/?LinkId=245535).
The FULL CONTROL share permissions and NTFS permissions on the SMB share folders should be restricted to: SQL Server service account, SQL Server Agent service account and windows users with admin server roles.
It is recommended to use domain account as a SQL Server service account. If system account is used as a service account, grant the permissions for the machine account in the format: <domain_name>\<computer_name>$.
During SQL Server setup, it is required to specify domain account as a service account if SMB file share is specified as a storage option. With SMB file share, System account can only be specified as a service account post SQL Server installation.
Virtual accounts cannot be authenticated to a remote location. All virtual accounts use the permission of machine account. Provision the machine account in the format <domain_name>\<computer_name>$.
The account used to install SQL Server should have FULL CONTROL permissions on the SMB file share folder used as the data directory, or any other data folders (User database directory, user database log directory, TempDB directory, TempDB log directory, backup directory) during Cluster Setup.
The account used to install SQL Server should be granted SeSecurityPrivilege privileges on the SMB file server. To grant this privilege, use the Local Security Policy console on the file server to add the SQL Server setup account to the Manage auditing and security log policy. This setting is available in the User Rights Assignments section under Local Policies in the Local Security Policy console.
After you detach a SQL Server 2016 database that resides on network-attached storage, you might run into database permission issue while trying to reattach the SQL Server database. The issue is defined in this KB article (http://go.microsoft.com/fwlink/?LinkId=237321). To work around this issue, see the More Information section in the KB article.
If SMB file share is used as a storage option for a clustered instance of SQL Server, by default the SQL Server Failover Cluster Diagnostics Log cannot be written to the file share because SQL Server Resource DLL lacks the read/write permission on the file share. To resolve this issue, try one of the following methods:
Grant read/write permissions on the file share to all computer objects in the cluster.
Set the location of the diagnostic logs to a local file path. See the following example: