リンクサーバーを使用した変更トラッキングによる SQL Server データベースの Babelfish for Aurora PostgreSQL への移行

お客様は通常、SQL Server データベースを Babelfish for Aurora PostgreSQL に移行するために AWS Database Migration Service (AWS DMS) を選択します。AWS DMS は、フルロードの移行は、すべてのエディションの SQL Server をサポートしています。ただし、継続的なレプリケーションと進行中の変更の場合、ソースとなる SQL Server はトランザクションレプリケーションまたは変更データキャプチャ (CDC) を有効にする必要があります。これにより、AWS DMS はデータベースのトランザクションログファイルまたはトランザクションログバックアップから変更を読み取り、ターゲットデータベースにレプリケートすることができます。

Enterprise、Standard、Developer などの SQL Server エディションには、トランザクションレプリケーションと CDC 機能が付属しています。したがって、これらのエディションでは Babelfish for Aurora PostgreSQL への継続的なレプリケーションを実装できます。ただし、SQL Server Web エディションを使用している場合や Azure SQL 上で SQL Server ワークロードを実行している場合は、トランザクションレプリケーションと CDC のいずれもサポートされていません。そのような場合は、継続的なレプリケーションなしでフルロードのみを実行できます。

この制限を回避するには、リンクサーバーと併せて変更トラッキングを使用するワークアラウンドがあります。このアプローチでは、Azure SQL または SQL Server Web Edition での変更をトラッキングし、対象データベースに効果的にレプリケーションできます。

この投稿では、SQL Server Web Edition (ソース) の変更トラッキング機能と、Babelfish for Aurora PostgreSQL (ターゲット) のリンクサーバー機能を使用して、進行中の変更をレプリケーションする手順を提供します。

ソリューション概要

変更トラッキングはデータベース内のデータ変更をトラッキングするメカニズムです。テーブルで変更トラッキングが有効になると、SQL Server は内部的に別の内部テーブルで変更(挿入、更新、または削除)をトラッキングします。この内部テーブルにはテーブルの主キー列が含まれています。

トラッキングが有効になってからの変更はすべて、CHANGETABLE という関数を使用して取得できます。変更トラッキングの詳細については、「変更トラッキングの操作 (SQL Server)」を参照してください。

次の図は、ソリューションアーキテクチャを示しています。

このソリューションを実装するには、次の設定手順を完了してください。

ソースサーバーでデータベースとテーブルレベルの変更トラッキングを有効にします。
AWS DMS を使用して、ソースデータベースの初期フルロードをターゲットに対して行います。
ターゲットにソース SQL サーバーへのリンクサーバーを作成します。
ターゲットにアンカーテーブルを作成します。
ソースから変更されたデータを抽出し、ターゲットテーブルにロードします。

前提条件

このソリューションを試すには、次の前提条件が必要です。

ソースとなる SQL Server または Azure SQL インスタンス
SQL Server 上の Northwind データベース
バージョン 4.0 または以降の Babelfish for Aurora PostgreSQL インスタンス
SQL Server と PostgreSQL のファンクションに関する知識
SQL Server に接続するための SQL Server Management Studio (SSMS) または他のクライアントツール

このソリューションには、新しい AWS リソースの作成と利用が伴います。したがって、アカウントに料金が発生します。詳細については、AWS 料金ぺージをご確認ください。本番環境にこのソリューションを実装する前に、非本番インスタンスでセットアップを行い、エンドツーエンドの検証を実行することを強くお勧めします。

ソースデータベスでの変更トラッキング有効化

ソースサーバーで変更トラッキングを有効にするには、次の手順に従ってください。

SSMS を使用して SQLServer インスタンスに接続します。
Northwind データベースを選択し、[新しいクエリ] を選択します。
次のコマンドを使用してデータベースレベルで変更トラッキングを有効にします。
```
ALTER DATABASE NORTHWIND SET CHANGE_TRACKING = ON(CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = OFF)
```
SQL
継続的レプリケーションのターゲットの一部として含めたいテーブルで変更トラッキングを有効にします。ここでは、次のコマンドを使用して顧客テーブルで変更トラッキングを有効にします。
```
ALTER TABLE DBO.CUSTOMERS ENABLE CHANGE_TRACKING
```
SQL

次のコマンドを実行して、テーブルで変更トラッキングが有効になっていることを確認してください。

SELECT schema_name(T.schema_id) as SCH_NAME ,T.NAME,CT.min_valid_version  ,ct.*
FROM northwind.sys.tables T LEFT OUTER JOIN sys.change_tracking_tables CT on T.object_id = CT.object_id
WHERE min_valid_version is not null

変更トラッキングが更新された行をどのように取得するかをテストするには、次のコマンドを実行します。
1. 関数 CHANGE_TRACKING_CURRENT_VERSION() を使用して、現在の変更トラッキングバージョンを確認します。この値が 26 であると仮定しましょう。
2. customers テーブルの行を更新します。
3. customers テーブルのどの行が更新されたかを確認するには、バージョン番号 (この例では 26) をパラメーターとして CHANGETABLE 関数を使用します。

