Win32 file system interfaces provide streaming access to the data. This is an excellent
solution for say, a web site that hosts video content.
FILESTREAM uses the NT system cache for caching file data. This helps reduce any
effect that FILESTREAM data might have on Database Engine performance. The SQL
Server buffer pool is not used; therefore, this memory is available for query
processing.
When Should You Use FILESTREAM:
The size and use of the data determines whether you should use database storage or
file system storage. If the following conditions are true, you should consider
using FILESTREAM:
Objects that are being stored are, on average, larger than 1 MB.
Fast read access is important.
You are developing applications that use a middle tier for application logic.
For smaller objects, storing varbinary(max) BLOBs in the database often provides
better streaming performance.
To enable and change FILESTREAM settings:
On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2,
point to Configuration Tools, and then click SQL Server Configuration Manager.
In the list of services, right-click SQL Server Services, and then click Open.
In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server
on which you want to enable FILESTREAM.
Right-click the instance, and then click Properties.
In the SQL Server Properties dialog box, click the FILESTREAM tab.
Select the Enable FILESTREAM for Transact-SQL access check box.
If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM
for file I/O streaming access. Enter the name of the Windows share in the Windows
Share Name box.
If remote clients must access the FILESTREAM data that is stored on this share, select
Allow remote clients to have streaming access to FILESTREAM data.
Click Apply.
In SQL Server Management Studio, click New Query to display the Query Editor.
In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
Click Execute.
To create a FILESTREAM-enabled database:
In SQL Server Management Studio, click New Query to display the Query Editor.
Copy the Transact-SQL code from the following example into the Query Editor. This
Transact-SQL code creates a FILESTREAM-enabled database called MyVideos.
CREATE DATABASE MyVideos
ON
PRIMARY (
NAME = MyVideosDB,
FILENAME = 'C:\Databases\MyVideosDB.mdf'
), FILEGROUP MyVideosFS CONTAINS FILESTREAM(
NAME = MyVideosFS,
FILENAME = 'C:\Databases\MyVideosFS')
LOG ON (
NAME = MyVideosLOG,
FILENAME = 'C:\Databases\MyVideosLOG.ldf')
GO
-- CREATE A TABLE:
USE MYVIDEOS
GO
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
GO
INSERT DATA:
For the purpose of this example, let's use OPENROWSET(BULK..) to load the content
of the video file (wmv) from the disk to a VARBINARY(MAX) variable. Once the
content of the video is loaded to the variable, we can store it to the FILESTREAM
enabled column. The code that does it:
-- Declare a variable to store the video data
USE MYVIDEOS
DECLARE @video AS VARBINARY(MAX)
-- Load the image data
SELECT @video= CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK
'C:\temp\FeedHorses.AVI',
SINGLE_BLOB ) AS x
-- Insert the data to the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), 'MS1001','Horse Scene', @video
// sample data access code (C#)
//Create a connection to the database
String conStr;
conStr = "server=(local);database=myVideos;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(conStr) ;
con.Open() ;
'Retrieve the FilePath() of the image file
SqlCommand sqlCommand=new SqlCommand() ;
sqlCommand.Connection = con;
sqlCommand.CommandText = "SELECT ItemImage FROM items WHERE ItemNumber = 'MS1001'" ;
Byte[] buffer = sqlCommand.ExecuteScalar();
MemoryStream ms = new MemoryStream(buffer) ;
// Stream the video to a control here
con.Close();
FILESTREAM Storage:
FILESTREAM storage is implemented as a varbinary(max) column in which the data is
stored as BLOBs in the file system. The sizes of the BLOBs are limited only by
the volume size of the file system. The standard varbinary(max) limitation of
2-GB file sizes does not apply to BLOBs that are stored in the file system.
To specify that a column should store data on the file system, specify the FILESTREAM
attribute on a varbinary(max) column. This causes the Database Engine to store
all data for that column on the file system, but not in the database file.
FILESTREAM data must be stored in FILESTREAM filegroups. A FILESTREAM filegroup is
a special filegroup that contains file system directories instead of the files
themselves. These file system directories are called data containers. Data containers
are the interface between Database Engine storage and file system storage.
When you use FILESTREAM storage, remember the following:
When a table contains a FILESTREAM column, each row must have a nonnull unique row
ID.
FILESTREAM data containers cannot be nested.
When you are using failover clustering, the FILESTREAM filegroups must be on shared
disk resources.
FILESTREAM filegroups can be on compressed volumes.
Integrated Security
In SQL Server, FILESTREAM data is secured just like other data is secured: by granting
permissions at the table or column levels. If a user has permission to the FILESTREAM
column in a table, the user can open the associated files.
Integrated Management
Because FILESTREAM is implemented as a varbinary(max) column and integrated directly
into the Database Engine, most SQL Server management tools and functions work
without modification for FILESTREAM data. For example, you can use all backup
and recovery models with FILESTREAM data, and the FILESTREAM data is backed up
with the structured data in the database. If you do not want to back up FILESTREAM
data with relational data, you can use a partial backup to exclude FILESTREAM
filegroups.
Dual Programming Model to Access BLOB Data
After you store data in a FILESTREAM column, you can access the files by using Transact-SQL
transactions or by using Win32 APIs.
Transact-SQL Access
By using Transact-SQL, you can insert, update, and delete FILESTREAM data:
You can use an insert operation to prepopulate a FILESTREAM field with a null value,
empty value, or relatively short inline data. However, a large amount of data
is more efficiently streamed into a file that uses Win32 interfaces.
When you update a FILESTREAM field, you modify the underlying BLOB data in the file
system. When a FILESTREAM field is set to NULL, the BLOB data associated with
the field is deleted. You cannot use a Transact-SQL chunked update, implemented
as UPDATE.Write(), to perform partial updates to the data.
When you delete a row or delete or truncate a table that contains FILESTREAM data,
you delete the underlying BLOB data in the file system.
File System Streaming Access
The Win32 streaming support works in the context of a SQL Server transaction. Within
a transaction, you can use FILESTREAM functions to obtain a logical UNC file
system path of a file. You then use the OpenSqlFilestream API to obtain a file
handle. This handle can then be used by Win32 file streaming interfaces, such
as ReadFile() and WriteFile(), to access and update the file by way of the file
system.
Because file operations are transactional, you cannot delete or rename FILESTREAM
files through the file system.
Statement Model
The FILESTREAM file system access models a Transact-SQL statement by using file open
and close. The statement starts when a file handle is opened and ends when the
handle is closed. For example, when a write handle is closed, any possible AFTER
trigger that is registered on the table fires as if an UPDATE statement is completed.
Storage Namespace
In FILESTREAM, the Database Engine controls the BLOB physical file system namespace.
A new intrinsic function, PathName, provides the logical UNC path of the BLOB
that corresponds to each FILESTREAM cell in the table. The application uses this
logical path to obtain the Win32 handle and operate on the BLOB data by using
regular Win32 file system interfaces. The function returns NULL if the value
of the FILESTREAM column is NULL.
Transacted File System Access
A new intrinsic function, GET_FILESTREAM_TRANSACTION_CONTEXT(), provides the token
that represents the current transaction that the session is associated with.
The transaction must have been started and not yet aborted or committed. By obtaining
a token, the application binds the FILESTREAM file system streaming operations
with a started transaction. The function returns NULL in case of no explicitly
started transaction.
All file handles must be closed before the transaction commits or aborts. If a handle
is left open beyond the transaction scope, additional reads against the handle
will cause a failure; additional writes against the handle will succeed, but
the actual data will not be written to disk. Similarly, if the database or instance
of the Database Engine shuts down, all open handles are invalidated.
Transactional Durability
With FILESTREAM, upon transaction commit, the Database Engine ensures transaction
durability for FILESTREAM BLOB data that is modified from the file system streaming
access.
Write-Through from Remote Clients
Remote file system access to FILESTREAM data is enabled over the Server Message Block
(SMB) protocol. If the client is remote, no write operations are cached by the
client side. The write operations will always be sent to the server. The data
can be cached on the server side. We recommend that applications that are running
on remote clients consolidate small write operations to make fewer write operations
using larger data size.
Creating memory mapped views (memory mapped I/O) by using a FILESTREAM handle is
not supported. If memory mapping is used for FILESTREAM data, the Database Engine
cannot guarantee consistency and durability of the data or the integrity of the
database.
Windows Logo Certification
The FILESTREAM RsFx driver is certified for Windows Server 2008 R2. For more information
and catalog file download, see SQL Server 2008 R2 FileStream Driver Windows Logo
Certification in the Microsoft Download Center.
Remote Blob Storage (RBS)
SQL Server Remote BLOB Store (RBS) is an optional add-on component that lets database
administrators store binary large objects in commodity storage solutions instead
of directly on the main database server.
RBS can be downloaded from the SQL Server 2008 R2 Feature Pack page. RBS is not included
on the SQL Server 2008 R2 installation media, and is not installed by the SQL
Server 2008 R2 Setup program.
RBS provides the following benefits:
Optimized database storage and performance
Storing BLOBs in the database can consume large amounts of file space and expensive
server resources. RBS efficiently transfers the BLOBs to a dedicated storage
solution of your choosing, and stores references to them in the database. This
frees server storage for structured data, and frees server resources for database
operations.
Efficient management of BLOBs
Several RBS features support the convenient management of stored BLOBs:
BLOBS are managed with ACID (atomic consistency isolation durable) transactions.
BLOBs are organized into collections.
Garbage collection, consistency checking, and other maintenance functions are included.
Standardized API
RBS defines a set of APIs that provide a standardized programming model for applications
to access and modify any BLOB store. Each BLOB store can specify its own provider
library, which plugs into the RBS client library and specifies how BLOBs are
stored and accessed.
Download RBS: http://goo.gl/itJYG
RBS Samples: http://sqlrbs.codeplex.com/