Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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;
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/

Total article views: 4613 | Views in the last 30 days: 6
 
Related Articles
BLOG

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...

FORUM

FILESTREAM storage requirement

FILESTREAM

SCRIPT

Enable Filestream on Instance and database

Enable Filestream on SQL instance and database with database name.

FORUM

FileStream

Turning on FileStream post installation

FORUM

Disabling FileStream

Database off-line after disabling filestream

Tags
filestream    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones