Thoughts on FILESTREAM

A few weeks ago, I had a post about running into some issues due to a database using the FILESTREAM feature. To be fair, the problem wasn’t with FILESTREAM itself, just that I hadn’t accounted for it being in use (or previously in use) while configuring Availability Groups. FILESTREAM has been around for years but isn’t something I’ve seen in use too often. In case you’re not familiar with it, let’s take a brief look at what it is and some gotchas to watch out for.

Defining FILESTREAM

FILESTREAM was released way back in SQL Server 2008 and offers the ability to manage BLOB (Binary Large OBject) data or unstructured data right from the database. This means storing and maintaining images, videos, PDF files, or whatever other documents your heart desires. It’s a feature with the goal of getting the best of NTFS along with the transactional consistency of SQL Server.

Worth the Hassle?

Doubtful. There’s a reason it’s called a “database.” It’s a place to store and manage data. Not images or videos. Not PDF files. Storing complete files can end up making your life more difficult than it needs to be.

There are a few downsides to consider if you’re managing files in your database with FILESTREAM. One is that your backups are going to have that much more data to account for. When dealing with larger backups, that also means longer restore times. FILESTREAM may also not be a great option depending on your security requirements. For example, keep in mind FILESTREAM data isn’t something that gets encrypted if you’re using Transparent Data Encryption (TDE).

Final Thoughts

I’m not saying it’s something to never use. I’m sure there are environments and situations where it can fit. But it’s not something I would go out of my way to use.

To read more about FILESTREAM, check out the Microsoft article here.

Thanks for reading!