SQL over SMB2 - One of the top 10 hidden gems in SQL Server 2008 R2

[アーティクル]
02/24/2011

Introduction

The SQL CAT team has posted a blog last year about the "Top 10 hidden gems in SQL Server 2008 R2", which included the support for placing SQL database files on an SMB network file share. This works even better if you use the SMB2 version of the protocol, which is included in Windows Server 2008 and Windows Server 2008 R2. In a recent presentation about the performance, scalability and availability improvements in the Windows Server 2008 R2 file services, I used this "SQL over SMB2" scenario in one of my demos. It was interesting that many in the audience were unaware of this feature in SQL Server and were actually surprised by it. I guess at least this one feature continues to be a "hidden gem".

Here's a quote from that SQL CAT post:

SMB stands for Server Message Block and this protocol is now officially supported by SQL Server 2008 R2 and beyond. This improvement has formalized the support status of placing SQL database files on SMB network file shares. From Kevin Farlee, the owner of this feature in SQL Server: “This presents a better-together story with the work that Windows has done in Windows 7 and Windows Server 2008 R2 to make the Windows SMB stack far more performant and resilient than in the past. It is also a recognition that with the increasing acceptance of iSCSI, customers are viewing Ethernet as a viable way to connect to their storage. Finally, it gives customers in consolidation environments a very simple to manage method for moving databases between servers without investing in a large SAN infrastructure.”

How to use “SQL over SMB2”

To use “SQL over SMB2” in SQL Server 2008 R2, you simply have to use a UNC path like “\fileserversharedatabase.mdf” in the FILENAME parameter when creating a new database. Here’s a sample command to create a database:

CREATE DATABASE Sales

ON (NAME=Sales_data, FILENAME='\JOSE-S4SQLDATASALESDATA.MDF')

LOG ON (NAME=Sales_log, FILENAME='\JOSE-S4SQLLOGSSALESLOG.LDF');

You want to make sure that the account used to run SQL Server has “Full Control” permissions to the file share and NTFS folder where you want to place the database files and log files. If your SQL Server instance is running under “Network Service”, you should grant the share and folder permissions to the computer account of the computer running SQL Server.

In SQL Server 2008 R2, you can’t place a system database on an SMB2 share or configure SQL Server in a failover cluster using SMB2 as shared storage.

“SQL over SMB2” and earlier versions of SQL Server

In SQL Server 2008 and earlier versions, trying to create a database in an SMB2 file share will return an error like this:

Msg 5110, Level 16, State 2, Line 1

The file "\JOSE-S4SQLDATASALESDATA.MDF" is on a network path that is not supported for database files.

Msg 1802, Level 16, State 1, Line 1

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

In SQL Server versions before SQL Server 2008 R2, you could enable this behavior using trace flag 1807 (running the “DBCC TRACEON(1807)” command), but this was not a supported scenario for that version of SQL Server. This old behavior is described in KB article 304261 (https://support.microsoft.com/kb/304261).

Performance Recommendations

Obviously, you want to make sure your file server is configured properly and can deliver the throughput and IOPS required for your specific scenario, just like you would using other types of storage like DAS, FC SAN and iSCSI SAN. Here are a few recommendations to improve performance of “SQL over SMB2”:

· Use Windows Server 2008 R2 on both the SQL Server and File Server site, so they can negotiate up to SMB version 2.1.

· Create different shares for database files and log files, each using a different volume and a different set of spindles.

· Create the file shares on volumes using multiple spindles, in a RAID-10 configuration.

· Use at least 1GbE NIC (10GbE if possible) and watch if the network is becoming a bottleneck.

· Use multiple network paths between the SQL Server and the File Server, with different paths for database files and log files.

As you can probably recognize, many of these recommendations are similar to the recommendations regarding other types of storage used with SQL Server, specially iSCSI SANs.

Conclusion

If you never experimented with “SQL over SMB2” before, give it a try. You will find a whole lot of flexibility and, with the right storage subsystem and network infrastructure, it will should perform beyond your expectations.

Related Links

Post from the SQL CAT team and details on main features and the 10 hidden gems in SQL Server 2008 R2:

https://sqlcat.com/technicalnotes/archive/2010/05/07/top-10-hidden-gems-in-sql-2008-r2.aspx

Details on the differences between SMB1 and SMB2:

https://blogs.technet.com/b/josebda/archive/2008/12/09/smb2-a-complete-redesign-of-the-main-remote-file-protocol-for-windows.aspx

Details on the differences between SMB2 to SMB 2.1:

https://www.snia.org/events/storage-developer2009/presentations/tuesday/DavidKruse_SMBv21.pdf

Details on which SMB version your configuration is using:

https://blogs.technet.com/b/josebda/archive/2010/10/26/what-version-of-smb2-am-i-using-on-my-windows-file-server.aspx