ユーティリティ オブジェクト スクリプトを使用して SQL Server の取り込みを準備する

Lakeflow Connect使用してDatabricksに取り込むSQL Serverデータベースのセットアップ タスクを完了します。

要件

• スクリプトを実行するユーザーは、 db_ownerロールのメンバーである必要があります。このロールはセットアップ スクリプトを実行する場合にのみ必要であり、取り込みユーザーには必要ありません。

ユーザーをdb_ownerロールに追加するには、次のいずれかの方法を使用します。

• 最新のSQL Server（2012+） : 使用 ALTER ROLE

  SQL

  USE [your_database];
  ALTER ROLE db_owner ADD MEMBER [your_setup_user];
  GO

• レガシーSQL Serverまたは制限された環境 :使用 sp_addrolemember

  SQL

  USE [your_database];
  EXEC sp_addrolemember 'db_owner', 'your_setup_user';
  GO

• CT セットアップの場合: プラットフォーム上で変更追跡が利用可能である必要があります。

• CDCセットアップの場合: プラットフォームで変更データ キャプチャが利用可能である必要があります。

ステップ 1：ユーティリティオブジェクトをインストールまたはアップグレードする

このステップでは、 SQL Serverセットアップに必要なユーティリティ ストアド プロシージャと関数をインストールします。 インストールされる内容の詳細については、 「SQL Server ユーティリティ オブジェクト スクリプト リファレンス」を参照してください。

同じスクリプトで、初回インストールと以前のバージョンからのアップグレードの両方を処理します。以下の番号付きステップに従ってください。（アップグレードのみ）とマークされたステップは、ユーティリティオブジェクトの以前のバージョンがすでにインストールされている場合にのみ適用されます。初回インストールの場合はスキップしてください。

重要

(アップグレードのみ) スクリプトを実行する前に、ゲートウェイ (ゲートウェイベースのパイプライン) を停止するか、パイプライン (統合CDC) を停止する（pause）かしてください。一時停止することで、短いカットオーバー中にスキーマの変更によるクイックな失敗や再試行の必要性を回避できます。

1. スクリプトをダウンロード：

utility_script.sql をダウンロード

2. SQL Server Management Studio (SSMS)、Azure Data Studio、またはお好みの SQL クライアントでスクリプトを開きます。

3. db_ownerロールを持つユーザーとして SQL Server インスタンスに接続します。

4. ターゲット データベースに接続していることを確認してください。

5. (アップグレードのみ) 取り込みの一時停止：ゲートウェイを停止する (ゲートウェイベースのパイプライン)、またはパイプラインを一時停止する (統合 CDC)。

6. スクリプトを実行します。アップグレード時に、スクリプトはユーティリティ関数を再作成し、レガシーな replicant プレフィックスが付いたオブジェクトのみを削除します。既存の DDL サポート オブジェクトはそのまま維持されるため、次のステップでセットアップ手順を再実行するまで、現在の DDL Trigger によるスキーマ変更のキャプチャが継続されます。

7. （アップグレードのみ）使用するキャプチャ方法ごとにセットアップ手順を再実行します。変更追跡を使用する場合は lakeflowSetupChangeTracking、CDC を使用する場合は lakeflowSetupChangeDataCapture を実行します。これにより、オブジェクトが新しいバージョンに引き継がれ、取り込みユーザーの権限が復元されます。@User のみをパスします。アップグレード後もテーブルで変更追跡とCDCが有効な状態に維持されるため、@Tables は必要ありません。

   SQL

   -- If you use change tracking:
   EXEC dbo.lakeflowSetupChangeTracking
       @User = 'your_ingestion_user';  -- omit @User if you did not pass it originally
   
   -- If you use CDC:
   EXEC dbo.lakeflowSetupChangeDataCapture
       @User = 'your_ingestion_user';  -- omit @User if you did not pass it originally

   アップグレードによって構成が保持されるよう、default以外のオプションを再度渡してください。

   • 変更の追跡： by default、DDL 監査テーブルと Trigger は作成されないため、ほとんどのアップグレードではここに追加の項目は渡されません。インストールの際に DDL スキーマ変更キャプチャ（lakeflowDdlAudit テーブルがある場合）を使用している場合は、lakeflowSetupChangeTracking で @CreateDdlSupportingObjects = 1 を渡し、これらのオブジェクトを新しいバージョンに移動します。このオプションが存在する前のインストールではこれらが自動的に作成されていたため、DDL キャプチャに依存している場合は、一度も設定したことがない場合でもフラグを含めてください。
   • CDC： lakeflowSetupChangeDataCapture で @AllowDisablePreExistingCaptureInstances = 1 を使用する場合は、再度含めます。

