SQL Theory: Filestream Overview, Enable Filestream, Filestream Database, Filestream Table

FILESTREAM Overview

SQL Server 2008 R2

Other Versions

35 out of 41 rated this helpful - Rate this topic

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.

For a walkthrough that shows how to use FILESTREAM, see Getting Started with FILESTREAM Storage.

When to Use FILESTREAM

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

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

SQL Server 2008 R2

Other Versions

1 out of 3 rated this helpful - Rate this topic

 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

SQL Server 2008 R2

Other Versions

10 out of 11 rated this helpful - Rate this topic

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

1. 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.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. 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.
8. 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.
9. Click Apply.
10. In SQL Server Management Studio, click New Query to display the Query Editor.
11. In Query Editor, enter the following Transact-SQL code:
    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE
    

12. Click Execute.
13. Restart the SQL Server service.

How to: Create a FILESTREAM-Enabled Database

SQL Server 2008 R2

Other Versions

5 out of 6 rated this helpful - Rate this topic

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

1. In SQL Server Management Studio, click New Query to display the Query Editor.
2. 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.

3. To build the database, click Execute.

Example

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.

Transact-SQL

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, seeCREATE 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

SQL Server 2008 R2

Other Versions

2 out of 4 rated this helpful - Rate this topic

This topic shows how to create a table for storing FILESTREAM data.

Note
This topic requires the Archive database that is created in How to: Create a FILESTREAM-Enabled Database.

To create a table to store FILESTREAM data

1. In SQL Server Management Studio, click New Query to display the Query Editor.
2. Copy the Transact-SQL code from the following example into the Query Editor. This Transact-SQL code creates a FILESTREAM-enabled table called Records.
3. To create the table, click Execute.

Example

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.

Transact-SQL

CREATE TABLE Archive.dbo.Records
(
 [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
 [SerialNumber] INTEGER UNIQUE,
 [Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO