Configure Filestream in SQL Server 2008

SQL Server 2008 introduces a new feature called FILESTREAM which enables SQL Server based applications to store unstructured data such as documents, images, audios, videos etc. in the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact SQL statements users can insert, update, delete and select the data stored in FILESTREAM enabled tables.

Configure and Enable Filestream during SQL Server 2008 Installation
SQL Server DBA's can enable the FILESTREAM feature in SQL Server 2008 during the installation process. The option to enable FILESTREAM will be available in the Database Engine Configuration screen of the SQL Server 2008 setup. To enable FILESTREAM for Transact-SQL access, a DBA needs to select Enable FILESTREAM for Transact-SQL access option. Once the option is enabled all the other access controlling options will be available for configuration. Select Enable FILESTREAM for file I/O streaming access option to enable Win32 streaming access for the files. Then you need to provide the name of the windows share where the FILESTREAM data will be stored for access. Select the Allow remote clients to have streaming access to FILESTREAM data control, in case you plan to allow remote clients to access this FILESTREAM data on the server. In the below snippet we have enabled the FILESTREAM and also provide a windows share name for allowing file I/O streaming access.



During SQL Server 2008 installation, if you forget to enable the FILESTREAM feature, then the feature can be enabled after the installation by following the methods below.

Configure and Enable Filestream using SQL Server Configuration Manager

1. Click the Start Menu | Programs | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager
2. In the left panel, click on SQL Server Services which will list all the SQL Server 2008 related services in the right panel
3. Click on the SQL Server (MSSQLSERVER) Service Instance on which you want to enable FILESTREAM; and right click to see the properties of the service
4. In the SQL Server Properties dialog box, click the FILESTREAM tab
5. Select Enable FILESTREAM for Transact-SQL access checkbox, which will enable rest of the options
6. If you want to provide read and write FILESTREAM data access from windows then select the option to Enable FILESTREAM for I/O streaming access and enter the name of the windows share
7. The last option, Allow remote clients to have streaming access to FILESTREAM data will allow remote clients to access FILESTREAM data on the server.
8. Click Apply to save the changes

Configure and Enable Filestream using TSQL Query

1. In the SQL Server Management Studio, click the new query window and type the below TSQL Query

Use Master
Go
EXEC sp_configure 'filestream access level', 2
Go
RECONFIGURE
Go

SQL Server 2008 supports 3 levels of FILESTREAM access

a. If the value is 0 then, FILESTREAM support for the instance is Disabled
b. If the value is 1 then, FILESTREAM for Transact-SQL Access is Enabled
c. If the value is 2 then, FILESTREAM for Transact-SQL and Win32 streaming access is Enabled
Configure and Enable Filestream at Server Instance Level Using GUI

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio
2. In Object Explorer, right click the server and select properties to view the Server Property window
3. Under Advanced, Click on the drop down next to Filestream Access Level and select Full access enabled
4. To do this the user needs to be a member of the sysadmin or serveradmin fixed server role to change the default settings for Filestream
5. Click Ok to save changes

How to create a Filestream Database
The FILESTREAM feature uses a special type of filegroup when you create the database. You need to specify the CONTAINS FILESTREAM clause for at least one filegroup in the create database statement.

Use Master
Go
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FileStreamDB')
DROP DATABASE FileStreamDB
GO

USE Master
GO
CREATE DATABASE FileStreamDB ON PRIMARY
( NAME = FileStreamDB_Data,
FILENAME = N'D:\FileStream\FileStreamDB_Data.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 15%),
FILEGROUP MyDBData
( NAME = MyFileStream_Data,
FILENAME = N'D:\FileStream\FileStreamDB_Data.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB),
FILEGROUP FileStream CONTAINS FILESTREAM
( NAME = FileStream,
FILENAME = N'D:\FileStream\FileStreamData')
LOG ON
( NAME = 'FileStreamDB_Log',
FILENAME = N'D:\FileStream\FileStreamDB_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB);
GO

The image below shows the properties of FileStreamDB Database which was created by executing the above TSQL statement:



The below snippet displays the files which got created for FileStreamDB database in D:\FileStream folder. You will see a folder named FileStreamData which will store all the varbinary (max) values.



Within the D:\FileStream\FileStreamData folder you can see a sub folder named $FSLOG and a file named filestream.hdr, this file is a header file for the FILESTEAM container.


How to create a table to store Filestream data
If a table needs to store FILESTREAM data then the table should have a ROWGUIDCOL column defined. This is used by the storage engine to keep track of instances in the NTFS file system. The column which will be used to store the Filestream data should have varbinary (max) data type and FILESTREAM attribute. In SQL Server 2008 varbinary (max) data type can store more than 2 GB of data. The TSQL Query below can be used to create the FileStreamTable table:

USE FileStreamDB
Go
CREATE TABLE dbo.FileStreamTable
(
FS_ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
FSData VARBINARY(MAX) FILESTREAM
);

How to insert data into FileStreamTable
Execute the below TSQL to insert the Filestream data into the FileStreamTable table in the FileStreamDB database.

USE FileStreamDB
Go
INSERT INTO FileStreamTable
VALUES(newid(), CAST ('Inserting data into FileStreamTable........' As VARBINARY(MAX)))
Go

Once the data is successfully inserted you can see a new folder getting created under D:\FileStream\FileStreamData folder.

Advantages of FILESTREAM

• BLOB data can now be stored in the NTFS system and it’s under the control of SQL Server 2008
• SQL Server will maintain the transactional consistency for all the BLOB data
• There are no limitations for the size of the file which can be stored in FILESTREAM . As long as you have space on the disk drive that much bigger FILESTREAM files can be stored
• When the FILESTREAM enabled database is backed up, all the BLOB files in the specified FileStreamData folder will also be backed up which will help the DBA to manage the BLOB easily
• Data can be easily accessed through TSQL and the NTFS streaming APIs with great streaming performance

Disadvantages of Filestream

• Database mirroring cannot be configured on database which has FILESTREAM enabled on it
• Database snapshots don’t snapshot FILESTREAM data which will be there in the FILESTREAM database

Conclusion
The FILESTREAM feature of SQL Server 2008 helps organizations to manage their BLOB data more efficiently as they can store the data in the NTFS file system and take advantage of the benefits of SQL Server’s transactional consistency. Database Administrators can now easily take the backup of FILESTREAM data in the organization by just taking the backup of the FILESTREAM database rather than taking a backup of each BLOB file independently. This allows them to save lot of time and more over there will no data loss by not backing up some files.