啟用 FileTable 的必要條件Enable the Prerequisites for FileTable

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

描述如何啟用建立和使用 FileTable 的必要元件。Describes how to enable the prerequisites for creating and using FileTables.

啟用 FileTable 的必要條件Enabling the Prerequisites for FileTable

若要啟用建立和使用 FileTable 的必要條件,請啟用下列項目:To enable the prerequisites for creating and using FileTables, enable the following items:

在執行個體層級啟用 FILESTREAMEnabling FILESTREAM at the Instance Level

FileTable 會擴充 SQL ServerSQL Server之 FILESTREAM 功能的能力。FileTables extend the capabilities of the FILESTREAM feature of SQL ServerSQL Server. 因此,您必須先在 Windows 層級和 SQL ServerSQL Server 執行個體上啟用 FILESTREAM 的檔案 I/O 存取,然後才能建立和使用 FileTable。Therefore you have to enable FILESTREAM for file I/O access at the Windows level and on the instance of SQL ServerSQL Server before you can create and use FileTables.

如何:在執行個體層級啟用 FILESTREAMHow To: Enable FILESTREAM at the Instance Level

如需如何啟用 FILESTREAM 的相關資訊,請參閱 啟用及設定 FILESTREAMFor information about how to enable FILESTREAM, see Enable and Configure FILESTREAM.

當您呼叫 sp_configure 以在執行個體層級啟用 FILESTREAM 時,必須將 filestream_access_level 選項設定為 2。When you call sp_configure to enable FILESTREAM at the instance level, you have to set the filestream_access_level option to 2. 如需詳細資訊,請參閱 Filestream 存取層級伺服器組態選項For more information, see filestream access level Server Configuration Option.

如何:允許 FILESTREAM 通過防火牆How To: Allow FILESTREAM through the Firewall

如需有關如何允許 FILESTREAM 通過防火牆的詳細資訊,請參閱< Configure a Firewall for FILESTREAM Access>。For information about how to allow FILESTREAM through the firewall, see Configure a Firewall for FILESTREAM Access.

在資料庫層級提供 FILESTREAM 檔案群組Providing a FILESTREAM Filegroup at the Database Level

資料庫必須具有 FILESTREAM 檔案群組,然後您才能在該資料庫中建立 FileTable。Before you can create FileTables in a database, the database must have a FILESTREAM filegroup. 如需此必要條件的詳細資訊,請參閱 建立啟用 FILESTREAM 的資料庫For more information about this prerequisite, see Create a FILESTREAM-Enabled Database.

在資料庫層級啟用非交易式存取Enabling Non-Transactional Access at the Database Level

FileTable 可讓 Windows 應用程式取得 FILESTREAM 資料的 Windows 檔案控制代碼,而不需要使用交易。FileTables let Windows applications obtain a Windows file handle to FILESTREAM data without requiring a transaction. 若要允許對儲存在 SQL ServerSQL Server中的檔案進行這種非交易式存取,您必須針對將包含 FileTable 的每個資料庫,指定在資料庫層級啟用非交易式存取的所需層級。To allow this non-transactional access to files stored in SQL ServerSQL 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

查詢 sys.database_filestream_options (Transact-SQL) 目錄檢視,並檢查 non_transacted_accessnon_transacted_access_desc 資料行。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

非交易式存取的可用層級是 FULL、READ_ONLY 和 OFF。The available levels of non-transactional access are FULL, READ_ONLY, and OFF.

使用 Transact-SQL 指定非交易式存取的層級Specify the level of non-transactional access by using Transact-SQL

  • 建立新資料庫時,請使用 NON_TRANSACTED_ACCESS FILESTREAM 選項,呼叫 CREATE DATABASE (SQL Server Transact-SQL) 陳述式。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' )  
    
  • 改變現有資料庫時,請使用 NON_TRANSACTED_ACCESS FILESTREAM 選項,呼叫 ALTER DATABASE (Transact-SQL) 陳述式。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' )  
    

