SQL Server から Salesforce へストアドプロシージャで自動連携：SQL Gateway

こんにちは、テクニカルサポートエンジニア の宮本(@miyamon44)です。

今回は SQL Server の CDC 機能＋ストアドプロシージャを使い、リンクサーバー経由で Salesforce のデータを定期的に更新する方法をご紹介します。

• シナリオ
• SQL Gatewayとは
• 手順
  • CData ODBC Driver のインストール＆DSN設定
  • SQL Gateway で TDS プロトコル IF を作成
  • リンクサーバーの作成
  • SQL Server の設定（CDC）
  • ストアドプロシージャの作成
  • スケジュール設定
• SQLServer のデータを更新
• おわりに

シナリオ

やりたいことは、SQL Server にある既存テーブルの更新された値を、Salesforce に自動的に取り込むことを行います。
構成は、Salesforce と SQL Server の間に SQL Gateway を配置し、SQL Gateway からデータソースへのアクセスは HTTP リクエスト、SQL Gateway からクライアントへはTDSプロトコル（SQLServer）でアクセスできるようにします。

SQL Gatewayとは

Salesforce や Kintone、スマレジなど、CData が対応しているデータソースに MySQL や SQLServer の I/F でアクセスすることができるツールとなります。

https://www.cdata.com/jp/sqlgateway/

これを使うことで、SQLServer のリンクサーバー経由や MySQL へ接続できるツール、サービスなどからも SaaS にアクセスすることが可能になります。 もちろん参照だけではなく更新もできます！

手順

やることは大枠でこちらの内容です。

• [CData ODBC Driver] インストール＆ DSN 設定
• [SQL Gateway] TDSプロトコル IF を作成
• [SQL Server] リンクサーバーを作成
• [SQL Server] CDC 機能をオンにする
• [SQL Server] 更新用ストアドプロシージャの作成
• [SQL Server] SQL Server エージェントでスケジュール設定

いろいろ書いてますが特に難しいところはなく、淡々と設定できると思います。
では、さっそくやっていきましょう。

CData ODBC Driver のインストール＆DSN設定

実は CData ODBC Driver をインストールすると SQL Gateway も併せてインストールされるようになっています。

今回は Salesforce ODBC Driver を利用しますが、他のデータソースでも利用可能ですので、その際は接続設定やテーブル名などを読み替えていただければ大丈夫です。では以下のリンクから Salesforce をクリックしてインストーラーをダウンロードします。
https://www.cdata.com/jp/odbc/

ダウンロードボタンをクリックして Salesforce ODBC Driver をダウンロードします。

ダウンロードした SalesforceODBCDriver.exe をダブルクリックしてインストールを開始します。
インストール自体はデフォルトのまま進んで構いません。

インストール後にDSN 設定画面が開きますので、Salesforce の接続情報を設定します。接続テストが成功したらそのままOKボタンを押して設定情報を保存します。

ここまでで、CData Salesforce ODBC Driver の設定が完了しました。
続いては SQL Gateway の設定に入っていきます。

SQL Gateway で TDS プロトコル IF を作成

Windows メニューにある SQL Gateway クリックして起動します。

SQL Gateway が起動されたらこのようなコンソール画面が表示されます。
最初に「サービス」タブから追加ボタンをクリックします。

以下の内容で設定します。

• サービス名＝任意、
• IF＝サービス名下のラジオボタンでTDS(SQL Server) を選択
• データソース＝先ほど設定したDSN
• ポート＝未使用のもの

外部からこの SQL Gateway にアクセスする際のユーザー情報を作成します。

作成後は「変更を保存」→「開始」の順でボタンをクリックします。サービスが正常に開始されると、このように緑色のランプが点灯します。

これで データソース～SQL Gateway 間の接続設定と、TDS プロトコルでのインタフェース設定が完了しました。

リンクサーバーの作成

次に SQL Server と SQL Gateway つなぐために、リンクサーバーを作成していきます。
SSMS で対象の SQL Server に接続し、「サーバーオブジェクト」→「リンクサーバー」で右クリックでリンクサーバーの新規作成を行います。

• リンクサーバー名：任意
• サーバーの種類：その他
• プロバイダー：SQL Server Native Client
• データソース：SQL Gateway を起動しているインスタンスとポート
  「インスタンス,ポート」の書き方。
• カタログ：SQL Gateway で指定したDSN

セキュリティに移動して、SQL Gateway で作成したユーザー情報を入力して設定は完了です。

これで Salesforce のオブジェクトがリンクサーバーにテーブルとして表示されるようになりました。

SQL Server の設定（CDC）

まずは CDC（Change Data Capture） 機能をオンにしていきます。ちなみにこの CDC とは、各テーブルのデータの変更情報を保持してくれる機能です。
※ちなみに CDC は Standard Edition 以上、もしくは Developer Edition で利用可能です。

