It’s the second Tuesday of the month and time for T-SQL Tuesday again. This is a monthly blog party, where the participants write on a particular theme. This month Jen McCown, of Midnight DBA fame, invites us to talk about files and filegroups in SQL Server.
If you’re like to participate, write a post and drop a comment (or pingback) on Jen’s blog. Watch the #tsql2sday hashtag on twitter for next month’s invitation.
Filestream and Filegroups
I have a couple talks that deal with Filestream related topics, so I decided on a quick introductory lesson on how this works.
Filestream was built into the AdventureWorks 2008 sample database. Requiring administrators to turn on Filestream caused some confusion and complaints, despite the fact that it’s easy to do.
What does Filestream have to do with filegroups? In a database that is enabled for Filestream data, you need to add a filegroup specifically for the Filestream data. This is actually a folder on your file system, which you can access through T-SQL, or through the Win32 API. If you are using SQL Server 2012 or later, you can also access this data with a Filetable, which is built on Filestream.
Let’s create a database, and add a filegroup for Filestream. We start with the “New Database” dialog in SSMS.
With the normal defaults, we see a data file (FS_Test) and a log file (FS_test_log). For Filestream data, we need a new place to store it. Let’s add a file:
Once I add the file, I mark it as storing Filestream data. The other options are rows (data files) or log files. However this presents a problem. When I scroll right, I see that there is no filegroup for Filestream data. I can’t put this in an existing filegroup.
Let’s add one of those. Here’s the default filegroup dialog.
I can click add, and put in a filestream filegroup. The name doesn’t matter, it’s just for administrative purposes. Once I do that, I can go back to the files dialog, and if I select the dropdown, my new filegroup appears.
Now I need a location. Outside of SQL Server, I created a folder in my data directory. This can be anywhere, but I did it in the default location. It’s called FilestreamDataTest.
I then select this in the files dialog, using the ellipsis to the right of the Path column.
I see my folder in the file picker and choose it.
Once I’ve selected it, I don’t click OK. I click “Script” and get the script below:
CREATE DATABASE [FS_test] CONTAINMENT = NONE ON PRIMARY ( NAME = N'FS_test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [fs_test_fsdata] CONTAINS FILESTREAM DEFAULT ( NAME = N'fs_test_fsdata', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FilestreamDataTest\fs_test_fsdata' , MAXSIZE = UNLIMITED) LOG ON ( NAME = N'FS_test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO
I can run this, and once I do, if I go into the folder that contains my Filestream file, I see this:
As I create tables that hold Filestream data, including FileTable data, I’ll see entries in here for each column (or Filetable) that holds this data. There is a folder that holds logging information for this data, which I do not manage.
Hopefully this is a quick, short piece that helps you understand Filegroups and Filestream.
Voice of the DBA 
What a lovely post… Well done
LikeLike
I hit my head against the wall for an hour because of this article whilst trying to test it out. While I appreciate it was free and that you took the time to write it for us, I also feel like by leaving out a critical part at the end about how to use it might be a bit of a disservice to readers.
I started my research looking for how to move a table into a filegroup, because that’s what your article discussed – creating a filestream filegroup. You are probably laughing now because you’d know that is the wrong approach, but for someone who has never used it before it seems like a valid place to look.
Naturally when moving to a filegroup it’s all about creating the table on that filegroup, or dropping a clustered index and re-creating it on the new filegroup. That doesn’t work on a filestream filegroup and to make matters worse it gives a horribly misleading error message saying the filegroup doesn’t exist, even though it does, and what it really means is there is no “normal” non-filestream filegroup of that name and that filestream filegroups are not treated the same.
By the end of my head-butting, I discovered an article that pointed out filestream filegroups are different, that the tables and indexes stay in a normal filegroup, and that to use a filestream all you have to do is:
alter table blah add [id] uniqueidentifier not null unique rowguidcol default newid()
alter table blah add [data] varbinary(max) FILESTREAM
And that’s it – everything gets dumped into that folder you defined earlier. (There is also some syntax to make all the filestreams for a table go to a filestream filegroup, too).
There is probably a whole lot more to it when it comes to moving your existing varbinary(max) data over, but with the above information I think it would have reduced the learning time by a wide margin.
Thanks
LikeLike
My apologies if you had issues, but this post wasn’t designed to try and help you test Filestream or move data into a filestream filegroup. It was based on the idea of just explaining how the filegroups work in the Filestream feature. That’s just a piece of what is needed for Filestream. I didn’t talk about moving data into a group, but rather how the filegroup differs from other “row” or “log” filegroups and how this appears in your file system.
I should write on the data piece, and I have a few a pieces on Filestream, but not the data side.
LikeLike
Pingback: Adding Data to Filestream with T-SQL | Voice of the DBA