Do you know that you can store system and user databases in a fileshare? Even for a clustered instance this is an option now. In this article we will check how to implement this solution.
On the latest versions of SQL Server new possibilities to assign storage to a clustered instance became available. In this article, we will talk about one of these options, which is also an option for standalone instances by the way: The network-attached storage – SMB Fileshare.
At first view, this looks weird and not a good option but, in fact, Microsoft worked to make this happen. The fileshare we are talking about has the Service Message Block (SMB) network protocol supporting it, which had an evolution on its latest versions looking to achieve a better reliability and performance. As an example, here is a list of some of those improvements:
Windows Server 2008 (SMB 2.0)
Durability, which helps recover from temporary network glitches.Windows Server 2008 R2 (SMB 2.1)
Significant performance improvements, specifically for SQL OLTP style workloads.Windows Server 2012 (SMB 3.0)
Support for transparent failover of file shares providing zero downtime.Windows Server 2012 R2 (SMB 3.02)
MTU is turned on by default, which significantly enhances performance in large sequential transfers like SQL Server data warehouse and database backup or restore.- \\ServerName\ShareName\
- \\ServerName\ShareName
- Loopback path: \\localhost\..\ or \\127.0.0.1\…\
- Administrative shares: \\servername\x$
- Other UNC path formats like \\?\x:\
- Mapped network drives.
- Understanding backups on AlwaysOn Availability Groups – Part 2 - December 3, 2015
- Understanding backups on AlwaysOn Availability Groups – Part 1 - November 30, 2015
- AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4 - October 13, 2015
As you can notice, at this moment (January 2015) the current version of SMB is 3.02, the one associated to the Windows Server 2012 R2, also the current version of Windows Server. Observing the improvements, we can clearly notice that the official support of a network-attached storage is being drawn since Windows Server 2008.
The list of improvements above has only one improvement per version, as I selected the most important ones for this article, anyway more things were improved.
Store a database in a fileshare is supported since SQL Server 2008 R2, although only user databases could be stored on fileshares. Since SQL Server 2012, not only the user databases are supported, but also the system databases can be placed in SMB fileshares. The list above showed improvements since the Windows Server 2008, but SQL Server 2008, and versions before, are not officially supporting the use of fileshares.
Anyway, you can enable this possibility by activating the trace flag “1807”, by running the “DBCC TRACEON(1807)” command. However, remember that this is not supported on SQL Server 2008! You can check more details on this KB https://support.microsoft.com/kb/304261?wa=wsignin1.0.
How to implement
In order to use this capability, we need to specify the file path using the Universal Naming Convention (UNC), as the bellow examples:
Supported Universal Naming Convention (UNC):
The following methods are not supported:
Moreover, the SQL Server engine and SQL Server agent service accounts should have FULL CONTROL share permissions and NTFS permissions on the SMB share folders, otherwise we will be frustrated with a not awesome error message 🙂
To make this “magic” happen, you have a few options. For example, you can define this during the installation process:
You just specify the shares that you want to use, instead of the common paths, a warning message will be shown and that’s it! If the right permissions are set, the installation will succeed. Notice that this is valid for clustered and non clustered instances.
In case you define the shown configuration for a clustered instance, this is what you’re going to see in your SQL Server role, in the Failover Cluster Manager:
In the image above, you can see that no disks are assigned to the SQL Server role, so the disks are not controlled to the Failover Cluster. Because of this, a different approach is needed in order to monitor the instance, as if a problem with the fileshare occurs, the SQL Server service may not become offline, depending of what the fileshare is being used for. Talking about cluster, a clustered fileshare is the best option to reach the best reliability and availability of this storage option.
It is also possible to use the supported UNCs to install SQL Server using the command line, via Setup.exe.
Another possibility to define a database file stored in a fileshare, is to use the good old T-SQL! In the following example, we are creating a database with its data file and log file stored in a fileshare:
|
1 2 3 4 5 6 7 8 9 |
CREATE DATABASE [SmbBasedDB] ON PRIMARY ( name = N’ SmbBasedDB_data’, filename = N’\\filesharesrv\datafiles\SmbBasedDB.mdf’, size = 1024kb, maxsize = unlimited, filegrowth = 1024kb ) log ON ( name = N’ SmbBasedDB_log’, filename = N’\\ filesharesrv \logfiles\SmbBasedDB_log.ldf’, size = 512kb, maxsize = 1024gb, filegrowth = 15%) go |
Monitoring
As said before, the way we monitor the SQL Server storage, and also the service availability, need to be modified, as we are dealing with files stored in another server in a storage not controlled by the SQL Server’s host operating system.
It’s a good idea to have access to some performance indicators from the server where the fileshare is physically located, this way, if you have an abnormal performance problem, you first check if, for example, the physical disk counters, memory, network and CPU load of the fileshare server are ok, and then you can start troubleshooting SQL Server performance as usual. It will save your time, and avoid an extra headache…
Using SMB fileshare to store database files, will make the Network performance more than critical, so consider the use of a dedicated network to access the fileshare, avoiding more traffic in the network.
Concluding…
Use a fileshare is a real option nowadays, and I know some cases that this working without issues. Of course, it will depend of your environment, load and budget. With the Failover Cluster being able to use a fileshare as quorum, you can build an entire cluster solution, including a clustered SQL Server instance, without SAN, which is cheaper.
In other hands, have a SAN give us other possibilities that make it worth its price. For non-critical environments, this option may work really fine, if we take care of the details that we referred. In the end of the day, if you ask if this the best option to take, the default answer for most of the SQL Server related questions can be applied here: it depends. So, analyze, test, think and design the solution carefully. I hope this article is useful, see you soon 🙂
Reference
Install SQL Server with SMB Fileshare as a Storage Option
With experience working in Portugal, Holland, Germany and United Kingdom, he's always available to learn and share his knowledge, in order to contribute to SQL Server community,
View all posts by Murilo Miranda