Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008

Problem

SQL Server 2008 provides the capability of storing BLOBs (e.g. MP3, Word, Excel, PDF, etc.) in the NTFS file system rather than in a database file. I am interested in how to configure the FILESTREAM technology in Management Studio. Could you help us to understand the benefits and provide details on how to implement this new feature in SQL Server 2008? Further, I am interested in how to use this functionality with the C# programming language.

Solution

A BLOB is a binary large object. Video, audio, image and document files (e.g. MP3, Word, Excel, PDF, etc.) are all examples of BLOBs. Applications often want to store and retrieve these various BLOBs together with the typical relational data. SQL Server has supported storing BLOBs in the database for quite some time. SQL Server 2008 provides a new option for storing BLOBs on the NTFS file system in lieu of in a database file. According to Books on Line use FILESTREAM when:

You are storing BLOBs with an average size of 1 megabyte or more

Fast read access is important

You want to access BLOBs from your application's middle tier code

The advantages of using FILESTREAM are:

You store and retrieve your BLOBs together with your relational data in a single data store

The BLOBs are included in database backups and restores

Inserting, updating and deleting BLOBs and your relational data happens in the same database transaction

The 2 GB max size for a varbinary(max) column doesn't apply; you are only limited by the available space on the NTFS file system

The SQL Server buffer pool memory is not used to operate on BLOBs; in previous versions large BLOBs could consume a lot of this memory

All BLOB access can be performed from .NET code, allowing your middle tier code to easily work with these objects

The NTFS file system can save and retrieve large BLOBs faster than SQL Server

There are several configuration steps that need to be performed in order to use FILESTREAM. The main one is creating a filegroup specifically for storing BLOBs. As BLOBs are inserted, updated or deleted, the action is transparently performed on the NTFS file system rather than in a database file. You will typically perform these actions on the BLOBs from .NET code rather than T-SQL.

In this tip we are going to gain an understanding of FILESTREAM by walking through a simple code sample to demonstrate the steps to:

Configure FILESTREAM

Add a BLOB from T-SQL code

Add a BLOB from C# code

The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008.

Configure FILESTREAM

There are a couple of configuration steps required in order to use the FILESTREAM feature:

Enable FILESTREAM for the database instance

Create a filegroup for BLOB storage

Create a table with a BLOB column

To enable FILESTREAM for the database instance, execute the system stored procedure sp_filestream_configure:

EXEC sp_filestream_configure
@enable_level = 3
, @share_name = N'FS';

The valid values for the @enable_level parameter are:

0 = disabled (this is the default)

1 = enabled only for T-SQL access

2 = enabled for T-SQL access and local file system access

3 = enabled for T-SQL access, local file system access, and remote file system access

The @share_name parameter is used to create a file share that can be used to access the BLOBs via the file system.

You can also enable FILESTREAM for the database instance from SQL Server Management Studio (SSMS). Right click on the database instance (i.e. root node) in the Object Explorer, then select Properties from the context menu. Click Advanced and set the Filestream Access Level to Full access enabled.

You can examine the status of FILESTREAM support on the database instance by executing this query:

You should see the ConfiguredLevel =3 and EffectiveLevel = 3. As of this writing (February 2008 CTP) changing the Filestream Access Level requires a restart of the database instance. When you enable FILESTREAM you would see the ConfiguredLevel = the value you specified for the @enable_level parameter and EffectiveLevel would be 0. Right click on the database instance in Object Explorer then select Restart from the context menu.

In order to store BLOBs on the NTFS file system you need to create a filegroup with the CONTAINS FILESTREAM attribute. Execute the following script to add a filegroup to an existing database (change the database name and filename as appropriate):

Note that for a FILE that is added to a FILESTREAM filegroup, the FILENAME parameter is actually a path. In the example above C:\db must already exist; a folder named fs cannot already exist; it will be created under C:\db.

The final configuration step for FILESTREAM is to create a table to hold the BLOBs. For example:

