Art of the DBA Rotating Header Image

May 2nd, 2012:

FILESTREAM on Server Core

A couple weeks ago, I did a post for T-SQL Tuesday on the Filetable, a cool new SQL 2012 feature. One thing I glossed over a little bit was enabling FILESTREAM for your SQL Server, where I just linked the MSDN article walking you through configuring the service using the Configuration Manager.  This is great as long as you have the GUI to use. However, I’ve said previously that I’m trying to do all my work (demos and blog posts) using SQL 2012 on Server core, which means no GUI. This makes it a leeeeeeeeeeeeeetle trickier and requires some more advanced techniques.

We need to flex our Powershell muscles for this. For me, this still means hitting the Google search to find the command syntax I need, though I understand it a little better. I found this excerpt from Wrox’s Microsoft SQL Server 2008 Administration with Windows PowerShell on Google Books.  The example shows, quite clearly, how to use Powershell to hit the WMI and the appropriate methods to enable FILESTREAM with the right perms and names.

I took the example there and reworked it slightly, mostly to change the namespace so that it will use the correct SQL 2012 interface.  It’s not much, when you get right down to it.  The core purpose is to create the WMI object and then use the EnableFilestream method (which may be hard to do if you don’t know the appropriate namespace to use):

instance="MSSQLSERVER"
$wmi=Get-WmiObject -namespace "root\Microsoft\SqlServer\ComputerManagement11" -class FILESTREAMSettings | where {$_.InstanceName -eq $instance}
$wmi.EnableFILESTREAM(3,$instance)

There are two parameters to the EnableFILESTREAM method. The first is access level, with 4 possible values that relate to how much access is granted to the filesystem. The easiest way to understand it is to relate the values to the settings in the GUI:

(Access level 0 is where no boxes are checked, essentially disabling FILESTREAM)

The second parameter is simply the share name, so in this example we would create\\<SERVER NAME>\MSSQLSERVER for our FILESTREAM objects.  Now, note that you still have to run the SQL to configure the SQL Server side of things (see my previous post).  This step just replaces activating the service properties from the Configuration Manager.

The other alternative to this method is to use the remote management tools to connect to the instance and alter the service settings through the GUI. I like this way better, though, because I have more control over it and I don’t need any additional connections or tools.

Edit:  I forgot to mention that this will require you to restart the SQL Service to apply (thanks Hayden Hancock for pointing this out in the comments).  Since we’re Powershelling it:

Get-Service $instance = “MSSQLSERVER” | Restart-Service