Once you’ve enabled filestream, the next step is to add a filegroup to your database to hold the filestream data. This is pretty easy to do, and I’ll show you the SSMS and code versions.

If you want to know more about these Filestream containers, you can read BOL. Let’s create a simple database:

-- create a new database
create database UnstructuredData
go

This is a simple database with my instance defaults in place. It has a single mdf, a single ldf, and the default Primary filegroup. Let’s not add a new filegroup:

-- add a filestream FG
ALTER DATABASE [UnstructuredData]
  ADD FILEGROUP [FS] CONTAINS FILESTREAM 
GO

Here I am adding the filegroup (empty) and specifying this as a filestream container. You cannot mix Filestream data and non-Filestream data in the same filegroup in SQL Server 2012.

To add a file, we can use the ALTER DATABASE command:

-- Add a file to the Filestream FG
ALTER DATABASE [UnstructuredData] 
  ADD FILE ( NAME = N'UnstructuredFS', 
             FILENAME = N'c:\fs\UnstructuredFS' ) 
     TO FILEGROUP [FS]
go

Here I am adding a file, which is actually a folder in this case. According to the documentation, the path up to the last folder (c:\fs in this case) must exist, but the last folder (UnstructuredFS) must not.

You could do all of this in one statement, as shown here:

CREATE DATABASE [UnstructuredData]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'UnstructuredData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FS] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'UnstructuredFS', FILENAME = N'c:\fs\UnstructuredFS' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'UnstructuredData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData_log.ldf' , SIZE = 784KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

This gets you a space for holding your Filestream data. In 2012, you can now have more than one file for Filestream data, so you can separate out your filegroup across different physical locations if you have a need to do so for performance or scalability.

In the next post, I’ll build a FileTable and store some documents in it.

Filed under: Blog Tagged: Filestream, Filetable, sql server, syndicated