To store a BLOB using FILESTREAM you must have a column of type VARBINARY(MAX) with the FILESTREAM attribute specified. In addition the table must have a UNIQUEIDENTIFIER column with the ROWGUIDCOL attribute.

Add a BLOB from T-SQL

To add a BLOB from T-SQL, you execute an INSERT statement, and simply CAST the BLOB contents to VARBINARY(MAX). Since the BLOB column type is VARBINARY(MAX), selecting it will render in hexadecimal. The PathName() function will return the UNC path to the BLOB file. Notice that the last part of the path is the value of the UNIQUEIDENTIFIER column.

You can use the T-SQL UPDATE statement to replace the contents of the BLOB. You can use the T-SQL DELETE statement to delete it. Even though you are issuing T-SQL commands, the action on the BLOB is being performed on the NTFS file system.

Add a BLOB from C#

Probably the most likely scenario for using FILESTREAM is where you will be manipulating the BLOBs from your application's middle tier code. For demonstration purposes we'll create a simple example in C# that can save a BLOB and retrieve a BLOB. To keep the demo code simple, we'll create a console application where you specify command line arguments; for example:

BLOB [ get | put ] filename [id]

Get is used to retrieve a BLOB; you must also specify id which is the UNIQUEIDENTIFIER that identifies the row to retrieve

Put is used to save a BLOB

Filename is the full path to the BLOB file; put will read the file from the file system and save it as a BLOB in the database, get will retrieve the BLOB from the database using the id and write it out to this filename on the NTFS file system

Let's review a few snippets from the sample code (the full Visual Studio 2008 project is available here). The declaration below is used to call the OpenSqlFilestream API function from C# code. OpenSqlFilestream is not part of the .NET Framework, therefore you need the declaration below to call it from .NET code. This function is used to both retrieve and save the BLOB. Note that this API function is actually contained in sqlncli10.dll, which is the SQL 2008 Native Client DLL.

To add a BLOB we will execute the stored procedure dbo.stp_AddBLOB. This procedure inserts a row into our BLOB table with an empty BLOB column, then returns a result set with the UNIQUEIDENTIFIER value for the row and the path to the BLOB. The path is what we need to pass to the OpenSqlFilestream API function in order to read or write the BLOB from our C# code.

To retrieve a BLOB we will call the stored procedure dbo.stp_GetBLOB. It requires the UNIQUEIDENTIFIER value for the row as a parameter then returns the path for the BLOB.

Let's review the key points in the C# code that saves and retrieves the BLOBs. The partial code snippet below saves a BLOB:

The numbered comments in the above code identify the key points for discussion:

Read the filename specified on the command line into a byte array.

Invoke the dbo.stp_AddBLOB stored procedure to add an empty BLOB to the database and return the path to the BLOB. Note that this call is made inside of a transaction.

Get the transaction context. This value will be passed as a parameter to the OpenSqlFilestream function call, allowing it to participate in the current transaction.

Call the OpenSqlFilestream function to create the handle necessary to write the BLOB to the NTFS file system.

Create a FileStream object which will use the handle from step 4 and actually write the BLOB to the NTFS file system. The FileStream object is part of the .NET Framework and is used for reading and writing files.

The code snippet below is used to retrieve a BLOB based on the value of the UNIQUEIDENTIFIER column in the row:

The numbered comments in the above code identify the key points for discussion:

Invoke the stored procedure dbo.stp_GetBLOB to get the path for the BLOB identified by the blobID which is a UNIQUEIDENTIFIER passed in on the command line. Note that this call is made inside of a transaction.

Get the transaction context. This value will be passed as a parameter to the OpenSqlFilestream function call, allowing it to participate in the current transaction.

Call the OpenSqlFilestream function to create the handle necessary to read the BLOB from the NTFS file system.

Create a FileStream object which will use the handle from step 3 and actually read the BLOB from the NTFS file system. The FileStream object is part of the .NET Framework and is used for reading and writing files.

