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.
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.
EXEC sp_configure ‘show advanced options’
EXEC sp_configure filestream_access_level, 1
RECONFIGURE WITH OVERRIDE
- 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
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 ….
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
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
FILENAME = ‘J:\SQLData\demo\filestream’
TO FILEGROUP filestream_data
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, CAST(‘tsql Tuesday BLOB example’ AS VARBINARY(MAX)))
SELECT ID, BLOB
WHERE ID = @ID
The ID: BF9A3D53-CC91-410E-9606-E2A43E36312A
WHERE ID = @ID
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.
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.