使用 SQL Server Management Studio 指定非交易式存取的層級Specify the level of non-transactional access by using SQL Server Management Studio
在 [資料庫屬性] 對話方塊中,您可以透過 [選項] 頁面的 [FILESTREAM 非交易式存取] 欄位,來指定非交易式存取的層級。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).

在資料庫層級指定 FileTable 的目錄Specifying a Directory for FileTables at the Database Level

當您在資料庫層級啟用檔案的非交易式存取時,可以選擇性地使用 DIRECTORY_NAME 選項,一併提供目錄名稱。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. 如果您在啟用非交易式存取時沒有提供目錄名稱,則之後必須先提供此名稱,然後才能在資料庫中建立 FileTable。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.

在 FileTable 資料夾階層中,這個資料庫層級目錄會成為在執行個體層級中針對 FILESTREAM 指定之共用名稱的子系,以及在資料庫中建立之 FileTable 的父系。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. 如需詳細資訊,請參閱 Work with Directories and Paths in FileTablesFor more information, see Work with Directories and Paths in FileTables.

如何:在資料庫層級指定 FileTable 的目錄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.

使用 Transact-SQL 指定 FileTable 的目錄Specify a directory for FileTables by using Transact-SQL

  • 建立新資料庫時,請使用 DIRECTORY_NAME FILESTREAM 選項,呼叫 CREATE DATABASE (SQL Server Transact-SQL) 陳述式。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  
    
  • 改變現有資料庫時,請使用 DIRECTORY_NAME FILESTREAM 選項,呼叫 ALTER DATABASE (Transact-SQL) 陳述式。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  
    
  • 附加資料庫時,請使用 FOR ATTACH 選項和 DIRECTORY_NAME FILESTREAM 選項,呼叫 CREATE DATABASE (SQL Server Transact-SQL) 陳述式。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  
    
  • 還原資料庫時,請使用 DIRECTORY_NAME FILESTREAM 選項,呼叫 RESTORE (Transact-SQL) 陳述式。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  
    

使用 SQL Server Management Studio 指定 FileTable 的目錄Specify a directory for FileTables by using SQL Server Management Studio
在 [資料庫屬性] 對話方塊中,您可以透過 [選項] 頁面的 [FILESTREAM 目錄名稱] 欄位,來指定目錄名稱。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

若要檢視執行個體的現有目錄名稱清單,請查詢 sys.database_filestream_options (Transact-SQL) 目錄檢視,並檢查 filestream_database_directory_name 資料行。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

  • 當您呼叫 CREATE DATABASEALTER DATABASE 時, DIRECTORY_NAME是選擇性設定。Setting the DIRECTORY_NAME is optional when you call CREATE DATABASE or ALTER DATABASE. 如果您沒有指定 DIRECTORY_NAME的值,則目錄名稱會維持 Null。If you do not specify a value for DIRECTORY_NAME, then the directory name remains null. 不過,如果您未在資料庫層級中指定 DIRECTORY_NAME 的值,就無法在資料庫中建立 FileTable。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.

  • 當資料庫包含 FileTable 時,您無法將 DIRECTORY_NAME 設定回 Null 值。When the database contains FileTables, you cannot set the DIRECTORY_NAME back to a null value.

  • 當您附加或還原資料庫時,如果新的資料庫具有已經存在目標執行個體中的 DIRECTORY_NAME 值,此作業就會失敗。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. 因此,當您呼叫 CREATE DATABASE FOR ATTACHRESTORE DATABASE 時,請針對 DIRECTORY_NAME指定唯一的值。Specify a unique value for DIRECTORY_NAME when you call CREATE DATABASE FOR ATTACH or RESTORE DATABASE.

  • 當您將現有的資料庫升級為 SQL ServerSQL Server時, DIRECTORY_NAME 的值為 Null。When you upgrade an existing database to SQL ServerSQL Server, 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.

  • 當您卸除為 FileTable 啟用的資料庫時,會一併移除資料庫層級目錄和其下所有 FileTable 的全部目錄結構。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.