Well it’s the end of our first week in China – tomorrow we fly to Beijing for another week of teaching. Hopefully we’ll get to do some sightseeing on Sunday – the last time I was in Beijing (for TechEd ’06) the conference hotel was about 25 miles outside the city centre – so I haven’t really been to Beijing yet.

During this week I’ve been playing with FILESTREAM for some demos I’m writing for a Microsoft class I’ll be teaching when I get back to Redmond. The class is about SQL Server 2008 for DBAs and the attendees will be a bunch of Microsoft Field personnel and SQL Server MVPs. Anyway, as I was playing, I realized that I hadn’t blogged anything about FILESTREAM yet, so this is the start of a series of posts about the feature. First up – what is it?

One problem that SQL Server users face is how to store related structured and unstructured data (BLOBs) together while:

  • Providing a way to keep the data in sync (transactionally consistent)
  • Providing fast streaming access to the BLOBs
  • Keeping costs low
  • Enabling scalability
  • Providing ease of management

There’s also been a strong desire for a data type that supports BLOB values greate than 2GB for many years.

Before SQL Server 2008, the solutions centered around:

  1. Storing the BLOBs in the file system

    • Advantages: low cost per GB; great streaming performance
    • Disadvantages: difficult to integrate with structured data; complex app development/deployment

  2. Storing the BLOBs on a dedicated BLOB store

    • Advantages: good scale/expandability; cost decreases as scale increases
    • Disadvantages: difficult to integrate with structured data; complex app development/deployment; streaming support/performance is solution dependent

  3. Storing the BLOBs in a database

    • Advantages: integrated management; BLOBs integrated with structured data; easy app development/deployment
    • Disadvantages: high cost per GB; poor streaming performance; 2GB size limit per BLOB

Enter FILESTREAM. It provides the following:

  • BLOB data is stored in the file system but rooted in the database (in the table of which it is part)
  • BLOB data is kept transactionally consistent with structured data
  • BLOB data is accessible through T-SQL and the NTFS Streaming APIs – with great streaming performance
  • BLOB size is limited only by the NTFS volume size
  • Manageability is integrated into SQL Server

Sounds pretty good eh? Well, it is – mostly. There are a few drawbacks with v1 however:

  • Database mirroring cannot be configured on databases with FILESTEAM data
  • Database snapshots don’t snapshot FILESTEAM data
  • FILESTREAM data can’t be natively encrypted by SQL Server

Personally, I think the first of these could be a major barrier to adoption in the enterprise – hopefully this restriction will be lifted in v2.

So – there’s a taster. In the next few posts I’ll detail how to enable FILESTREAM and create FILESTREAM data.

2 thoughts on “SQL Server 2008: FILESTREAM

  1. Could you please give an example with the help of create table structure how to create blob column to store more than 2 gb of data.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.