Create a Filestream Filegroup for Filetables – SQL Server 2012
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.