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.
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:
- In SQL Server Configuration Manager, in FILESTREAM tab, enable FILESREAM for Transact-SQL access.
- Configure the level of access for FILESTREAM by using sp_configure. Valid values for filestream_access_level option can be:
- 0 disables filestream.
- 1 enables filestream for T-SQL access only.
- 2 enables for both Win32 streaming and T-SQL access.
[info] After enabling FILESTREAM, restart the SQL server service. [/info]The following example enables FILESTREAM access level in SQL Server instance:
EXEC SP_CONFIGURE 'filestream_access_level', 2; RECONFIGURE; GO
[info] 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. [/info]
Creating a FILESTREAM-Enabled Database
A dedicated filegroup is required for FILESTREAM. The following example creates a database with dedicated FILESTREAM filegroup:
CREATE DATABASE ArchiveONPRIMARY (NAME = Archive_data, FILENAME = 'E:Dataarchive_Data.mdf'),FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Archive_fs, FILENAME = 'G:filestreamArchive_fs')LOG ON ( NAME = Archive_log, FILENAME = 'F:Logarchive_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
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.
[info]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. [/info]
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;GOCREATE TABLE Images AS FILETABLEWITH( FileTable_Directory = ‘Images’, FileTable_Collate_Filename = database_default);GO
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.