Skip to content

Latest commit

 

History

History
125 lines (81 loc) · 12.7 KB

install-sql-server-with-smb-fileshare-as-a-storage-option.md

File metadata and controls

125 lines (81 loc) · 12.7 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom monikerRange
Install SQL Server With SMB Fileshare Storage
In SQL Server, system databases and Database Engine user databases can be installed with Server Message Block (SMB) file server as a storage option.
rwestMSFT
randolphwest
01/08/2025
sql
install
install-set-up-deploy
intro-installation
>=sql-server-2016

Install SQL Server with SMB fileshare storage

[!INCLUDE SQL Server -Windows Only]

In [!INCLUDE ssSQL11] and later versions, system databases (master, model, msdb, and tempdb), and [!INCLUDE ssDE] user databases can be installed with Server Message Block (SMB) file server as a storage option. This applies to both [!INCLUDE ssNoVersion] stand-alone and [!INCLUDE ssNoVersion] failover cluster installations (FCI).

Note

FILESTREAM is currently not supported on an SMB file share.

Installation considerations

SMB fileshare formats

When you specify the SMB file share, the following values are supported universal naming convention (UNC) path formats for standalone and FCI databases:

  • \\ServerName\ShareName\
  • \\ServerName\ShareName

For more information, see Universal Naming Convention.

The loopback UNC path (a UNC path whose server name is localhost, 127.0.0.1, or the local machine name) isn't supported. As a special case, [!INCLUDE ssNoVersion] with File Server Cluster hosted on the same node as [!INCLUDE ssNoVersion] is also not supported. To prevent this situation, you should create [!INCLUDE ssNoVersion] and the File Server Cluster on separated Windows Clusters.

The following UNC path formats aren't supported:

  • Loopback path, such as \\localhost\...\ or \\127.0.0.1\...\
  • Administrative shares, such as \\servername\x$
  • Other UNC path formats like \\?\x:\
  • Mapped network drives

Supported data definition language (DDL) statements

The following [!INCLUDE tsql] DDL statements and database engine stored procedures support SMB file shares:

  1. CREATE DATABASE
  2. ALTER DATABASE
  3. RESTORE statements
  4. BACKUP

Installation options

  • In the setup user interface, on the Database Engine Configuration page, on the Data Directories tab, set the parameter Data root directory as \\<FileServer>\<Share1>\. Replace <FileServer> and <Share1> with values from your environment.

  • In the command prompt installation, specify /INSTALLSQLDATADIR as \\<FileServer>\<Share1>\.

    Here's the sample syntax to install [!INCLUDE ssNoVersion] on a standalone server using the SMB file share option:

    Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<password>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="<DomainName\UserName>" /AGTSVCPASSWORD="<password>" /INSTALLSQLDATADIR="\\FileServer\Share1\" /IACCEPTSQLSERVERLICENSETERMS

    [!INCLUDE sql-eula-link]

    To install a single-node [!INCLUDE ssNoVersion] failover cluster instance with the [!INCLUDE ssDE] and [!INCLUDE ssASnoversion], 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="<password>" /AGTSVCACCOUNT="<DomainName\UserName>" /AGTSVCPASSWORD="<password>" /INSTALLSQLDATADIR="\\FileServer\Share1\" /SQLCOLLATION="SQL_Latin1_General_CP1_CS_AS" /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<password>" /SQLSYSADMINACCOUNTS="<DomainName\UserName> /IACCEPTSQLSERVERLICENSETERMS

    For more information about the usage of various command-line parameter options in [!INCLUDE ssnoversion], see Install and configure SQL Server on Windows from the command prompt.

Note

Your passwords should follow the [!INCLUDE ssnoversion-md] default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.

Operating system considerations (SMB protocol vs. SQL Server)

Different Windows operating systems have different SMB protocol versions, and the SMB protocol version is transparent to [!INCLUDE ssNoVersion]. You can find the benefits of different SMB protocol versions with respect to [!INCLUDE ssnoversion].

Operating system SMB2 protocol version Benefits to SQL Server
[!INCLUDE winserver2012] and later versions, including Server Core 3.0 Support for transparent failover of file shares providing zero downtime, with no intervention required for the database administrator or file server administrator in file server cluster configurations.

Support for IO using multiple network interfaces simultaneously, and tolerance to network interface failure.

Support for network interfaces with RDMA capabilities.

For more information on these features and Server Message Block, see Server Message Block overview.

Support for Scale Out File Server (SoFS) with continuous availability.
[!INCLUDE winserver2012] R2 and later versions, including Server Core 3.2 Support for transparent failover of file shares providing zero downtime, with no intervention required for the database administrator or file server administrator in file server cluster configurations.

Support for IO using multiple network interfaces simultaneously, and tolerance to network interface failure, using SMB Multichannel.

Support for network interfaces with RDMA capabilities using SMB Direct.

For more information on these features and Server Message Block, see Server Message Block overview.

Support for Scale Out File Server (SoFS) with continuous availability.

Optimized for small random read/write I/O common to [!INCLUDE ssNoVersion] transactional style workloads.

Maximum Transmission Unit (MTU) is turned on by default, which significantly enhances performance in large sequential transfers like [!INCLUDE ssNoVersion] data warehouse and database backup or restore.

Security considerations

  • The [!INCLUDE ssNoVersion] service account and [!INCLUDE ssNoVersion] agent service account should have FULL CONTROL share permissions and NTFS permissions on the SMB share folders. The [!INCLUDE ssNoVersion] 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.

    [!NOTE]
    The FULL CONTROL share permissions and NTFS permissions on the SMB share folders should be restricted to the [!INCLUDE ssNoVersion] service account, the [!INCLUDE ssNoVersion] Agent service account, and Windows users with administrator server roles.

    Use a domain account as a [!INCLUDE ssNoVersion] 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>*$*.

    [!NOTE]
    During [!INCLUDE ssNoVersion] setup, you must specify the domain account as a service account if the SMB file share is specified as a storage option. With SMB file share, the System account can only be specified as a service account after installing [!INCLUDE ssNoVersion].

    Virtual accounts can't be authenticated to a remote location. All virtual accounts use the permission of the machine account. Provision the machine account in the format <domain-name>\<computer-name>*$*.

  • The account used to install [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] setup account to the Manage auditing and security log policy. This setting is available in the User Rights Assignments section under Local Policies.

Known issues and limitations

After you detach a [!INCLUDE ssnoversion] database that resides on network-attached storage, you might run into database permission issues while trying to reattach the [!INCLUDE ssNoVersion] database. For more information, see Error 5120.

If SMB file share is used as a storage option for a clustered instance of [!INCLUDE ssNoVersion], by default the [!INCLUDE ssNoVersion] Failover Cluster Diagnostics Log can't be written to the file share because the [!INCLUDE ssNoVersion] Resource DLL lacks read/write permission on the file share. To resolve this issue, try one of the following methods:

  1. Grant read/write permissions on the file share to all computer objects in the cluster.

  2. Set the location of the diagnostic logs to a local file path. See the following example:

    ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG PATH = 'C:\logs';

When you host [!INCLUDE ssnoversion] data files on SMB file shares, all I/O against the files goes through the network interface on the server or virtual machine. Ensure that there's enough network bandwidth to support the I/O required by the workload.

Unavailability of the file share hosting the [!INCLUDE ssnoversion] data files due to network connectivity issues or other failure might result in I/O delays or failures in [!INCLUDE ssnoversion]. For mission critical workloads, ensure there's redundancy built into the network and file share and that the file share supports SMB 3.0 transparent failover, also known as continuous availability.

Related content