啟用及設定 FILESTREAMEnable and configure FILESTREAM

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

在您開始使用 FILESTREAM 之前,必須先在 SQL Server Database EngineSQL Server Database Engine執行個體上啟用 FILESTREAM。Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database EngineSQL Server Database Engine. 此主題描述如何使用 SQL Server 組態管理員來啟用 FILESTREAM。This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.

啟用 FILESTREAMEnabling FILESTREAM

若要啟用和變更 FILESTREAM 設定To enable and change FILESTREAM settings

  1. 指向 [開始] 功能表上的 [所有程式] ,然後依序指向 [SQL Server 2017SQL Server 2017] 和 [組態工具] ,再按一下 [SQL Server 組態管理員] 。On the Start menu, point to All Programs, point to SQL Server 2017SQL Server 2017, point to Configuration Tools, and then click SQL Server Configuration Manager.

  2. 在服務的清單中,以滑鼠右鍵按一下 [SQL Server 服務] ,然後按一下 [開啟] 。In the list of services, right-click SQL Server Services, and then click Open.

  3. 在 [SQL Server 組態管理員] 嵌入式管理單元中,找出您想要啟用 FILESTREAM 的 SQL ServerSQL Server 執行個體。In the SQL Server Configuration Manager snap-in, locate the instance of SQL ServerSQL Server on which you want to enable FILESTREAM.

  4. 以滑鼠右鍵按一下該執行個體,然後按一下 [屬性] 。Right-click the instance, and then click Properties.

  5. 在 [SQL Server 屬性] 對話方塊中,按一下 [FILESTREAM] 索引標籤。In the SQL Server Properties dialog box, click the FILESTREAM tab.

  6. 選取 [啟用 FILESTREAM 的 Transact-SQL 存取] 核取方塊。Select the Enable FILESTREAM for Transact-SQL access check box.

  7. 如果您想要從 Windows 讀取和寫入 FILESTREAM 資料,請按一下 [啟用 FILESTREAM 的檔案 I/O 資料流存取] 。If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. 在 [Windows 共用名稱] 方塊中,輸入 Windows 共用的名稱。Enter the name of the Windows share in the Windows Share Name box.

  8. 如果遠端用戶端必須存取儲存在這個共用上的 FILESTREAM 資料,請選取 [允許遠端用戶端具有 FILESTREAM 資料的資料流存取權] 。If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

  9. 按一下 [套用]Click Apply.

  10. SQL Server Management StudioSQL Server Management Studio中,按一下 [新增查詢] 顯示 [查詢編輯器]。In SQL Server Management StudioSQL Server Management Studio, click New Query to display the Query Editor.

  11. 在 [查詢編輯器] 中,輸入下列 Transact-SQLTransact-SQL 程式碼:In Query Editor, enter the following Transact-SQLTransact-SQL code:

    EXEC sp_configure filestream_access_level, 2  
    RECONFIGURE  
    
  12. 按一下 [執行]Click Execute.

  13. 重新啟動 SQL ServerSQL Server 服務。Restart the SQL ServerSQL Server service.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

最佳做法Best practices

實體組態和維護Physical configuration and maintenance

當您設定 FILESTREAM 儲存體磁碟區時,請考慮下列指導方針:When you set up FILESTREAM storage volumes, consider the following guidelines:

  • 在 FILESTREAM 電腦系統上關閉簡短檔案名稱。Turn off short file names on FILESTREAM computer systems. 簡短檔案名稱會花費更長的時間來建立。Short file names take significantly longer to create. 若要停用簡短檔案名稱,請使用 Windows fsutil 公用程式。To disable short file names, use the Windows fsutil utility.

  • 定期重組 FILESTREAM 電腦系統。Regularly defragment FILESTREAM computer systems.

  • 使用 64-KB NTFS 叢集。Use 64-KB NTFS clusters. 壓縮的磁碟區必須設定為 4-KB NTFS 叢集。Compressed volumes must be set to 4-KB NTFS clusters.

  • 針對 FILESTREAM 磁碟區停用索引並且設定 disablelastaccessDisable indexing on FILESTREAM volumes and set disablelastaccess. 若要設定 disablelastaccess,請使用 Windows 的 fsutil 公用程式。To set disablelastaccess, use the Windows fsutil utility.

  • 如不需要,請停用 FILESTREAM 磁碟區的防毒掃描。Disable antivirus scanning of FILESTREAM volumes when it is not necessary. 如果防毒掃描是必要的功能,請避免設定自動刪除違規檔案的原則。If antivirus scanning is necessary, avoid setting policies that will automatically delete offending files.

  • 針對容錯和應用程式所需的效能設定並微調 RAID 層級。Set up and tune the RAID level for fault tolerance and the performance that is required by an application.

RAID 層級RAID level 寫入效能Write performance 讀取效能Read performance 容錯Fault tolerance RemarksRemarks
RAID 5RAID 5 一般Normal 一般Normal 非常好Excellent 效能高於單一磁碟或 JBOD,而低於具有條狀配置的 RAID 0 或 RAID 5。Performance is better than one disk or JBOD; and less than RAID 0 or RAID 5 with striping.
RAID 0RAID 0 非常好Excellent 非常好Excellent NoneNone
RAID 5 + 條狀配置RAID 5 + striping 非常好Excellent 非常好Excellent 非常好Excellent 成本最高的選項。Most expensive option.
         

實體資料庫設計Physical database design

當您設計 FILESTREAM 資料庫時,請考慮下列指導方針:When you design a FILESTREAM database, consider the following guidelines:

  • FILESTREAM 資料行必須附帶對應的 uniqueidentifierROWGUID 資料行。FILESTREAM columns must be accompanied by a corresponding uniqueidentifierROWGUID column. 這些種類的資料表也必須附帶唯一的索引。These kinds of tables must also be accompanied by a unique index. 一般而言,這個索引不是叢集索引。Typically this index is not a clustered index. 如果資料庫商務邏輯需要叢集索引,您就必須確定儲存在索引中的值不是隨機的。If the databases business logic requires a clustered index, you have to make sure that the values stored in the index are not random. 隨機值將會導致每次在資料表中加入或移除資料列時,重新排列索引。Random values will cause the index to be reordered every time that a row is added or removed from the table.

  • 基於效能考量,FILESTREAM 檔案群組和容器應該位於作業系統、 SQL ServerSQL Server 資料庫、 SQL ServerSQL Server 記錄、tempdb 或分頁檔以外的磁碟區上。For performance reasons, FILESTREAM filegroups and containers should reside on volumes other than the operating system, SQL ServerSQL Server database, SQL ServerSQL Server log, tempdb, or paging file.

  • FILESTREAM 無法直接支援空間管理和原則。Space management and policies are not directly supported by FILESTREAM. 不過,您可以透過將每個 FILESTREAM 檔案群組指派至個別的磁碟區,並使用磁碟區的管理功能,以間接方式管理空間和套用原則。However, you can manage space and apply policies indirectly by assigning each FILESTREAM filegroup to a separate volume and using the volume's management features.