docs.microsoft.com

今回はこの CDC 機能を使って変更データを抽出し、Salesforce に連携していきます。

CDC 機能を利用するデータベースを指定して以下SQLを実行します。

USE cdata;
GO 
EXECUTE sys.sp_cdc_enable_db;  
GO

CDC の対象とするテーブルを設定します。

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'Account',  
@role_name     = N'cdc_role',  
@supports_net_changes = 1
GO 

各種パラメータの説明は以下を参照ください。上記以外にも設定できるようです。
sys.sp_cdc_enable_table (Transact-sql) - SQL Server | Microsoft Docs

これで対象 DB のシステムテーブルに、変更情報を保持してくれるテーブルが表示されました。

何も変更していない状態では、dbo_Account_CT テーブルのレコードはない状態です。

CDC 対象のAccount テーブルにある Name 列の値を変更してみます。

もう一度、dbo_Account_CT テーブルを参照すると変更前後のレコードが格納されました。ちなみに$operation=3 で更新前、4で更新後となります。

これで SQL Server での変更したデータを確認することが出来るようになりました！

ストアドプロシージャの作成

シンプルに CDC テーブルの Name という項目だけを使用して、それをリンクサーバーの [Salesforce].[Account] テーブルにInsert、Delete、Update で連携するということをやってみます。 ※もちろんUpsert でもOKですが、今回は汎用的なものなので使用してません

CREATE PROCEDURE SalesforceSP
AS
BEGIN
SET ANSI_NULLS ON 
SET ANSI_WARNINGS ON

--カーソルの値を取得する変数宣言
DECLARE @Id varchar(50)
DECLARE @Name varchar(50)
DECLARE @Operation int

--カーソル定義
DECLARE CUR_1 CURSOR FOR
SELECT a.Id, a.Name,a.__$operation
FROM [cdata_jp].[cdc].[dbo_Account_cdatajp_CT] AS a, 
    (SELECT Id AS id , MAX([__$seqval]) AS seqval
     FROM   [cdata_jp].[cdc].[dbo_Account_cdatajp_CT]
     GROUP BY Id) AS b
WHERE a.Id = b.id
AND a.[__$seqval] = b.seqval
--'3'は更新前レコード
AND a.[__$operation] <> '3'

--カーソルオープン
OPEN CUR_1;
FETCH NEXT FROM CUR_1
INTO @Id,@Name,@Operation;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- 削除レコードの場合
    IF @Operation = 1 
        BEGIN
          DELETE FROM [SALESFORCE_GATEWAY].[CData Salesforce Sys].[Salesforce].[Account] WHERE Id=@Id;
        END
    -- 新規レコードの場合
    ELSE IF @Operation = 2
        BEGIN
          INSERT INTO [SALESFORCE_GATEWAY].[CData Salesforce Sys].[Salesforce].[Account] (Name) Values(@Name);
        END
    -- 更新レコードの場合
    ELSE IF @Operation = 4
        BEGIN
          UPDATE [SALESFORCE_GATEWAY].[CData Salesforce Sys].[Salesforce].[Account] SET Name = @Name WHERE Id=@Id;
        END

    --次のレコード
    FETCH NEXT FROM CUR_1
    INTO @Id,@Name,@Operation;
END

--カーソルクローズ
CLOSE CUR_1;
DEALLOCATE CUR_1;

--CDC変更テーブルのレコード削除
Truncate table [cdc].[dbo_Account_cdatajp_CT]

END

スケジュール設定

最後にSQL Server エージェントでストアドプロシージャのスケジュール実行を設定します。

ステップで新規作成から、先ほど作成したストアドプロシージャを実行するよう設定します。

実行タイミングを設定します。今回は夜中に毎日実行されるようスケジューリングしました。

設定後はこのように次にジョブが実行される時間やステータスなどが表示されています。

これで設定が完了しました！

SQLServer のデータを更新

以下は変更テーブルの中身です。赤枠内のレコードが リンクサーバーを介して Salesforce に反映される想定です。
赤枠レコードの内訳は、

• 赤枠1行目：更新レコード
• 赤枠2行目：追加レコード
• 赤枠3行目：削除レコード

ではこれでAM3:00 に実行されるのを待ってみると、ジョブが実行されました。

リンクサーバーの Account テーブルを参照すると、先ほど確認したレコードが削除も含めて反映されていました。

これであとはこのまま起動しておくだけで、SQL Server で更新したレコードをもとに Salesforce を更新されるようになりました。

おわりに

いかがでしたでしょうか。SQL Server の CDC 機能とストアドプロシージャ、リンクサーバーで Salesforce に自動連携できるようになりました。
今回は Salesforce でしたが、他のデータソースでも同じようにご利用いただけます。CData ODBC Driver は 30 日間の無償評価版がご利用可能です。是非お試しください！

www.cdata.com

関連コンテンツ