MENU
AWSを中心としたクラウドインフラ技術サイト
  1. ホーム
  2. AWS
  3. 【AWS】DMS について詳細調査（SQL Server → Redshift）

【AWS】DMS について詳細調査（SQL Server → Redshift）

AWS DMS データベース

DMS とは何か

AWS DMS（AWS Database Migration Service）は、DB 間でデータを移行・継続同期するためのマネージドサービスです。

AWSインフラ研究所
【AWS】DMSの基本
https://go-journey.club/archives/17499
AWS DMS とはDMS は AWS Database Migration Service の略で、簡単に言うと、あるデータベースのデータを、別のデータベースや S3 などの保存先へ移行・同期するための AWS サービスです。リレーショナルDB、データウェアハウス、NoSQL DB、その他データストアの移行に使えるサービスと説明されています。DMS は、元DBを読み取り、DMSが中継し、先DBへデータを流す仕組みです。以下のイメージです。SQL Server / MySQL / PostgreSQL / Oracle など ↓ AWS DMS ↓Redshift / RDS / Aurora / S3 などこのように、DBのデ...

SQL Server → Redshift の場合は、以下のような役割です。

SQL Server
  ↓
AWS DMS レプリケーションインスタンス
  ↓
一時的な CSV / 変更データ
  ↓
S3
  ↓
Redshift COPY
  ↓
Redshift テーブル

DMS のタスクでは、主に次の3つの移行方式があります。

方式内容用途
フルロード（再ロード、Full Load）既存データを一括コピー初回移行
CDC only変更分だけ連携既にターゲットに初期データがある場合
フルロード + CDC初期データをコピーし、その後の変更も継続反映本番系の継続レプリケーションでよく使う

DMS タスクは Full load、Full load + CDC、CDC only を選択でき、Full load + CDC では既存データをロードしながらソース側の変更を捕捉し、フルロード後に変更分をターゲットへ適用すると説明されています。

DMS の主要コンポーネント

DMS は、主に以下で構成されます。

コンポーネント役割
レプリケーションインスタンスDMS タスクを実行する実体。ソースから読み、ターゲットへ書く処理を行う
ソースエンドポイントSQL Server への接続情報
ターゲットエンドポイントRedshift への接続情報
レプリケーションタスクどのテーブルを、どの方式で、どの設定で連携するか
テーブルマッピング対象スキーマ・テーブル・除外条件・変換ルール
タスク設定 JSONタスク設定 JSON

DMS タスクには、レプリケーションインスタンス、ソースエンドポイント、ターゲットエンドポイント、移行方式、テーブルマッピング、CloudWatch ログなどを指定します。

SQL Server → Redshift の全体像

SQL Server → Redshift の DMS は、SQL Server のトランザクションログ / CDC 情報を読み取り、Redshift に反映する仕組みです。

ただし Redshift は OLTP DB ではなく、分析向けの DWH です。そのため、DMS は Redshift に対して1行ずつ直接 JDBC で INSERT / UPDATE / DELETE するというより、S3 を中間置き場として使い、Redshift の COPY でまとめて取り込む動きになります。

OLTP とは、Online Transaction Processing の略で、オンライントランザクション処理とも言います。日々の業務で発生するデータの登録・更新・削除を高速に処理する仕組みです。

Redshift がターゲットの場合、DMS はまずデータを CSV ファイルにエクスポートし、S3 バケットへアップロードし、その後 Redshift の COPY コマンドで対象テーブルにロードすると説明されています。

Amazon Redshiftへのデータベース移行中、AWS DMSはまずデータをAmazon S3バケットに移動します。ファイルがAmazon S3バケットに格納されると、AWS DMSはそれらをAmazon Redshiftデータウェアハウス内の適切なテーブルに転送します。AWS DMSは、Amazon Redshiftデータベースと同じAWSリージョンにS3バケットを作成します。AWS DMSレプリケーションインスタンスも、同じAWSリージョンに配置する必要があります。

AWS DMS は、Amazon S3 バケットを使用してデータを Amazon Redshift データベースに転送します。AWS DMS がバケットを作成するには、コンソールで IAM ロールを使用します 
dms-access-for-endpoint。AWS CLI または DMS API を使用して、ターゲット データベースとして Amazon Redshift を指定してデータベース移行を作成する場合は、この IAM ロールを作成する必要があります。

Amazon Redshift ターゲットの中間ストレージとして使用する S3 バケットでは、バージョン管理を有効にしないでください。S3 バージョン管理が必要な場合は、ライフサイクル ポリシーを使用して古いバージョンを積極的に削除してください。そうしないと、S3
list-object 呼び出しのタイムアウトによりエンドポイント テストの接続が失敗する可能性があります。

DMS ログを調査すると S3 バケットへのアップロードなどが出てきます。SQL Server → Redshift のレプリケーションで S3 は中間領域として使われます。

初回フルロードと CDC の流れ

たとえば SQL Server に以下のテーブルがあるとします。

CREATE TABLE dbo.user (
    id INT NOT NULL PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    status NVARCHAR(20) NULL,
    updated_at DATETIME2 NOT NULL
);

DMS タスクを Full load + CDC で開始すると、流れは以下になります。

フェーズ1：Full Load（フルロード）

最初に、SQL Server の既存データを読みます。

SELECT * FROM dbo.user;

DMS は取得したデータを内部的に CSV 化し、S3 に置き、Redshift の COPY で取り込みます。

SQL Server dbo.user
  ↓
DMS
  ↓
S3: 一時CSV
  ↓
Redshift COPY
  ↓
Redshift public.user

Redshift ターゲットでは、フルロード（Full Load）時に DMS がソースレコードを CSV ファイルへ変換し、S3 の BucketFolder/TableID 配下へロードし、COPY 完了後にファイルを削除する仕様です。CDC 時は NetChanges テーブル用のパスへ変更データをロードします。

フェーズ2：Full Load 中の変更をキャッシュ

Full Load 中にもアプリケーションは SQL Server を更新し続ける可能性があります。

INSERT INTO dbo.user (id, name, status, updated_at)
VALUES (101, N'山田', N'active', SYSDATETIME());

UPDATE dbo.user
SET status = N'inactive', updated_at = SYSDATETIME()
WHERE id = 10;

DELETE FROM dbo.user
WHERE id = 20;

DMS はフルロード（Full Load）中に発生した変更を CDC として捕捉し、あとで Redshift に反映します。

フェーズ3：CDC 継続同期

Full Load が終わった後、DMS は SQL Server の変更ログを読み続けます。

SQL Server の INSERT / UPDATE / DELETE
  ↓
DMS が変更を捕捉
  ↓
S3 に変更データを一時出力
  ↓
Redshift に反映

DMS は CDC では DB エンジンのログを使って変更を収集します。変更適用が追いつかない場合、DMS はレプリケーションインスタンス上に変更をバッファし、メモリが足りなくなるとディスク上の Change Cache に、一時的にスピルする（メモリからディスク溢れだす）ことがあります。

SQL Server 側の CDC の考え方

SQL Server を DMS のソースにして継続レプリケーションする場合、DMS は SQL Server 側で変更を追跡できる状態を必要とします。

公式ドキュメントでは、SQL Server ソースで CDC を使うには、SQL Server が Full Backup 構成であること、リカバリモデルが FULL または BULK_LOGGED であること、DMS の CDC には SQL Server 側でフルトランザクションログが必要であり、MS-Replication または SQL Server CDC を有効にする必要があると説明されています。

BULK_LOGGED

BULK_LOGGED とは、SQL Server の 復旧モデル（Recovery Model） の1つです。

大量データ投入やインデックス作成などの一部のバルク処理で、トランザクションログの出力量を減らすためのモードです。

SQL Server の復旧モデルには主に以下があります。

復旧モデル（リカバリモデル）概要CDC 利用
SIMPLEログバックアップなし。復旧できるのは基本的にバックアップ時点まで✖
FULLすべての変更をログに記録。任意時点復旧が可能〇
BULK_LOGGEDFULL に近いが、一部の大量処理を最小ログ記録にする〇

オンプレミスや EC2 上の SQL Server の場合

オンプレミスや EC2 上の SQL Server では、DMS は基本的に以下の使い分けをします。

テーブル状態DMS の変更取得方式
主キーありSQL Server Replication
主キーなしSQL Server CDC

