Feb
27
2012

FILESTREAM filegroups can contain multiple files in SQL Server 2012

This post describes a new FILESTREAM feature in SQL Server 2012 that enhances the I/O scalability of FILESTREAM data.

In SQL Server 2012, a FILESTREAM filegroup can now contain more than one file.

You can improve I/O scalability for FILESTREAM data by placing different files within the same FILESTREAM filegroup on different volumes. This feature eliminates the need for complicated workarounds that use partitioning and multiple FILESTREAM filegroups.

The following example creates the BlobStore1 database. The database is created with one row filegroup and one FILESTREAM filegroup, FS. The FILESTREAM filegroup contains two files, FS1 and FS2. Then the database is altered by adding a third file, FS3, to the FILESTREAM filegroup.

Thank you to SQL Server tester Sandu Chirica for this Transact-SQL code sample, which will also appear in the CREATE DATABASE topic in SQL Server 2012 Books Online.

 

-- This example assumes that the directory C:\BlobStore exists.
-- The file sizes in this example are arbitrary.

USE [master]
GO

CREATE DATABASE [BlobStore1]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'BlobStore1',
FILENAME = N'C:\BlobStore\BlobStore1.mdf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB
),
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT
(
NAME = N'FS1',
FILENAME = N'C:\BlobStore\FS1',
MAXSIZE = UNLIMITED
),
(
NAME = N'FS2',
FILENAME = N'C:\BlobStore\FS2',
MAXSIZE = 100MB
)
LOG ON
(
NAME = N'BlobStore1_log',
FILENAME = N'C:\BlobStore\BlobStore1_log.ldf',
SIZE = 100MB,
MAXSIZE = 1GB,
FILEGROWTH = 1MB
)
GO

ALTER DATABASE [BlobStore1]
ADD FILE
(
NAME = N'FS3',
FILENAME = N'C:\BlobStore\FS3',
MAXSIZE = 100MB
)
TO FILEGROUP [FS]
GO

I hope you find this new feature in SQL Server 2012 useful!

-Doug