Implementation of SMB file share to host SQL databases: A walk-through
Recently I worked on a scenario where the requirement was to host more than 25 SQL server instances on a cluster. SQL supports 50 instances on a failover cluster if SMB file shares are used to host the databases. In this blog, I have shared the steps to implement the SMB file share as a storage option in the SQL Server.
By default, SQL Server doesn’t allow creation of SQL databases on a network path. This isn’t true from SQL 2012 onwards though. The screenshots below demonstrate the error in SQL 2008 R2 for your reference.
Figure 1: Creating database on Network share in SQL 2008 R2.
Figure 2: Error while creating the database
The Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files. But this is not recommended practice.
Reference: https://support2.microsoft.com/default.aspx?scid=kb;EN-US;304261
Starting SQL 2012, the databases (including system databases) can be installed on a Server Message block (SMB) file server. This is applicable to both the stand alone and cluster instances. With SMB implementation SQL Server supports 50 instances on a failover cluster if you choose SMB file shares as the storage option for your cluster installation.
Note: Filestream is not supported on an SMB file share.
To demonstrate the SMB file share solution with SQL Server, I created two Windows 2012 R2 clusters: one to host SQL resources and the other to host file share resources. This walkthrough provides an example of how to use SMB file share location to host SQL databases. The names and numbers of servers used are for example purposes only.
SQL:
Windows virtual cluster name: SQLCluster:
Node 1: SQLNode1
Node 2: SQLNode2
File share:
Windows virtual cluster name: SMBCluster:
Node 1: SMBNode1
Node 2: SMBNode2
File share resources: Fileshare1 and Fileshare2
SQL Cluster:
Figure 3: SQL cluster
SMB Cluster:
Figure 4: SMB cluster
Figure 5: File share resources
Figure 6: File share location: \\Fileshare1\Share1
Figure 7: File share location: \\Fileshare2\Share2
How to check if the file share resources are configured correctly? If you access the file share UNC path from the SQL cluster nodes, they should be accessible.
Now during the time of the SQL Cluster installation, specify the location of the file share which is already configured.
Figure 8: Database Engine Configuration Window.
SQL Setup displays the below warning message when file share location is chosen:
“You have specified a file server as the data directory \\Fileshare1\Share1. To avoid possible failures in the installation process, you must verify that the SQL Server service account has full control share permissions on the specified file server before continuing.”
Figure 9: Warning Message
Figure 10: SQL setup configuration
Figure 11: Successful installation of SQL.
Successfully installed SQL cluster. Using the SQL Add node operation, added the node SQLNode2 as a part of the existing SQL cluster.
To confirm the user database default location is set to file share location:
Figure 12: Database default location Window.
Created a database “SMBDB” at the file share location successfully:
Figure 13: Database hosted on file share.
Figure 14: Database location
Alright, now the databases are hosted on file shares. What about database reliability and performance? This blog has addressed these: https://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/10/18/sql-databases-on-file-shares-it-s-time-to-reconsider-the-scenario.aspx
I hope the guidelines in the blog helps.
Reference Links:
Install SQL Server with SMB file share as a storage option:
https://msdn.microsoft.com/en-us/library/hh759341(v=sql.110).aspx
Description of support for network database files in SQL Server
https://support.microsoft.com/kb/304261
Create a SQL Server Database on a network shared drive
https://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx
How to create file shares on a cluster
https://support.microsoft.com/kb/224967
SQL over SMB2 - One of the top 10 hidden gems in SQL Server 2008 R2
https://blogs.technet.com/b/josebda/archive/2011/02/24/sql-over-smb2-one-of-the-top-10-hidden-gems-in-sql-server-2008-r2.aspx
Please share your feedback, questions and/or suggestions.
Thanks,
Don Castelino | SQL Server Support Team | CSS – Microsoft
Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.