SQL Server 公用程式物件指令碼參考

存取 SQL Server 公用程式物件指令碼的參考資料，包括元件、參數和疑難排解。

概觀

指令碼會安裝版本化的公用程式的預存程序和函式，以配置 SQL Server 資料庫，將其匯入至 Lakeflow Connect 中。 設定工作包括：

• 權限管理
• 變更追蹤 （CT） 設定
• 變更資料擷取 （CDC） 設定
• 平台偵測
• DDL 提供建立用於結構描述變更追蹤的物件，讓系統能有效追蹤 schema 的變更情況。

版本資訊

• 目前版本：1.7
• 主要版本：1
• 次要版本：7
• 版本功能： lakeflowUtilityVersion()

1.7 版本新增內容

Highlights

• 修正了結構演化中資料遺失的錯誤。 在早期版本中，若在同時包含既有（非 Lakeflow）擷取實例的資料表中新增欄位，則可將新欄位匯入為 NULL。 此問題已修正，並解決了多項相關的結構演化失敗（參見錯誤修正）。
• DDL 支援物件現在可選用於變更追蹤。 DDL 稽核表與觸發器不再需要自動結構演化，且預設關閉。 只有在你還想要時才設定@CreateDdlSupportingObjects = 1lakeflowSetupChangeTracking。
• 更可靠的結構演化。 約束變更會被分類，避免非破壞性的變更，例如外鍵，不再觸發不必要的完整刷新。

BUG 修正

• 修正了多項 ADD COLUMN 結構演化失敗，包括使用動態資料遮蔽、大小寫區分或二進位整理的資料表，以及在已有擷取實例存在時的重新初始化迴圈或中斷擷取。
• 修正了非預設伺服器整合資料庫上的整合衝突錯誤。
• 修正了在非預設 SET 選項的會話中執行時可能失敗的 DDL 觸發器。
• 修正 lakeflowFixPermissions 後會從主資料庫授予所需的伺服器範圍權限。
• 固定的卸載（CLEANUP 模式）會留下孤立的 DDL 觸發器，可能會阻擋 ALTER TABLE。

其他變更

• 補充@AllowDisablePreExistingCaptureInstanceslakeflowSetupChangeDataCapture。 設定為 1 讓 Lakeflow Connect 接手並管理資料表中既有（非 Lakeflow）的 CDC 擷取實例，而不是讓它留在原位。

主要元件

Functions

lakeflowDetectPlatform()

偵測 SQL Server 平台類型。

返回：'AZURE_SQL_DATABASE'、'AZURE_SQL_MANAGED_INSTANCE'、'AMAZON_RDS'、'ON_PREMISES'或'UNKNOWN'

lakeflowUtilityVersion()

偵測公用程式物件版本。

傳回：'1.7'

預存程序

lakeflowFixPermissions

授予使用者進行資料匯入作業所需的權限。

參數：

@Tables 參數選項：

它能做什麼：

• 授與必要的系統檢視權限（SELECT、sys.objects、sys.tables、sys.columns等）
• 授予系統預存程序（EXECUTE、sp_tables 等）的sp_columns_100
• 選擇性地根據SELECT參數授與@Tables權限於使用者資料表上
• 處理平臺特定的差異 （Azure SQL 資料庫、受控執行個體、RDS、內部部署）

lakeflowSetupChangeTracking

支援資料庫與資料表層級的變更追蹤，並可選配DDL支援（選擇加入）。

參數：

@Tables 參數選項：

它能做什麼：

• 如果尚未啟用，則在資料庫層級啟用變更追蹤
• 當 @CreateDdlSupportingObjects = 1時，會建立一個版本化的 DDL 稽核表（）lakeflowDdlAudit_1_7及一個觸發器以捕捉結構變更
• 在指定的表上啟用 CT（跳過沒有主鍵的表）
• 將權限授與 VIEW CHANGE TRACKING 指定的使用者
• CLEANUP mode：移除 DDL 支援物件

重要行為：

• 自動略過沒有主索引鍵的資料表 （建議使用 CDC）
• 使用'ALL'參數進行智慧探索
• 冪等：可安全多次運行

lakeflowSetupChangeDataCapture

在資料庫和資料表層級啟用 CDC，支援 DDL 和擷取實例管理。

