FILESTREAM Data in SQL Server 2008 (ADO.NET)

SQL Server 2008 introduces the FILESTREAM storage attribute for binary (BLOB) data stored in a varbinary(max) column. SQL Server has always provided the capability to store binary data, but working with it has required special handling. Unstructured data, such as text documents, images and video, is often stored outside of the database, making it difficult to manage.

Applications that rely on relational and non-relational data typically use one of three architectures.

• Relational data in the database and nonrelational binary large object (BLOB) data in file systems and file servers.

• Relational data in the database and nonrelational data in a dedicated BLOB store.

• Relational data and non-relational data in the database.

Each approach has its own benefits and drawbacks. For example, storing unstructured data in file servers or dedicated BLOB stores can reduce costs in terms of dollars per gigabyte, but typically adds complexity for managing and building applications, because the application has to maintain integrity between records in the database and the separate system that contains the BLOBs to which those records are associated. On the other hand, storing BLOBs in the database conveniently centralizes data storage, but typically comes at higher cost and can result in reduced performance.

There are a number of challenges associated with creating applications that combine structured and unstructured data. SQL Server helps meet these challenges by reducing the cost of managing both types of data while simplifying the development of applications that use both relational and non-relational data.

Storage Options for BLOB Data

SQL Server 2005 introduced the varbinary(max) data type, which enables you to store large binary data values of up to 2 GB in a SQL Server column. When using the max modifier, you can control how the data is physically stored in the data pages of the table by setting the large value types out of row table option. When this option is set to ON, all values are stored on separate linked pages and a 16-byte root pointer to these pages is stored on the data page for the row. When this option is set to OFF, values of up to 8,000 bytes are stored inline in the data page for the row, and larger values are stored on separate linked pages.

SQL Server 2008 introduces two new capabilities for storing BLOB data:

• FILESTREAM: An attribute you can set on a varbinary column so that the data is stored on the file system (and therefore benefits from its fast streaming capabilities and storage capabilities) but is managed and accessed directly within the context of the database.

• Remote BLOB Storage (RBS): A client-side application programming interface (API) that is designed to move storage of BLOBs from Microsoft SQL Server to external storage solutions.

Additionally, SQL Server 2008 continues support for standard BLOB columns through the varbinary data type.

Specifying the FILESTREAM attribute on a varbinary(max) column causes SQL Server to store the data on the local NTFS file system instead of in the database file. FILESTREAM data is stored in special FILESTREAM filegroups, called containers, that contain the file system directories, not the files themselves. Individual files stored in a FILESTREAM column cannot be opened directly from the NTFS file system. They can only be manipulated as part of a SQL Server transaction.

There are several benefits to FILESTREAM storage:

• Performance matches the streaming performance of the file system.

• The size of the BLOB is limited only by the file system volume size.

• FILESTREAM columns do not need to be managed separately. They are fully integrated with maintenance operations, such as backup and restore.

• SQL Server ensures data-level consistency between the relational data in the database and the unstructured data that is physically stored on the file system by providing full transactional support.

• FILESTREAM data is completely integrated with the SQL Server security model.

Some features, such as database mirroring, transparent encryption, and table-valued parameters, are not supported for FILESTREAM columns.

For documentation about setting up and configuring FILESTREAM data on SQL Server, see Designing and Implementing FILESTREAM Storage in SQL Server 2008 Books Online.

FILESTREAM Scenarios

Storing large BLOBs as FILESTREAM data can improve database engine performance by using the NT system cache for caching file data, which frees up memory for query processing. Consider using FILESTREAM if any of the following conditions are true:

• The objects being stored are larger than 1 MB.

• Fast read access is important.

• The BLOB data will be accessed by an n-tier application.

For smaller objects and client-server applications, storing BLOBs in varbinary(max) columns in the database often provides better streaming performance. For more information on working with additional types of large-value data, see Modifying Large-Value (max) Data (ADO.NET).

Businesses that are amassing large volumes of non-relational, non-structured data in the form of digital images, documents, videos, or other multimedia formats are good candidates for FILESTREAM data storage.

For example, consider the following business scenarios that would benefit from FILESTREAM data storage:

• An insurance company needs to store policy documents and retrieve them for claims processing.

• An online retailer needs to store video footage of its products for display in an e-commerce site.

• A telephony system needs to store voicemail messages as audio streams so that they can be retrieved remotely.

• A radio station needs to make a searchable library of podcasts available for download from its Web site.

• A legal practice needs to store electronic copies of documents as images and easily retrieve the documents relating to an individual client or case.

• An architect partnership needs to store and retrieve digital plans with the associated client data.

• A library needs to convert and archive large volumes of existing paper and analog content for indexing and use in a digital research tool.

FILESTREAM and Full-Text Search

One of the ways SQL Server improves the FILESTREAM developer experience is with improvements to full-text search, which is fully supported for FILESTREAM data. In versions of SQL Server prior to SQL Server 2008, full-text search was implemented using the Windows Search Service, which added complexity to development and deployment. In addition, queries that combined full-text search with standard Transact-SQL predicates suffered performance problems. SQL Server 2008 integrates the full-text engine directly into the SQL core, improving performance while reducing the cost of managing and deploying full-text search applications.

Full-text indexing works with a FILESTREAM column in the same way that it does with a varbinary(max) column. The FILESTREAM table must have a column that contains the file name extension for each FILESTREAM BLOB. For more information, see Full-Text Search Developer InfoCenter in SQL Server 2008 Books Online.

