The FILESTREAM feature of SQL 2008 allows more effective storage and retrieval to BLOB data. It accomplishes this by utilizing both SQL 2008 and the NTFS file system. In the past, most developers stored BLOB data in the SQL Server using varbinary(max). This has some drawbacks including: you had a 2GB max, streaming performance was awful, and recovery from fragmentation was poor. The FILESTREAM can hold up to the size of the NTFS volume size, streaming performance is excellent, and it can recover from fragmentation on disk nicely.
The one drawback that I can see is when the client needs to perform small & frequent BLOB updates. Rather than writing to SQL Server data files, FILESTREAM writes to the file system. Locating, creating, updating, deleting files outside of the SQL Server data files do come at a cost. So some consideration as to the size & frequency of BLOB data would need to be made.
There are a few ways to enable FILESTREAM (SQL Server Configuration Manager, SQL Server Management Studio properties of the connected instance, and via TSQL scripting). Personally I was only able to get it to work by using the SQL Server Configuration Manager. Apparently installing Visual Studio/SQL 2005 can screw up FILESTREAM features of SQL 2008… Anyway, here’s the basic FILESTREAM access levels which you’ll need to understand when enabling it.
FILESTREAM access levels:
a) 0 = Disables FILESTREAM support for this instance.
b) 1 = Enables FILESTREAM for Transact-SQL access.
c) 2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access.
I created a script which enables FILESTREAM for the SQL Instance, then it enables it for my “testdb” database. It auto-detects where your database’s data/log files reside and then creates a directory in there for FILESTREAM objects.
USE [master];
GO
DECLARE @filestream_access_level SMALLINT;
SELECT @filestream_access_level = CONVERT(SMALLINT, SERVERPROPERTY('FilestreamEffectiveLevel'));
IF @filestream_access_level <> 2
BEGIN
EXEC sp_configure filestream_access_level, 2;
RECONFIGURE WITH OVERRIDE;
END;
GO
USE [testdb];
GO
IF NOT EXISTS (SELECT * FROM sys.filegroups AS fg WITH (NOLOCK) WHERE name = 'testdb_FILESTREAM')
BEGIN
--add a file group and indicate that it will contain FILESTREAM objects
ALTER DATABASE testdb ADD FILEGROUP testdb_FILESTREAM CONTAINS FILESTREAM;
END;
GO
IF NOT EXISTS (SELECT *
FROM sys.filegroups AS fg WITH (NOLOCK)
INNER JOIN sys.database_files AS df WITH (NOLOCK) ON fg.data_space_id = df.data_space_id
WHERE fg.name = 'testdb_FILESTREAM'
AND df.name = 'testdb_FILESTREAM')
BEGIN
DECLARE @path NVARCHAR(MAX), @sql_statement NVARCHAR(MAX);
SELECT @path = mf.physical_name FROM sys.master_files AS mf WITH (NOLOCK) WHERE mf.name = 'testdb' AND mf.type_desc = 'ROWS';
SELECT @path = SUBSTRING(@path, 1, LEN(@path) - CHARINDEX('\', REVERSE(@path))) + '\testdb_FILESTREAM';
--tell the file group where the FILESTREAM objects should be stored
SELECT @sql_statement = N'ALTER DATABASE testdb ADD FILE (NAME = ''testdb_FILESTREAM'', FILENAME = ''' + @path + ''') TO FILEGROUP testdb_FILESTREAM;';
EXEC sp_executesql @statement = @sql_statement;
END;
GO
At this point I was ready to design a table in SQL Management Studio with a FILESTREAM column. A FILESTREAM column is actually a varbinary(max) column with the FILESTREAM attribute. Well, the table designer in SQL Management Studio does not support specifying the FILESTREAM attribute. You have to script it.
Here’s some rules which you’ll need to adhere to when creating FILESTREAM columns.
1) The table can have multiple FILESTREAM columns, but they must all be in the same FILEGROUP.
2) If you don’t specify the FILESTREAM_ON clause, the default FILEGROUP will be used.
3) The table must have a uniqueidentifier column with the ROWGUIDCOL attribute. It cannot contain NULL values and must have either a UNIQUE or PRIMARY KEY single column constraint.
USE [testdb];
GO
IF NOT EXISTS (SELECT * FROM sys.tables AS t WITH (NOLOCK) WHERE t.name = 'Document' AND t.type = 'U')
BEGIN
CREATE TABLE dbo.Document (
DocumentID INT IDENTITY PRIMARY KEY,
DocumentGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID(),
Document VARBINARY(MAX) FILESTREAM NULL
) ON [PRIMARY];
END;
GO
In my next post, I will show .NET streaming capabilities with the new FILESTREAM features in SQL 2008.