Månad: april 2009

MS SQL 2008 Store Blobs with FILESTREAM

Today I digged in myself in the new MS SQL function Filestream that was shipped with MS Sql 2008. The problem this function is trying to solve is to store images and other binary files in the database preserving speed and security. To get the maximum performance many developers stores files in the NTFS filesystem and just the path in the database, drawback is that these files dont get backed up along with an ordinary databasebackup. You can restore the paths to the files but the files are gone if the disked has crashed. You could of course backup the files also, but then you have two backup plans to maintain and worrying about. The other solution was to store the files as BLOB objects in the database, MS SQL is´nt realy built for these kind of storage it has a performance hit, here you can se som comparsion of the methods http://msdn.microsoft.com/en-us/library/cc949109.aspx

Now with Filestream you could use a combinatin of SQL and NTFS storage, thats what it´s all about a filestorage handled by the MS SQL Server engine, when you do a Database backup the files also are backed up I dont go in to details just google around and read about it. When you are ready for try this out I have made a very simple Sample project feel free to download it, I have ripped most of the code from Guy Bersteins site (links below)

My sample project is here: http://cid-e0d0e097982463fd.skydrive.live.com/self.aspx/CodeSamples/MsSqlFileStreamSample.zip

Some configuratin is needed for your MS SQL 2008 databse (Filestream must be enabled), read more about this here: http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/09/sql-server-2008-filestream-part-1.aspx