FILESTREAM with Multiple Data Containers (Files)

In the last blog post about FILESTREAM, I discussed the impact of the MAXSIZE setting for the data container. I encourage you to review that blog post first.

This time, let’s review a new feature that was added in SQL Server 2012: the ability to have multiple data containers in a single filegroup. This feature comes in handy when you want to load-balance the I/O across multiple disk systems, or when you’ve run out of space in the data container and you need to expand your storage capacity.

In order to set up a test of this feature, create a test database using the script below. It creates a database called “Demo_FILESTREAM2012” with a single data file, single log file and a single FILESTREAM filegroup. The FILESTREAM filegroup then has two data containers: Demo_FILESTREAM_fs1 and Demo_FILESTREAM_fs2. For this demo, I didn’t set any MAXSIZE property, although you could if you wanted to test MAXSIZE combined with multiple files.

USE Master
GO

CREATE DATABASE Demo_FILESTREAM2012
ON PRIMARY
	(NAME = Demo_FILESTREAM2012,
	 FILENAME = 'C:\SQLData\Data\Demo_FILESTREAM2012.mdf'),

FILEGROUP Demo_FILESTREAM2012_FS CONTAINS FILESTREAM
	(NAME = Demo_FILESTREAM2012_fs1,
	 FILENAME = 'C:\SQLData\Data\Demo_FILESTREAM2012_fs1'),
	(NAME = Demo_FILESTREAM2012_fs2,
	 FILENAME = 'C:\SQLData\Data\Demo_FILESTREAM2012_fs2')

LOG ON 
	(NAME = Demo_FILESTREAM2012_log,
	 FILENAME = 'C:\SQLData\Data\Demo_FILESTREAM2012_log.ldf');

Now, let’s add some records containing FILESTREAM data. You’ll need to create a table like the one in the script below:

USE [Demo_FILESTREAM2012]
GO

CREATE TABLE [dbo].Filestream(
	[ID] INT IDENTITY(1,1) NOT NULL,
	[value] VARCHAR(50) NOT NULL,
	[blob] VARBINARY(MAX) FILESTREAM NOT NULL,
	[rowguid] UNIQUEIDENTIFIER ROWGUIDCOL  NOT NULL UNIQUE,
	CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([ID] ASC)
)
ON [PRIMARY] FILESTREAM_ON Demo_FILESTREAM2012_FS
GO

And here is some code to add rows to the new table, including FILESTREAM data. It assumes you have 3 photos stored in a folder C:\Temp.

INSERT INTO [dbo].Filestream
	(rowguid, value, blob)
	SELECT NEWID(), 'Photo1' ,
		bulkcolumn FROM OPENROWSET(BULK 'C:\temp\photo1.jpg', SINGLE_BLOB) AS x
INSERT INTO [dbo].Filestream
	(rowguid, value, blob)
	SELECT NEWID(), 'Photo2' ,
		    bulkcolumn FROM OPENROWSET(BULK 'C:\temp\photo2.jpg', SINGLE_BLOB) AS x
INSERT INTO [dbo].Filestream
	(rowguid, value, blob)
	SELECT NEWID(), 'Photo3' ,
		    bulkcolumn FROM OPENROWSET(BULK 'C:\temp\photo3.jpg', SINGLE_BLOB) AS x

The code adds three rows to the table, but the question is, which data container will these files end up in? SQL Server will actually apply some type of round-robin algorithm: in my tests, the first inserted photo goes to the first data container, photo 2 in the second, and photo 3 in the first data container again. If you run the same INSERT code again, photo 1 ends up in data container 2, photo 2 in data container 1 and photo 3 again in data container 2.

The ability to now have multiple data containers in a single filegroup is a great addition. If you previously wanted to load balance the FILESTREAM I/O or had run out of space on the volume, you would have had to partition the entire table. While partitioning a table can still be useful if you’re interested in load balancing the data rows also, you no longer have to incur that complexity if the only load-balancing need is for the FILESTREAM I/O.