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:
- Enable FILESTREAM support on the SQL Server 2008 service.
- Configure the database to support FILESTREAM storage.
- 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. In the SQL Server Configuration Manager, open the properties for the service and go to the FILESTREAM tab, as shown in Figure 1.
[IMAGE]
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, howev...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
er, 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:
[IMAGE]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. His books include Beginning MySQL (part of the Wrox Programmer-to-Programmer series), SQL: A Beginner's Guide (based on the SQL:1999 standard), MCSE Training Kit: Designing Highly Available Web Solutions with Microsoft Windows 2000 Server Technologies, and MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. You can find more information at www.rhsheldon.com.
