優化資料庫檔案群組

發行項
10/14/2023

在生產BizTalk Server環境中，檔案輸入/輸出 (I/O) 爭用通常是限制因素或瓶頸。 BizTalk Server非常需要大量資料庫的應用程式，而BizTalk Server所使用的SQL Server資料庫則非常耗用檔案 I/O。

本主題描述如何充分利用SQL Server的檔案和檔案群組功能，將檔案 I/O 爭用的情況降到最低，並改善BizTalk Server解決方案的整體效能。

概觀

每個BizTalk Server解決方案最終都會遇到檔案 I/O 競爭，因為輸送量會增加。 I/O 子系統或儲存引擎是任何關係資料庫的重要元件。成功的資料庫實作通常需要在專案早期就進行周密的規劃。這項規劃應考量下列問題：

應使用哪種磁碟硬體，如 RAID (磁碟陣列) 裝置。
如何使用檔案和檔案群組在磁片上分派資料。如需在 SQL Server 中使用檔案和檔案群組的詳細資訊，請參閱資料庫檔案和檔案群組。
實作最佳索引設計，以改善存取資料時的效能。如需設計索引的詳細資訊，請參閱設計索引。
如何設定SQL Server組態參數以獲得最佳效能。如需設定SQL Server最佳組態參數的詳細資訊，請參閱伺服器組態選項。

BizTalk Server的主要設計目標之一是確保訊息永遠不會遺失。為了減輕訊息遺失的可能性，訊息通常會在處理訊息時寫入 MessageBox 資料庫。當協調流程處理訊息時，訊息會在協調流程中的每個持續性點寫入 MessageBox 資料庫。這些持續性點會導致 MessageBox 將訊息和相關狀態寫入實體磁片。在較高的輸送量上，此持續性可能會導致相當大量的磁片爭用，而且可能會成為瓶頸。

SQL Server中的檔案和檔案群組功能獲得最佳使用，可有效地解決檔案 IO 瓶頸，並改善BizTalk Server解決方案的整體效能。此優化只能由有經驗的SQL Server資料庫管理員完成，而且只有在所有BizTalk Server資料庫都已正確備份之後。此優化應該在BizTalk Server環境中的所有SQL Server電腦上執行。

SQL Server檔案和檔案群組可用來改善資料庫效能，因為此功能可讓資料庫跨多個磁片、多個磁碟控制卡或 RAID (獨立磁片的備援陣列) 系統建立。例如，若電腦有 4 個磁碟，您可以建立由 3 個資料檔和 1 個記錄檔組成的資料庫，每一個檔案各在一個磁碟上。當存取資料時，四個讀取/寫入頭可以平行存取資料。這會大幅加速資料庫作業。如需針對SQL Server磁片實作硬體解決方案的詳細資訊，請參閱線上 https://go.microsoft.com/fwlink/?LinkID=71419 SQL Server叢書中的。

此外，檔案和檔案群組會啟用資料放置，因為資料表可以在特定檔案群組中建立。這可改善效能，因為給定資料表的所有檔案 I/O 都可以導向特定磁片。例如，大量使用的資料表可以放在檔案群組中的檔案上，位於一個磁片上，而資料庫中其他較不頻繁存取的資料表可以位於位於另一個檔案群組的不同檔案上，位於第二個磁片上。

在資料庫層的瓶頸中，會詳細討論檔案 IO 瓶頸。檔案 I/O (磁片 I/O) 最常見的指標是「實體磁片：平均磁片佇列長度」計數器的值。當任何 SQL Server 上任何指定磁片的「實體磁片：平均磁片佇列長度」計數器值大於大約 3 個時，檔案 I/O 可能是瓶頸。

如果套用檔案或檔案群組優化無法解決檔案 I/O 瓶頸問題，可能需要新增額外的實體或 SAN 磁片磁碟機來增加磁片子系統的輸送量。

本主題描述如何手動套用檔案和檔案群組優化，但也可以編寫這些優化腳本。本主題結尾會提供範例 SQL 腳本。請務必注意，必須修改此腳本，以容納任何指定BizTalk Server解決方案SQL Server資料庫所使用的檔案、檔案群組和磁片組態 () 。

