SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Data Sync の基本構成について

leave a comment

SQL Data Sync の基本構成について少しまとめてみたいと思います。
今回はオンプレミスの SQL Server と SQL Database 間の同期設定を対象としています。

基本的な情報については SQL データ同期プレビュー が参考になります。
SQL Data Sync は Sync Framework を元に作成されているようですので詳細な動作に関してはこのあたりも確認をしておくとよいかもしれないですね。

なお、旧ポータルからの操作は 5 月で実施できなくなるようです。
image

フィルターに関しては現状のポータルでは作成できなくなるようですのでこの辺は注意が必要そうですね。

 

■Sync Agent のインストール


SQL Data Sync をインストールするためには、SQL Server の Feature Pack から以下のモジュールをインストールする必要があります。
image

  • Shared Management Objects
  • System CLR Types

気を付けるポイントとしては英語版のモジュールをインストールしないと Data Sync Agent をインストールする際の前提チェックを通すことができません。

前提モジュールがインストールできていると Data Sync のエージェントをインストールすることができます。
エージェントは Azure のポータルから SyncAgent を作成する際にダウンロードすることができます。
エージェントを作成する際にダウンロードを忘れた場合には、再度エージェントの作成を行うことで以下のダイアログが開きますのでエージェントをダウンロードすることができます。

Sync Agent は共通のモジュールでインストール後に設定するキーによって接続先が変わりますので、エージェント自体はどのサブスクリプションや新規エージェントの画面からダウンロードしても差はないかと思います。
image

エージェントはサービスとしてインストールされ、サービスの起動アカウントが SQL Server に接続ができる必要があります。インストールが完了すると [Microsoft SQL Data Sync] というサービスがインストールされます。
image

■Sync Agent の設定


Data Sync のインストールが終わるとスタートから Data Sync Agent を起動することができるようになり、オンプレミスの SQL Server 側の設定はここから実施します。
image

設定画面はシンプルで以下のような画面しかありません。
image

エージェントの設定ですが、最初にポータルから エージェントのキーを取得します。
image
image

取得したキーをオンプレミスの環境の設定画面に登録するとデータの同期対象のデータベースを選択することができるようになります。
# Ping Sync Service で PING を実行できるかは確認しておいたほうがよいかもしれないですね。
image

こちらがデータベースを追加した画面になります。同期をするデータベースを追加するとポータルから DataSync の対象として選択することができるようになります。
# ポータルから選択できるようになるには少し時間がかかるようですが。
image

■Data Sync の設定


Data Sync は

  • 同期エージェント
  • 同期グループ

の 2 種類で構成されます。

実際の同期の設定は同期グループで設定し、このグループ単位で同期間隔や同期対象のオブジェクトを設定することになります。

  • マスターテーブル
  • トランザクションテーブル
  • 頻繁に更新が行われる大量データ件数のテーブル

というような同期のタイミングを個別に設定したい場合にはグループを複数作成して同期タイミングを分けるとよいと思います。

同期の構成は旧ポータルの画面がイメージしやすいと思います。
image

オンプレミス / または Azure の SQL Database 間をハブに設定した SQL Database を介して同期を行うための仕組みが Data Sync となります。
この画面の項目を設定するための管理単位が [同期グループ] となります。
imageimageimage

同期グループを設定したら [同期規則] を設定します。
この同期規則を設定したタイミングで SQL Server / SQL Database にオブジェクトが作成されます。
imageimageimage

Data Sync はプライマリーキーが設定されているテーブルをデータ同期の対象とすることができます。
そのため、プライマリキーが設定されていないテーブルに関してはデータ同期の対象として選択をすることはできません。

同期規則を設定して保存をしたタイミングで各データベースに以下のようなテーブルが作成されます。

  • DataSync.provision_marker_dss
  • DataSync.schema_info_dss
  • DataSync.scope_config_dss
  • DataSync.scope_info_dss
  • DataSync.<テーブル名>_dss_tracking

また、ストアドプロシージャが同期対象のテーブル単位に作成されます。
# GUID は管理用のテーブルの config_id (scoepe_config_id) と関連づいているようですね。
image

各テーブル向けにデータ変更のトラッキング用のテーブルが作成され、その他のテーブルは DataSync の設定を管理するための設定になります。
DataSync.scope_config_dss を見るとどのテーブルのどの列を同期するように設定しているかの XML を確認することができますので、どのような設定になっているかをクエリで確認する場合にはこのテーブルを参照するとよいかと。
image

 

■DataSync の基本的な仕組み


細かな実装については以下の技術情報が参考になります。

同期ループの理解と回避
方法: 既存の同期グループを変更する (SDS)

DataSync の基本的な実装ですがトリガーを使用した INSERT / UPDATE / DELETE をトラッキングになります。
同期規則を設定すると同期対象のテーブルにトリガーが作成されます。
image

同期の設定をしているテーブルに対してトリガーにより変更が追跡されその結果が [DataSync.Table_1_dss_tracking] に格納されます。
トラッキング用のテーブルは、元となるテーブルのキー項目を使用して作成されており、レコード単位で変更の履歴が作成されます。
image

その内容がサービスとして起動している [Microsoft SQL Data Sync] によって SQL Database と同期が行われるという仕組みになっています。
# SQL Database 間も基本的な仕組みは同じかと。

image

トリガーをベースにデータの変更を追跡していますので Truncate Table は一括挿入のようなトリガーが動作しないような変更をするとその変更は同期されないので注意が必要です。
# 一括挿入については FIRE_TRIGGERS を設定した場合は別だと思いますが。

 

サービスが常駐していることは [sys.dm_exec_sessions] 等から確認することができます。
image

アプリメーション名が [.Net SqlClient Data Provider] となっていますのでこれを確認すると、どのような動作をしているか追うことができます。

プロファイラで以下のようなフィルターを設定すると同期の動作を詳細に追うことができると思います。
image

DataSync は同期先のテーブルにデータが入っている状態からも初期同期を開始できるようですが、20,000 件程度のデータが同期元 / 同期先のテーブルに入っている状態で動機を開始したところ同期にかかる時間は以下のようになりました。
# 同期元と同期先でデータ件数は差を出した状態で開始しています。

Sync completed successfully in 4233.82 seconds.    
Upload:   20030 changes applied   
Download: 20030 changes applied

 

データが入っていない状態だと以下のような結果になります。
データが入っている状態からのデータ同期はかなり時間がかかりそうな雰囲気ですね。
# 両方のテーブルの全件比較をして競合の解決や同期の方向によって処理は変わるかもしれませんが。

Sync completed successfully in 35.27 seconds.    
Upload:   20030 changes applied   
Download: 0 changes applied

 

初期同期をマイグレーションウィザードや bacpac によって実施してから、Data Sync の同期を開始すれば同期時間が短くなるかなとも考えたのですが、なかなかうまくいかないようですね。
# 差分同期についてはデータの論理削除や物理削除を考えるとハードルが高くなったりもしますが。

トリガーによるデータトラッキングと同期タイミングによるデータ連携が行われるということが分かっていれば動作を詳細に追っていくことはできそうですね。
# 同期が行われるタイミングで作成されたストアドが実行されているようですね。

Written by masayuki.ozawa

5月 12th, 2013 at 9:10 pm

Posted in SQL Azure,SQL Server

Tagged with ,

Leave a Reply

*