參數：

@Tables 參數選項：

它能做什麼：

• 在資料庫層級啟用 CDC （如果尚未啟用）
• 建立擷取实例追蹤表 （lakeflowCaptureInstanceInfo_1_7）
• 建立協助管理擷取執行個體的輔助程序：
  • lakeflowDisableOldCaptureInstance_1_7
  • lakeflowMergeCaptureInstances_1_7
  • lakeflowRefreshCaptureInstance_1_7
• 建立 ALTER TABLE 觸發器，以自動處理資料庫架構變更
• 在指定的資料表上啟用 CDC
• 將必要的 CDC 權限授與指定的使用者
• CLEANUP 模式：移除所有 CDC DDL 支援物件

重要行為：

• 適用於有或沒有主鍵的表格
• 自動處理結構變更時的擷取實例輪換
• 冪等：可安全多次運行

平台支援

• 本地部署的 SQL Server（EngineEdition 1-4）
• Azure SQL 資料庫 （EngineEdition 5）
• Azure SQL 受控執行個體 （EngineEdition 8）
• Amazon RDS for SQL Server （由伺服器名稱模式偵測）

先決條件

• 執行指令碼的使用者必須是角色的 db_owner 成員
• 為了設定 CT，平台必須提供變更追蹤功能。
• 針對 CDC 設定：平台上必須設有變更資料擷取功能

安裝指示

下載並執行指令碼

1. 下載最新版本的劇本：

   下載utility_script.sql

2. 執行腳本：

   1. 在 SQL Server Management Studio （SSMS）、Azure Data Studio 或您慣用的 SQL 用戶端中開啟下載的腳本。
   2. 連接到您的 SQL Server 實例。
   3. 確認您已連線至要安裝公用程式物件的目標資料庫。
   4. 執行腳本。

3. 驗證安裝：

   -- Verify installation
   SELECT dbo.lakeflowUtilityVersion() AS UtilityVersion;
   SELECT dbo.lakeflowDetectPlatform() AS Platform;
   

替代方法：使用命令列執行

如果您喜歡使用 sqlcmd：

sqlcmd -S YourServerName -d YourDatabase -E -i utility_script.sql

範例：修正權限 （僅限系統）

-- Grant system permissions only
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser';

範例：修正權限 （具有資料表存取權）

-- Grant system permissions plus access to all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'ALL';

-- Grant permissions for specific schemas
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'SCHEMAS:Sales,HR,Production';

-- Grant permissions for specific tables
EXEC dbo.lakeflowFixPermissions
    @User = 'myuser',
    @Tables = 'Sales.Orders,HR.Employees';

範例：變更追蹤設定

僅限資料庫層級

-- Setup CT infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = NULL,
    @User = 'myuser';

在所有表格上啟用

-- Enable CT on all user tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

架構型設定

-- Enable CT on all tables in specific schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'myuser',
    @Retention = '3 DAYS';

特定表格

-- Enable CT on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'dbo.Table1,Sales.Orders,HR.Employees',
    @User = 'myuser';

建立 DDL 支援物件

-- Enable CT and create the DDL audit table and trigger for schema-change capture
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser',
    @CreateDdlSupportingObjects = 1;

範例：CDC 設定

僅限資料庫層級

-- Setup CDC infrastructure without enabling on tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = NULL,
    @User = 'myuser';

在所有表格上啟用

-- Enable CDC on all user tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

特定表格

-- Enable CDC on specific tables
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'dbo.Table1,Sales.Orders',
    @User = 'myuser';

取得已存在的擷取實例的所有權

-- Allow Lakeflow to disable pre-existing (non-Lakeflow) capture instances
-- during schema change handling
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser',
    @AllowDisablePreExistingCaptureInstances = 1;

範例：混合式方法

-- Step 1: Enable CT on tables with primary keys
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'myuser';

