If you’re reading this then I’m guessing you already know a little bit about FILESTREAM and what you want (or think you want…) to do with it. SQL Server has come a pretty long way since the days using image and varbinary data-types to store files, and although it’s sort of tried and tested, storing verbose paths in your database to physical files is pretty rubbish if we’re being honest.
It’s likely you’re looking to store unstructured data objects like images and documents in a way that keeps things transactionally consistent and secure, as well as having an easy mechanism of inserting and retrieving them later.
If that’s even close to what you’re after, then I’ve got some good news for you: FILESTREAM could be your answer. Note, if you’re using SQL Server 2012 or later, then FileTables might be an even better solution, but… we’ll cover that in another article.
I’m not going to go into the nitty-gritty of how FILESTREAM works here. I’m just going to walk through the steps of enabling and configuring FILESTREAM in as clear of a way as possible, then move on to having a closer look at building the FILESTREAM enabled database and tables, before finally looking at how to insert and retrieve FILESTREAM data.
This is a two-part blog, and in this first part we will look at enabling FILESTREAM on the SQL Server instance and configuring the FILESTREAM enabled database and table.
First things first, we need to enable FILESTREAM at the SQL instance level. We do that by opening SQL Server Configuration Manager on the server, right click the instance, and open the Properties dialog.
On the FILESTREAM tab, you want to check the three boxes:
Firstly, we’re enabling FILESTREAM for Transact-SQL access. This is the bread and butter of how FILESTREAM works.
Secondly we’re enabling FILESTREAM for file I/O access because, for the purpose of this demo, we want to be able to read/write FILESTREAM objects using Windows.
Thirdly, we do want to allow remote clients access to FILESTREAM data, so we’ll enable that too.
You’ll also need to enter a share name in the Windows share name box. I’ve seen a lot of people (with good reason) using their instance name here, but since the instance name of my test server is not very descriptive, I’ll change the Windows share name to ‘fs_share’.
Creating a FILESTREAM enabled database
So now we’ve enabled FILESTREAM, we need to create a database that is capable of utilising it.
But first, we need to do something at the instance level. Connect to the SQL Server instance in Management Studio, and run the following to enable the FILESTREAM feature.
EXEC sp_configure filestream_access_level, 2 RECONFIGURE;
The 2 means we are configuring FILESTREAM for both T-SQL and Windows access.
I’m going to create a database called MyImages using the following script:
CREATE DATABASE MyImages ON PRIMARY ( NAME = myimages1_data, FILENAME = 'c:\data\myimages1.mdf'), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = filestream1, FILENAME = 'c:\data\filestream1') LOG ON ( NAME = myimages1_log, FILENAME = 'c:\data\myimages1.ldf') GO
This isn’t much different than creating a normal database. The only exception is that we have an extra FILESTREAM filegroup.
Creating the FILESTREAM table
The last piece of this particular puzzle is to create a table capable of storing FILESTREAM data.
We only need two columns (although in a real world scenario we’ll no doubt require more).
A uniqueidentifier column with ROWGUIDCOL properties.
A VARBINARY(MAX) column specifying the FILESTREAM property.
CREATE TABLE Images ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [SerialNumber] INTEGER UNIQUE, [Doc] VARBINARY(MAX) FILESTREAM NULL ) GO
So you’ve now enabled and configured FILESTREAM at an instance level.
Created a FILESTREAM enabled database with the correct filegroups.
Created a table which is capable of storing FILESTREAM data.
In the next part of this article we’ll look at inserting BLOB objects into this table, namely a few images, and talk briefly about retrieving objects using FILESTREAM.