DECLARE @CTCV INT;
SELECT @CTCV = CHANGE_TRACKING_CURRENT_VERSION () ;

-- In this example , the current value is 26
SELECT @CTCV

-- update a record in customer table
UPDATE dbo.CUSTOMERS SET CompanyName ='camilo2' where CUSTOMERID = 'ALFKI'

-- Get all the changes that are made after version 26 in this example.
SELECT P.*,'--',CT.* FROM customers AS P JOIN CHANGETABLE(CHANGES northwind.dbo.customers,@CTCV ) AS CT ON  P.customerid = CT.customerid;

AWS DMS を使ってフルロードを開始する前に、継続的なレプリケーションが必要なテーブルで変更トラッキングを有効にすることが不可欠です。これにより、システムはフルロードの開始時点から指定されたテーブルに対するすべてのデータ変更操作 (DML) を追跡できるようになります。

AWS DMS を使用して初回のフルロードでソースデータベースをターゲットデータベースに移行

ターゲットにフルロードを使用してソースデータベースを AWS DMS で移行するには、次の手順を実行します。「Compass ツールと AWS DMS を使用した SQL Server の Babelfish for Aurora PostgreSQL への移行」を参照し、Babelfish for Aurora PostgreSQL クラスターを作成して接続します。投稿では、AWS DMS を使用して SQL Server から Babelfish にデータを移行する手順も概説されています。

ターゲットサーバーで、Northwind データベースのスキーマを作成してください。Northwind サンプルスキーマは、GitHub リポジトリからダウンロードできます。
AWS DMSのフルロード設定を使用して、ソースからターゲットにデータを移行します。手順については、「AWS Database Migration Service のターゲットとしての Babelfish for Aurora PostgreSQL の使用」を参照してください。

ソース SQL Server に対するリンクサーバーをターゲットに作成

Babelfish for Aurora PostgreSQL でリンクサーバーを構成するには、「Babelfish は、リンクサーバーをサポートしています」を参照してください。次の手順を完了してください。

SSMS または SQLCMD を使用してターゲットのデータベースインスタンスに接続し、次のコマンドを実行します。ここでは、SQLCMD を使用して Babelfish for Aurora PostgreSQL インスタンスに接続します。適切な値でパラメーターを置き換えてください。
```
sqlcmd -S your-DB-instance.aws-region.rds.amazonaws.com -U test -P password
```
SQL

tds_fdw 拡張機能をインストールします。

EXEC sp_execute_postgresql N'CREATE EXTENSION tds_fdw';

ターゲットインスタンスで次のコマンドを使用してリンクサーバーを作成します。@datasrc、@rmtuser、および @rmtpassword パラメーターを適切な値に置き換えてください。

Use Northwind; 
GO 
EXEC master.dbo.sp_addlinkedserver @server=N'ls_northwind', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'myserver.xxxxx.US-WEST-2.RDS.AMAZONAWS.COM', @catalog='northwind'; 
GO 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N' ls_northwind',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='password';

リモートサーバー上のテーブル、ビュー、またはその他のサポートされているオブジェクトを参照するには、次のコードに示されているように OPENQUERY() T-SQL を使用するか、標準の 4 部構成の名前付け規則を使用します。
```
SELECT * FROM OPENQUERY(ls_northwind, 'SELECT * FROM customers');
```
SQL
初期の変更トラッキングバージョン (この例では 0 を使用) に基づいて customers テーブルの変更されたレコードを取得するには、次のコマンドを実行します。
```
SELECT * FROM OPENQUERY(ls_northwind, 'SELECT C.* FROM customers AS C JOIN CHANGETABLE(CHANGES northwind.dbo.customers,0 ) AS CT ON C.customerid = CT.customerid');
```
SQL
次のスクリーンショットは私たちの出力結果です。

変更トラッキングバージョンに基づいて、ソースの変更済みレコードを正常にクエリしました。ソリューションを自動化するには、変更トラッキングバージョンを保存する必要があります。変更トラッキングバージョンをアンカーテーブルと呼ばれる別のテーブルに保存できます。これにより、前回の同期以降の変更のみを取得できます。

ターゲットにアンカーテーブルを作成

リンクサーバーを使用してソースのレコードを照会した後、ターゲットにアンカーテーブルを作成します。ここでは、次のコマンドを使用して Northwind データベース内に CT_ANCHOR_NORTHWIND という名前のテーブルを作成します。このテーブルはアンカーとして機能し、データがレプリケーションされるたびに変更トラッキングバージョン番号を保持します。

CREATE TABLE ct_anchor_northwind
(
sch_name varchar(256),
tbl_name varchar(256),
ct_fetched_ver int, -- 0 during first execution
ct_next_ver int  -- current version at the source database , to fetch next version of the records
)

アンカーテーブルを作成した後、追跡する各テーブルの行を挿入します。ここでは、Northwind データベースの customers テーブルのスキーマ名、顧客名、取得した変更追跡バージョン、次の変更トラッキングバージョンの値を挿入します。