self-managed SQL Server では、主キーありテーブルの変更捕捉に MS-Replication、主キーなしテーブルの変更捕捉に MS-CDC を使うと説明されています。

RDS for SQL Server の場合

Amazon RDS for SQL Server は MS-Replication をサポートしないため、DMS は主キーあり・なしに関係なく MS-CDC を使う必要があると説明されています。

そのため、RDS SQL Server → Redshift の構成では、データベースレベル CDC とテーブルレベル CDC が非常に重要です。

MS-Replication

MS-Replication は、正式には Microsoft SQL Server Replication のことです。

SQL Server にあるデータやDBオブジェクトを、別のDBへコピー・配布し、その後も同期するための SQL Server 標準機能です。Replication は「あるDBから別のDBへデータやDBオブジェクトをコピー・配布し、整合性を保つために同期する技術群」と説明されています。

テーブルレベル CDC が重要な理由

SQL Server の CDC には、ざっくり2段階あります。

データベースレベル CDC 有効化
  ↓
テーブルレベル CDC 有効化

たとえば SQL Server では以下のような状態です。

-- DB レベル CDC 確認
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = 'mydatabase';

-- テーブルレベル CDC 確認
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.is_tracked_by_cdc
FROM sys.tables t
JOIN sys.schemas s
  ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
  AND t.name = 'mytable';

is_tracked_by_cdc = 1 なら、そのテーブルは SQL Server の CDC 対象です。

逆に is_tracked_by_cdc = 0 の場合、そのテーブルは SQL Server CDC の変更追跡対象ではありません。

特に RDS for SQL Server → DMS → Redshift の構成では、RDS 側で MS-Replication が使えないため、テーブルレベル CDC が無効なテーブルは、継続変更や DDL 追跡で期待通りに動かない可能性が高くなります。

Redshift ターゲット特有の注意点

Redshift は RDBMS というより DWH なので、DMS のターゲットとして使う場合にいくつか注意点があります。

S3 中間バケットを使う

Redshift ターゲットでは、DMS は S3 バケットを中間領域として使います。Redshift クラスターはレプリケーションインスタンスと同一 AWS アカウント・同一リージョンである必要があり、DMS は Redshift と同じリージョンに S3 バケットを作成・利用します。

そのため、DMS のログに以下のような S3 関連のログが出るのは自然です。

Uploading file to S3...
COPY command to Redshift...
BucketFolder...
NetChanges...

Redshift は主キーを強制しない

Redshift では主キー制約を定義できても、一般的な OLTP DB のように一意性を強制するわけではありません。Redshift は主キーを強制しないため、DMS タスク再開時に CDC が再適用されると重複レコードが出る可能性があると説明されています。

つまり、Redshift 側で分析用途に使う場合は、必要に応じて以下を考える必要があります。

SELECT id, COUNT(*)
FROM usertable
GROUP BY id
HAVING COUNT(*) > 1;

または、データマート側で ROW_NUMBER() を使って最新行だけを見る設計にすることもあります。

Redshift では、主キー・一意制約・外部キー制約は informational only、つまり情報目的のみで、データ投入時に強制されません。制約に違反するデータを INSERT しても成功する場合があります。

普通のDBの主キー

SQL Server や MySQL などでは、主キーを付けると 同じ主キーの行を2件入れることはできません。

たとえば SQL Server でこういうテーブルがあるとします。

CREATE TABLE dbo.usertable (
    id INT NOT NULL PRIMARY KEY,
    name NVARCHAR(100)
);

この場合、以下は1回目は成功します。

INSERT INTO dbo.usertable (id, name)
VALUES (1, N'山田');

しかし、同じ id = 1 をもう一度 INSERT するとエラーになります。

INSERT INTO dbo.company (id, name)
VALUES (1, N'ABC株式会社');

SQL Server 的には、

id = 1 はすでに存在するので、2件目は入れません

となります。

つまり、SQL Server の主キーは 重複を物理的に防いでくれる制約です。

Redshift の主キーは違う

Redshift でも、文法上はこう書けます。

CREATE TABLE public.usertable (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(100)
);

一見すると、SQL Server と同じように見えます。

