Playing with the SQL Server 2008 FileStream Attribute

Thursday, October 18, 2007

Playing with the SQL Server 2008 FileStream Attribute

rdoherty's WebLog - Getting Traction with SQL Server 2008 Filestream

"Zach and I just spent a couple of days figuring out how to make use of the new FILESTREAM support in SQL Server 2008 and we thought we'd share a little bit about the experience in hopes it might save somebody some time.

...

Now SQL Server 2008 can store blobs in its own private namespace on the local NTFS filesystem instead of in-line with relational data. That's good cuz the NTFS file system was built to stream blobs. NTFS is even more interesting form a database perspective because it's transactional and supports recovery. So you can imagine the SQL Server Storage Engine and NTFS having a little mutual self-respect love fest.
Again this has obvious advantages from a manageability, backup and concurrency perspective that I won't reiterate here. But what I will do is talk about our first experience with FILESTREAM from a database perspective, and Zach's gonna talk about it from an application development perspective.
..."

The new SQL Server 2008 FileStream attribute and Remote BLOB API are two features I'm watching pretty closely.

What I'm going to have to see is how well FileStream can scale. For example, what happens to the file system when I have a table with tens of millions of rows, each with a blob? How will the NTFS files be hived? A single folder with tens of millions of files (that would be bad)? The fact that FileStream only works on local NTFS storage doesn't concern me, but how well it can handle millions of files does.

I really like this best of both worlds approach... I hope it works out in the real world.