Enabling FILESTREAM on SQL Server 2008

One of the newly touted features of SQL Server 2008 is file streaming. For some time now SQL Server has allowed users to store large binary objects in the database inside a varbinary(max) field. Performance began to suffer, however, when the binary object was over 1 megabyte or so in size.

SQL Server 2008 solves this through its new FILESTREAM feature. With FILESTREAM SQL Server lets the operating system, Windows Server, do what it does best: handle the storage of binary objects, better known as files. In the database, SQL Server simply stores a reference that will let it open and close the file from the disk. FILESTREAM gives us the best of all worlds. When the file group is backed up so are the files, they can also be made part of a transaction.

By default, FILESTREAM is not enabled when you install SQL Server 2008. Activating it is a simple, two step process. As always though make sure to do proper backups before making any changes to your SQL Server, and be sure to first implement on development, then test systems before making changes to any production server.

First, you will need to enable FILESTREAM for the Windows Server it is running on. Begin by opening the SQL Server Configuration Manager. Once inside click on “SQL Server Services” then highlight the server you want to activate. In this example we clicked on “SQL Server (MSSQLSERVER)”.

image

Now that it is highlighted, right click on it and pick properties from the menu. Click on the FILESTREAM tab in the dialog that appears.

image

If you have a default install, your dialog should appear with nothing enabled, as this one does. Check on the first line, “Enable FILESTREAM for Trancact-SQL access”. This turns on the FILESTREAM feature, but with a drawback. It only works from T-SQL, and then only on the server itself. To make it useful we need to go further.

Once the first line is checked on, the second line, “Enable FILESTREAM for file I/O streaming access” will become enabled. Check it on. At this point the server will allow FILESTREAM from a separate application, but only when run on the same Windows Server that SQL Server is running on.

Since most of your users will not be running on the SQL Server itself, you will want to check on the final option which is now available, “Allow remote clients to have streaming access to FILESTREAM data”. Once this is checked, you can click OK to save your changes.

Note there may be one exception to checking on the final option. If you are running SQL Server Express, it is quite possible your application and the database may all be running on the same computer, and that you do not want any other machine to get access to this database. While this would be an uncommon situation, please note that the capability does exist for you to set things up this way if needed.

Now that the Windows Server is ready to handle FILESTREAM we need to enable the SQL Server. Fortunately this winds up being a simple matter. Open up SQL Server Management Studio, and enter the following command:


  EXEC sp_configure filestream_access_level, 2
  RECONFIGURE

Note the number at the end of the first line. Valid values for this are “0”, “1”, and “2”. “0” will disable FILESTREAM completely. “1” will enable it, but only for T-SQL code. “2” will fully enable it and allow access via .NET or other code. Note that these are slightly different from the way the Windows Server rights are set. Here, in order to run anything other than T-SQL you must use a “2”, regardless of where the code is executed (local or remote).

It is also possible to change this setting in the GUI. Right click on the server in SQL Server Management Studio, in this case it is “(local)”, and pick “Properties” from the menu. Go to the “Advanced” page and the FILESTREAM is the top most item, you can see it here with all of its options.

image 

Now your server has been properly configured to handle file streaming, you’ll want to create a database in a way to handle file streaming. We’ll do that in the next installment in this series.

About these ads

7 Responses to “Enabling FILESTREAM on SQL Server 2008”

  1. Jason Gurtz Says:

    Wow, i’m really looking forward to this series on what I consider to be sql server 2008’s most killer new feature other than the transparent record compression!

    It looks like just the thing for a roll your own web “file transfer” application a la yousendit.com etc… Now to figure out on the fly streaming via ASP.NET. Also, GIS data store for fly-over photos…..hmmm

  2. Enabling FILESTREAM In The Database « Arcane Code Says:

    [...] Enabling FILESTREAM on SQL Server 2008 [...]

  3. Derek Morrison Says:

    Thanks for highlighting this feature! I wasn’t aware of it, and I’m starting to see how it can be applicable in several scenarios.

  4. Full Text Searching a FILESTREAM VARBINARY (MAX) Column « Arcane Code Says:

    [...] these examples will be done against the data we’ve stored with FILESTREAM over the lessons from the last few days, know that this technique is identical for binary objects stored in a VARBINARY(MAX) field without [...]

  5. Josh Comley Says:

    I was looking for a decent explanation of how to enable FILESTREAM, none of the others explained things enough or had a decent array of screenshots (arguibly the most useful part of any step-by-step tutorial!).

    You can tell I’m a developer by the fact I said “array of screenshots”. Sigh.

    Thanks!

  6. Ehsan Mirsaeedi Says:

    thanks, your articles about FileStream feature was really helpful.

  7. Samir Says:

    Excellent. One of the best tutorial ever. Good luck.


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

Follow

Get every new post delivered to your Inbox.

Join 100 other followers

%d bloggers like this: