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

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

概觀

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

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

版本資訊

• 當前版本：1.1
• 主要版本：1
• 次要版本：1
• 版本功能： lakeflowUtilityVersion_1_1()

主要元件

Functions

lakeflowDetectPlatform()

偵測 SQL Server 平台類型。

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

lakeflowUtilityVersion_1_1()

偵測公用程式物件版本。

傳回：'1.1'

預存程序

lakeflowFixPermissions

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

參數：

@Tables 參數選項：

它能做什麼：

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

lakeflowSetupChangeTracking

啟用具有 DDL 支援的資料庫和資料表層級的變更追蹤。

參數：

@Tables 參數選項：

它能做什麼：

• 如果尚未啟用，則在資料庫層級啟用變更追蹤
• 建立版本化的 DDL 稽核資料表 （lakeflowDdlAudit_1_2）
• 建立 DDL 稽核觸發程式以擷取結構描述變更
• 在指定的表上啟用 CT（跳過沒有主鍵的表）
• 將權限授與 VIEW CHANGE TRACKING 指定的使用者
• CLEANUP mode：移除 DDL 支援物件

重要行為：

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

lakeflowSetupChangeDataCapture

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

參數：

@Tables 參數選項：

它能做什麼：

• 在資料庫層級啟用 CDC （如果尚未啟用）
• 建立擷取实例追蹤表 （lakeflowCaptureInstanceInfo_1_2）
• 建立協助管理擷取執行個體的輔助程序：
  • lakeflowDisableOldCaptureInstance_1_2
  • lakeflowMergeCaptureInstances_1_2
  • lakeflowRefreshCaptureInstance_1_2
• 建立 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. 執行指令碼

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

3. 確認安裝

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

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

如果您喜歡使用 sqlcmd：

sqlcmd -S YourServerName -d YourDatabase -E -i lakeflow_utility_objects.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';

範例：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';

範例：混合式方法

-- 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 等檢視限制存取權。

升級路徑

• 指令碼會在執行時自動捨棄所有先前的版本。
• 版本控制方案： objectName_majorVersion_minorVersion
• 目前物件會使用 _1_1 字尾進行版本追蹤。

最佳做法

• 始終以 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 文件資料中