Beyond Relational - Installing SQL Server FileTable

A long time ago in a land far far away, somebody had the bright idea of using a database as part of a file system and the Windows Future System (WinFS) was born – but this is not that story.  However the idea and some of the team moved to the SQL Server engineering team and FileStream was the result in SQL Server 2008.   I have several posts on FileStream (here) and in essence it allows a win32 call to create read update and delete files form within the context of a SQL Server transaction.  This preserves the referential integrity between the data about the objects in the database and the objects themselves and backing up and restoring the database also picks up the objects as well.  The most interesting use case of FileStream is SQLRace developed by McLaren Electronics to monitor the performance of F1 cars not just for them but for the FIA and other manufacturers.

In the next release of SQL Server (Project Denali) there is a new feature built on top of this called FileTable.  This provides the ability to read write and update the FileStream objects directly for example from explorer via a UNC share.  The way this works is that you declare a share for the database instance and when you create or modify a database for use with FileTable you associate a directory name with it.  Underneath that directory there is then a directory for each FileTable.  FileTables are like ordinary database tables except that their schema is predefined and cannot be changed.

I have a short video here on exactly how to do this ..

In subsequent posts I’ll show you what you can do with FileTable data both from inside and outside of SQL Server.

Notes:

If want to try this yourself all you’ll need SQL Server Denali ctp 3

I created the database with this script..

 CREATE DATABASE [Denali_FileTable]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Denali_FileTable', 

    FILENAME = N'C:\Program Files\Microsoft SQL Server\

    MSSQL11.MSSQLSERVER\MSSQL\DATA\Denali_FileTable.mdf' , 

    SIZE = 102400KB , FILEGROWTH = 1024KB ),
FILEGROUP FilestreamFG CONTAINS 

 FILESTREAM 

    (name= FilestreamFG, FILENAME= 'C:\FileStremFG')
LOG ON 
   ( NAME = N'Denali_FileTable_log', 

     FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.

     MSSQLSERVER\MSSQL\DATA\Denali_FileTable_log.ldf' , 

     SIZE = 1024KB , FILEGROWTH = 10%)
WITH FILESTREAM 

    ( NON_TRANSACTED_ACCESS = FULL, 

      DIRECTORY_NAME = N'FileTable')

at the time of writing books online has doesn’t mention the need to create a FileStream file group (in my case FilestreamFG) – and this will get changed as we get nearer to the release of Denali

The script to create my FileTable looks like this..

 USE Denali_FileTable
CREATE TABLE MyDecks AS FileTable
    WITH ( 
          FileTable_Directory = 'My Decks',
          FileTable_Collate_Filename = database_default
         );