8. インストールを確認します。

   SQL

   SELECT dbo.lakeflowUtilityVersion() AS UtilityVersion;
   SELECT dbo.lakeflowDetectPlatform() AS Platform;

9. (アップグレードのみ) 取り込みの再開：ゲートウェイを再開する、またはパイプラインを再開する。

注記

db_ownerロールは、このセットアップ スクリプトを実行するユーザーにのみ必要です。取り込みユーザー (後続のステップの@Userで指定) は、セットアップ手順によって付与された特定の権限のみを必要とします。 詳細については、 Microsoft SQL Server データベース ユーザー要件を参照してください。

注記

（アップグレードのみ）アップグレード期間中は、追跡対象テーブルのスキーマ変更を行わないようにしてください。カットオーバーはトランザクション内で実行されるため、並列のスキーマ変更が失われることはありませんが、一時的にブロックされるか、または直ちに失敗してカットオーバーが完了するまで再試行が必要になる場合があります。

ステップ 2: 変更追跡を有効にする (主キーを持つテーブルの場合)

変更トラッキングは、テーブル行の変更を追跡する軽量なメカニズムです。このステップでは、指定されたテーブルのデータベースレベルで CT を有効にします。スキーマ変更（DDL）もキャプチャするには、@CreateDdlSupportingObjects = 1 を渡して DDL サポートオブジェクトを作成します。これはオプトインであり、defaultでオフになっています。詳細については、SQL Server ユーティリティオブジェクトスクリプトリファレンスの lakeflowSetupChangeTracking を参照してください。

SQL

-- Enable change tracking on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'Sales.Orders,Production.Products,HR.Employees',
    @User = 'your_ingestion_user',
    @Retention = '2 DAYS';

代替オプション:

• 主キーを持つすべてのテーブルの場合: @Tables = 'ALL'
• 特定のスキーマの場合: @Tables = 'SCHEMAS:Sales,HR,Production'
• データベース レベルのセットアップのみ (テーブルの有効化なし): @Tables = NULL

ステップ 3: 変更データキャプチャを有効にする (主キーのないテーブルの場合)

CDC は挿入、更新、削除のアクティビティをキャプチャし、主キーのないテーブルに特に役立ちます。このステップでは、データベース レベルでCDC有効にし、キャプチャ インスタンス管理を設定し、自動スキーマ変更処理のトリガーを作成します。 詳細については、 SQL Server ユーティリティ オブジェクト スクリプト リファレンスのlakeflowSetupChangeDataCaptureを参照してください。

SQL