SqlClient Support for FILESTREAM

The .NET Data Provider for SQL Server, System.Data.SqlClient, supports reading and writing to FILESTREAM data by using the SqlFileStream class that is defined in the System.Data.SqlTypes namespace. SqlFileStream inherits from the System.IO.Stream class, which provides methods for reading and writing to streams of data. Reading from a stream transfers data from the stream into a data structure, such as an array of bytes. Writing transfers the data from the data structure into a stream.

The SqlFileStream API works in the context of a SQL Server transaction. The first step for working with FILESTREAM data is to use the FILESTREAM functions to obtain a logical UNC file system path of a file. You then obtain the file handle. This handle can then be used to access and update the file by using the SqlFileStream methods. All FILESTREAM data access is performed in a SQL Server transaction. Transact-SQL statements can also be executed in the same transaction to maintain consistency between SQL Server data and FILESTREAM data.

Security and Code Access Security (CAS) Permissions

FILESTREAM data is secured just like any other data is secured: by granting permissions at the table or column levels. Users must be granted permissions on the FILESTREAM column in order to open the associated files. FILESTREAM columns cannot be encrypted.

The following table lists the code access security (CAS) permissions that all callers in the stack must have in order to use the SqlFileStream constructors.

For more information about CAS, see Code Access Security and ADO.NET.

Creating the SQL Server Table

The following Transact-SQL statements creates a table named employees and inserts a row of data. Once you have enabled FILESTREAM storage, you can use this table in conjunction with the code examples that follow. The links to resources in SQL Server 2008 Books Online are located at the end of this topic.

CREATE TABLE employees
(
  EmployeeId INT  NOT NULL  PRIMARY KEY,
  Photo VARBINARY(MAX) FILESTREAM  NULL,
  RowGuid UNIQUEIDENTIFIER  NOT NULL  ROWGUIDCOL
  UNIQUE DEFAULT NEWID()
)
GO
Insert into employees
Values(1, 0x00, default)
GO

Reading FILESTREAM Data Example

The following code fragment demonstrates how to read data from a FILESTREAM. The code gets the logical path to the file, setting the FileAccess to Read and the FileOptions to SequentialScan. The code then reads the bytes from the SqlFileStream into the buffer. The bytes are then written to the console window.

// Assumes connectionString is a valid connection string.
using (SqlConnection connection = new SqlConnection(
    connectionString ))
{
    connection.Open();
    SqlCommand command = new SqlCommand("", connection);

    SqlTransaction tran = connection.BeginTransaction(
       System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;

    command.CommandText = 
        "select Top(1) Photo.PathName(), " 
        + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for the file
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

            // Create the SqlFileStream
            SqlFileStream fileStream = new SqlFileStream(path,
            (byte[])reader.GetValue(1),
                FileAccess.Read,
                FileOptions.SequentialScan, 0);

            // Read the contents as bytes and write them to the console
            for (long index = 0; index < fileStream.Length; index+)
            {
                Console.WriteLine(fileStream.ReadByte());
            }
            fileStream.Close();
        }
    }
    tran.Commit();
}

Overwriting FILESTREAM Data Example

The following code fragment demonstrates how to write data to a FILESTREAM in which all existing data is overwritten. The code gets the logical path to the file and creates the SqlFileStream, setting the FileAccess to Write and the FileOptions to SequentialScan. A single byte is written to the SqlFileStream, replacing any data in the file.

using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();

    SqlCommand command = new SqlCommand("", connection);
    command.CommandText = "select Top(1) Photo.PathName(), "
    + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";

    SqlTransaction tran = connection.BeginTransaction(
        System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for file 
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

            // Create the SqlFileStream
            SqlFileStream fileStream = new SqlFileStream(path,
                (byte[])reader.GetValue(1),
                FileAccess.Write,
                FileOptions.SequentialScan, 0);

            // Write a single byte to the file. This will
            // replace any data in the file.
            fileStream.WriteByte(0x01);

            fileStream.Close();
        }
    }
    tran.Commit();
}

Inserting FILESTREAM Data Example

The following example demonstrates how to write data to a FILESTREAM by using the Seek method to append data to the end of the file. The code gets the logical path to the file and creates the SqlFileStream, setting the FileAccess to ReadWrite and the FileOptions to SequentialScan. The code uses the Seek method to seek to the end of the file, appending a single byte to the existing file.

using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();

    SqlCommand command = new SqlCommand("", connection);
    command.CommandText = "select Top(1) Photo.PathName(), "
    + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";

    SqlTransaction tran = connection.BeginTransaction(
        System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for file
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

            SqlFileStream fileStream = new SqlFileStream(path,
                (byte[])reader.GetValue(1),
                FileAccess.ReadWrite,
                FileOptions.SequentialScan, 0);

            // Seek to the end of the file
            fs.Seek(0, SeekOrigin.End);

            // Append a single byte 
            fileStream.WriteByte(0x01);
            fileStream.Close();
        }
    }
    tran.Commit();
}

Resources in SQL Server Books Online

The complete documentation for FILESTREAM is located in the following sections in SQL Server 2008 Books Online.

See Also

Concepts

Code Access Security and ADO.NET

Retrieving Large Data (ADO.NET)

Other Resources

SQL Server Data Types and ADO.NET

Retrieving and Modifying Data in ADO.NET

Binary and Large-Value Data (ADO.NET)

ADO.NET Managed Providers and DataSet Developer Center