FileTable: SQL Server 2012's little gasp-maker

When was the last time you looked at a SQL Server new feature list and said, “Ohhh, WOW!”? Was it around March of last year? No? Then you might have missed something really, really cool.

Let me introduce you to my friend, SQL Server 2012′s FileTable.

A Brief History of Files

Before SQL Server 2008, if you wanted to store file information (“unstructured data”) in your database, you had two options: you could either store your file in the database as an IMAGE (or VARBINARY) column, which was good for management, but pretty damn bad for performance. Or, you could store the file in the file system, and store a link in a database…great for performance, lousy for managing your files and keeping them in sync with the database.

Microsoft addressed this in SQL Server 2008 with FILESTREAM, which lets you store files in the NT file system, and keep them transactionally consistent with the database. What’s more, you can search those files’ contents using Full-text search. Good stuff, that.

FileTable Defined

Now in SQL Server 2012, Microsoft has built FileTable on the FILESTREAM foundation. FileTables are a special kind of table that stores files in the database – where they can be managed, accessed, backed up, and searched (via Full-text or the new and similarly awesome semantic search). AND! AND! AND! Those files can be accessed from Windows as if they were stored on the NT file system. SQL Server exposes a “virtual file share”, which you can open up just like any UNC and look at, add, remove, and modify files all the day long.

You can access FileTable files just like any other file stored on the server: programatically, or via a directory window!

That’s really spectacularly cool, but you’ve got to see it in action.

Get Set Up for FileTable

Before you can use FileTable on an instance, get your prerequisites in order! Note: Standard warnings about testing this in a test environment apply. If you run out and do this on production without trying it out elsewhere first, it’s your own durn fault, kids.

Enable and configure Filestream:

Open SQL Server Configuration Manager.
Bring up Properties for your instance.
Select Enable Filestream for T-SQL Access AND for File I/O Access
And run this statement in SSMS:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
Restart the SQL Server service.

Easy-easy. Now, for our example, we’ll create a database with Filestream:

CREATE DATABASE FileTableDemo ON PRIMARY
( NAME = N'FileTableDemo',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemo.mdf'),
 FILEGROUP FilestreamFG CONTAINS FILESTREAM
( NAME = FilestreamData,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemoFS' )
 LOG ON 
( NAME = N'FileTableDemoLog',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTableDemoLog.ldf')
WITH FILESTREAM
(
 NON_TRANSACTED_ACCESS = FULL,
 DIRECTORY_NAME = N'FileTable'
);
GO

As of this moment, your virtual directory is available, at \\{servername}\MSSQLSERVER\FileTable\. BUT you can’t do anything with it yet! You need an actual FileTable, to put your files into.

FileTable in Action

Now let’s create a FileTable:

CREATE TABLE JensAwesomeFiles AS FileTable
WITH
( FileTable_Directory = 'JenAwesomeFiles', 
 FileTable_Collate_Filename = database_default
);

Very simple, very easy. You don’t list any column names or attributes, because a FileTable has a fixed structure that SQL Server already knows. You can now SELECT * FROM JensAwesomeFiles (though it is currently empty), and access it at \\{servername}\MSSQLSERVER\FileTable\JenAwesomeFiles\.

Now, drop a file in that directory. Go ahead, any old file. Create a new text file and save it there, or copy a JPG in. Then when you select from JensAwesomeFiles in SSMS, it shows up:

Go ahead, copy a bunch of files in and select. It’s fun! Know what’s more fun? Changing file names, extensions, or attributes with T-SQL commands. For example:

UPDATE JensAwesomeFiles SET is_readonly = 1 WHERE file_type = 'docx'; -- Set the readonly attribute!
UPDATE JensAwesomeFiles SET NAME = 'NEW NAME_' + name WHERE creation_time > GETDATE()-1; -- Change file names, based on creation date!
DELETE JensAwesomeFiles WHERE is_readonly = 0; -- Delete files RIGHT OUTTA THERE!

After each change, go take a look at the virtual directory, just to Ooh and Ahh over the changes.

What’s it For?

Of course, like anything in computers, this feature is for whatever you can think of. I’ve used it to streamline and improve document handling and searching in companies with legacy applications – where we couldn’t alter the actual file-in-file-out process, but we could point the app to a new directory….and so all our files were dropped straight into SQL Server. Aww, yeah…

Oh, and you PowerShell freaks out there: Tell me you couldn’t have TONS of fun by combining POSH with FileTable. Go on, I dare ya.

Drop me a line, kids, with your “oh WOW”s, your questions, your corrections, and how you’ve used (or will use) FileTable.

Happy days,

Jen McCown

http://www.MidnightDBA.com/Jen