Tip

Implementing SQL Server 2008 FILESTREAM functionality

The new FILESTREAM functionality in SQL Server 2008 lets you configure a varbinary(max) column so that the actual data is stored on the file system, rather than within the database. You can still query the column as you would a regular varbinary(max) column even though the data itself is stored externally.

The FILESTREAM feature integrates Windows NTFS with the SQL Server database engine by storing binary large object (BLOB) data as files on the local file system. You can use Transact-SQL statements to query, insert or update the data, or you can use Win32 file system interfaces for direct streaming access to the data.

Microsoft recommends that you use FILESTREAM only if (1) your BLOB data files are, on average, larger than 1 MB, (2) you need fast read access to that data, and (3) your application uses a middle tier for application logic. Otherwise, you should use a regular varbinary(max) column.

If you do decide to implement a FILESTREAM column, you'll need to take the following steps:

  1. Enable FILESTREAM support on the SQL Server 2008 service.
  2. Configure the database to support FILESTREAM storage.
  3. Define a column that supports FILESTREAM storage.

Enabling FILESTREAM support on the SQL Server service

To enable FILESTREAM support on a specific instance of SQL Server 2008, you must first configure the SQL Server service for that instance.

    Requires Free Membership to View

In the SQL Server Configuration Manager, open the properties for the service and go to the FILESTREAM tab, as shown in Figure 1.


Figure 1: The FILESTREAM tab of the Properties dialog box for the SQL Server service

You must, at the very least, select the Enable FILESTREAM for Transact-SQL access checkbox. Because the most efficient way to insert and update data is through Win32 interfaces, however, you should also enable the service to support streaming (including access for remote clients, if necessary).

After you've enabled FILESTREAM support on the SQL Server service, you must then set the stream access level, which you can do in SQL Server Management Studio. To set the level, run the following T-SQL statements:

EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO

In this case, I used the sp_configure system stored procedure to set the access level to 2, which supports both T-SQL and Win32 streaming access. If I wanted to support only T-SQL access, I would have set the level to 1. A 0 disables FILESTREAM support on the SQL Server instance. After you run the stored procedure, you must then run the RECONFIGURE command to apply the new option setting.

Configuring the database to support FILESTREAM storage

The next step for supporting FILESTREAM storage is to add a FILESTREAM filegroup to the database definition. A FILESTREAM filegroup is a special type of filegroup that contains file system directories (data containers). For example, in the following database definition, I created a FILESTREAM filegroup named FileStreamGrp:

USE master
GO
IF
EXISTS
  SELECT name FROM sys.databases
  WHERE name =  'HumanResources')
DROP DATABASE HumanResources
GO
CREATE DATABASE HumanResources
ON
PRIMARY(
  NAME = HumanRscsDat,
   FILENAME =  'C:\Data\HR\HumanRscsDat.mdf'),
FILEGROUP FileStreamGrp CONTAINS FILESTREAM(
  NAME = HumanRscsFs,
  FILENAME =  'C:\Data\HR\FileStream')
LOG ON(
  NAME = HumanRscsLog,
  FILENAME =  'C:\Data\HR\HumanRscsLof.ldf')

Notice that the FILEGROUP definition includes the CONTAINS FILESTREAM keywords, followed by the logical name and file name. Notice that the file name in this case is only a directory path, without an actual name. When you specify the path, every object but the last must exist, and the last cannot exist. For instance, the C:\Data\HR folder must exist before you can run this statement, but C:\Data\HR\FileStream cannot exist.

When you add a FILESTREAM file group to a database definition, SQL Server automatically creates the necessary folders and files to support the FILESTREAM functionality. This includes the filestream.hdr file, which is a header file for the FILESTREAM container, and an $FSLOG folder that supports FILESTREAM logging.

Defining a column to support FILESTREAM storage

The next step in setting up FILESTREAM storage is to configure a FILESTREAM column. In order for a table to include a FILESTREAM column, it must also contain a ROWGUIDCOL keyword and be configured as NOT NULL and UNIQUE. The column is necessary to support the Win32 streaming access.

The FILESTREAM column itself must be configured with the varbinary(max) data type and include the FILESTREAM keyword, as shown in the following CREATE TABLE statement:

USE HumanResources
GO
IF OBJECT_ID (N'Candidate', N'U') IS NOT NULL
DROP TABLE dbo.Candidate
GO
CREATE TABLE
Candidate(
  CandidateId INT IDENTITY PRIMARY KEY,
  CandidateGuid UNIQUEIDENTIFIER ROWGUIDCOL
   NOT NULL UNIQUE DEFAULT NEWID(),
  CandidateResume VARBINARY(MAX) FILESTREAM NULL

As you can see, the CandidateResume column includes the FILESTREAM keyword, which follows the name of the data type.

Querying a FILESTREAM column

Once you've set up SQL Server to support FILESTREAM storage, you can use T-SQL statements to query and modify the data. For example, the following INSERT statement adds binary data to the CandidateResume column:

INSERT INTO Candidate(CandidateResume)
VALUES (CAST(
'Resume test data'
AS VARBINARY(MAX)))

You can then retrieve data from the CandidateResume column as you would retrieve data from any other column:

SELECT CandidateResume
FROM Candidate
WHERE CandidateId = 1


The SELECT statement returns the following results, as binary data:

0x526573756D6520746573742064617461

You can also just as easily update the data by replacing the value:

UPDATE Candidate
SET CandidateResume =
CAST( 'New resume test data' AS VARBINARY(MAX))
WHERE CandidateId = 1

Notice that, once again, I pass in the data as a binary value. If you were to rerun the SELECT statement shown above, you would now receive the following results.

0x4E657720726573756D6520746573742064617461

As you can see in the preceding examples, using a T-SQL statement to access the data in the CandidateResume column is a very straightforward process. However, it should be apparent that the test data I inserted and updated is much smaller than you would normally store in a FILESTREAM column. In fact, you'll usually want to use Win32 streaming to access the data.

To support Win32 streaming, SQL Server 2008 provides functions and an API that you can access from within your application. Although a discussion of Win32 streaming is beyond the scope of this article, it is important to know that FILESTREAM functionality in SQL Server 2008 readily supports efficient data access from your application.

For more details about Win32 streaming of FILESTREAM data, see the topic Managing FILESTREAM Data by Using Win32 in SQL Server 2008 Books Online. You can also find more details about FILESTREAM storage in general in the topic Designing and Implementing FILESTREAM storage. In the meantime, you should now have the details you need to set up FILESTREAM storage in your SQL Server instance and define columns that will support that storage.

ABOUT THE AUTHOR

Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

This was first published in May 2009

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.