The Truth Behind FilestreamEffectiveLevel and FilestreamConfiguredLevel SERVERPROPERTY values

There is a lot of confusion (not in small part due to Books Online’s inaccurate descriptions) around the meaning of the SQL Server server properties FilestreamEffectiveLevel and FilestreamConfiguredLevel. There are 2 problems with the way Microsoft describes these properties: the list of values they can have is incomplete, and their actual description (as well as their name) is incorrect and misleading.

Possible Values for FilestreamEffectiveLevel and FilestreamConfiguredLevel

Books Online (at http://msdn.microsoft.com/en-us/library/cc645956.aspx) lists 3 possible values for these properties:

• 0 (zero) means that filestream is disabled.
• 1 means that only T-SQL access to filestream data is allowed.
• 2 means that T-SQL access and local streaming access is allowed.

So, there is actually also a level 3, which means that T-SQL access is allowed, as well as local and remote streaming access. Notice the word “remote,” that’s the critical difference. If the FILESTREAM access level is 2, only applications running on the same host as the SQL Server instance will be able to access FILESTREAM data using the Win32 I/O streaming API.

UPDATE: Paul Randal from SQLSkills.com documented this part a long time ago in his blog at http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-Enabling-FILESTREAM-through-T-SQL-and-SSMS.aspx. (Note that this post was based on pre-release software and some things have changed since then, like sp_filestream_configure doesn’t exist anymore.)

Interestingly, there are four enum values in the FilestreamEffectiveLevel enumeration (Microsoft.SqlServer.Management.Smo namespace in assembly Microsoft.SqlServer.Smo) listed on http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.filestreameffectivelevel.aspx. The names of these values are actually very descriptive and (to me) correctly describe the meaning of each value. Which is more than can be said from the explanation of the enum values on that same page. At best, they are very ambiguous, at worst, they are incorrect.

Also of note is that Microsoft has actually published a Knowledge Base (KB) article indicating the existence of the value ‘3’ for these options. Unfortunately, the article only indicates that “3 is stored internally.” That’s not entirely correct… As discussed below, if the FilestreamConfiguredLevel is set to 2, then the FilestreamEffectiveValue will not be 3, it will be 2.

Meaning of FilestreamEffectiveLevel versus FilestreamConfiguredLevel

This is probably the biggest source of confusion. Books Online (here: http://msdn.microsoft.com/en-us/library/ms174396(v=sql.100).aspx) as well as whitepapers published by Microsoft indicate that the FilestreamEffectiveLevel property returns the current level of FILESTREAM access, whereas the FilestreamConfiguredLevel returns the value that is configured. The reason that is provided why these may be different has to do with the fact that restarts are required (sometimes) when the value is changed.

This is very misleading. In effect, these server properties refer to two completely different settings. In order to successfully configure the SQL Server FILESTREAM feature AFTER installation of SQL Server, two steps are required:

1. Enable filestream (at the appropriate access level) using SQL Server Configuration Manager. See the screen shot below of the FILESTREAM tab of the SQL Server property dialog box.
   
   This is a similar dialog to what is seen during SQL Server setup (described at http://msdn.microsoft.com/en-us/library/cc645996.aspx). However, when enabling FILESTREAM during setup, you don’t need to perform step 2.
2. Enable FILESTREAM at the SQL Server instance level (using SSMS or sp_configure).

So, what happens when you set the value in step 1 to “full” access (meaning T-SQL, local and remote streaming), but the value in step 2 to ‘1’ (meaning T-SQL access only)? Well, you’ll only have T-SQL access. When you set the value in step 1 to ”T-SQL only” and in step 2 to ‘3’ (meaning T-SQL and local and remote streaming). You’ll still only have T-SQL access.

Why Two Steps/Settings?

Just a quick side note: why would there be two steps that need to be completed to enable FILESTREAM after installation, but only one step during installation?

This has to do with how administrative tasks in most large IT organizations are divided: there is a system administrator who manages the operating systems on the server and a DBA who manages the SQL Server instances. To avoid turf wars (?), it wouldn’t be fair for the DBA to be able to create a new file share on the server without the involvement of the Windows administrator. Therefore, the Windows administrator has to agree to the notion of streaming access to the FILESTREAM data (which involves creating a file share, albeit it a special one) before the DBA can actually activate it.

UPDATE: This reasoning is documented in this SQL Server product team blog post: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/03/filestream-configuration-and-setup-changes-in-sql-server-2008-february-ctp.aspx

During setup, you have to have Administrator rights on the machine, and of course, you can give yourself sysadmin rights on the new SQL Server instance during setup, so that’s why Microsoft saves you from performing the second step. (If they hadn’t, I am sure plenty of others would have noted these incorrect descriptions.)

The Truth…

The reality is that FilestreamConfiguredLevel returns the value set in the SQL Server Configuration Manager by the Windows administrator and the FilestreamEffectiveLevel returns the lower of the two values. Because the SQL Server instance value cannot override the value set in Configuration Manager, FilestreamEffectiveLevel can indeed be described as the “effective” level.

You can actually query for the value that’s configured at the instance level by executing this query:

sp_configure filestream_access_level

This will return five columns, one of which is config_value, which indicates the configured value at the instance level.

Furthermore, if Configuration Manager allows any kind of streaming access (local or local and remote), the file share will exist. It’ll be useless if the value at the instance is set to Transact-SQL access only, but so be it.

Verify My Claims

How can you verify my claims? Easy… First, work with a SQL Server instance that you can easily restart (that means a test instance, not a production instance). If you have already enabled “full” FILESTREAM access during setup, great (this is the assumption I am working from).

First, open both SSMS and Configuration Manager. In SSMS, create a new query with this query text:

SELECT SERVERPROPERTY('FilestreamEffectiveLevel') 'Effective',
 SERVERPROPERTY('FilestreamConfiguredLevel') 'Configured'

Execute the query. The values should be ‘3’ for both columns.

Now, in Configuration Manager, set the FILESTREAM access level to T-SQL only (or turn it off completely). Execute the query again, and lo and behold, both values are still ‘3’! According to Microsoft’s definition of FilestreamConfiguredLevel, this should already have changed.

Now, restart the server. Execute the query again. Notice both values are now ‘1’.

Next, right-click on the server instance in SSMS and click on Properties. Go the Advanced page. Find the FILESTREAM settings. What’s the setting for FILESTREAM Access Level? It’s “Full access enabled.” Sure, that’s what you set at the instance level, but don’t try to access FILESTREAM data using the Win32 I/O streaming APIs, it will fail. Why? Click on the Running Values radio button at the bottom. Now observe what the FILESTREAM Access Level there reads: “Transact-SQL access enabled.” Of course, because the Windows administrator said “no way, no file shares on this system.”

OK, so let’s go back to Configured Values and set it to “Transact-SQL access enabled.”  Click OK to close the Server Properties dialog box and return to your query window. Execute the query again. (Ignore the fact that SSMS says some changes won’t take effect until the service is restart, it’s not true in this case, as the Running Values is already “Transact-SQL access enabled.”) The values all read ‘1’, which makes sense.

Finally, return to Configuration Manager, and enable “full” access again (meaning all checkboxes on the FILESTREAM tab are checked). Restart the service again. Execute the query again. We now see different values: FilestreamEffectiveLevel reads ‘1’ and FilestreamConfiguredLevel reads ‘3’, even after restarting the service. This again contradicts the descriptions provided by Microsoft (which indicate that after a restart you should not see differences between both values).

If you want to do one more test, go back to the Server Properties dialog in SSMS, Advanced page and set FILESTREAM Access Level to “Full access enabled.” Don’t restart the service, and execute the query again. Now, Effective and Configured are the same value (‘3’).

Bottom Line

What happened here? I don’t know. Perhaps the design of the FILESTREAM feature called for FilestreamConfiguredLevel and FilestreamEffectiveLevel to mean what they are described to mean in the documentation, and the SQL Server developers got it wrong (and the testers missed it). Or perhaps the feature works as designed, but the documentation team got it wrong. Either way, the result is that confusion ensues.

In short: FilestreamConfiguredValue refers to the setting in Configuration Manager and has 4 possible values:

• 0 means that filestream is disabled.
• 1 means that only T-SQL access to filestream data is allowed.
• 2 means that T-SQL access and local streaming access is allowed.
• 3 means that T-SQL access and local and remote streaming access is allowed.

FilestreamEffectiveValue is indeed the “effective” value at the time it’s queried, and has the same 4 possible values. It is in essence the lowest value of what’s configured in Configuration Manager and in the SQL Server instance (using SSMS or sp_configure).

Restart Requirement

There is even more confusion, specifically about service restart requirements. If you change the value in Configuration Manager, you will need to restart only if you are enabling a higher level of access. If you are disabling streaming access and only allowing T-SQL access, that change takes effect immediately. However, if you are currently at T-SQL access but want to allow streaming access, you need to restart the service before the change takes effect.

If you change the value at the instance level (again, using SSMS or sp_configure), you do not need to restart, even though SSMS says you need to.

Applies To

I have observed this behavior with SQL Server 2008, 2008 R2 and 2012 RC0. Specifically, for this blog post, I validated everything with SQL Server 2012 RC0.