title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |
---|---|---|---|---|---|---|---|---|---|---|
Enable the prerequisites for FileTable |
To use FileTables, first turn on FILESTREAM, specify a directory, and set certain options and access levels. Learn how to meet all prerequisites. |
MikeRayMSFT |
mikeray |
randolphwest |
10/02/2023 |
sql |
filestream |
conceptual |
|
[!INCLUDE SQL Server]
Describes how to enable the prerequisites for creating and using FileTables.
To enable the prerequisites for creating and using FileTables, enable the following items:
-
At the instance level:
-
At the database level:
FileTables extend the capabilities of the FILESTREAM feature of [!INCLUDE ssNoVersion]. You have to enable FILESTREAM for file I/O access at the Windows level, and on the instance of [!INCLUDE ssNoVersion], before you can create and use FileTables.
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).
For information about how to allow FILESTREAM through the firewall, see Configure a Firewall for FILESTREAM Access.
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.
FileTables let Windows applications obtain a Windows file handle to FILESTREAM data without requiring a transaction. To allow this nontransactional access to files stored in [!INCLUDE ssNoVersion], you have to specify the desired level of nontransactional access at the database level for each database that will contain FileTables.
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
The available levels of nontransactional access are FULL, READ_ONLY, and OFF.
When you create a new database, call the CREATE DATABASE (SQL Server Transact-SQL) statement with the NON_TRANSACTED_ACCESS
FILESTREAM option.
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' );
You can specify the level of nontransactional 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).
When you enable nontransactional access to files at the database level, you can optionally provide a directory name at the same time with the DIRECTORY_NAME
option. If you don't provide a directory name when you enable nontransactional 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.
The name that you specify must be unique across the instance for database-level directories.
When you create a new database, call the CREATE DATABASE (SQL Server Transact-SQL) statement with the DIRECTORY_NAME
FILESTREAM option.
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
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).
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
-
Setting the
DIRECTORY_NAME
is optional when you callCREATE DATABASE
orALTER DATABASE
. If you don't specify a value forDIRECTORY_NAME
, then the directory name remains null. However you can't create FileTables in the database until you specify a value forDIRECTORY_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 can't 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 forDIRECTORY_NAME
when you callCREATE DATABASE FOR ATTACH
orRESTORE DATABASE
. -
When you upgrade an existing database, the value of
DIRECTORY_NAME
is null. -
When you enable or disable nontransactional access at the database level, the operation doesn't 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 structures of all the FileTables under it are removed.