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.

Advertisements

3 thoughts on “FILESTREAM with Multiple Data Containers (Files)

  1. Sven, I have been looking for this exact information to explain how SQL 2012+ handles the filestream data in such a configuration.

    I’ve found documents that indicate it can be done, but none that explained to me how SQL allocated the files across the different data container file paths. Thank you for doing these tests and clearly explaining your results. Very helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s