Configuring FILESTREAM

Configuring FILESTREAM

                              FILESTREAM enables SQL Server–based applications to store unstructured data, such as

images and documents, on the host computer’s file system. To use FILESTREAM, you must create or modify a database to host a special type of filegroup, after which you can create or modify tables so that they can use the varbinary(max) column with the FILESTREAM attribute.

                    You should use FILESTREAM under the following conditions:

• Objects that you want to store are greater than 1 MB. The traditional varbinary(max) limit of 2 GB does not apply to BLOBs (binary large objects) stored in the file system.

• Fast read access is importantFor objects smaller than 1 MB, use the varbinary(max) BLOB data type. You can’t enable FILESTREAM if you are running a 32-bit version of SQL Server 2012 on a 64-bit operating system.

To enable FILESTREAM, perform the following steps:

1. Open SQL Server Configuration Manager from the Configuration Tools folder.

2. Edit the properties of the instance on which you want to enable FILESTREAM.

3. On the FILESTREAM tab, select Enable FILESTREAM For Transact-SQL Access. You can

also use this dialog box to enable FILESTREAM for file I/O streaming access and to

allow rem Server Management Studio, execute the following query:

EXEC sp_configure filestream_access_level, 2    RECONFIGURE

5. Restart the SQL Server Service related to the instance on which you are enabling

FILESTREAM by using SQL Server Configuration Manager.

6. Create a FILESTREAM filegroup for the database. For example, to create a FILESTREAM

filegroup named FileStreamFileGroup for the Litware2012 database, use the following

query:

USE master

GO

ALTER DATABASE Litware2012 ADD

FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM;

GO

7. Add FILESTREAM files to the FILESTREAM filegroup by specifying a folder location that

does not currently exist. For example, to create and associate the C:\FSTRM directory

with the FILESTREAM file named FileStrmFile in the FileStreamFileGroup FILESTREAM

filegroup for the Litware2012 database, use the following query:

USE master

GO

ALTER DATABASE Litware2012 ADD FILE (NAME = FileStrmFile,

FILENAME = 'C:\FSTRM'

TO FILEGROUP FileStreamFileGroup