Home » Beginner »Intermediate »sql server »sqlserverpedia-syndication »SSC »Tech and Learning » Currently Reading:

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:

  1. Open SQL Server Configuration Manager.
  2. Bring up Properties for your instance.
  3. Select Enable Filestream for T-SQL Access AND for File I/O Access
  4. And run this statement in SSMS:
    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE
  5. 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

Currently there are "6 comments" on this Article:

  1. Andreas Bergstedt says:

    Jen,

    Thank you for this article, I can see many happy days using this sort of thing to manage flat files.

    Tell me what is the file size limitation and what about compression?

  2. Devesh says:

    Nice Article. Thanks for sharing

  3. Have you seen any performance numbers around lets say storing billions of files (yes that is billions with a B)?

  4. [...] (By the way, if you’d like an extension on this topic, take a look at my blog “FileTable: SQL Server 2012′s little gasp-maker“.) [...]

  5. Camila Reis says:

    Undoudtedly, the best post on FileTables.
    Thank you!

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

September 13: SQL Saturday Kansas City
November 3-7: PASS Summit, Seattle, WADecember 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/