Like many other features, FILESTREAM
is disabled by default in SQL Server 2012, and you must first enable it
before the feature can be used. Enabling FILESTREAM is slightly more
involved than enabling other SQL Server features because it requires
two distinct steps. First, the feature needs to be enabled for the
machine, and then it needs to be enabled for the server instance. These
two independent FILESTREAM configuration layers are by design, and they
draw a separation of security concerns between the role of Windows
administrator and database administrator.
Enabling FILESTREAM for the Machine
The first step is to enable FILESTREAM for the machine by
setting an access level. This step can actually be performed at the
time that SQL Server is initially installed by choosing a FILESTREAM
access level during setup. To enable FILESTREAM for the machine after
SQL Server has been installed, use the SQL
Server Configuration Manager to set the access level. (This tool can be
launched from the Configuration Tools folder of the Microsoft SQL
Server 2012 program group on the Start menu.)
The SQL Server
Configuration Manager opens with a treeview on the left. In the
treeview, click SQL Server Services to display the list of available
services in the main panel. Right-click the SQL Server instance that
you want to enable FILESTREAM for, choose Properties, and in the
Properties dialog box, select the FILESTREAM tab. The three check boxes
on the FILESTREAM tab allow you to select the various levels of
FILESTREAM access. Figure 1 shows the Properties dialog box with all three check boxes selected.
When all three check boxes are cleared, FILESTREAM is completely disabled. Selecting the first check box enables FILESTREAM, but only for Transact-SQL (T-SQL) access. This provides for utter transparency, where SQL Server will store BLOBs contained inside varbinary(max) FILESTREAM columns in the file system behind the scenes just as we’ve discussed. But it won’t allow you to take advantage of direct file streaming between the database and your client applications using more advanced FILESTREAM features, such as SqlFileStream and FileTable that you’ll be learning about soon.
The
real power of FILESTREAM comes into play when you enable direct file
I/O streaming, which delivers the best possible BLOB performance with
SQL Server. You enable direct file I/O streaming access by selecting
the second check box. Streamed file access also creates a Windows share
name that is used to construct logical Universal Naming Convention
(UNC) paths to BLOB data during FILESTREAM access, as you’ll see
further on when you use SqlFileStream
and FileTable. The share name is specified in a text box after the
second check box and is set by default to the same name as the server
instance (MSSQLSERVER, in Figure 8-1).
In
most cases, client applications will not be running on the same machine
as SQL Server, and so you will usually also need to select the third
check box to enable FILESTREAM for remote client file I/O streaming
access with SqlFileStream.
In addition, the new FileTable feature in SQL Server 2012 requires that
you check this option in order to expose its data as a file system
through the Windows share created by FILESTREAM (you’ll examine
FileTable after you finish learning about FILESTREAM). So practically,
you must check all three checkboxes to get the most out of FILESTREAM . An exception to
this general practice might be when using Microsoft SQL Server 2012
Express edition as a local data store for a client application with
everything running on the same machine. In this case, you could use the
more secure setting and leave the third check box cleared. Doing so
would enable SqlFileStream and FileTable access for the local client application but deny such access to remote clients. If you are using SQL
Server Configuration Manager to change this setting after SQL Server
has already been installed, you must remember to restart the SQL Server instance for the setting change to take effect.
Note
More Info There is no T-SQL equivalent script that can set the FILESTREAM access level for the machine. However, Microsoft posts a VBScript file available over the Internet that allows you to enable FILESTREAM
from the command line as an alternative to using SQL Server
Configuration Manager.
Enabling FILESTREAM for the Server Instance
The second step is to enable FILESTREAM
for the server instance. The concept here is similar to what we just
described. Varying levels of access correspond to the checkboxes in Figure 1.
Naturally, the access level defined for the server instance must be
supported by the access level defined for the machine. Typically,
therefore, the machine and server instance access levels should be set
to match one another. FILESTREAM can be enabled for the server instance
with a simple call to the sp_configure system stored procedure, as follows:
EXEC sp_configure filestream_access_level, n
RECONFIGURE
In the preceding code, replace n with a number from 0 to 2 to set the access level. A value of 0 disables the FILESTREAM feature completely. Setting the access level to 1 enables FILESTREAM for T-SQL access only, and setting it to 2
enables FILESTREAM for full access (which includes local or remote file
I/O streaming access as enabled for the machine in the first step).
You can also set the FILESTREAM access level for the server instance in SQL Server Management Studio (SSMS)
from the Advanced Server Properties dialog box. Right-click any server
instance in Object Explorer, choose Properties, and then select the
Advanced page. The various levels are available as choices in the
Filestream Access Level drop-down list, as shown in Figure 2.
Note
You can use either SQL Server Data Tools (SSDT) inside Visual Studio or SSMS to set the access level, but only SSMS provides the ability to
set it in the graphical user interface (GUI). Using SSDT, you can view
the setting’s property from the SQL Server Object Explorer, but you can
only change it by running the EXEC statement in a query window.