-- Step 2: Enable CDC on remaining tables (without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'myuser';

範例：清理

-- Remove CT DDL support objects
EXEC dbo.lakeflowSetupChangeTracking
    @Mode = 'CLEANUP';

-- Remove CDC DDL support objects
EXEC dbo.lakeflowSetupChangeDataCapture
    @Mode = 'CLEANUP';

建立的 DDL 支援物件

會建立下列 DDL 支援物件，視您使用變更追蹤或 CDC 而定。

以進行變更追蹤

對於疾病預防控制中心

變更追蹤限制

• 需要主索引鍵：沒有主索引鍵的資料表無法使用變更追蹤。
• 指令碼會自動略過沒有 PK 的資料表，並建議改用 CDC。

平台特定行為

• Azure SQL 資料庫：預設可存取系統預存程序 （不需要 EXECUTE 授權）。
• 伺服器範疇檢視：在 Azure SQL 資料庫中如 sys.change_tracking_databases 等檢視限制存取權。

升級路徑

要升級，請重新執行工具物件腳本，然後重新執行設定程序。 腳本重建工具函式，並僅移除早期版本的舊 replicant有 -前綴物件。 它會保留你現有的 Lakeflow DDL 支援物件，所以目前的 DDL 觸發器會持續捕捉結構變更，直到你重新執行設定程序。 相關說明請參見 步驟 1：安裝或升級工具物件。

升級過程中會發生什麼事：

• 腳本定義的工具函式與設定程序會被捨棄並重新建立。
• 先前版本中 legacy replicant前綴的物件會被移除。
• 現有的 Lakeflow DDL 支援物件（lakeflowDdlAudit_*、、lakeflowDdlAuditTrigger_*lakeflowCaptureInstanceInfo_*、 及相關的程序和觸發器）會保留。 重新執行設定程序會將它們轉移到新版本。 每當你重新執行 lakeflowSetupChangeDataCapture時，擷取實例物件就會移動。 DDL 審計表和觸發器只有在你重新執行 lakeflowSetupChangeTracking 時 @CreateDdlSupportingObjects = 1才會移動，因為它們是選擇加入的。
• 更改追蹤功能，CDC 在你的表格中仍保持啟用。 升級不會讓它們失效。
• CDC 擷取實例不會受到升級腳本的影響。

執行升級腳本後，請重新執行設定程序，以重建新版本的 DDL 支援物件：

• lakeflowSetupChangeTracking：帶有 @CreateDdlSupportingObjects = 1，攜帶 DDL 審計表與觸發器 forward。lakeflowDdlAudit_1_7 如果你的安裝使用 DDL schema-change 擷取（它有表格 lakeflowDdlAudit ），或物件仍維持舊版本，就要傳遞這個旗標。
• lakeflowSetupChangeDataCapture： 重現 lakeflowCaptureInstanceInfo_1_7 相關程序與觸發器。

這兩種程序皆為冪등 法，且可安全地以原始設定參數重新執行。

版本管理方案： objectName_majorVersion_minorVersion。 目前的物件使用後綴。_1_7

最佳做法

• 始終以 db_owner 或具有同等權限的使用者執行。
• 先在非生產資料庫上進行測試。
• 使用混合方法進行全面覆蓋。
• 在設定之後執行 lakeflowFixPermissions ，以確保使用者存取正確。
• 根據您的匯入頻率來考量保留期間。

故障排除

「執行此指令碼的使用者不是『db_owner』角色成員」

解決方案：以具有db_owner角色的使用者身分執行

「未在目錄上啟用變更追蹤」

解決方案：在資料庫層級啟用 CT 或讓程序自動處理

「未在目錄上啟用變更資料擷取」

解決方案：在資料庫層級啟用 CDC 或讓程序自動處理

「由於缺少主鍵而跳過的表格」

解決方案：改為 lakeflowSetupChangeDataCapture 用於這些表格

驗證整合

下列公用程式物件會由 Java 驗證架構驗證：

移轉注意事項

如果從舊版 DDL 支援物件升級到公用程式物件之前的版本：

• 指令碼會自動清除舊版物件。
• 不需要手動清理。
• 1.1 版將所有功能整合到統一的程序中。

其他資源

• 使用實用物件腳本準備 SQL Server 以進行擷取
• 將 Microsoft SQL Server 配置以整合至 Azure Databricks
• Microsoft SQL Server 資料庫使用者需求
• 追蹤 SQL Server 檔中的資料變更 （SQL Server）
• 關於 SQL Server 檔中的變更追蹤 （SQL Server）
• 什麼是異動資料擷取 (CDC)？ 在 SQL Server 文件資料中