Much data is unstructured, such as text documents, images, and videos. This unstructured data is often stored outside the database, separate from its structured data. This separation can cause data management complexities. Or, if the data is associated with structured storage, the file streaming capabilities and performance can be limited.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
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.
 Note |
|---|
FILESTREAM data is not encrypted even when transparent data encryption is enabled.
|
In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. 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.
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, consider 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.
| Note |
|---|
Encryption is not supported on FILESTREAM data.
|
Only the account under which the SQL Server service account runs is granted NTFS permissions to the FILESTREAM container. We recommend that no other account be granted permissions on the data container.
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.
Getting Started with FILESTREAM Storage
FILESTREAM is not automatically enabled when you install or upgrade SQL Server. You must enable FILESTREAM by using SQL Server Configuration Manager and SQL Server Management Studio. To use FILESTREAM, you must create or modify a database to contain a special type of filegroup. Then, create or modify a table so that it contains a varbinary(max) column with the FILESTREAM attribute. After you complete these tasks, you can use Transact-SQL and Win32 to manage the FILESTREAM data.
How to: Enable FILESTREAM
Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine. This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.
| Note |
|---|
You cannot enable FILESTREAM on a 32-bit version of SQL Server running on a 64-bit operating system.
|
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.
Restart the SQL Server service.
How to: Create a FILESTREAM-Enabled Database
This topic shows how to create a database that supports FILESTREAM. Because FILESTREAM uses a special type of filegroup, when you create the database, you must specify the CONTAINS FILESTREAM clause for at least one filegroup.
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 Archive.
| Note |
|---|
For this script, the directory C:\Data must exist.
|
To build the database, click Execute.
The following code example creates a database that is named Archive. The database contains three filegroups: PRIMARY, Arch1, and FileStreamGroup1.PRIMARY and Arch1 are regular filegroups that cannot contain FILESTREAM data. FileStreamGroup1 is the FILESTREAM filegroup.
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
FILENAME = 'c:\data\filestream1')
LOG ON ( NAME = Archlog1,
FILENAME = 'c:\data\archlog1.ldf')
GO
For a
FILESTREAM filegroup,
FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist. In this example,
c:\datamust exist. However, the
filestream1 subfolder cannot exist when you execute the
CREATE DATABASE statement. For more information about the syntax, see
CREATE DATABASE (Transact-SQL).
After you run the previous example, a filestream.hdr file and an $FSLOG folder appears in the c:\Data\filestream1 folder. The filestream.hdr file is a header file for the FILESTREAM container.
| Important |
|---|
The filestream.hdr file is an important system file. It contains FILESTREAM header information. Do not remove or modify this file.
|
For existing databases, you can use the
ALTER DATABASE statement to add a FILESTREAM filegroup.
How to: Create a Table for Storing FILESTREAM Data
This topic shows how to create a table for storing FILESTREAM data.
| Note |
|---|
|
|
To create a table to store FILESTREAM data
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 table called Records.
To create the table, click Execute.
When the database has a FILESTREAM filegroup, you can create or modify tables to store FILESTREAM data. To specify that a column contains FILESTREAM data, you create a varbinary(max) column and add the FILESTREAM attribute.
The following code example shows how to create a table that is named Records. The Id column is a ROWGUIDCOL column and is required to use FILESTREAM data with Win32 APIs. The SerialNumber column is a UNIQUE INTEGER. The Chart column is a FILESTREAM column and is used to store the Chart in the file system.
CREATE TABLE Archive.dbo.Records
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[SerialNumber] INTEGER UNIQUE,
[Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO
Understanding the Differences Between Structured
and Unstructured Documents
What is the difference between structured and unstructured documents? With a structured
document, certain information always appears in the same location
on the page. For example, in an employment application the applicant’s name always appear in the same box in the same place on
the document. In contrast, an unstructured document has the opposite characteristics
– information can appear
in unexpected places
on the document. An example would be in a hand
written note or a whitepaper.
