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

by 宮本航太 | 2021年04月06日

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

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

シナリオ

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

SQL Gatewayとは

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

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

これを使うことで、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/

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 Gateway で TDS プロトコル 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 Server と SQL Gateway つなぐために、リンクサーバーを作成していきます。
SSMS で対象の SQL Server に接続し、「サーバーオブジェクト」→「リンクサーバー」で右クリックでリンクサーバーの新規作成を行います。

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

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

関連コンテンツ

トライアル・お問い合わせ

30日間無償トライアルで、CData のリアルタイムデータ連携をフルにお試しいただけます。記事や製品についてのご質問があればお気軽にお問い合わせください。