SQL Server 2012 : Native File Streaming - Creating a FILESTREAM-Enabled Database

Once FILESTREAM is enabled for both the machine and the server instance, any database running on the server instance can support unstructured data by defining a filegroup with the special FILEGROUP…CONTAINS FILESTREAM clause of the CREATE DATABASE statement. For example, the statement in Example 1 creates a PhotoLibrary database that can store pictures using FILESTREAM. Before you can run this code, you need to create an empty folder named C:\Demo\PhotoLibrary.

Example 1. Creating a FILESTREAM-enabled database with FILEGROUP…CONTAINS FILESTREAM.

CREATE DATABASE PhotoLibrary
 ON PRIMARY
  (NAME = PhotoLibrary_data,
   FILENAME = 'C:\Demo\PhotoLibrary\PhotoLibrary_data.mdf'),
 FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
  (NAME = PhotoLibrary_group2,
   FILENAME = 'C:\Demo\PhotoLibrary\Photos')
 LOG ON
  (NAME = PhotoLibrary_log,
   FILENAME = 'C:\Demo\PhotoLibrary\PhotoLibrary_log.ldf')

The FILEGROUP…CONTAINS FILESTREAM clause in this otherwise ordinary CREATE DATABASE statement enables the FILESTREAM feature for the PhotoLibrary database.

As when creating any database, the directory (or directories) specified for the primary and log filegroups must exist at the time the database is created. In Example 1, the CREATE DATABASE statement will fail if the C:\Demo\PhotoLibrary directory specified by FILENAME in its ON PRIMARY and LOG ON clauses does not exist. For the new FILESTREAM group specified by the FILEGROUP…CONTAINS FILESTREAM clause, a FILENAME keyword is specified pointing to a directory (not a file) that must not exist at the time that the database is created (although the path leading up to the final directory must exist), or the CREATE DATABASE statement will fail as well. SQL Server takes control of creating and managing this directory—called the FILESTREAM data container—much as it does for creating and managing the .mdf and .ldf files in the other filegroups. In Example 1, SQL Server automatically creates the C:\Demo\PhotoLibrary\Photos folder when the CREATE DATABASE statement is executed, and it will use that folder for storing all BLOB data (photos, in this example) in the PhotoLibrary database.

Execute the code in Example 1 to create the database. SQL Server creates the usual .mdf and .ldf files for you, and also creates the Photos subdirectory for the FILESTREAM group, as shown in Figure 1.

Figure 1. FILESTREAM storage in the file system.

Behind the scenes, SQL Server will store all your pictures as files in the Photos subdirectory, and will transparently associate those files and the relational tables that they logically belong to in columns defined as varbinary(max) FILESTREAM. Unless you explicitly exclude the FileStreamGroup1 filegroup from a backup or restore command, all your picture files in the Photos subdirectory will be included with the relational database in the backup or restore operation.

Note

You can create multiple FILESTREAM filegroups, with each one pointing to a different file system location. Doing so helps to partition BLOB data when you need to scale up, because you can designate specific filegroups for each varbinary(max) FILESTREAM column you define in your tables.

Creating a Table with FILESTREAM Columns

You’re now ready to create a new PhotoAlbum table. SQL Server requires that any table using FILESTREAM storage has a uniqueidentifier column that is not nullable and specifies the ROWGUIDCOL attribute. You must also create a unique constraint on this column. Only one ROWGUIDCOL column can be defined in any given table, although you can then declare any number of varbinary(max) FILESTREAM columns in the table that you want for storing BLOB data. The statement in Example 2 creates the PhotoAlbum table with a Photo column declared as varbinary(max) FILESTREAM.

Example 2. Creating a FILESTREAM-enabled table.

USE PhotoLibrary
GO

CREATE TABLE PhotoAlbum(
 PhotoId int PRIMARY KEY,
 RowId uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
 Description varchar(max),
 Photo varbinary(max) FILESTREAM DEFAULT(0x))

With this statement, you satisfy the FILESTREAM requirement for the ROWGUIDCOL column, yet you won’t actually have to do anything to maintain that column. By declaring the RowId column with its DEFAULT value set to call the NEWSEQUENTIALID function, you can just ignore this column when inserting rows—simply not providing values for it will cause SQL Server to automatically generate the next available globally unique identifier (GUID) that it needs to support FILESTREAM on the table. The column is set to not accept NULL values and is defined with the required unique constraint.

You have also declared an integer PhotoId column for the table’s primary key value. You’ll use the PhotoId column to identify individual photos in the album, and SQL Server will use the RowId column to track and cross-reference photos in the file system with rows in the PhotoAlbum table. The Photo column holds the actual BLOB itself, being defined as a varbinary(max) data type with the FILESTREAM attribute applied. This means that it gets treated like a regular varbinary(max) column, but you know that its BLOB is really being stored under the covers in the file system by SQL Server. The Photo column is also defined with a default value of 0x, which represents a zero-length binary stream. This will come into play later when you start programming with SqlFileStream. We’re not there yet, so you can just ignore the default assignment for now.