Over the past few years a common trend has started in using the database to store Binary Large Objects (BLOB), i.e Documents ( pdf, .doc, .docx .csv, .xls etc) or Graphic images (.png, .gif, .jpg) . The FileStream attribute was introduced in SQL Server 2008, and the implementation has further been improved in SQL Server 2012. The following video is an excellent resource to get you up to speed and provides some additonal background information.
I won’t be going through the setps of configuring your SQL server to use FileStream, only because the above video takes you through it for SQL server 2008, and there are a number of great blog posts out there that will assist you for SQL server 2012,
Personally I found this video, to be the most useful, well worth a watch. There is just loads of information, that this guy pumps out, and a lot of it really interesting.
Now that we’ve got all the knowledge as to what FileStream is and how to use it, we now need to put our developer hat on, and figure out how we use FileStreams and FileTables within an application.
Generally most developers doing MVC or WPF work will be thinking cool, I’ll just use this with Entity Framework (EF) and I’ll be able to quickly be able to leverage file storage in my project, all will be cool with the world. Well not so fast my little codemonkey, it’s not that simple! Unfortunately, at the time of writing anyway, EF does not support the sqlFileStream type, so unfortunately you will have to do some old school coding stuff to get it to work! To my mind there is a very good reason for this, and some I think need further explanation.
1.) FileStream and FileTable softened security requirements and Disaster recovery
If you’ve watched some of the above videos, you would’ve picked up from something that Bob said, that you may need to consult your divine data greatness in your organization a.k.a Your DBA, to configure your SQL server, to enable FileStream, this may make you DBA’s a little nervous from a security perspective, essentially it increases threat exposure to SQL server, an attacker may be able to corrupt your SQL server, but coming at it from FileSystem. ( a complete discussion of this threat etc, quite frankly is beyond the scope of this article and also my limited knowledge!) . So to avoid any fall outs with your DBA, I propose you evaluate whether you are able to create a seperate database from your application database, Specifically designed for creating a FileStorage.
A side note, I usually advocate this because, I for one don’t understand why anybody would want to burden application database, with storing huge blobs of data, it makes Backup and recovery of your application an arduous task, let alone inflating your database size! It’s also bit like storing your auditing and applciation logging in your application database! What happens when the DB goes down, how do you read your Audits to find out what went wrong? It always amazes me that the Aerospace industry do this, Race on to find Malaysia Airlines black box as battery sending signal ‘will run out in 30 days’ , surely it makes sense that if this is such crucial information to help investigators find out what went wrong with the plane or even finding out where it is, it shouldn’t dissapear with the plane, surely with the technology available these days we can log the data remotely? But I digress!
2.) Entity Framework doesn’t support FileTable
This is a pretty good reason from a Lazy Developer perspective
! If you’re like me and are somewhat lazy when it comes to code, and only really want to write whats absolutely necessary, and use tools to alleviate much of the grunt work as much as possible, you’ll discover to your horror that tools like Entity Framework Power Tools
, will stop working if you have FileTables incorporated into your database project! You’ll start getting errors like One or more errors occurred while loading schema information.
error 6004: The table 'FileTableDB.dbo.FileTableTb' is referenced by a relationship, but cannot be found.
This is caused by hierarchyid data type used in File Table, which is not supported in Entity Framework.
When you attempt to Reverse Engineer Code First using the power tools. This can become a major PITA on a large scale project, days consumed by writing boring POCO’s and Mapping logic. Enough already don’t do it!
3. ) Always Abstract out your File Storage
Architecturally speaking I always try abstract out file storage from the application. Primarily due to scalability and exstensibility. If you do so much plumbing code to store images and files withing the DB, what do you do when the client says, “Hey!, I hear all the cool kids are playing with CDN, can we do that too?” Migrating all that data out of your database, becomes a full scale war of data! because no doubt, regardless of how good your software teams intentions, to isolate concerns, bad habits will emerge, and there will be all sorts of primary key constraints, and data references that creep in!