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:

_{(source: sql-server-performance.com)}

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

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);
    }
}