Changing FILESTREAM Settings

By default, FILESTREAM is disabled. Before starting to use it on a SQL Server instance, FILESTREAM needs to be enabled on the instance. The FILESTREAMSettings class provides an EnableFILESTREAM method that accepts two parameters:
EnableFILESTREAM(System.UInt32 AccessLevel, System.String ShareName)

Note: FILESTREAM Access Level Values
0----Disables FILESTREAM support for this instance
1----Enables FILESTREAM for Transact-SQL access
2----Enables FILESTREAM for Transact-SQL and local file system access
3----Enables FILESTREAM for Transact-SQL, local file system access, and remote file system access

 

To enable FILESTREAM for Transact-SQL and local file system access(EnableFileStream.ps1):

# . for the local computer. If you want to connect to a remote machine, specify the machine name here.
$strComputer = "."
# Name of the targeted SQL Server instance. Here the default instance is targeted.For a named instance INSTANCE1, use "INSTANCE1".
$strInstanceName = "MSSQLSERVER"
$wmi=Get-WmiObject -computerName $strComputer -namespace root\Microsoft\SqlServer\ComputerManagement10 `
-class FILESTREAMSettings -filter "InstanceName=’$strInstanceName’"
# Prints out the AccessLevel property before changing it.
Write-Host "The access level of FILESTREAM before the change is set to"
$wmi.AccessLevel ", and the file share name is " $wmi.ShareName
$wmi.EnableFILESTREAM(2, ‘MSSQLSERVER’) | Out-Null
$wmi=Get-WmiObject -computerName $strComputer -namespace root\Microsoft\SqlServer\ComputerManagement10 `
-class FILESTREAMSettings -filter "InstanceName=’$strInstanceName’"
# Confirm the AccessLevel property has been set.
Write-Host "The access level of FILESTREAM after the change is set to"
$wmi.AccessLevel ", and the file share name is " $wmi.ShareName

 

Note that you still need to restart the SQL Server instance and reconfigure the server option in SSMS to fully enable FILESTREAM. For example, run the following statement in SSMS to enable FILESTREAM for Transact-SQL and Win32 streaming access:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE