Welcome to MSDN Blogs Sign in | Join | Help

To FILESTREAM or not to FILESTREAM... That is the question.

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.

Published Monday, March 03, 2008 10:13 PM by ZachSkylesOwens
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# re: To FILESTREAM or not to FILESTREAM... That is the question.

Hi Zach,

To add to the questions you've addressed here: Will we be able to access FILESTREAM blobs through 1) the Entity Framework, 2) LINQ to Entities and/or 3) SQL Server Data Services?

If 3) is true, then "the concept of OpenFile("C:\Path_To_My_File\File_Name.docx")" would be baked in, no?

Lastly - if so, when?

Thanks,

David

Sunday, March 30, 2008 10:31 PM by David

# re: To FILESTREAM or not to FILESTREAM... That is the question.

Correction - Make that ADO.NET Data Services/Astoria for Q3...too many data services to keep track of these days. :)

Sunday, March 30, 2008 10:54 PM by David

# re: To FILESTREAM or not to FILESTREAM... That is the question.

Hello,

I do not see much good in Filestreaming or maybe for small companies, to avoid to deal with file server, San and all.

I see so many features that are not available when using filestreaming...

SQL server should handle data, not files!

Sunday, May 03, 2009 9:16 AM by Clement

# re: To FILESTREAM or not to FILESTREAM... That is the question.

Clement,

That's an interesting perspective...  Which features are you referring to that aren't available when using FileStream?

FileStream isn't really targeted at avoiding the need for a file server...  It's all about managing binary data alongside relational data when consistency and manageability are important.  Building a content management system is a classic example where developers have had to decide to store the binary data in the database or out in the file system with a pointer to its location stored as text in a table.  Maintaining consistency is then up to the application which isn't always a viable option when it comes to database backups, restore, etc.

Tuesday, May 05, 2009 11:38 AM by ZachSkylesOwens

# re: To FILESTREAM or not to FILESTREAM... That is the question.

Clement,

That's an interesting perspective...  Which features are you referring to that aren't available when using FileStream?

FileStream isn't really targeted at avoiding the need for a file server...  It's all about managing binary data alongside relational data when consistency and manageability are important.  Building a content management system is a classic example where developers have had to decide to store the binary data in the database or out in the file system with a pointer to its location stored as text in a table.  Maintaining consistency is then up to the application which isn't always a viable option when it comes to database backups, restore, etc.

Tuesday, May 05, 2009 11:38 AM by ZachSkylesOwens

Leave a Comment

(required) 
required 
(required) 

  
Enter Code Here: Required
 
Page view tracker