How to Configure SQL Server Database to Support FILESTREAM

When you install Microsoft SQL Server, the FILESTREAM feature is not enabled. To store Proxy recording files as FILESTREAMS, you configure SQL Server to support FILESTREAM.
cminder12902
When you install Microsoft SQL Server, the FILESTREAM feature is not enabled. To store Proxy recording files as FILESTREAMS, you configure SQL Server to support FILESTREAM.
Ignore Step 3 in case you are configuring SQL Server to support FILESTREAM before installing the Enterprise Management Server. When you install Enterprise Management Server, the installer creates FILESTREAM enabled recording tables in case the SQL Server and the recording database are FILESTREAM enabled.
Enable FILESTREAM on SQL Server
You enable FILESTREAM on an instance of SQL Server using SQL Server Configuration Manager and SQL Server Management Studio.
Perform the following actions:
  1. Navigate to 
    Start
    All Programs
    SQL Server
    Configuration Tools
    , and click 
    SQL Server Configuration Manager
    .
  2. Right-click 
    SQL Server Services
    , and click 
    Open
    .
  3. Locate the instance of SQL Server on which you want to enable FILESTREAM, in the SQL Server Configuration Manager snap-in.
  4. Right-click the instance, and click 
    Properties
    .
  5. In the 
    SQL Server Properties
     screen, click 
    FILESTREAM
    .
  6. Select 
    Enable FILESTREAM for Transact-SQL access
    .
  7. Select 
    Enable FILESTREAM for file I/O streaming access 
    to read and write FILESTREAM data from Windows. Provide the name of the Windows share.
  8. Select 
    Allow remote clients to have streaming access to FILESTREAM data
     to let remote clients access the FILESTREAM data on the share.
  9. Click 
    Apply
    .
  10. In the SQL Server Management Studio, click 
    New Query.
  11. In the Query Editor, type the following Transact-SQL code.
    EXEC sp_configure filestream_access_level, 2 RECONFIGURE
  12. Click 
    Execute
  13. Restart the SQL Server service.
You have enabled FILESTREAM on SQL Server.
Enable FILESTREAM on a Database
After enabling FILESTREAM on an instance of the SQL Server, you configure database to support FILESTREAM. You add a file group in the database and indicate that the file group contains FILESTREAM objects. 
Follow these steps:
  1. Add a file group that contains the FILESTREAM objects to the database by running the following commands:
    ALTER DATABASE <Database> ADD FILEGROUP <FileStreamGroup> CONTAINS FILESTREAM GO
  2. Indicate the location to the file group to store the FILESTREAM data by running the following commands:
    ALTER DATABASE <Database> ADD FILE (NAME = FileStreamData, FILENAME = 'Drive:\<FolderName>\FileStreamData') TO FILEGROUP FileStreamFileGroup GO
    An error occurs, if you fail to create a folder on a drive to store the FILESTREAM data.
Example
: In this example, you add the file group 
Proxy_FileStreamGroup
 to the DBWind database and indicate that the group contains FILESTREAMS. You also indicate to the 
Proxy_FileStreamGroup
 the path 
C:\Filegroup\filestreamdata
 to store the FILESTREAM data.
 
ALTER DATABASE DBWind ADD FILEGROUP Proxy_FileStreamGroup CONTAINS FILESTREAM GO ALTER DATABASE DBWind ADD FILE (NAME = Proxy_FileStreamData, FILENAME = 'C:\FileGroup\Proxy_FileStreamData') TO FILEGROUP Proxy_FileStreamGroup GO
You have enabled FILESTREAM on a database.
Enable FILESTREAM on a Database Table
After you enable FILESTREAM on the database, you configure the Proxy recording tables and columns to support FILESTREAM.
Perform this step when you configure FILESTREAM on SQL Server after installing the Enterprise Management Server only.  
Follow these steps:
  1. From the Query Editor window, modify the Proxy recording tables to support FILESTREAM by running the following commands:
    ALTER TABLE RECORDING_FILE SET (FILESTREAM_ON = <FileStreamGroup>)
  2. Modify the table column to support FILESTREAM.
    1. Add a column that supports FILESTREAM.
      ALTER TABLE RECORDING_FILE ADD RECORDING_FILE_NEW varbinary(max) FILESTREAM
    2. Copy the content of the old column to the new column that supports FILESTREAM.
      UPDATE RECORDING_FILE SET RECORDING_FILE_NEW = RECORDING_FILE
    3. Delete the old column.
      ALTER TABLE RECORDING_FILE DROP COLUMN RECORDING_FILE
    4. Rename the new column name to the old one. Log in as the Enterprise Management Server database user to execute this command.
      EXEC sp_rename 'RECORDING_FILE.RECORDING_FILE_NEW','RECORDING_FILE','COLUMN'
You have enabled FILESTREAM on the Proxy recording tables and columns.