CData Software Blog

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

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

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

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

シナリオ

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

SQL Gatewayとは

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

https://www.cdata.com/jp/sqlgateway/ f:id:sennanvolar44:20200610170647p:plain

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

手順

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

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

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

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

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

f:id:sennanvolar44:20210405155829p:plain

ダウンロードボタンをクリックして Salesforce ODBC Driver をダウンロードします。
f:id:sennanvolar44:20210405155937p:plain

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

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

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

SQL GatewayTDS プロトコル IF を作成

Windows メニューにある SQL Gateway クリックして起動します。
f:id:sennanvolar44:20210405162229p:plain

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

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

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

f:id:sennanvolar44:20210405170413p:plain

外部からこの SQL Gateway にアクセスする際のユーザー情報を作成します。
f:id:sennanvolar44:20210405171037p:plain

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

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

リンクサーバーの作成

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

f:id:sennanvolar44:20210405181145p:plain

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

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

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 

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

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

何も変更していない状態では、dbo_Account_CT テーブルのレコードはない状態です。
f:id:sennanvolar44:20210405175244p:plain

CDC 対象のAccount テーブルにある Name 列の値を変更してみます。
f:id:sennanvolar44:20210405175529p:plain

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

これで 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 エージェントでストアドプロシージャのスケジュール実行を設定します。
f:id:sennanvolar44:20210405231305p:plain

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

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

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

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

SQLServer のデータを更新

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

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

f:id:sennanvolar44:20210405233611p:plain

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

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

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

おわりに

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

www.cdata.com