しかし Redshift では、この PRIMARY KEY は 一意性を強制しません。

つまり、以下のように同じ id = 1 を2回 INSERT できてしまいます。

INSERT INTO public.usertable (id, name)
VALUES (1, '山田');

INSERT INTO public.usertable (id, name)
VALUES (1, '山田');

その結果、Redshift 側はこうなり得ます。

id | name
---+--------------
1  | 山田
1  | 山田

これが、

Redshift は主キーを強制しない

という意味です。

Redshift の PRIMARY KEY は何のためにあるのか

Redshift の主キーは、主に クエリ最適化のヒントとして使われます。

Redshift に対して、この id は本来ユニークなはずですよ と教えるための情報です。

Redshift のドキュメントでも、主キーや外部キーはクエリプランナーが統計計算や JOIN 最適化に使うヒントであり、Redshift は「ロードされたキーが正しい」と仮定して使うと説明されています。

つまり、Redshift の主キーはこうです。

SQL Server の主キー
  → 重複を禁止するルール

Redshift の主キー
  → この列はユニークなはず、というヒント

なぜ Redshift はそんな仕様なのか

Redshift は、SQL Server のような業務アプリ用 DB ではなく、分析用 DWH です。

目的が違います。

SQL Server / MySQL / PostgreSQL
  → アプリケーションの正確な更新・参照を支える OLTP DB
  → 1件ずつの INSERT / UPDATE / DELETE の整合性が重要

Redshift
  → 大量データを高速に集計・分析する DWH
  → COPY で大量ロードし、分析クエリを速く処理することが重要

そのため Redshift では、主キー制約を厳密にチェックしてロードを止めるよりも、大量データを高速に取り込むことを優先しています。

UPDATE / DELETE は主キーが重要

Redshift ターゲットで CDC の変更適用を効率化するには、ソース・ターゲット双方に Primary Key があることが重要です。Redshift ターゲットで CDC 中の変更を扱うには BatchApplyEnabled=true が推奨され、Primary Key が必要で、Primary Key がない場合は変更がステートメント単位で適用され、ターゲット遅延やコミットキューへの影響が出る可能性があると説明されています。

DDL処理（変更処理）のタスク設定

以下の設定は、AWS DMS が変更データキャプチャ (CDC) 中にターゲットテーブルのデータ定義言語 (DDL) の変更をどのように処理するかを決定します。

変更処理 DDL を処理するためのタスク設定には以下があります。

  • HandleSourceTableDroppedtrueの場合、ソーステーブルが削除されたときにターゲットテーブルも削除されます。
  • HandleSourceTableTruncatedtrueの場合、ソーステーブルが切り捨てられたときにターゲットテーブルも切り捨てられます。
  • HandleSourceTableAlteredtrueの場合、ソーステーブルが変更されたときにターゲットテーブルも変更されます。

以下は、変更処理DDLを扱うタスク設定がタスク設定JSONファイル内でどのように表示されるかの例です。

                "ChangeProcessingDdlHandlingPolicy": {
                   "HandleSourceTableDropped": true,
                   "HandleSourceTableTruncated": true,
                   "HandleSourceTableAltered": true
                },

DDL とは

DDL は Data Definition Language の略で、テーブル構造を変えるSQLです。

代表例は以下です。

ALTER TABLE dbo.usertable ADD new_col nvarchar(100) NULL;
DROP TABLE dbo.usertable;
TRUNCATE TABLE dbo.usertable;

つまり、通常のデータ変更である INSERT / UPDATE / DELETE とは違い、テーブルそのものの定義や状態を変える操作です。

AWS DMS がサポートする DDL には、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE、ADD COLUMN、DROP COLUMN、RENAME COLUMN、CHANGE COLUMN DATA TYPE などがあります。ただし、DMS はすべてのソースエンジンですべてのDDLを同じように捕捉するわけではなく、ターゲットエンジンによって適用方法も変わります。

すべての ALTER が必ず反映されるわけではないので注意

HandleSourceTableAltered=true は「すべての ALTER が必ず反映される」という意味ではありません。

サポートされる DDL は、ソース・ターゲットエンドポイントの仕様に依存します。公式ドキュメントでも、どの DDL がサポートされるかは各エンドポイントのトピックを確認するよう記載されています。

