Enable the Prerequisites for FileTable

Article
06/14/2017

Describes how to enable the prerequisites for creating and using FileTables.

Enabling the Prerequisites for FileTable

To enable the prerequisites for creating and using FileTables, enable the following items:

At the instance level:
- Enabling FILESTREAM at the Instance Level
At the database level:

Enabling FILESTREAM at the Instance Level

FileTables extend the capabilities of the FILESTREAM feature of SQL Server. Therefore you have to enable FILESTREAM for file I/O access at the Windows level and on the instance of SQL Server before you can create and use FileTables.

How To: Enable FILESTREAM at the Instance Level

For information about how to enable FILESTREAM, see Enable and Configure FILESTREAM.

When you call sp_configure to enable FILESTREAM at the instance level, you have to set the filestream_access_level option to 2. For more information, see filestream access level Server Configuration Option.

How To: Allow FILESTREAM through the Firewall

For information about how to allow FILESTREAM through the firewall, see Configure a Firewall for FILESTREAM Access.

Providing a FILESTREAM Filegroup at the Database Level

Before you can create FileTables in a database, the database must have a FILESTREAM filegroup. For more information about this prerequisite, see Create a FILESTREAM-Enabled Database.

Enabling Non-Transactional Access at the Database Level

FileTables let Windows applications obtain a Windows file handle to FILESTREAM data without requiring a transaction. To allow this non-transactional access to files stored in SQL Server, you have to specify the desired level of non-transactional access at the database level for each database that will contain FileTables.

How To: Check Whether Non-Transactional Access Is Enabled on Databases

Query the catalog view sys.database_filestream_options (Transact-SQL) and check the non_transacted_access and non_transacted_access_desc columns.

SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc  
    FROM sys.database_filestream_options;  
GO

How To: Enable Non-Transactional Access at the Database Level

The available levels of non-transactional access are FULL, READ_ONLY, and OFF.

Specify the level of non-transactional access by using Transact-SQL

When you create a new database, call the CREATE DATABASE (SQL Server Transact-SQL) statement with the NON_TRANSACTED_ACCESS FILESTREAM option.

```sql  
CREATE DATABASE database_name  
    WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name' )  
```

When you alter an existing database, call the ALTER DATABASE (Transact-SQL) statement with the NON_TRANSACTED_ACCESS FILESTREAM option.
```
ALTER DATABASE database_name  
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name' )  
```

Specify the level of non-transactional access by using SQL Server Management Studio
You can specify the level of non-transactional access in the FILESTREAM Non-transacted Access field of the Options page of the Database Properties dialog box. For more information about this dialog box, see Database Properties (Options Page).

Specifying a Directory for FileTables at the Database Level

When you enable non-transactional access to files at the database level, you can optionally provide a directory name at the same time by using the DIRECTORY_NAME option. If you do not provide a directory name when you enable non-transactional access, then you have to provide it later before you can create FileTables in the database.

In the FileTable folder hierarchy, this database-level directory becomes the child of the share name specified for FILESTREAM at the instance level, and the parent of the FileTables created in the database. For more information, see Work with Directories and Paths in FileTables.

How To: Specify a Directory for FileTables at the Database Level

The name that you specify must be unique across the instance for database-level directories.

Specify a directory for FileTables by using Transact-SQL

When you create a new database, call the CREATE DATABASE (SQL Server Transact-SQL) statement with the DIRECTORY_NAME FILESTREAM option.

```sql  
CREATE DATABASE database_name  
    WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name' );  
GO  
```

When you alter an existing database, call the ALTER DATABASE (Transact-SQL) statement with the DIRECTORY_NAME FILESTREAM option. When you use these options to change the directory name, the database must be exclusively locked, with no open file handles.
```
ALTER DATABASE database_name  
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name' );  
GO  
```
When you attach a database, call the CREATE DATABASE (SQL Server Transact-SQL) statement with the FOR ATTACH option and with the DIRECTORY_NAME FILESTREAM option.
```
CREATE DATABASE database_name  
    FOR ATTACH WITH FILESTREAM ( DIRECTORY_NAME = N'directory_name' );  
GO  
```
When you restore a database, call the RESTORE (Transact-SQL) statement with the DIRECTORY_NAME FILESTREAM option.
```
RESTORE DATABASE database_name  
    WITH FILESTREAM ( DIRECTORY_NAME = N'directory_name' );  
GO  
```

Specify a directory for FileTables by using SQL Server Management Studio
You can specify a directory name in the FILESTREAM Directory Name field of the Options page of the Database Properties dialog box. For more information about this dialog box, see Database Properties (Options Page).

How to: View Existing Directory Names for the Instance

To view the list of existing directory names for the instance, query the catalog view sys.database_filestream_options (Transact-SQL) and check the filestream_database_directory_name column.

SELECT DB_NAME ( database_id ), directory_name  
    FROM sys.database_filestream_options;  
GO

Requirements and Restrictions for the Database-Level Directory

Setting the DIRECTORY_NAME is optional when you call CREATE DATABASE or ALTER DATABASE. If you do not specify a value for DIRECTORY_NAME, then the directory name remains null. However you cannot create FileTables in the database until you specify a value for DIRECTORY_NAME at the database level.
The directory name that you provide must comply with the requirements of the file system for a valid directory name.
When the database contains FileTables, you cannot set the DIRECTORY_NAME back to a null value.
When you attach or restore a database, the operation fails if the new database has a value for DIRECTORY_NAME that already exists in the target instance. Specify a unique value for DIRECTORY_NAME when you call CREATE DATABASE FOR ATTACH or RESTORE DATABASE.
When you upgrade an existing database to SQL Server 2014, the value of DIRECTORY_NAME is null.
When you enable or disable non-transactional access at the database level, the operation does not check whether the directory name has been specified or whether it is unique.
When you drop a database that was enabled for FileTables, the database-level directory and all the directory stuctures of all the FileTables under it are removed.

Share via