使用預設BizTalk Server組態建立的資料庫

根據設定BizTalk Server時啟用的功能而定，最多可以在SQL Server中建立 13 個不同的資料庫，而且所有這些資料庫都會在預設檔案群組中建立。除非使用 ALTER DATABASE 命令變更預設檔案群組，否則SQL Server的預設檔案群組是 PRIMARY 檔案群組。下表列出設定BizTalk Server時，如果啟用所有功能，則會列出在 SQL Server 中建立的資料庫。

BizTalk Server 資料庫

資料庫	預設資料庫名稱	Description
設定資料庫	BizTalkMgmtDb	BizTalk Server群組中所有BizTalk Server實例的中央中繼資訊存放區。
BizTalk MessageBox 資料庫	BizTalkMsgBoxDb	儲存訂用帳戶述詞。它是主機平臺，並保留每個BizTalk Server主機的佇列和狀態資料表。 MessageBox 資料庫也儲存訊息及訊息屬性。
BizTalk 追蹤資料庫	BizTalkDTADb	儲存BizTalk Server追蹤引擎所追蹤的商務和健康情況監視資料。
BAM 分析資料庫	BAMAnalysis	SQL Server Analysis Services保留商務活動的匯總歷程記錄資料的資料庫。
BAM 星狀結構描述資料庫	BAMStarSchema	轉換從商務活動監視進行 OLAP 處理收集的資料。使用 BAM 分析資料庫時，需要此資料庫。
BAM 主要匯入資料庫	BAMPrimaryImport	儲存來自商務活動的事件，然後在活動實例之後查詢進度和資料。此資料庫也會執行即時匯總。
BAM 封存資料庫	BAMArchive	儲存訂用帳戶述詞。 BAM 封存資料庫可將 BAM 主要匯入資料庫中的商務活動資料的累積降到最低。
SSO 資料庫	SSODB	安全地儲存接收位置的組態資訊。儲存 SSO 聯盟應用程式的資訊，以及所有聯盟應用程式的加密使用者認證。
規則引擎資料庫	BizTalkRuleEngineDb	的存放庫： - 原則，這是一組相關的規則。 - 詞彙，這些詞彙是規則中資料參考的使用者易記網域特定名稱集合。
追蹤 Analysis Server Administration 資料庫	BizTalkAnalysisDb	儲存商務和健康情況監視 OLAP Cube。

資料檔和記錄檔的區隔

如上所述，預設BizTalk Server組態會將 MessageBox 資料庫放在預設檔案群組中的單一檔案中。根據預設，MessageBox 資料庫的資料和交易記錄會放在相同的磁片磁碟機和路徑上。這是為了容納具有單一磁片的系統而完成。在生產環境中，單一檔案/檔案群組/磁片組態 並非最佳 。為了達到最佳效能，資料檔案和記錄檔應該放在不同的磁片上。

注意

記錄檔絕不能做為檔案群組的一部分。記錄空間將與資料空間分別管理。

散發BizTalk Server資料庫的 80/20 規則

大部分BizTalk Server解決方案中爭用的主要來源，可能是因為磁片 I/O 爭用或資料庫爭用，而BizTalk Server MessageBox 資料庫。這在單一和多重 MessageBox 案例中都是如此。合理地假設散發 BizTalk 資料庫的值為 80% 的量，會衍生自優化 MessageBox 資料檔案和記錄檔。下面詳述的範例案例著重于優化 MessageBox 資料庫的資料檔案。接著，您可以視需要針對其他資料庫遵循這些步驟，例如，如果解決方案需要大量追蹤，也可以優化追蹤資料庫。

逐步將檔案手動新增至 MessageBox 資料庫

本節說明可遵循的步驟，以手動將檔案新增至 MessageBox 資料庫。在此範例中，會新增三個檔案群組，然後將檔案新增至每個檔案群組，以將 MessageBox 的檔案分散到多個磁片上。

注意

為了完成本指南的效能測試，檔案群組已透過使用腳本進行優化，併發布為BizTalk Server效能優化指南的一部分。下列步驟僅供參考之用。

