To use FILESTREAM feature both Windows administrator and SQL Server administrator have to enable it. While enabling from Windows there are three options to choose from:
- Enable FILESTREAM for Transact-SQL Access check box.
- Enable FILESTREAM for file I/O streaming access. This is to read and write FILESTREAM data from Windows. Enter the name of the Windows share in the Windows Share Name box.
- Select Allow remote clients to have streaming access to FILESTREAM data. This is to let remote clients access the FILESTREAM data stored on this share.
For SQL Server instance there are three options to choose from:
- 0 – Disable FILESTREAM support for this instance
- 1 – enable FILESTREAM for Transact-SQL access only
- 2 – enable FILESTREAM for Transact-SQL and Win32 streaming access
Note that FILESTREAM would perform worse for 1MB+ size files for full read/write if Transact-SQL is chosen (and Win32 streaming is not chosen) than no FILESTREAM BLOB.
Now that FILESTREAM is enabled, we can go ahead to do the rest of the setups.
First, create a file group to keep the FILESTREAM data. There is a 1:1 mapping between data containers and file groups.
ALTER DATABASE MyDatabase ADD FILEGROUP MyFileStreamGroup1 CONTAINS FILESTREAM;
Second, define data container. In the below example, “D:\Production” must exist already. “D:\Production\FileStreamDataContainerRoot” is going to be the root of the data container.
ALTER DATABASE MyDatabase ADD FILE ( NAME = FileStreamGroup1File, FILENAME = 'D:\Production\FileStreamDataContainerRoot') TO FILEGROUP MyFileStreamGroup1 ;
Third, create a table with a FILESTREAM column. All FILESTREAM columns of a table must go to a single FILESTREAM group that can be specified, defaulting to default FILESTREAM filegroup. Table must have a column of the non-null uniqueidentifier data type with ROWGUIDCOL attribute and either a UNIQUE or PRIMARY KEY single-column constraint. FILESTREAM data container will have a directory for a table with subdirectory corresponding to the FILESTREAM column of the table.
USE MyDatabase ; GO CREATE TABLE TableWithFileStreamColumn ( DocumentID INT IDENTITY PRIMARY KEY, Document VARBINARY (MAX) FILESTREAM NULL, DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID ()) FILESTREAM_ON MyFileStreamGroup1;