-- Enable CDC on specific tables (particularly those without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail',
    @User = 'your_ingestion_user';

代替オプション:

• すべてのテーブルについて: @Tables = 'ALL'
• 特定のスキーマの場合: @Tables = 'SCHEMAS:Sales,HR'
• データベース レベルのセットアップのみ: @Tables = NULL

Lakeflow Connect にテーブルの既存の（Lakeflow 以外の）CDC キャプチャインスタンスをそのまま残さずに引き継がせるには、@AllowDisablePreExistingCaptureInstances = 1 を設定します。

注記

変更追跡または CDC のいずれかを使用することも、両方を使用することもできます。Databricks包括的なカバレッジを実現するために、主キーのあるテーブル (ステップ 2) には変更追跡を使用し、主キーのないテーブル (ステップ 3) にはCDC使用することをお勧めします。

キャプチャインスタンス管理

LakeFlow Connectプレフィックスベースの命名規則を使用して、他のシステムまたはプロセスによって作成された既存のキャプチャ インスタンスに影響を与えずにCDCキャプチャ インスタンスを管理します。

LakeFlowキャプチャインスタンスの命名

LakeFlow Connect 、次の命名パターンを使用してキャプチャ インスタンスを作成および管理します。

• lakeflow_<schema>_<table>_1
• lakeflow_<schema>_<table>_2

LakeFlow Connect 、この命名パターンに一致するキャプチャ インスタンスのみを管理します。 異なる名前を持つ既存のキャプチャ インスタンスは保持され、 LakeFlow操作の影響を受けません。

注記

ユーティリティスクリプトのバージョン1.4より前では、LakeFlow Connect 、キャプチャインスタンスにNew_プレフィックスを使用しました (例: New_schema_table_1 )。 以前のバージョンからアップグレードする場合は、更新されたユーティリティ スクリプトを実行して、 lakeflow_命名規則に移行します。スクリプトは、移行中に既存のNew_プレフィックス付きキャプチャ インスタンスとの下位互換性を自動的に処理します。

キャプチャインスタンスのスロット要件

SQL Server では、テーブルごとに最大 2 つのキャプチャ インスタンスが許可されます。LakeFlow Connect CDCで動作させるには:

• LakeFlow lakeflow_プレフィックス付きインスタンスを作成するには、2 つのキャプチャ インスタンス スロットのうち少なくとも 1 つが使用可能である必要があります。
• 両方のスロットがすでにLakeFlow以外のキャプチャ インスタンスによって占有されている場合、 LakeFlow Connect独自のキャプチャ インスタンスを作成して管理できません。 LakeFlow既存のキャプチャ インスタンスから読み取ることはできますが、完全な更新またはスキーマ進化の操作を実行することはできません。

ヒント

両方のキャプチャ インスタンス スロットが使用されている場合は、代わりに変更の追跡を使用するか、不要になった既存のキャプチャ インスタンスの 1 つを削除します。

他のCDC消費者との共存

LakeFlow Connect 、同じテーブル上の他のCDCコンシューマーと安全に共存できます。

• 既存のキャプチャ インスタンスは、すべてのLakeFlow操作 (完全な更新やスキーマ進化など) 中に保持されます。
• LakeFlow 、必要な場合にのみ、独自のlakeflow_プレフィックス付きインスタンスを削除して再作成します。
• LakeFlow以外のキャプチャ インスタンスからのCDCデータを消費する他のシステムは、中断されることなく機能し続けます。

LakeFlowキャプチャ インスタンスを再作成する操作:

次の操作により、 LakeFlow lakeflow_プレフィックス付きキャプチャ インスタンスを削除して再作成します (他のインスタンスは削除されません)。

• 完全な更新操作
• テーブルに列を追加しています ( ADD COLUMN )

シナリオ例:

テーブルにmy_app_cdcという名前の既存のキャプチャ インスタンスがある場合:

1. LakeFlow Connect lakeflow_schema_table_1を作成します。
2. 両方のキャプチャインスタンスは安全に共存します。
3. LakeFlow完全な更新またはスキーマ進化を実行すると、 lakeflow_schema_table_1のみが再作成されます。
4. my_app_cdcインスタンスはそのまま残り、他のシステムに対して機能し続けます。

ステップ 4: 追加のアクセス許可を付与します (必要な場合)

このステップにより、取り込みユーザーに必要なシステムおよびテーブルレベルの権限が付与されます。 ステップ 2 と 3 は CT および CDC 固有の権限を付与しますが、このステップにより、ユーザーが必要なすべてのSELECT権限を持っていることが保証されます。 詳細については、 SQL Server ユーティリティ オブジェクト スクリプト リファレンスのlakeflowFixPermissions参照してください。

SQL

-- Grant system-level and table-level permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'your_ingestion_user',
    @Tables = 'Sales.Orders,Production.Products,HR.Employees';

代替オプション:

• すべてのテーブルについて: @Tables = 'ALL'
• システム権限のみ: @Tables = NULL
• 特定のスキーマ: @Tables = 'SCHEMAS:Sales,HR'

注記

ステップ 2 および 3 のセットアップ手順では、必要な CT およびCDC権限が自動的に付与されますが、追加のテーブル レベルのSELECT権限を付与する場合や権限が取り消されている場合は、この手順を実行する必要がある場合があります。

ステップ 5: セットアップを確認する

次のクエリを実行して、データベースとテーブルで変更追跡と CDC が適切に構成されていることを確認します。

SQL

-- Check Change Tracking status
SELECT
    d.name AS DatabaseName,
    ctd.is_auto_cleanup_on,
    ctd.retention_period,
    ctd.retention_period_units_desc
FROM sys.change_tracking_databases ctd
INNER JOIN sys.databases d ON ctd.database_id = d.database_id
WHERE d.name = DB_NAME();

-- Check tables with Change Tracking enabled
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.is_track_columns_updated_on,
    ct.begin_version,
    ct.cleanup_version
FROM sys.change_tracking_tables ct
INNER JOIN sys.tables t ON ct.object_id = t.object_id;

-- Check CDC status
SELECT
    DB_NAME() AS DatabaseName,
    is_cdc_enabled
FROM sys.databases
WHERE database_id = DB_ID();

-- Check tables with CDC enabled
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.capture_instance,
    ct.start_lsn,
    ct.create_date
FROM cdc.change_tables ct
INNER JOIN sys.tables t ON ct.source_object_id = t.object_id;

ユーティリティオブジェクトのアップグレード

アップグレードでは、初回インストール時と同じスクリプトを使用します。ステップ 1：ユーティリティオブジェクトのインストールまたはアップグレードに従ってください。これには、(アップグレードのみ) とマークされたステップが含まれており、取り込みの一時停止、セットアッププロシージャの再実行、および取り込みの再開について説明しています。

注記

以前のバージョンのユーティリティ オブジェクト スクリプトに元に戻すには、Databricks サポートにお問い合わせください。

例: ハイブリッドアプローチ

注記

この例では、簡潔にするために、 'ALL'使用してすべてのテーブルで CT と CDC を有効にします。本番運用で使用する場合は、特定のスキーマまたはテーブルを対象とするこのページの一般的なシナリオを検討してください。

SQL

-- Step 1: Already completed (script installed)

-- Step 2 & 3: Enable both CT and CDC
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'lakeflow_user',
    @Retention = '2 DAYS';

EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'lakeflow_user';

-- Step 4: Grant all necessary permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'ALL';

一般的なシナリオ

シナリオ 1: 変更追跡のみ (特定のスキーマ)

SQL

EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,Production',
    @User = 'lakeflow_user',
    @Retention = '2 DAYS';

EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'SCHEMAS:Sales,Production';

シナリオ2: CDCのみ（特定のテーブル）

SQL

EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail,dbo.TempRecords',
    @User = 'lakeflow_user';

EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'Staging.ImportData,Logs.AuditTrail,dbo.TempRecords';

シナリオ 3: ハイブリッド アプローチ (一部のスキーマには CT、特定のテーブルには CDC)

SQL

-- Enable CT on transactional schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'lakeflow_user',
    @Retention = '3 DAYS';

-- Enable CDC on specific staging tables without primary keys
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail',
    @User = 'lakeflow_user';

-- Grant permissions on all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'ALL';

その他のリソース

• SQL Server ユーティリティ オブジェクト スクリプト リファレンス
• Databricks への取り込み用に Microsoft SQL Server を構成する
• Microsoft SQL Server データベースのユーザー要件
• SQL Server ドキュメントの「データの変更を追跡する (SQL Server)」