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.