Chris Umbel

SQL 2008 FILESTREAM

this post was written during the early release candidates and the actual implementation has changed slightly. your mileage may vary with this post.

over the years people have attempted to put large amounts of non relational data in SQL. images strike me as the most popular case. generally people either stored the data directly in SQL or stored it on the filesystem with the path in SQL. neither of these were terrific options. the former was difficult to manage and frequently resulted in poor performance while the later tended to become inconsistent very quickly because SQL had no way of understanding how the filesytem was modified behind its back.

enter SQL 2008's FILESTREAM storage which allows you to store LOB fields directly on the filesystem but still function as columns in a regular old SQL table. this allows your LOBs to be limited only by the size of the filesystem and also guarantees consistency with SQL.

this post won't cover configuring the storage engine to use FILESTREAM. that information can be found here. in RC0 there's currently a few problems you may encounter with the setup. this post will help you solve them.

now... let's get started.

take, for example, an existing database named "Sandbox". the first thing we have to do to allow SQL to talk to FILESTREAM storage is to create a filegroup specifically for that purpose:

alter database Sandbox 
add FILEGROUP Sandbox_FS CONTAINS FILESTREAM

alter database Sandbox 
add file 
( NAME = Sandbox_FS,
FILENAME = 'C:\DATA\Sandbox_FS')
to filegroup Sandbox_FS

now that we have our database ready to play with FILESTREAM we'll create a table named "Things" we can use for testing:

create table Things (
	Thing_GUID uniqueidentifier rowguidcol 
 not null primary key default newid(),
	filedata varbinary(max) FILESTREAM
)

note the rowguidcol, it's imperitive that a rowguid not null column exist in order for any sibling columns to be stored in FILESTREAM storage. also note that our LOB field is decorated with the FILESTREAM attribute.

just to give us something to work with i'm going to create a row in here with an empty LOB we can play with:

insert into Things (filedata) values (0x)

from here on in we'll switch over to C# where we can manipulate this row's filedata column. slap all the following code in the main class for a console application for easiest use. we're going to need to talk directly to the SQL native client because at this point there's nothing within .net to access the sql LOB streams. a few constants and an external function call are necessary (Update 2009/12/07: The constants may no longer be necessary. 2k8 was still in beta at the time of this post and it's in sore need of an update.):

        const UInt32 DESIRED_ACCESS_READ = 0x00000000;
        const UInt32 DESIRED_ACCESS_WRITE = 0x00000001;
        const UInt32 DESIRED_ACCESS_READWRITE = 0x00000002;

        const UInt32 SQL_FILESTREAM_OPEN_NO_FLAGS = 0x00000000;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_ASYNC = 0x00000001;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING = 
 0x00000002;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_NO_WRITE_THROUGH = 
 0x00000004;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_SEQUENTIAL_SCAN = 
 0x00000008;
        const UInt32 SQL_FILESTREAM_OPEN_FLAG_RANDOM_ACCESS = 0x00000010;

        [DllImport("sqlncli10.dll", SetLastError = true,
              CharSet = CharSet.Unicode)]
        static extern SafeFileHandle OpenSqlFilestream(
                string FilestreamPath,
                UInt32 DesiredAccess,
                UInt32 OpenOptions,
                byte[] FilestreamTransactionContext,
                UInt32 FilestreamTransactionContextLength,
                Int64 AllocationSize);

since the necessary interop is ready we can do the typical plumbing and get our connection set up.

        static void Main(string[] args)
        {
            using (SqlConnection conn = 
                new SqlConnection(@"Data Source=(local)\TWOKEIGHT;
                Initial Catalog=Sandbox;Integrated Security=SSPI")) 
            {
                conn.Open();

we'll call the PathName function on the filedata we just created. this will return a UNC-path-looking-thing that is the identifier we'll pass into OpenSqlFilestream.

                SqlCommand pathCmd = 
                    new SqlCommand("
 SELECT TOP 1 filedata.PathName() FROM dbo.Things", conn);
                String path = pathCmd.ExecuteScalar().ToString();

all direct FILESTREAM manipulation must occur in a transaction. GET_FILESTREAM_TRANSACTION_CONTEXT gets the context from the transaction we just started so we can supply it to the native client.

                SqlTransaction tran = conn.BeginTransaction();

                SqlCommand transCmd = 
                    new SqlCommand("
 SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", conn, tran);
                byte[] transContext = (byte[])transCmd.ExecuteScalar();

here we're going to open the LOB using OpenSqlFilestream to get a handle so we can treat it like any old stream in managed code.

                SafeFileHandle handle = OpenSqlFilestream(
                         path,
                         DESIRED_ACCESS_READWRITE,
                         SQL_FILESTREAM_OPEN_NO_FLAGS,
                         transContext,
                         (UInt32)transContext.Length,
                         0);

                byte[] buff = new byte[512];
                using(FileStream stream = new FileStream(
                        handle,
                        FileAccess.ReadWrite,
                        buff.Length,
                        false)){
                    StreamWriter writer = new StreamWriter(stream);

to complete the illustration we'll write some data and commit up our transaction.

                    writer.Write("abc");
                }

                tran.Commit();
            }
        }

pretty cool eh, we opened a LOB in SQL that actually lived on the filesystem and treated it like a regular filestream within the context of a sql transaction.

Sat Aug 04 2007 00:08:00 GMT+0000 (UTC)

Follow Chris
RSS Feed
Twitter
Facebook
CodePlex
github
LinkedIn
Google