insert into ct_anchor_northwind (sch_name,tbl_name,ct_fetched_ver,ct_next_ver) values ('dbo' ,'customers',0,0)

次のスクリーンショットは私たちの出力結果です。

各レプリケーションでは、CT_NEXT_VER カラムを手動またはスクリプトを使用して変更トラッキングテーブルの現在のバージョン ID で更新する必要があります。この値を使用して、次の変更されたレコードのセットを取得できます。

次の図は、各同期中にバージョン番号を維持する方法を説明しています。

ソースからデータの変更部分を抽出してターゲットテーブルにロード

ソースの SQL Server とターゲットの Babelfish for Aurora PostgreSQL 間でデータを同期するには、次の手順を実行します。これらの手順はスケジュールに従って実行することもできます。

ターゲットデータベースに接続し、アンカーテーブル (ct_northwind_anchor) から ct_next_ver( 次にレコードを取得するバージョン ) の値を @ct_next_ver という変数に取得します。
```
declare @ct_next_ver varchar(9)
SELECT @ct_next_ver = ct_next_ver FROM ct_anchor_northwind WHERE sch_name = 'dbo'  and tbl_name =  'customers'
print @ct_next_ver
-- result: 0
```
SQL

変数 @ct_src_current_ver にソースからの現在の変更トラッキングバージョンを取得します。ターゲットでリンクサーバーのクエリを使用して値を取得できます。

declare @ct_src_current_ver varchar(9)
SELECT @ct_src_current_ver  = src_current_ver FROM OPENQUERY(ls_northwind,'select change_tracking_current_version () as src_current_ver from northwind.sys.change_tracking_tables')
print @ct_src_current_ver
-- result: 10

ソーステーブルの変更された行の主キー値に一致するターゲットのレコードを、特定の変更トラッキングバージョン (@ct_next_ver) に基づいて削除します。

DELETE FROM [dbo].[customers] from [dbo].[customers] x
JOIN ( SELECT * from openquery(ls_northwind,'select x.[customerid] FROM [northwind].[dbo].[customers] x  JOIN changetable(changes [northwind].[dbo].[customers],0) as y on x.[customerid]=y.[customerid] ')) y on x.[customerid]=y.[customerid] ;
-- result : 2 rows affected

特定の変更トラッキングバージョン (@ct_next_ver) に基づいて、ソースから新しいレコードを抽出し、それらの宛先に配置します。

INSERT INTO [dbo].[customers]([customerid],[companyname],[contactname],[contacttitle],[address],[city],[region],[postalcode],[country],[phone],[fax])
SELECT * FROM OPENQUERY(ls_northwind,'select x.[customerid], x.[companyname], x.[contactname], x.[contacttitle], x.[address], x.[city], x.[region], x.[postalcode], x.[country], x.[phone], x.[fax] from [northwind].[dbo].[customers] x  join changetable(changes [northwind].[dbo].[customers],0) as y on x.[customerid]=y.[customerid] ')
-- result: 2 rows affected

アンカーテーブルの列の値を適切な値で更新してください。これらの値は次の実行時に次のセットを取得するために使用されます。
```
UPDATE ct_anchor_northwind  SET ct_fetched_ver = @ct_next_ver  , ct_next_ver = @ct_src_current_ver
WHERE sch_name = 'dbo' and tbl_name = 'customers'
```
SQL
ターゲットテーブルの値をソースと照合して、同期を確認してください。
```
SELECT * FROM customers WHERE customerid  = 'alfki'
```
SQL

クリーンアップ

今後の課金を避け、このユースケースのテスト中に作成されたコンポーネントを削除するには、以下の手順を実行してください。

SSMS を使用して、ソースの SQL Server インスタンスに接続します。
マスターデータベースを選択し、[新しいクエリ] を選択します。
次のコマンドを実行してください。

Drop database Northwind
GO

まとめ

この投稿では、リンクサーバーを使用した変更トラッキングによって、SQL Server データベースを Babelfish for Aurora PostgreSQL に移行する方法を示しました。この構成により、最小のダウンタイムで SQL Server ワークロードを Babelfish for Aurora PostgreSQL に移行できます。このソリューションは、スクリプトをスケジュールされたジョブとして実行することで自動化できます。

翻訳はソリューションアーキテクトの Yoshinori Sawada が担当しました。原文はこちらです。

著者について

Chandra Pathivada は、アマゾンウェブサービスのシニアデータベーススペシャリストソリューションアーキテクトです。Amazon RDS for PostgreSQL や Amazon Aurora PostgreSQL などのオープンソースデータベースエンジンを中心に Amazon RDS チームで働いています。AWS 上でリレーショナルデータベースワークロードを設計、デプロイ、最適化するお客様をサポートすることを楽しんでいます。

Minesh Chande は、アマゾンウェブサービスのシニアデータベーススペシャリストソリューションアーキテクトです。彼は様々な業界のお客様が SQL Server のワークロードを Amazon RDS、Amazon RDS Custom、Babelfish for Aurora PostgreSQL などのマネージドデータベースプラットフォームに設計、移行、最適化するのを支援しています。

Amazon Web Services ブログ