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.
8 thoughts on “FILESTREAM with Multiple Data Containers (Files)”
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.
does this supported in SQL Server 2012 standard edition as well. Some of the URLs suggest only 2012 ENT. support this feature.
Mridul: All editions support FILESTREAM.
It’s only the enterprise version of 2012 that supports multiple FILESTREAM containers.
What happens when one of the files becomes full? Does SQL Server just keep adding to the other files or is an error generated?
Connie you’ll get an error message similar to if you’ve set a MAXSIZE for your FILESTREAM filegroups:
Could not allocate space for object ‘dbo.Documents’ in database ‘XYZ’ because the ‘FilestreamFileGroup’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Thank you – one question: Can you spread the multiple filestream files (of the same filegroup) across different drives? We are getting an ‘invalid device’ error as soon as we add a second drive. Microsoft premier support hasn’t been able to help us.
Hi Nicole, it’s been a while since I have worked with FILESTREAM, but yes that’s possible. You cannot, however, put the FILESTREAM container in the root folder of the drive. Maybe that’s your issue?