たとえば Redshift ターゲットでは、以下のような DDL はサポート対象外です。

ALTER TABLE table_name
MODIFY COLUMN column_name data_type;

Redshift ターゲットの制限として、ALTER TABLE ... MODIFY COLUMN ... はサポートされないと明記されています。

また SQL Server ソースでは、DMS は ALTER TABLE による DEFAULT の設定・解除や、NULL / NOT NULL の変更を change processing としてサポートしません。

そのため、以下のような DDL は注意です。

ALTER TABLE dbo.usertable
ADD new_col NVARCHAR(100) NULL
    CONSTRAINT DF_apply_new_col DEFAULT N'';

この場合、DMS が Redshift 側にカラムを追加できたとしても、DEFAULT 制約そのものまで同じ意味で反映されるとは限りません。
DMS の DDL 連携は、あくまで「対応している範囲のスキーマ変更をターゲットに反映する」ものです。

「カラム追加が連携されるテーブル / されないテーブル」が出る理由

SQL Server → Redshift の DMS で以下のような差が出ることがあります。

テーブルテーブルレベル CDCALTER ADD COLUMN の反映
dbo.usertable有効反映される
dbo.companytable無効反映されない / 不安定

この場合、考え方としては以下です。

DMS は CDC タスク中に DDL を検知してターゲットへ反映します。しかし SQL Server 側でそのテーブルが DMS の継続変更捕捉対象として正しく扱われていない場合、DMS がそのテーブルの変更や DDL を安定して認識できません。

特に RDS for SQL Server の場合、DMS は MS-CDC に依存するため、テーブルレベル CDC が無効なテーブルは、CDC 連携対象として期待通りに動かない可能性が高いです。RDS for SQL Server では MS-Replication が使えず、DMS は MS-CDC を使う必要があるためです。

まとめると以下のようになります。

テーブルレベル CDC 有効
  → DMS が変更対象として正しく追跡しやすい
  → ALTER ADD COLUMN も反映される可能性がある

テーブルレベル CDC 無効
  → DMS がそのテーブルの変更を正しく追えない
  → ALTER ADD COLUMN が反映されない可能性がある

更に CDC 有効化のタイミングも関連する

テーブルレベル CDC が有効かどうかに加えて、その CDC がいつ有効化されたかも関係します。

特に関係するタイミングはこの3つです。

1. DMS タスク開始前に、テーブルレベル CDC が有効だったか
2. ALTER TABLE ADD COLUMN の前に、テーブルレベル CDC が有効だったか
3. DMS タスクがその ALTER を CDC 中の DDL として読める状態だったか

SQL Server CDC 側の重要な仕様

SQL Server でテーブルレベル CDC を有効化すると、その時点で capture instance と cdc.xxx_CT という変更テーブルが作られます。テーブルを CDC 有効化すると、そのテーブルに対する DML がトランザクションログに書かれ、CDC プロセスがそれを change table に書き出します。また sp_cdc_enable_table により sys.tables.is_tracked_by_cdc が 1 になります。

重要なのは、CDC の変更テーブルは CDC 有効化時点の captured columns を元に作られるという点です。cdc.<capture_instance>_CT の説明では、変更テーブルの列は capture instance 作成時に「captured columns」として識別された列で構成されます。

SQL Server CDC では、ソーステーブルに ADD COLUMN などの DDL が行われた場合、その DDL は cdc.ddl_history に記録されますが、既存の CDC 変更テーブルの定義には自動反映されません。列追加・列削除などの DDL は cdc.ddl_history に記録されるが、change table には適用されず、change table の定義は一定のままです。さらに、captured column list に存在しない列は capture process が無視します。

まとめると以下のようになります。

CDC 有効化
  ↓
cdc.dbo_xxx_CT が作られる
  ↓
その後で ALTER TABLE ADD new_col
  ↓
SQL Server の元テーブルには new_col が増える
  ↓
ただし既存の cdc.dbo_xxx_CT には new_col は自動では増えない

なので、「CDC テーブルに新カラムがない」こと自体は、SQL Server CDC の仕様としてあります。

DMS 側ではどう見えるのか

