FILESTREAM Setup and Testing without Visual Studio

As is the case with many SQL developers, my company plans to use SQL Server 2008 for a major new development project. After getting my SQL Server 2008 test environment set up, I knew there are many new features to test in general, but I wanted to specifically test out the new FILESTREAM functionality in SQL Server 2008. Our new project may benefit from FILESTREAM, but without some testing I could not be sure. Also, for my own purposes, I wanted to complete my initial testing without using Visual Studio or any .NET code to facilitate the import of file objects.

To get things working, first I had to turn on FILESTREAM during the SQL Server 2008 installation process. If you already installed SQL Server 2008 and need to change your configuration, open the SQL Server Configuration Manager, right-click on the SQL Server service, click on Properties, and go to the FILESTREAM tab. There you can check the options to turn on FILESTREAM.

Next I had to activate FILESTREAM at the server level using the following:

-- If a new SQL Server installation, activate at the server level
EXEC sp_configure filestream_access_level, 2;
GO
RECONFIGURE;
GO

I should note that the significance of the "2" in the statement above is to turn on FILESTREAM for both TSQL and for streaming through the API. Option "1" will turn on just TSQL and option "0" disables FILESTREAM.

I then created the following database for testing:

-- Create Demo database for testing using the default settings.
CREATE DATABASE [Demo]

Next I added the filegroup FileStreamGroup1 to the Demo database which will be used for FILESTREAM. You need a different file group for FILESTREAM because it actually creates and manages and NTFS folder (as you will see below) thereby serving a different function than a traditional SQL filegroup.

-- Add the FILESTREAM FILEGROUP to the database
ALTER DATABASE Demo ADD
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;
GO

Then I added a file to the database for FILESTREAM to use. Notice this is actually a file path, not a filename. This file path will contain the folders and files that FILESTREAM uses to manage your FILESTREAM objects. Change the FILENAME path to suit your purposes. You must choose a local drive location for FILESTREAM filegroups which makes sense. A remote share that failed could break the file system. You want to choose a local file location that will not be accessable to users through traditional Windows file sharing because this file structure is an extension of your SQL database. If someone browses the folders and deletes a file, it is gone.

-- Add FILE to Demo for the FileStreamGroup1
ALTER DATABASE Demo ADD FILE ( 
NAME = FSGroup1File, 
FILENAME = 'C:\FILESTREAM\Demo')
TO FILEGROUP FileStreamGroup1;
GO

Next I created a test table which contains a varbinary(max) column that will use FILESTREAM. When creating the table, I had to associate the FileStreamGroup1 with this table to handle the FILESTREAM data.

-- Create the test table for FILESTREAM
CREATE TABLE [dbo].[tFileStreamTest]( 
[id] [int] IDENTITY(1,1) NOT NULL, 
[FileStreamTest] [varbinary](max) FILESTREAM NULL, 
[FileGUID] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL 
UNIQUE DEFAULT NEWID()
) ON [PRIMARY]
FILESTREAM_ON [FileStreamGroup1];
GO

Before I go on, if you are following these directions you now have some automatically created file structures on your system. Browse in Windows Explorer to the path you defined as your FILENAME, which in my case is 'C:\FILESTREAM\Demo'. You will see this folder and maybe a subfolder with a GUID-like folder name. All this is created and managed by SQL Server.

Next I imported an image file using OPENROWSET. I used the ielock.jpg image from Internet Explorer, but you can point to any file of any type that you prefer and change the path-filename in the sample code below.

Insert into dbo.tFileStreamTest (FileStreamTest)
SELECT * FROM 
OPENROWSET(BULK N'C:\Documents and Settings\Administrator\Desktop\ielock.jpg' ,SINGLE_BLOB) AS Document

Now look in Windows Explorer at your managed folder location. In one of your GUID-like folders, you will see a GUID-like filename without an extension. That is your file. In fact, if you imported an image as I did, you can right-click on the file and open it with an image editor or IE and it will open right up. The file is intact and in a usable state.

To test the file management, I deleted the new row from my tFileStreamTest table and sure enough, the GUID-like filename disappeared from Windows Explorer.

I still have more testing to do, but I am impressed by the relative ease of the initial setup and implementation. How well it scales and dealing with the trade-offs such as no Database Mirroring are still to be considered for this project, but my first impressions are favorable.

Hugh Thomas has a blog at http://devsqlserver.blogspot.com/