SQL Server 2012 - FileStream Enhancements

This post explains the FileStream Concept and its Enhancements in SQL Server 2012 with an example. At a high level this post covers following topics:

• What is FileStream
• How to Configure FileStream
• An Example to leverage FileStream
• FileStream Enhancements in SQL Server 2012

First we will look at what is FileStream. In Short FileStream is the ability to store unstructured data like documents, images on the file system but still be managed by SQL server, which means these files are part of Database Managment activities like Backup & Restore.

By Default FileStream is Disabled for database server instance. This feature can be configured during the SQL Server Installation or even post Installation. Following steps explain on how we can enable & configure FileStream.

Open SQL Server Configuration Manager by Clicking Start -> All Programs -> Microsoft SQL Server 2012 ->  Configuration Tools Folder -> SQL Server Configuration Manager

In the SQL Server Configuration Manager console highlight the SQL Server Services as shown in the picture

Select the SQL Server instances in the right pane for which you want to enable FILESTREAM, Right click on the server and select properties as shown in the picture:

In the property window select FileStream Tab as shown below

Check the FILESTREAM For Transact-SQL Access to enable FileStream and then select Enable FILESTREAM For File I/O Access so that we can read and write data from Windows. And also type the name of the Windows share name "FileStreamShare" or you can even leave it to default value as shown below

If there is a need for the remote clients to access the data then we need to enable Allow Remote Clients Access To FILESTREAM Data otherwise leave it to default. Now go ahead click Apply followed by OK button.

With this we completed enabling FileStream on a SQL Server Instance. Now we will see in the following steps on we can enable this feature for a particular database.

Let us start with creating a Database called "FileStreamSample" using following script which is generated in SSMS (SQL Server Management Studio).

To execute the script, Open SSMS by Clicking Start -> All Programs -> Microsoft SQL Server 2012 -> SQL Server Management Studio and then copy & paste following code. Now execute the code either by pressing F5 key in the keyboard or by clicking on execute icon.

CREATE DATABASE [FileStreamSample]  CONTAINMENT = NONE  ON  PRIMARY ( NAME = N'FileStreamSample', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamSample.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )  LOG ON ( NAME = N'FileStreamSample_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamSample_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO ALTER DATABASE [FileStreamSample] SET COMPATIBILITY_LEVEL = 110 GO ALTER DATABASE [FileStreamSample] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [FileStreamSample] SET ANSI_NULLS OFF GO ALTER DATABASE [FileStreamSample] SET ANSI_PADDING OFF GO ALTER DATABASE [FileStreamSample] SET ANSI_WARNINGS OFF GO ALTER DATABASE [FileStreamSample] SET ARITHABORT OFF GO ALTER DATABASE [FileStreamSample] SET AUTO_CLOSE OFF GO ALTER DATABASE [FileStreamSample] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [FileStreamSample] SET AUTO_SHRINK OFF GO ALTER DATABASE [FileStreamSample] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [FileStreamSample] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [FileStreamSample] SET CURSOR_DEFAULT  GLOBAL GO ALTER DATABASE [FileStreamSample] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [FileStreamSample] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [FileStreamSample] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [FileStreamSample] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [FileStreamSample] SET  DISABLE_BROKER GO ALTER DATABASE [FileStreamSample] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [FileStreamSample] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [FileStreamSample] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [FileStreamSample] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [FileStreamSample] SET  READ_WRITE GO ALTER DATABASE [FileStreamSample] SET RECOVERY FULL GO ALTER DATABASE [FileStreamSample] SET  MULTI_USER GO ALTER DATABASE [FileStreamSample] SET PAGE_VERIFY CHECKSUM  GO ALTER DATABASE [FileStreamSample] SET TARGET_RECOVERY_TIME = 0 SECONDS GO USE [FileStreamSample] GO IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [FileStreamSample] MODIFY FILEGROUP [PRIMARY] DEFAULT GO

Once Database is created, Select the database and click on "New Query" Icon as shown below:

Now we have to configure the file stream access level for the database "FileStreamSample" by executing the following Transact-SQL code.

EXEC sp_configure filestream_access_level, 2 RECONFIGURE

There are three access level options for FileStream as described below:

0 -> No FileStream Support
1 -> Enable FileStream for Transact-SQL access only
2 -> Enable FileStream for Transact-SQL as well as Win32 Streaming access

In our example we are going with the last option as to provide support for Win32 Streaming.

Now In Query Editor, type the above Transact-SQL code and then click on Execute as shown below:

The final step is to configuring File Group & Database Files to store FileStream Data. Execute the following commands in the Query Editor:

Use Master ALTER DATABASE [FileStreamSample] ADD FILEGROUP [FileStreamSampleFG] CONTAINS FILESTREAM GO ALTER DATABASE [FileStreamSample] ADD FILE ( NAME = N'FileStreamSample_FilestreamFile', FILENAME = N'C:\FileStreamSample_FilestreamFile' ) TO FILEGROUP [FileStreamSampleFG] GO

We can also create the File Group and Files using Managment Studio Interface itself but its always easy to go with Transact-SQL Script.

Now lets create a table using following SQL Script:

Create Table [FSExample] (     [fid] uniqueidentifier ROWGUIDCOL  NOT NULL Unique,     [sid] [int] NOT NULL Unique,     [fcontent] [varbinary](max) FileStream NULL )

There are certain point we need to keep in mind while designing tables containing FileStream. Here are few important point:

1. FileStream columns must be accompanied by a corresponding uniqueidentifier ROWGUID column.
2. These kind of table must also be accompanied by a unique index
3. ROWGUIDCOL is required if we need to access FileStream data using Win32 APIs
4. FileStream File Groups and Containers should reside on volumes other than the OS

All the DML operations are pretty much same as any other table. Following are some of the examples:

Insert Into FSExample values (newid(), 1, CAST('my first content' as varbinary(max))) Select * from FSExample Update FSExample     Set fcontent = CAST('Updated content' as varbinary(max)) WHERE sid = 1 Select * from FSExample Delete FROM FSExample WHERE sid=1 Select * from FSExample

While executing these commands keep an eye on folder "C:\FileStreamSample_FilestreamFile", you will notice news files after insert operations.

FileStream Enhancements in SQL Server 2012

There are many enhancements to FileStream in SQL Server 2012. Some of them are:

Ability to support multiple containers & file groups
Ability to specify the max size for the containers
Ability to add multiple storage devices for scalability

You can refer to my previous post on GC related improvements @ SQL Server FileStream GC Made Easy