各製品の資料を入手。
詳細はこちら →
製品をチェック
SQL Server からBusiness b-ridge へストアドプロシージャで自動連携：SQL Gateway
SQL Server のCDC 機能とストアドプロシージャを使って、リンクサーバー経由でBusiness b-ridge のデータを定期的に更新する方法
最終更新日：2022-03-22
こんにちは！ドライバー周りのヘルプドキュメントを担当している古川です。
シナリオ
SQL Serverにある既存テーブルの更新された値を、Business b-ridge に自動的に取り込んでいきます。構成は、Business b-ridge とSQL Server の間にSQL Gateway を配置し、SQL Gateway からデータソースへのアクセスはHTTP リクエスト、SQL Gateway からクライアントへはTDS プロトコル（SQL Server）でアクセスできるようにします。
SQL Gateway を使うことで、SQLServer のリンクサーバー経由やMySQL へ接続できるツール、サービスなどからもSaaS にアクセスすることが可能になります。もちろん参照だけでなく更新も可能です。
手順
やることは大枠でこちらの内容です。
- CData ODBC ドライバインストール & DSN 設定
- SQL Gateway TDS プロトコルI/F を作成
- SQL Server リンクサーバーを作成
- SQL Server CDC 機能をオンにする
- SQL Server 更新用ストアドプロシージャの作成
- SQL Server エージェントでスケジュール設定
CData ODBC ドライバとは？
CData ODBC ドライバは、以下のような特徴を持ったリアルタイムデータ連携ソリューションです。
- Business b-ridge をはじめとする、CRM、MA、会計ツールなど多様なカテゴリの270種類以上のSaaS / オンプレミスデータソースに対応
- 多様なアプリケーション、ツールにBusiness b-ridge のデータを連携
- ノーコードでの手軽な接続設定
- 標準 SQL での柔軟なデータ読み込み・書き込み
CData ODBC ドライバでは、1.データソースとしてBusiness b-ridge の接続を設定、2.SQL Server 側でODBC Driver との接続を設定、という2つのステップだけでデータソースに接続できます。以下に具体的な設定手順を説明します。
CData ODBC ドライバのインストールとBusiness b-ridge への接続設定
まずは、本記事右側のサイドバーからBusinessBridge ODBC Driver の無償トライアルをダウンロード・インストールしてください。30日間無償で、製品版の全機能が使用できます。
CData ODBC ドライバをインストールすると SQL Gateway もあわせてインストールされるようになっています。ではこちらのリンクからBusiness b-ridge をクリックして、インストーラーをダウンロードします。ダウンロードボタンをクリックしてBusiness b-ridge
ODBC Driver
をダウンロードします。
ダウンロードしたBusiness b-ridgeODBCDriver.exe をダブルクリックしてインストールを開始します。
インストール自体はデフォルトのまま進んで構いません。インストール後にDSN 設定画面が開きます。
- 接続プロパティの指定がまだの場合は、DSN （データソース名）で行います。Microsoft ODBC データソースアドミニストレーターを使ってODBC DSN
を作成および設定できます。一般的な接続プロパティは以下のとおりです：
Microsoft ODBC データソースアドミニストレーターで必要なプロパティを設定する方法は、ヘルプドキュメントの「はじめに」をご参照ください。
Business b-ridge 接続プロパティの取得・設定方法
B b-ridge への接続には、Company Key、Project Key、Subscription Key が必要となります。それぞれWeb API 利用申請時に取得できます。取得したKey を接続プロパティに設定して接続します。
- CompanyKey：Business b-ridge のCompany Key に設定。
- ProjectKey：Business b-ridge のProject Key に設定。
- SubscriptionKey：控えておいたSubscription Key に設定。
ここまでで、CData Business b-ridge ODBC Driver の設定が完了しました。続いてはSQL Gateway の設定に入っていきます。
SQL Gateway でTDS プロトコルI/F を作成
Windows メニューにある SQL Gateway クリックして起動します。
SQL Gateway が起動されたらこのようなコンソール画面が表示されます。最初に「サービス」タブから追加ボタンをクリックします。
以下の内容で設定します。
- サービス名：任意
- I/F：サービス名下のラジオボタンで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
で作成したユーザー情報を入力して設定は完了です。
これでBusiness b-ridge のオブジェクトがリンクサーバーにテーブルとして表示されるようになりました。
SQL Server の設定（CDC）
まずは CDC（Change Data Capture）
機能をオンにしていきます。ちなみにこの CDC
とは、各テーブルのデータの変更情報を保持してくれる機能です。
※CDC はStandard Edition 以上、もしくはDeveloper Edition
で利用可能です。
今回はこのCDC 機能を使って変更データを抽出し、Business b-ridge に連携していきます。
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'顧客データ_東北支店',
@role_name = N'cdc_role',
@supports_net_changes = 1
GO
各種パラメータの説明はMicrosoft の公式ドキュメントをご参照ください。上記以外にも設定できるようです。
これで対象 DB
のシステムテーブルに、変更情報を保持してくれるテーブルが表示されました。
何も変更していない状態では、dbo_顧客データ_東北支店_CT
テーブルのレコードはない状態です。
CDC 対象の顧客データ_東北支店
テーブルにあるメールアドレス列の値を変更してみます。
もう一度、dbo_顧客データ_東北支店_CT テーブルを参照すると変更前後のレコードが格納されました。ちなみに$operation=3 で更新前、4で更新後となります。
これで SQL Server で変更したデータを確認することが出来るようになりました！
ストアドプロシージャの作成
シンプルに CDC テーブルの Name という項目だけを使用して、それをリンクサーバーの [Business b-ridge].[顧客管理（営業支援パック）] テーブルに Insert、Delete、Update で連携するということをやってみます。
CREATE PROCEDURE Business b-ridgeSP
AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
--カーソルの値を取得する変数宣言
DECLARE @Id varchar(50)
DECLARE @email varchar(50)
DECLARE @Operation int
--カーソル定義
DECLARE CUR_1 CURSOR FOR
SELECT a.[RecordId], a.[メールアドレス],a.[__$operation]
FROM [cdata].[cdc].[dbo_顧客データ_東北支店_CT] AS a,
(SELECT[RecordId] , MAX([__$seqval]) AS seqval
FROM [cdata].[cdc].[dbo_顧客データ_東北支店_CT]
GROUP BY [RecordId]) AS b
WHERE a.[RecordId] = b.[RecordId]
AND a.[__$seqval] = b.seqval
--'3'は更新前レコード
AND a.[__$operation] <> '3'
--カーソルオープン
OPEN CUR_1;
FETCH NEXT FROM CUR_1
INTO @Id,@email,@Operation;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 削除レコードの場合
IF @Operation = 1
BEGIN
DELETE FROM [BUSINESS B-RIDGE_GATEWAY].[CData Business b-ridge Sys].[Business b-ridge].[顧客管理（営業支援パック）] WHERE
RecordId=@Id;
END
-- 新規レコードの場合
ELSE IF @Operation = 2
BEGIN
INSERT INTO [BUSINESS B-RIDGE_GATEWAY].[CData Business b-ridge Sys].[Business b-ridge].[顧客管理（営業支援パック）]
([メールアドレス])
Values(@email);
END
-- 更新レコードの場合
ELSE IF @Operation = 4
BEGIN
UPDATE [BUSINESS B-RIDGE_GATEWAY].[CData Business b-ridge Sys].[Business b-ridge].[顧客管理（営業支援パック）] SET
[メールアドレス] =
@email
WHERE
RecordId=@Id;
END
--次のレコード
FETCH NEXT FROM CUR_1
INTO @Id,@email,@Operation;
END
--カーソルクローズ
CLOSE CUR_1;
DEALLOCATE CUR_1;
--CDC変更テーブルのレコード削除
Truncate table [cdc].[dbo_顧客データ_東北支店_CT]
END
スケジュール設定
最後にSQL Server
エージェントでストアドプロシージャのスケジュール実行を設定します。
ステップで新規作成から、先ほど作成したストアドプロシージャを実行するよう設定します。
実行タイミングを設定します。今回は夜中に毎日実行されるようスケジューリングしました。
設定後はこのように次にジョブが実行される時間やステータスなどが表示されています。
これで設定が完了しました！
SQL Server のデータを更新
以下は変更テーブルの中身です。赤枠内のレコードが
リンクサーバーを介してBusiness b-ridge に反映される想定です。
赤枠レコードの内訳は、
- 1行目：削除レコード（__$operation:1）
- 2行目：更新前レコード（__$operation:3）
- 3行目：更新後レコード（__$operation:4）
- 4行目：追加レコード （__$operation:2）
ではこれでAM 3:00に実行されるのを待ってみると、ジョブが実行されました。
リンクサーバーの[Business b-ridge].[顧客管理（営業支援パック）]
テーブルを参照すると、先ほど確認したレコードが削除も含めて反映されていました。
これであとはこのまま起動しておくだけで、SQL Server で更新したレコードをもとにBusiness b-ridge が更新されるようになりました。
おわりに
いかがでしたでしょうか。SQL Server の CDC 機能とストアドプロシージャ、リンクサーバーでBusiness b-ridge に自動連携できるようになりました。
CData ODBC Driver for BusinessBridge は 30日間の無償評価版がご利用可能です。こちらから をぜひお試しください。
CData ODBC ドライバは日本のユーザー向けに、UI の日本語化、ドキュメントの日本語化、日本語でのテクニカルサポートを提供しています。
関連コンテンツ
