Adventure Works Samples Update and FILESTREAM

A bit late (released at the beginning of the month), but a follow up to post I made earlier this year, there is a new release of the Adventure Works Databases.  These can now be found here.  They are simplified into just 4 downloads, which makes things a lot easier! Also the install is a lot more straight forward, although for the install to complete you must have full text search and FILESTREAM installed and enabled.   It is good to see that FILESTREAM is being used already, and Adventure Works can be used to test and demonstrate this new functionality.

What is FILESTREAM?

Prior to SQL Server 2008 if you needed to store images, videos, PDF’s, or any other binary data we used a blob data type and it was in-line with relational data.  Now SQL Server 2008 can store blobs in its own private namespace on the file system, which simplifies the storage of blob data. Adventure Works examples have now used it to store instructions in word doc files. (See table Production.Documents)

Enable FILESTREAM

Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.

• In the list of services, right-click SQL Server Services, and then click Open.
• Locate instance of SQL Server Right-click and click Properties.
• Click the FILESTREAM tab.
• Select the Enable FILESTREAM for Transact-SQL access check box.
• If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
• If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
• Click Apply.
• In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Using FILESTREAM Databases

• Create a new database is SSMS:

• Under the “Filegroups” Tab add a new FILESTREAM:

• Once set up click OK. This should now have added a new folder as follows under your SQL installation:

From here we can see that we have our new FileStreamTestData directory as well as documents folder, which has been added from the install of the SQL2008 AdventureWorks samples.  So now we want to test the storage of some files in SQL.  So lets try and store some Pictures for example.  To begin with we will need to set up a new table.  It is worth noting here that we must add a ROWGUIDCOL column, preferably the primary key, and the unique identifier.

CREATE TABLE [dbo].[Media] (
[mediaId] [uniqueidentifier] NOT NULL ROWGUIDCOL PRIMARY KEY,
[dateCreated] [datetime] NOT NULL DEFAULT(GETDATE()),
[fileName] [nvarchar](256) NOT NULL,
[location] [geometry] NULL,
[file] [varbinary](max) FILESTREAM);
GO

This will give us a new table, that we can insert into.  So If we insert an dummy row and keep an eye on the folders created when creating this new filestream.  Execute the following statement:

INSERT INTO [FileStreamTest].[dbo].[Media] (
[mediaId] ,[fileName] ,[file] )
VALUES
(NEWID(),
‘NewFile.jpg’,
convert(varbinary(max), ‘Not a real Picture, but proves a point’))

You will now notice that a new file has been added to the file system.

The more rows we insert, the more files are added to this directory.  You will also notice that the date time stamps on the files are also reflected by when the items were added to the database.  If a row is updated with a new image, the new image is created,  however the old image remains.  This also applies when the row is deleted.  However, these directories are cleaned up periodically by the FILESTREAM garbage collector.

So, in short it seems that this will become the way forward for storing all sorts of files in your database that can be centrally stored, managed and backed up.  A Simple application could be easily written to save, update and retrieve images through SQL Server 2008, using file stream data.