THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

FILESTREAM: Storing Binary Objects in a database – or not

Many shops need to store binary large objects (sometimes called BLOBS) in a database. There are really only two ways to do this: store in them in a table structure in the database itself using a binary data type, or store them in the operating system in a file folder somewhere and point to the file using a text field in a table.

Both of these approaches have issues. Relational Databases aren’t really designed to hold that much data in a single field – not ours, not anybody’s. And pointing to a file is risky, since the file might change, it isn’t under database control for security and backups and so on.

Enter FILESTREAM. In SQL Server 2008 we introduced a feature that actually combines the two approaches into one. Using FILESTREAM, you enter the data into a “column”, but in fact SQL Server stores the data on the hard drive. It’s lightning fast, doesn’t lose the “pointers” to the files, and keeps the whole thing under database control. You can secure it with the same permissions as the database, and it gets picked up in backups and so on.

It does have some limitations and caveats, so be sure and check out the reference data in the following link. And then try it yourself – pretty easy to set up and manage.

http://technet.microsoft.com/en-us/library/bb933993.aspx

Published Thursday, January 28, 2010 7:47 AM by BuckWoody

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

 

Wiseman82 said:

Another good thing about FILESTREAM storage is that it doesn't count towards to 4Gb limit in SQL Express. It also allows you to store files larger than 2Gb in size. :-)

As you mentioned, it does have it's limitations so the choice of storage isn't straightforward.  e.g. Database Mirroring isn't supported and database snapshots don't work with FILESTREAM filegroups.  Also, in terms of performance, FILESTREAM storage might not be faster for smaller file sizes.

January 28, 2010 1:20 PM
 

Jonathan Gardner said:

Just an interesting note for those of us supporting SharePoint environments is that SharePoint 2010 is going to you Remote BLOB Storage instead of FILESTREAM.  

You can find more information about how they work on the SQL Server Team Blog.  http://bit.ly/auhnW7

January 29, 2010 10:57 AM
 

Gabi said:

I work with files from 1KB up to 50MB..Should I work with FILESTREAM? What do you think?

Thank you!

February 9, 2010 9:03 AM
 

Buck Woody said:

Gabi - I would certainly recommend looking into it.

February 9, 2010 11:01 AM

Leave a Comment

(required) 
(required) 
Submit

About BuckWoody

http://buckwoody.com/BResume.html

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement