Monday, December 07, 2009

SQL Server 2008 FILESTREAM - Part 1

SQL Server 2008 FILESTREAM SQL Server 2008 has a lot of new cool features that I've talked about before:

 

One of the great features in SQL 2008 is it's FILESTEAM support. To make things short, we can now not only store the path to the file in our database, but we can now store the whole file inside the SQL Server and than backup, restore and manage it just as we would do with any other data.

In this post series I will build a file management database with SQL 2008 FILESREAM and build a .net client application that works with it.

1. Enable FILESTREAM support

To enable FILESTREAM support, run the following sql command

exec [sp_filestream_configure] @enable_level = 3;

This command enables or disables the support according to the parameter. The value 3 means that FILESTREAM will be enabled for Transact-SQL, local file system access, and remote file system access.

You can find more details about sp_filestream_configure here.

2. Create a database with a File Group that contains FILESTREAM

To create a database instance with a file group that contains FILESTREAM, run the following sql command. This creates the database with 3 file groups, but only one of them contains FILESTREAM as you can see in the command.

CREATE DATABASE FileManagement

ON

PRIMARY (

    NAME = FileManagement_Primary,

    FILENAME = 'c:\temp\data\FileManagement.mdf'),

FILEGROUP FileStreamGroup CONTAINS FILESTREAM (

    NAME = FileManagement_FileGroup,

    FILENAME = 'c:\temp\data\FileManagement')

LOG ON ( NAME = FileManagement_Log,

    FILENAME = 'c:\temp\data\FileManagementLog.ldf')

GO

This command will create the following directories structure:

SQL Server 2008 FILESTREAM

In the Data Directory, I could find the files specified above:

SQL Server 2008 FILESTREAM

and in the FileManagement directory, I could find the following content:

SQL Server 2008 FILESTREAM

3. Create a Table with FILESTREAM

Note: FILESTREAM is not a type of a column, but it is a property you put on a varbinary(max) column.

So, in order to create a table with a varbinary(max) column that will be used for FILESTREAM, run the following command. Note that I don't have any additional columns to the file itself.

CREATE TABLE [dbo].[Files]

(

    FileID uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,

    FileContents varbinary(max) FILESTREAM DEFAULT NULL

)

4. Add Test Data

In order to get the feeling of the experience of working with files, let add an empty file:

insert into Files

values (newid(), null);

If we now query the database to see the files in it:

select * from Files

we will get the following result (the GUID will probably be different...)

FileID                               FileContents

------------------------------------ ------------------------------------------------------------------

8247CE78-74DF-4BDE-A08D-9760AE0B1555

and If we look at the FileManagement directory, we can see that a new directory has been created

SQL Server 2008 FILESTREAM

If we add a file with some content:

insert into Files

values (newid(), CAST ('my test file' as varbinary(max)));

we can query for it and see:

FileID                               FileContents

------------------------------------ ------------------------------------------------------------------

8247CE78-74DF-4BDE-A08D-9760AE0B1555

3C028D84-BA68-4E7B-8E87-B9889900D728 0x6D7920746573742066696C65

Since this hexadecimal content doesn't mean anything to use, and the fact that File is a type in SQL, we can query for some additional data such as path name:

select FileID, FileContents.PathName() as Path from Files

and get the following result:

FileID                               Path

------------------------------------ ------------------------------------------------------------------

8247CE78-74DF-4BDE-A08D-9760AE0B1555 NULL

3C028D84-BA68-4E7B-8E87-B9889900D728 \\GUYBUR-PC1\SQLEXPRESS\v1\ 
                                     \FileManagement\dbo\Files\FileContents\ 
                                     \3C028D84-BA68-4E7B-8E87-B9889900D728

Summary

In this post I showed how to create an SQL Server 2008 database with FILESTREAM support and add some text data into it. In the next post I will show how to write a .net client application that works with this database to add and change files.

Enjoy!

No comments: