DMSを使ってSQL ServerのデータをS3に出力する

こんにちは、小澤です。

今回は、AWS Database Migration Service(以下DMS)を利用して、SQL ServerにあるデータをS3に保存する話を書きたいと思います。

動作環境

今回は、以下の環境で動作させています。

• AMI
  • Windows_Server-2008-R2_SP1-Japanese-64Bit-SQL_2008_R2_SP3_Standard-2017.03.15(ami-07f0ac60)
  • ※ コミュニティ AMIを利用
• インスタンスタイプ
  • m4.large
• SQL Server
  • SQL Server 2008R2

今回はchange data capture mode (以下CDC)も行うため、RDSは利用せず、EC2インスタンス上にSQL Serverを立てています。

SQL Server on EC2の動かし方

EC2上でSQL Serverを動かす際の設定などについては解説がすでにあるのでそちらをご覧ください。

• SQL Server on EC2でインスタンス作成時のハマりポイントまとめ

また、インスタンスのセキュリティグループのインバウンド設定で、以下の2つにアクセス可能な状態になっているものとします。

この後の内容はSQL Server認証で外部からアクセス可能な状態になっているものとします。

DMSの設定

DMSの基本的な利用法については以下の内容をご参照ください

• AWS Database Migration Serviceを試してみた

今回の目的である、S3への出力以外は他のtargetへの出力と同じですので、エンドポイントのtarget以外の設定に特別な設定は必要ありません。

sourceのエンドポイントでは「sqlserver」を選択し、必要な項目を入力します。

targetにS3を選択する

targetのエンドポイントを作成する際にターゲットエンジンでS3を選択すると、以下のような項目に切り替わります。

設定する項目は、ARN、S3のバケット、フォルダの3つとなります。

ARNの設定では、S3に対して適切な権限を与えたロールのARNを設定します。 必要な権限については、公式のドキュメントに記載されており、PutObject, GetObject, ListBucketの3つとなります。

(参考：Using Amazon S3 as a Target for AWS Database Migration Service)

{
    "Version": "2012-10-17",
    "Statement": [
       {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::<bucket_name>*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::<bucket_name>*"
            ]
        }
    ]
}

また、DMSから利用するために、信頼関係にdms.amazonaws.comを設定しておきます。

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "dms.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

出力は、設定したバケット、フォルダのしたに、スキーマ名、テーブル名でフォルダ分けされます。

接続テストを行って正常に接続可能なことを確認すれば、あとは他をtargetとした際と同様にタスクを作って実行することでS3にファイルが出力されることを確認できます。

その他の設定

エンドポイント作成時に「アドバンスト」を展開することでいくつか追加の設定を行うことができます。 設定できる項目は、先ほど参考にあげた公式ドキュメントの下部にある「Extra Connection Attributes」で確認することができます。

設定する値は、ドキュメントにもある通り、

<設定項目>=<値>;

の形式で記載していく形式になります。 この中には、区切り文字や圧縮の設定は比較的よく使うものかと思います。

CDCの利用

次にCDCを使って、sourceとなるSQL Serverにデータが追加された際に自動で取り込んでくれるように設定します。 こちらは、DMSの設定というより、SQL Server側での設定項目の解説が主となり、S3以外をtargetとした際にも同様の手順となりますが、解説します。

設定する項目は公式ドキュメントのMicrosoft SQL Server データベースの AWS Database Migration Service のソースとしての使用にある「SQL Server ソースから変更データキャプチャ (CDC) を行うときの特別な制限事項」に記載されたものになります。

CDCに対応したものはEnterprise、Standard、Developerのいずれかということなので、今回はStandard Editionを利用しています。

また、以降はSQL Server Management Studioを利用しているものとして、そのUI上で操作を行っていきます。

データベースの完全バックアップ

まず、データベースの完全バックアップを行います。 オブジェクトエクスプローラから、対象となるデータベースを右クリックし、タスク > バックアップ を選択します。

バックアップの設定画面が開くので、「バックアップの種類」を「完全」にして、必要に応じて他の項目も変更したのち、OKを選択するとバックアップが作成されます。

復旧モデルの設定

次に復旧モデルの設定を行います。

オブジェクトエクスプローラから、対象となるデータベースを右クリックして、プロパティを選択します。

データベースのプロパティが表示されたら、「ページの選択」から「オプション」を選択し、「復旧モデル」の項目を「完全」か「一括ログ」に設定します。

レプリケーションパブリッシャの設定

最後に、レプリケーションパブリッシャを使いして、トランザクションログとして新規で発生したデータをDMSが取り込めるようにします。

オブジェクトエクスプローラにある、レプリケーション > ローカルパブリッシャ を右クリックして、「新しいパブリケーション」を選択します。

「パブリケーションの新規作成ウィザード」に従って、対象のデータベースを選択したのち、「パブリケーションの種類」で「トランザクション パブリケーション」を選択します。

アーティクルでは、テーブルにチェックを入れます。 また、この際、主キーのないテーブルはトランザクションログ生成ができないのでご注意ください。

残りの項目は必要に応じて設定を行ってください。

タスクの設定

SQL Serverの設定が完了すれば、あとはDMSのタスクの設定を行うのみです。

タスク作成時の「移行タイプ」を利用法に合わせて、「Migrate existing data and replicate ongoing changes」か「Replicate data changes only」を選択してください。

「レプリケーション進行中」になれば正常に動いています。

SQL Serverにデータを追加すると、タイムスタンプ付きのファイルがS3に新たに追加されているのが確認できます。

終わりに

今回はDMSを使って、オンプレ環境にあるSQL ServerのデータをS3に保存する方法について書きました。 このような使い方は、オンプレ環境からクラウドへの移行に際して、分析で利用するためのデータを保存するためのデータレイクとして活用する際などに効果を発揮するのではないかと思います。