こんにちは！エンジニアの宮本です！
今回はCData SQL Gateway(V18版までは CData SQL Broker) と SQL Server Management Studio(SSMS) を使って、 マーケティングオートメーションツールのMarketo や MySQL のデータをリンクサーバーとして取り込んで、SQLServer 上のデータベースにまるまるバックアップしていく方法をご紹介します。
SQL Gateway とは？
あらゆるODBC のデータソースをSQL Server や MySQL にてあたかも同じデータベース内の１つのテーブルとして扱うことを実現してくれる CData の製品となります。
ユースケースしては、例えばSQLServer上のテーブルにまるっとデータをレプリケーションしたり、逆にデータを更新したりといったところがあげられます。
ちなみに GUI 上でレプリケーションの設定ができる製品として、CData Sync というレプリケーションツールも展開しております。
CData Sync についてはコチラ↓↓
https://www.cdata.com/jp/sync/
全体構成
各データソースのデータをレプリカする構成は下図のようになります。
※図ではSQLBrokerとなっていますが、V19版からSQLGatewayという名前に変更しています。
SSMSで見れるようにするために設定する箇所は大きく分けて２点です。
右側のカスタムアプリケーションや帳票と書かれている列を、今回の場合は「SQL Server Management Studio(SSMS)」と置き換えてください。
事前にインストールしておくもの
Marketo ODBC Driver
https://www.cdata.com/jp/drivers/marketo/odbc/MySQL ODBC Driver
https://www.cdata.com/jp/drivers/mysql/odbc/Marketo、MySQL、SQL Server については接続先を用意しておく。
SQL Gateway の設定方法
まずは、「スタートボタン」→「CData SQL Gateway」→ 「SQL Gateway」 の順でクリックして起動します。
次に、構成図①部分にあたるデータソースと SQL Gateway との接続設定をしていきます。
「サービス」タブを選択し、追加ボタンをクリックします。
- サービス名：任意ですが、今回は Marketo と付けます。
- プロトコル：SQLServer を使用するので TDS
- データソース： Marketo 用のODBC データソース
- ポート：SQLServer のデフォルトポートは1433なので、使っていれば他のポート番号を設定
MySQLの設定内容もほぼほぼ同じです。
次に、構成図②部分にあたる SQL Gateway と SSMS 間の接続設定をしていきます。
まずは、 SQL Gateway でユーザー設定をしていきます。ここでユーザー毎の設定をすることで、参照だけなのか更新許可するのかといった権限を付与することができます。
「ユーザー」タブを選択し、追加ボタンをクリックします。
- ユーザー：任意ですが、今回は user と付けます。
- パスワード：任意
- 権限： MySQL、Marketo のみにFull 権限を付与
これで SQL Gateway の設定は完了なので、「変更を保存」をクリックし設定内容を保存します。
作成したサービスを選択しながら右にある「開始」ボタンをクリックしてサービスを立ち上げます。
なお、この SQL Gateway を windows のサービスとして登録することもできます。
リンクサーバーの設定方法
SSMS 上で「サーバーオブジェクト」→「リンクサーバー」→「新しいリンクサーバー」の順でクリックします。
次に、新規作成するリンクサーバーの設定行っていきます。
左側にある「全般」タブを選択し、下記内容を入力します。
- リンクサーバー：任意
- サーバーの種類：その他のデータソース
- プロバイダー：SQL Server Native Client
- データソース：”SQL Gateway が起動しているマシン名,SQL Gateway で設定したポート番号”
※マシン名とポートの間にカンマがあるので忘れずに入力してください。 - カタログ：使用するデータソース名
左側にある「セキュリティ」タブを選択し、ログインするユーザーとパスワードを入力します。
ここで入力するユーザー名とパスワードは、SQL Gatewayで「ユーザー」タブで設定した内容となります。
入力後はOKボタンを押してリンクサーバーの作成は完了です。
作成したリンクテーブルをみていくと、Marketo のテーブルが表示されていることが確認できました。
MySQL についても同じようにデータソースとカタログ部分だけ変更するだけでリンクサーバーを作成することができます。
リンクサーバーのデータをSQLServerにバックアップ
リンクサーバーからデータを取得したり、または更新したりといったことをすることは可能ですが、 ある断面のテーブルの状態を保持しておきたい場合などは、以下のようなSQLでテーブル名をYYYYMMにして、SQLServerのデータベースにバックアップすることも可能です。
select * into Campaigns_Backup201906 from [MARKETO].[CData Marketo Sys].[REST].[Activities_ClickLink];
まとめ
今回は CData の SQL Gateway を使ってリンクサーバーを作成してみました。 SQL Gateway を使うことで簡単に様々なデータソースと接続することができるため、ご興味がある方はぜひお試しくださいませ。
