Is there an alternative of using a VBScript for enabling FileStream after SQL Server installation?

Question

To use Filestream on a DB 3 steps must be done:

1) enable it a server/instance level

2) enable it (sp_configure) at DB level

3) create a varbinary(max) field that supports filestream

(2) and (3) are done easily with T-SQL

(1) is doable manually from SQL Server Configuration Manager, basically what I need is to check all the 3 checkboxes:

but how is it possible to automize it?

I found this artcile "Enabling filestream usin a VBScript", is there another way to do it than using VBScripts? May be something that is possible to do only with 2008R2?

In case it VBScript is the only solution, which are the possible downsides?

Answer 1

Just run this.

USE master
Go
EXEC sp_configure 'show advanced options'
GO
EXEC sp_configure filestream_access_level, 3
GO
EXEC sp_filestream_configure 
  @enable_level = 3
, @share_name = N'FS';
GO
RECONFIGURE WITH OVERRIDE
GO

More on this

http://www.mssqltips.com/tip.asp?tip=1489

0 = disabled (this is the default)

1 = enabled only for T-SQL access

2 = enabled for T-SQL access and local file system access

3 = enabled for T-SQL access, local file system access, and remote file system access

You can store the script in a stored procedure and call it from your application or anywhere you want.

Here're links on this topic

http://www.mssqltips.com/tip.asp?tip=1838

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/03/filestream-configuration-and-setup-changes-in-sql-server-2008-february-ctp.aspx

http://technet.microsoft.com/en-us/library/cc645923.aspx

http://www.sql-server-performance.com/articles/dba/Configure_Filestream_in_SQL_Server_2008_p1.aspx

EDIT

Answer to your comment.

Here's what I call step 2

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'c:\data\archlog1.ldf')
GO

http://technet.microsoft.com/en-us/library/cc645585.aspx

Check link for all steps

http://stackoverflow.com/questions/2795911/filestream-in-sql-server-2008-express/2801080#2801080

Good Luck!

Answer 2

The only way other than clicking in the Configuration Manager is via WMI (which is what the VBScript does). If you don't like VB, here's how I've been configuring it from C# (note that the code needs to run with admin privileges (elevated)):

private ManagementObject GetFilestreamManagementObject(string machineName, string instanceName)
{
    string managementPath = string.Format(@"\\{0}\root\Microsoft\SqlServer\ComputerManagement10", machineName);
    ManagementScope managementScope = new ManagementScope(managementPath);
    managementScope.Connect();

    SelectQuery query = new SelectQuery("FilestreamSettings", string.Format("InstanceName='{0}'", instanceName));
    using (ManagementObjectSearcher searcher = new ManagementObjectSearcher(managementScope, query))
    {
        ManagementObjectCollection moc = searcher.Get();
        if (1 != moc.Count)
        {
            string exceptionText = String.Format("Expected single instance of FilestreamSettings WMI object, found {0}.", moc.Count);
            throw new FilestreamConfigurationException(exceptionText);
        }
        ManagementObjectCollection.ManagementObjectEnumerator enumerator = moc.GetEnumerator();
        if (false == enumerator.MoveNext())
        {
            throw new FilestreamConfigurationException("Couldn't move ManagementObjectEnumerator to the first entry.");
        }
        return (ManagementObject)enumerator.Current;
    }
}

private void EnableFilestream(int accessLevel)
{
    ManagementObject filestreamSettingsObject = GetFilestreamManagementObject("myMachine", "MSSQLSERVER");
    ManagementBaseObject methodArgs = filestreamSettingsObject.GetMethodParameters("EnableFilestream");
    methodArgs["AccessLevel"] = accessLevel;
    methodArgs["ShareName"] = ""; //default

    ManagementBaseObject returnObject = filestreamSettingsObject.InvokeMethod("EnableFilestream", methodArgs, null);
    if (returnObject == null)
    {
        throw new FilestreamConfigurationException("Result of calling filestreamSettingsObject.InvokeMethod(\"EnableFilestream\", methodArgs, null)" is null);
    }
    uint returnValue = (uint)returnObject.GetPropertyValue("ReturnValue");

    const uint errorSuccessRestartRequired = 0x80070BC3;
    if (returnValue != 0 && returnValue != errorSuccessRestartRequired)
    {
        Win32Exception win32Exception = new Win32Exception((int)returnValue);
        string exceptionText =
                string.Format("'EnableFilestream' method returned {0}: {1}", returnValue, win32Exception.Message);
        throw new FilestreamConfigurationException(exceptionText);
    }
}

Answer 3

I've tried to do this via the C# and WMI code posted by Pawel Marciniak but it doesn't work when run locally (yes, I run with admin rights)... only when I run it on the web-server and point it to a remote machine.

The error I am getting is that no FileStream objects can be found. Does anyone have anything else they could suggest to try?

Here's what I'm running... (Win7 64bit, .NET 4, SQL2008 SP1).

Microsoft SQL Server Management Studio 10.0.2531.0

Microsoft Analysis Services Client Tools 10.0.1600.22

Microsoft Data Access Components (MDAC) 6.1.7600.16385

Microsoft MSXML 3.0 5.0 6.0

Microsoft Internet Explorer 8.0.7600.16385

Microsoft .NET Framework 2.0.50727.4927

Operating System 6.1.7600

And I also have VS2008 and VS2010 installed, so express is running side-by-side with the full SQL dev edition.

Greg

Answer 4

Pawel's solution worked great for us. We were seeing about a 50% failure rate using the VBS -- haven't seen a failure yet with Pawel's approach. Unlike Greg's results, it has worked great for us against a local system. Actually, that's all we have tried it with.

We did have to make a couple of adjustments to Pawel's code. The line

throw new FilestreamConfigurationException("Result of calling filestreamSettingsObject.InvokeMethod(\"EnableFilestream\", methodArgs, null)" is null);

has the final quote character out of place. It should be after the "is null", right before the ");".

We also had to make sure we got the instanceName built correctly. For example, if we had "mymachine\myinstance", we had to make sure that "instanceName=myinstance" and not the full name. Further, if we had "mymachine" (the default instance), we had to have "instanceName=MSSQLSERVER". Maybe that is Greg's problem -- when we had the instanceName set to the wrong thing, we got the same results Greg reports.