Introduction

This is my post for the monthly TSQL Tuesday Blogging event.  With the release of SQL Server 2008, among the many new features was one that at first I took little notice of, FILESTREAM storage options. It did not take long to realize that this new feature has some powerful storage capabilities. You can store, (very simply at that) all of that unstructured data (Images, video, word docs, PDF’s… you name it). This post will give you an overview of how to enable the features necessary to be able to take advantage of this storage option under SQL Server 2008. (Note, to enable this option you need admin rights server or sys admin rights) Also, since this is using NTFS the filegroup that you configure to use filestream storage cannot be a compressed (at the windows OS level) volume. (Right Click the C:\data folder -> Properties -> Advanced Attributes -> Uncheck “Compress contents to save disk space”). PLEASE don’t put this directory on your OS drive… this should go onto a data drive. I configured mine to use a directory on the same drive that I have system databases, and the SSAS data files. Space needs will obviously be determined by what you store, but it should be treated like other data files, and NOT be located on your OS drive.

For the sake of “completeness” you can also enable by using management studio (or at install for that matter), I am describing the 2 options I use most often… tsql, and configuration manager.

TSQL

For those of you who don’t like to script things, or don’t like the ease of clicking check-box’s. (actually any of these really are easy)…. File_stream_access_level is an advanced option, so first you need to show advanced, then you can turn it on.

USE master
Go

EXEC sp_configure ‘show advanced options’
GO

EXEC sp_configure filestream_access_level, 1
GO

RECONFIGURE WITH OVERRIDE
GO

Filestream access levels:

• Disabled = 0 — FILESTREAM support for the instance is Disabled
• Enabled = 1 — FILESTREAM for Transact-SQL Access is Enabled
• Enabled = 2 — FILESTREAM for Transact-SQL and Windows (32) streaming access is Enabled
  

Configuration Manager

I like this option the best… seems to me “configuration manager” is the logical place to manage the configuration of the database service, so (for me at least) this is the first place I go look. And the point-click thing is also not a negative in my mind… save me a few trips to MSDN to find the options.

Pick the instance (in this case default), then right-click select and select properties

Enable the check-boxes:

• The first check-box turns it on…
• The second is the name of the windows share name if you enable file-io streaming access (I left it at the default…. Please name this something more intelligent, you don’t want “mssqlserver” on each of your servers, you will get things confused if you have more than 1 server).
• The third is if you are going to allow remote clients to access the file-stream data.
• Service needs a restart

You have it turned on, take it for a drive ….

Almost there!

Now that the instance is configured to allow for the storage, you need to create a file-group to store the file-stream data in the database you wish to use.

ALTER DATABASE demo
ADD FILEGROUP filestream_data CONTAINS FILESTREAM
GO

Now that you have a filegroup, add a file. The directory cannot exist, it will be built/managed by the instance. In My case I had built J:\SQLData\demo\ before I executed this command.

ALTER DATABASE demo
ADD FILE
(
NAME= ‘demo_filestream_data_1’,
FILENAME = ‘J:\SQLData\demo\filestream’
)
TO FILEGROUP filestream_data
GO

Now you need a table to hold the pointers to the filestream data being stored on the server (note the filestream syntax)
I am using a guid for the ID, you can use a identity if you like.

CREATE TABLE dbo.BLOB_EXAMPLE (
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
BLOB VARBINARY(MAX) FILESTREAM NULL
)

Now you insert into the table, I am using some text… you can put whatever you want into the varbinary…. Files images etc

DECLARE @ID UNIQUEIDENTIFIER

SET @ID = NEWID()

 INSERT INTO dbo.BLOB_EXAMPLE
(ID, BLOB)
VALUES
(@ID, CAST(‘tsql Tuesday BLOB example’ AS VARBINARY(MAX)))

 SELECT ID, BLOB
FROM dbo.BLOB_EXAMPLE
WHERE ID = @ID

The ID: BF9A3D53-CC91-410E-9606-E2A43E36312A

 SELECT BLOB.PathName()
FROM dbo.BLOB_EXAMPLE
WHERE ID = @ID

\\SERVER_NAME\MSSQLSERVER\v1\Demo\dbo\BLOB_EXAMPLE\BLOB\BF9A3D53-CC91-410E-9606-E2A43E36312A

There are several parts to the path you get back

• “SERVER_NAME” (where is this path…. UNC PATH)
• “MSSQLSERVER” (the share-name you created near the top of this post)
• “\v1\Demo\BLOB_EXAMPLE\BLOB” (database, table, field info automatically created for you)
• “BF9A3D53-CC91-410E-9606-E2A43E36312A” the ID that you can use to reference the BLOB to retrieve the data.

Conclusion

I hope this post is a starting point for you to setup some great SQL Server 2008 functionality. It really is easy, and will allow storage of what is a growing, and ever present world of unstructured data in your databases.