Creating Your First FileTable in SQL Server Denali CTP3

  • Article

With SQL Server Denali CTP3 now available, I'm starting to explore some of the new Database Engine capabilities.  One that caught my attention is the FileTable.

The FileTable builds upon FileStream storage which allows BLOB data to be stored as individual files separate from a database's data files.  In SQL Server 2008 (and 2008 R2), interactions with the FileStream files took place either through T-SQL or through code which engaged with OpenSqlFileStream API.  This was an interesting direction for applications with large volumes of BLOB data which needed to be accessed quickly, but it wasn't the most accessible implementation. (For more info on FileStream storage including its benefits and trade-offs, check out this excellent white paper which includes best practices for FileStream configuraiton which you will want to employ.)

The FileTable addresses this by making the Windows share associated with the externally stored files directly engageable and by allowing the share to employ a familiar, hiearchical folder structure. (The folder structure is represented by the underutilized hierarchyid data type first introdued in SQL Server 2008.  If you aren't familiar with it, check it out.) This, along with the semantic search capabilities of Denali, will be what make FileStream take off for database developers.

However, there are quite a number of steps involved with configuring a SQL Server instance and database before you can create your first FileTable, and if you are not familiar with the setup of FileStream storage, these steps can be challenging to perform. Having stumbled a couple times myself before finally getting it right, I figured document the steps involved with enabling the environment and creating a simple FileTable in SQL Server Denali CTP3.

1. Enable the FileStream driver on the SQL Server instance.

a. Open SQL Server Configuration Manager.
b. Navigate to the SQL Server Services folder in the left-hand pane.
c. In the right-hand pane, right-click the Database Engine instance on which you want to enable FileStream support and select Properties.
d. In the Properties dialog, navigate to the FILESTREAM tab.
e. Check the Enable FILESTREAM for Transact-SQL access and Enable FILESTREAM for file I/O access options.

NOTE You can also change the name of the Windows share, but for the purpose of these instructions, I'll leave it as its default, i.e. the name of the instance.

f. Check the Allow remote client access option for the purposes of this post. For production deployments, this may or may not be the right option for you and requires a consideration of the accessibility and security trade-offs.
i. Click the Apply button and then close the Properties dialog and SQL Server Configuration Manager.

At this point, you can see that the server now exposes a Windows share using the name of the Database Engine instance. You can see this by opening Windows Explorer and navigating to \\127.0.0.1.  If you enabled remote access on the FileStream share (in Step 1f), you can access the share but it will be empty at this point. (In SQL Server 2008 and 2008 R2, attempting to access the share in this manner would result in an error regardless of the remote access setting.)

2. Enable SQL Server to use the FileStream driver.

a. Open SQL Server Management Studio and cancel the Connect to Server dialog.
b. Click the New Query button and connect to the SQL Server Database Engine instance you configured in Step 1.
c. In the query window, execute the following statements:

exec sp_configure filestream_access_level, 2;
reconfigure;

NOTE The filestream_access_level configuration setting set in Step 2d takes one of three values: 0, 1, or 2.  The default value, 0, disables FileStream support.  The value 1 enables T-SQL only access to FileStream files, and the value 2 enables both T-SQL and Win32 (direct) access to the files.

With FileStream now configured at the instance-level, you can now setup a new (example) database employing FileStream storage.

3. Create a new database employing a FileStream file group.

a. In the query window used in Step 2d (or a new one), execute the following statement to create a new database:

create database FileTableDemo;

b. In that same query window, execute the following statement to add a FileStream file group to the database:

alter database FileTableDemo
add filegroup FileStreamGroup1 contains filestream;

c. In that same query window, execute the following statement to define the storage associated with the FileStream file group added in the previous step:

alter database FileTableDemo
add file (
    name = FileStream1,
filename = 'D:\Temp\FileStreamFileGroup1'
) to filegroup FileStreamGroup1;

NOTE Steps 3a, b, and c could be combined into a step using a single CREATE DATABASE statement.  It's been decomposed into smaller steps here for greater transparency.

This last step deserves a bit more discussion. The path to the FileStream storage, as identified in the filename setting, must be a valid path on the SQL Server system up to the last folder in the path.  In other words, D:\Temp must exist for the previous statement to work.  However, the last folder, FileStreamFileGroup1 in the case of the previous statement, must not exist.  That folder will be created by SQL Server upon statement execution.

With the database in place, you now need to configure how the database will present and control interactions with the FileStream data.

4. Configure how the database handles access to the FileStream data.

a. In the query windows from previous steps, define the name of the Share subfolder through which FileStream files associated with this database will be shared:

alter database FileTableDemo set filestream ( directory_name = 'FileTableDemo' );

b. In the query window, execute the following statement to enable direct read and write access to the FileStream data:

alter database FileTableDemo set filestream( non_transacted_access = full ); 

NOTE Options for non_transacted_access are off, read_only, and full.  Off disables non-transactional access so that data in the FileTableDemo subfolder will not be accessible.  Read_only provides read-only access to files (outside a transaction as handled through the OpenSqlFileStream API). Full allows files to be read and written through the subfolder.

If you accessed the share earlier, you might wish to review it again now.  The instance's share at \\127.0.0.1 now has a FileTableDemo subfolder per Step 4a.

With all this in place, you can now define a filetable in the database.

5. Create a FileTable within the FileTableDemo database by executing the following statement in the query window from the previous step:

use FileTableDemo;
go
create table MyFileTable as filetable;

NOTE The CREATE TABLE statement used in the last step is very simplistic compared to what is possible with the structure of a FileTable but it provides a good starting point for working with this new feature.  For more info on the broader syntax on the CREATE TABLE ... AS FILETABLE statement, please see this document.

With a FileTable now defined within the FileTableDemo database, you can now reconnect to \\127.0.0.1 and access the instance share.  The FileTableDemo subfolder defined in Step 4a now has its own subfolder, MyFileTable,  named for the FileTable created in Step 5.  As the database has been configured to allow full non-transactional access to this folder, you can drag and drop files within the FileTable folder and even create subfolders under it.  All of these will be reflected in the MyFileTable table when you interact with it using T-SQL.  As you use T-SQL to insert, update, and delete data in the FileTable table, those changes will be reflected in the associated folder on the Windows share.  Pretty cool stuff!