在 SQL Server 上手動將檔案新增至 MessageBox 資料庫

開啟SQL Server Management Studio以顯示 [連接到伺服器] 對話方塊。
在 [連接到伺服器] 對話方塊的 [伺服器名稱] 欄位中，輸入裝載BizTalk Server MessageBox 資料庫之SQL Server實例的名稱，然後按一下 [連線] 按鈕以顯示[Microsoft SQL Server Management Studio] 對話方塊。

在SQL Server Management Studio的 [物件總管] 窗格中，展開 [資料庫] 以檢視此實例的資料庫SQL Server。
以滑鼠右鍵按一下要加入檔案的資料庫，然後按一下 [ 屬性 ] 以顯示資料庫的 [ 資料庫屬性 ] 對話方塊。
在 [ 資料庫屬性 ] 對話方塊中，選取 [ 檔案群組] 頁面。按一下 [ 新增] 按鈕，為 BizTalkMsgBoxDb 資料庫建立其他檔案群組。在下列範例中，會新增三個額外的檔案群組。
在 [資料庫屬性] 對話方塊中，選取 [檔案] 頁面。

按一下 [ 新增 ] 按鈕以建立其他檔案以新增至檔案群組，然後按一下 [ 確定]。 MessageBox 資料庫現在分散在多個磁片上，這可大幅提升單一磁片組態的效能優勢。

在下列範例中，會針對稍早建立的每個檔案群組建立檔案，並將每個檔案放在個別的磁片上。

將檔案群組和檔案新增至 BizTalk MessageBox 資料庫的 SQL 腳本範例

下列範例 SQL 腳本會執行上一節中手動完成的相同工作。此範例腳本假設有相異邏輯磁片磁碟機 G 到 J。腳本會為每個檔案群組建立檔案群組和檔案，並將記錄檔放在 J 磁片磁碟機上。

注意

由於SQL Server會循序寫入其記錄檔，因此SQL Server資料庫建立多個記錄檔，因此沒有效能優勢。

-- Filegroup changes are made using the master database
USE [master]
GO

-- Script-wide declarations
DECLARE @CommandBuffer nvarchar(2048)
DECLARE @FG1_Path nvarchar(1024)
DECLARE @FG2_Path nvarchar(1024)
DECLARE @FG3_Path nvarchar(1024)
DECLARE @Log_Path nvarchar(1024)

-- Set the default path for all filegroups
SET @FG1_Path = N'G:\BizTalkMsgBoxDATA\'
SET @FG2_Path = N'H:\BizTalkMsgBoxDATA\'
SET @FG3_Path = N'I:\BizTalkMsgBoxDATA\'
SET @Log_Path = N'J:\BizTalkMsgBoxLog\'

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG1]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG1_Path +
N'BizTalkMsgBoxDb_FG1.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG1]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG2]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG2_Path +
N'BizTalkMsgBoxDb_FG2.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG2]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG3]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG3_Path +
N'BizTalkMsgBoxDb_FG3.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG3]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] MODIFY FILE ( NAME = N'BizTalkMsgBoxDb_log', SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )

GO -- Completes the previous batch, as necessary

下列範例 SQL 腳本可用來將特定檔案群組設定為預設檔案群組：

USE [BizTalkMsgBoxDb]
GO
declare @isdefault bit
SELECT @isdefault=convert(bit, (status & 0x10)) FROM sysfilegroups WHERE groupname=N'BTS_MsgBox_FG1'
if(@isdefault=0)
ALTER DATABASE [BizTalkMsgBoxDb] MODIFY FILEGROUP [BTS_MsgBox_FG1] DEFAULT
GO

腳本的優點是腳本可以快速執行多個工作、可以精確地重現，並減少人為錯誤的可能性。腳本的缺點是，不正確地撰寫腳本的執行可能會導致嚴重問題，而可能需要從頭重新設定BizTalk Server資料庫。因此，在生產環境中執行之前，會徹底測試本主題所列範例腳本等 SQL 腳本的重要性。

共用方式為