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.
cminder12901
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:
- Navigate toStart,All Programs,SQL Server,Configuration Tools, and clickSQL Server Configuration Manager.
- Right-clickSQL Server Services, and clickOpen.
- Locate the instance of SQL Server on which you want to enable FILESTREAM, in the SQL Server Configuration Manager snap-in.
- Right-click the instance, and clickProperties.
- In theSQL Server Propertiesscreen, clickFILESTREAM.
- SelectEnable FILESTREAM for Transact-SQL access.
- SelectEnable FILESTREAM for file I/O streaming accessto read and write FILESTREAM data from Windows. Provide the name of the Windows share.
- SelectAllow remote clients to have streaming access to FILESTREAM datato let remote clients access the FILESTREAM data on the share.
- ClickApply.
- In the SQL Server Management Studio, clickNew Query.
- In the Query Editor, type the following Transact-SQL code.EXEC sp_configure filestream_access_level, 2 RECONFIGURE
- ClickExecute.
- 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:
- 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
- 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 GOAn 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:
- 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>)
- Modify the table column to support FILESTREAM.
- Add a column that supports FILESTREAM.ALTER TABLE RECORDING_FILE ADD RECORDING_FILE_NEW varbinary(max) FILESTREAM
- Copy the content of the old column to the new column that supports FILESTREAM.UPDATE RECORDING_FILE SET RECORDING_FILE_NEW = RECORDING_FILE
- Delete the old column.ALTER TABLE RECORDING_FILE DROP COLUMN RECORDING_FILE
- 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.