SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

FILESTREAM Setup and Testing without Visual Studio

By Hugh Thomas,

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;

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.

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

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
NAME = FSGroup1File, 
TO FILEGROUP FileStreamGroup1;

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, 
FILESTREAM_ON [FileStreamGroup1];

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)
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/

Total article views: 4643 | Views in the last 30 days: 2
Related Articles

Create a Filestream Filegroup for Filetables – SQL Server 2012

Once you’ve enabled filestream, the next step is to add a filegroup to your database to hold the fil...


FILESTREAM storage requirement



Enable Filestream on Instance and database

Enable Filestream on SQL instance and database with database name.



Turning on FileStream post installation


Disabling FileStream

Database off-line after disabling filestream