Roger and I have been delivering a demo which highlights Spatial and FILESTREAM features of SQL Server 2008.  One of the common things we've heard from developers is...

For years we've been told that large binary files should never be stored in the database...  Are you telling us to start storing these files in the database now?  If so, why?  Are we just supposed to throw this best practice out the window?

This is really a great question which has prompted some interesting discussions.  Obviously the answer is not black and white. So let's start by looking at what has changed.

 

SQL Server 2008 is now a very powerful engine for storing binary files. 

  • These files can be accessed through high performance Win32 streaming API's in addition to T-SQL.
  • These files are managed by SQL Server in their own file groups which can be backed up restored along with the rest of your SQL Server data.  On the flip side you aren't required to backup and restore these file groups.
  • Reading and writing these files can now be part of a database transaction.

So you might be thinking to yourself...

Sounds great!!!  Let's start storing all of our binary data in SQL Server.

Well, there are some considerations to be made before signing up to rewrite your app to take advantage of FILESTREAM.  Here are some of the main considerations.

Do other applications need direct access to your binary files?

If you read my article about writing files to FILESTREAM you probably noticed that you have to go through SQL Server to access the data in FILESTREAM.  There is no concept of
OpenFile("C:\Path_To_My_File\File_Name.docx")

Does your architecture require database mirroring?

Database mirroring does not yet support FILESTREAM.

Those are just a couple of the things to think about.  I'd recommend checking out our FILESTREAM sample on CodePlex and make some decisions for yourself. 

FILESTREAM is a great technology and we are really excited to see how developers incorporate it into their applications.  Feel free to post comments here about your experience integrating FILESTREAM into your architecture.