CData Software Blog

クラウド連携のCData Software の技術ブログです。

SQL Gateway とリンクサーバーでSSMSから各データソースのデータをレプリカしてみる

こんにちは!エンジニアの宮本です!

今回はCData SQL Gateway(V18版までは CData SQL Broker) と SQL Server Management Studio(SSMS) を使って、 マーケティングオートメーションツールのMarketo や MySQL のデータをリンクサーバーとして取り込んで、SQLServer 上のデータベースにまるまるバックアップしていく方法をご紹介します。

SQL Gateway とは?

あらゆるODBC のデータソースをSQL ServerMySQL にてあたかも同じデータベース内の1つのテーブルとして扱うことを実現してくれる CData の製品となります。
ユースケースしては、例えばSQLServer上のテーブルにまるっとデータをレプリケーションしたり、逆にデータを更新したりといったところがあげられます。

ちなみに GUI 上でレプリケーションの設定ができる製品として、CData Sync というレプリケーションツールも展開しております。

CData Sync についてはコチラ↓↓
https://www.cdata.com/jp/sync/


全体構成

各データソースのデータをレプリカする構成は下図のようになります。
f:id:sennanvolar44:20190612181955p:plain ※図ではSQLBrokerとなっていますが、V19版からSQLGatewayという名前に変更しています。

SSMSで見れるようにするために設定する箇所は大きく分けて2点です。

  • 構成図の①部分:取得したいデータソース先と SQL Gateway 間の接続部分の設定
  • 構成図の②部分:SQL Gateway と SSMS 間の接続部分の設定

右側のカスタムアプリケーションや帳票と書かれている列を、今回の場合は「SQL Server Management Studio(SSMS)」と置き換えてください。

事前にインストールしておくもの


SQL Gateway の設定方法

まずは、「スタートボタン」→「CData SQL Gateway」→ 「SQL Gateway」 の順でクリックして起動します。
f:id:sennanvolar44:20190612193016p:plain

次に、構成図①部分にあたるデータソースと SQL Gateway との接続設定をしていきます。

「サービス」タブを選択し、追加ボタンをクリックします。
f:id:sennanvolar44:20190612193612p:plain

  • サービス名:任意ですが、今回は Marketo と付けます。
  • プロトコルSQLServer を使用するので TDS
  • データソース: Marketo 用のODBC データソース
  • ポート:SQLServer のデフォルトポートは1433なので、使っていれば他のポート番号を設定

f:id:sennanvolar44:20190612194412p:plain

MySQLの設定内容もほぼほぼ同じです。
f:id:sennanvolar44:20190612201824p:plain

次に、構成図②部分にあたる SQL Gateway と SSMS 間の接続設定をしていきます。
まずは、 SQL Gateway でユーザー設定をしていきます。ここでユーザー毎の設定をすることで、参照だけなのか更新許可するのかといった権限を付与することができます。

「ユーザー」タブを選択し、追加ボタンをクリックします。
f:id:sennanvolar44:20190612203046p:plain

  • ユーザー:任意ですが、今回は user と付けます。
  • パスワード:任意
  • 権限: MySQL、Marketo のみにFull 権限を付与

f:id:sennanvolar44:20190612203248p:plain

これで SQL Gateway の設定は完了なので、「変更を保存」をクリックし設定内容を保存します。 f:id:sennanvolar44:20190612203717p:plain

作成したサービスを選択しながら右にある「開始」ボタンをクリックしてサービスを立ち上げます。
f:id:sennanvolar44:20190612203946p:plain

なお、この SQL Gatewaywindows のサービスとして登録することもできます。

www.cdatablog.jp

リンクサーバーの設定方法

SSMS 上で「サーバーオブジェクト」→「リンクサーバー」→「新しいリンクサーバー」の順でクリックします。
f:id:sennanvolar44:20190613105731p:plain

次に、新規作成するリンクサーバーの設定行っていきます。
左側にある「全般」タブを選択し、下記内容を入力します。

  • リンクサーバー:任意
  • サーバーの種類:その他のデータソース
  • プロバイダー:SQL Server Native Client
  • データソース:”SQL Gateway が起動しているマシン名,SQL Gateway で設定したポート番号”
    ※マシン名とポートの間にカンマがあるので忘れずに入力してください。
  • カタログ:使用するデータソース名

f:id:sennanvolar44:20190613112747p:plain

左側にある「セキュリティ」タブを選択し、ログインするユーザーとパスワードを入力します。
ここで入力するユーザー名とパスワードは、SQL Gatewayで「ユーザー」タブで設定した内容となります。 入力後はOKボタンを押してリンクサーバーの作成は完了です。
f:id:sennanvolar44:20190613111636p:plain

作成したリンクテーブルをみていくと、Marketo のテーブルが表示されていることが確認できました。
f:id:sennanvolar44:20190613113400p:plain

MySQL についても同じようにデータソースとカタログ部分だけ変更するだけでリンクサーバーを作成することができます。
f:id:sennanvolar44:20190613114046p:plain

リンクサーバーのデータをSQLServerにバックアップ

リンクサーバーからデータを取得したり、または更新したりといったことをすることは可能ですが、 ある断面のテーブルの状態を保持しておきたい場合などは、以下のようなSQLでテーブル名をYYYYMMにして、SQLServerのデータベースにバックアップすることも可能です。

select * into Campaigns_Backup201906 
from [MARKETO].[CData Marketo Sys].[REST].[Activities_ClickLink];

まとめ

今回は CData の SQL Gateway を使ってリンクサーバーを作成してみました。 SQL Gateway を使うことで簡単に様々なデータソースと接続することができるため、ご興味がある方はぜひお試しくださいませ。