DMS は SQL Server の CDC / トランザクションログを使って継続レプリケーションします。AWS DMS の SQL Server がソース DB の場合、CDC ではトランザクションログから変更を読み取る必要があり、SQL Server 側で MS-Replication または Change Data Capture を有効にしてフルトランザクションログ記録を有効化する必要があります。

ただし、DMS が DDL を完全に何でも反映できるわけではありません。AWS DMS のベストプラクティスでは、DMS が継続レプリケーション中に反映する DDL は限定的であり、インデックス、ユーザー、権限、ストアドプロシージャなどテーブルデータに直接関係しない変更は伝播しないと説明されています。

さらに SQL Server ソースの制限として、DMS は DEFAULT の設定・解除、NULL / NOT NULL の変更、単一トランザクション内の DDL + DML のキャプチャなどをサポートしていません。

つまり DMS から見ると、

テーブルレベル CDC 有効
+ DMS タスクがそのテーブルを CDC 対象として見ている
+ ALTER TABLE ADD COLUMN が DMS の読める範囲で発生
+ DMS 側の DDL ハンドリング設定が true

この条件がそろうと、Redshift 側にカラム追加が反映される可能性が高くなります。

以下、パターン1～3 で、タイミング別に見てみます。

パターン1：CDC 有効化 → DMS タスク開始 → ALTER TABLE ADD COLUMN

テーブルレベル CDC 有効
  ↓
DMS タスク開始
  ↓
ALTER TABLE ADD new_col
  ↓
DMS が CDC 中の DDL として検知できる可能性あり
  ↓
Redshift 側に new_col が追加される可能性あり

この場合、DMS が ALTER をイベントとして見られる状態なので、HandleSourceTableAltered: true が効いて Redshift 側にカラムが追加される可能性があります。

ただし、SQL Server の cdc.xxx_CT に new_col が増えるとは限りません。
これは SQL Server CDC の仕様として、既存 change table の定義が自動で変わらないためです。

そのため、

SQL Server の CDC テーブルには新カラムがない
でも Redshift 側には新カラムがある

という状態はあり得ます。

パターン2：ALTER TABLE ADD COLUMN → その後にテーブルレベル CDC 有効化

ALTER TABLE ADD new_col
  ↓
その後でテーブルレベル CDC 有効化
  ↓
CDC capture instance は new_col を含む現在のテーブル定義を元に作られる
  ↓
以後の DML では new_col が CDC 対象に含まれる可能性が高い

この場合、SQL Server CDC の capture instance は カラム追加後のテーブル構造を元に作られます。sp_cdc_enable_table の @captured_column_list が NULL の場合、全列が change table に含まれる仕様なので、CDC 有効化時点で存在している new_col は captured columns に入る可能性が高いです。

ただし注意点があります。

このパターンでは、DMS から見ると ALTER TABLE ADD COLUMN という DDL イベント自体は、DMS タスク中に起きていない可能性があります。

そのため、Redshift 側の既存テーブルにそのカラムがなければ、DMS が自動で ALTER を流してくれるとは限りません。

つまり、

SQL Server 側の CDC には new_col が含まれる
でも Redshift 側テーブルには new_col がない
DMS は過去の ALTER を見ていない

となる可能性があります。

この場合は、Redshift 側に手動でカラム追加するか、DMS のテーブルリロードを検討することになります。

パターン3：DMS タスク開始時点では CDC 無効 → 後から CDC 有効化

DMS タスク開始
  ↓
対象テーブルのテーブルレベル CDC は無効
  ↓
その後で CDC 有効化
  ↓
DMS が自動でその変更を安定的に拾うとは限らない

DMS はタスク開始時に対象テーブルのメタデータや CDC 対象状態を見ています。後から SQL Server 側でテーブルレベル CDC を有効化しても、DMS タスクがその状態変化をきれいに再認識するとは限りません。

この場合は、少なくとも以下を確認した方がよいです。

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.is_tracked_by_cdc
FROM sys.tables t
JOIN sys.schemas s
  ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
  AND t.name = '対象テーブル名';

加えて、DMS タスク側で対象テーブルを 再ロード する、またはタスク再起動・再作成が必要になる可能性があります。

AWS DMS データベース
DMS redshift SQL Server

関連記事