Write the BLOB out to a file. This is done to validate that we have successfully retrieved the BLOB.

Next Steps

Download a copy of the latest Community Technology Preview of SQL Server 2008 from this site. The above examples were created using the February, 2008 CTP.

Download a copy of the sample code here and experiment with FILESTREAM.

Review the SQL Server 2008 Books on Line content for FILESTREAM for additional information.

Last Update: 2008-05-01

About the author

Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

We enabled Filestream on an existing server and an existing database. We altered an existing table and added columns specific to Filestream. The result was a new DocGUID column with GUIDs on all rows in the altered table and NULL on all rows for the new varbinary (filestream) column.

All code samples I have seen show how to insert a new record in this table and populate the varbinary column and we have that working.

The problem is trying to update the varbinary (filestream) column on a record that already exists. The code samples show getting the path info from the new column, then creating a SqlFileStream object and writing to it. The path returns NULL on all pre-existing records.

Have you ever managed to put more than 1 filestream column into one table? For migration purposes I need to put 6 objects (filestream instead of image) into the table (per record, that is). I used sql server 2008, and it seemed impossible. Or could it be that 2012 does offer that functionality and 2008 doesn't?

Open SQL Server Configuration Manager, click on SQL Server Services (tree view on left side of window) then right click SQL Server (MSSQLSERVER) in the list of services, and select Properties.
You will see a FILESTREAM tab. Click that tab then make sure to check Allow remote clients to have streaming access to FIELSTREAM data.
You may need to restart the SQL Server service for this to take effect. However after checking allow remote clients the query you note above will return 3 for both the Configured Level and Effective Level.
I checked this out in the release version of SQL Server 2008. I no longer have any of the CTP versions. It is possible that something changed between CTP and release.

Thanks for all that information, very helpful. I just have a small problem. I didn't enable filestream during installation, and know I'm struggling to get it 100%. All my settings are as you describe. In SQL Server Configuration Manager, I have enabled FILESTREAM up to level 'Allow remote clients to have streaming access to FILESTREAM Data'. The Filestream access level under Advanced properties on the instance is set to 'Full access enabled'. I have even managed to create a filestream filegroup on my database, and created a table with a filestream field to which I can add data.

You should be able to move the database by doing a backup and a restore.Enable FILESTREAM on the target database (command example in the article).Backup the source database; e.g. BACKUP DATABASE <sourcedbname> TO DISK = '<path>\sourcedbname.bak'Restore the database backup to the target: e.g. RESTORE DATABASE <sourcedbname> FROM DISK = '<path>\sourcedbname.bak'

If you need to restore the database files to a different location than theyexist in the source, check the MOVE option in the RESTORE command; see http://technet.microsoft.com/en-us/library/ms186858(SQL.100).aspx

Use RESTORE FILELISTONLY FROM DISK = '<path>\sourcedbname.bak' to get a look at thethe full path of the files in the source database. Without the MOVEoption they will be restored to exactly the same path.

Partial example of RESTORE FILELISTONLY output for a backup of the "sample" database:

Do you have any information - links, experience, advice, etc. - on how to move a SQL 2008 Filestream database to another server? I have used this article's information to develop a document versioning application on my local machine w/ the 2008 Feb. CTP, and now it's time to move it to a public development server, and I can't get it over there to save my life without completely recreating it from scratch. Any ideas would be appreciated.

The general recommendation for a SQL Server FILE is that you use locally attached storeage or SAN. However you can use trace flag 1807 to use a mapped or UNC location; see this KB article: http://support.microsoft.com/kb/304261 for the details.

I haven't seen anything definitive on whether this applies to the FILESTREAM filegroup.

The account that the SQL Server service runs as automatically gets permission to access the FILESTREAM container.

When you create the filegroup, can this be a UNC or does it have to be a drive letter? If the drive is not on the physcial server, then I would assume the service account running the DB Engine service would need rights to the remote server or is it a different account?

Thanks for the good info. Glad to see this feature in 2008.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.