Implementing FILESTREAM and FileTables

The estimated reading time for this post is 4 minutes

In last post, we talked about solutions to incorporate data files into databases and the advantages and disadvantages of each approach. Today, we are going to implement and introduce more details about two of those solutions: FILESTREAM and FileTables. Implementing FILESTREAM and FileTables integrate data files on the file system and stored relational data inside the database, while maintaining performance.

Implementing FILESTREAM

Before using FILESTREAM, it needs to be enabled for SQL Server instance. You can enable it whether during SQL Server installation, or after installation. FILESTREAM configuration after installation consists of two steps:

  1. In SQL Server Configuration Manager, in FILESTREAM tab, enable FILESREAM for Transact-SQL access.
  2. Configure the level of access for FILESTREAM by using sp_configure. Valid values for filestream_access_level option can be:
  3. 0 disables filestream.
  4. 1 enables filestream for T-SQL access only.
  5. 2 enables for both Win32 streaming and T-SQL access.

After enabling FILESTREAM, restart the SQL server service.

The following example enables FILESTREAM access level in SQL Server instance:

EXEC SP_CONFIGURE 'filestream_access_level', 2;
RECONFIGURE;
GO

Note: to enable FILESTREAM for both Win32 streaming and T-SQL access, you need to enable FILESTREAM for file I/O access in SQL Server Configuration Manager, in FILESTREAM tab.

Creating a FILESTREAM-Enabled Database

A dedicated filegroup is required for FILESTREAM. The following example creates a database with dedicated FILESTREAM filegroup:

CREATE DATABASE Archive
ON
PRIMARY (NAME = Archive_data,    
       FILENAME = 'E:\Data\archive_Data.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS
       FILESTREAM( NAME = Archive_fs, FILENAME = 'G:\filestream\Archive_fs')
LOG ON  ( NAME = Archive_log, FILENAME = 'F:\Log\archive_log.ldf');
GO

Creating a Table with FILESTREAM Column

FILESTREAM column uses a varbinary(max) datatype with the FILESTREAM attribute. You can specify the FILESTREAM column with either a CREATE TABLE statement or an ALTER TABLE statement. The following example creates a table with a FILESTREAM column:

CREATE TABLE Archive.dbo.Records
( 
       [id] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL UNIQUE,  
       [record_id] INT UNIQUE, 
       [documents] VARBINARY(MAX) FILESTREAM NULL
);
GO

Considerations

To implement FILESTREAM on a database consider the following tips:

  • FILESTREAM requires a uniqueidentifier rowguid column.
  • FILESTREAM attribute cannot be applied on an existing column.
  • Place FILESTREAM filegroup in a separate volume from OS files, paging files, database data and log files and tempdb.
  • Database backups include FILESTREAM data, so there is no requirement for separate maintenance of FILESTREAM data files.
  • Usually FILESTREAM is recommended for BLOBS that are larger than 1 MB in size.
  • FILESTREAM is not compatible with database mirroring.
  • Enabling TDE for a database does not encrypt FILESTREAM columns.

Note: FILESTREAM attribute cannot be applied on an existing column; To convert an existing varbinary(max) column to FILESTREAM, first create a new FILESTREAM enabled column, and then copy the data from the existing column into the new column.

Implementing FileTables

To enable FileTables on the database, you need to configure a directory which contains FileTable data for the database, same as what you have done for FILESTREAM configuration. Each FileTable in the database has its own subdirectory in the FileTable directory for the database. FileTable directory for a database can be specified by using the DIRECTORY_NAME options with either the CREATE DATABASE or ALTER DATABASE statement. The following example configures the Archive database for FileTables.

ALTER DATABASE [Archive]
SET FILESTREAM
(
       NON_TRANSACTED_ACCESS = FULL,
       DIRECTORY_NAME = N’Archive_Files’
)
GO

Create a FileTable

To create a FileTable, you should use the AS FILETABLE option in the CREATE TABLE statement followed by a specific name for FileTable directory which keep the data of the FileTable. The following example creates a FileTable named Image.

USE Archive;
GO
CREATE TABLE Images AS FILETABLE
WITH
(
       FileTable_Directory = ‘Images’,
       FileTable_Collate_Filename = database_default
);
GO

When NON_TRANSACTED_ACCESS option sets to FULL, you can add the files to the FileTable directly by saving or dragging files into the FileTable shared folder. To see all File tables in the database, open SSMS in Object Explorer >> Databases >> Database Name >> Tables >> FileTables. Once you expand it, you will see all your Filetables here; to navigate toward FileTable Shared folder, right click on table name and click Explore FileTable Directory.

image-3940

The following picture demonstrates a sample UNC (Universal Naming Convention) path of the Shared folder.

image-3941

Considerations

To implement FILETables in a database consider the following tips:

  • A database with FileTable in an AG changes failover behavior. After failover, FileTable data is only accessible in primary replica, while readable secondary replicas cannot access it.
  • Table Partitioning cannot be implemented on a FileTable.
  • Database Replication cannot replicate FileTables.
  • FileTable supports after AFTER triggers for DML operations, but it does not support INSTEAD OF triggers; While it fully supports DDL triggers.
  • You can create views on FileTables, but not indexed views.
  • Neither Transactional Rollback nor point-in-time recovery do not include FileTables.

Some Useful Functions for FILESTREAM and FileTable

  • FileTableRootPath returns the root-level UNC of FileTables for the current database or specific FileTable.
  • GetFileNameSpacePath returns the UNC path to a file or directory in the FileTable directory hierarchy.
  • GetPathLocator returns the hierarchyid value for a file or directory in the FileTable directory.
  • PathName returns the path of a BLOB in a FILESTREAM column.

 

Hamide B. Abshuri

Hamide B. Abshuri is SQL Server Database Developer and Administrator with extensive of working experience. She started work with SQL Server since year 2015. She is currently Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer.

More Posts - Website


Leave a Comment

avatar
1000
  Subscribe  
Notify of