CData Software Blog

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

DBAmp のご紹介 - Salesforce ⇔ SQLServer間の双方向データ同期

はじめに

CData技術ディレクター桑島です。本記事ではDBAmpを使用してSalesforceのデータとSQLServerのデータを双方向に同期する方法についてご紹介します。

f:id:kuwazzy:20210114160512p:plain

本記事での手順では、DBAmpがインストール済でリンクサーバ経由でSalesforceデータにアクセス出来ていることが前提となりますので、まだの方はこちらの記事をご覧ください。

www.cdatablog.jp

手順

データベースの作成、および、ストアドプロシージャの登録

Salesforceのデータを同期するためのSQLServerのデータベースを作成します。SQLServerのManagementStudioから「salesforce backups」という名称でデータベースを作成します。

f:id:kuwazzy:20210114191028p:plain

「C:\Program Files\DBAmp\SQL」配下の「Create DBAmp SPROCS.sql」をクエリエディタで開きます。

f:id:kuwazzy:20210114191352p:plain

データベースに「salesforce backups」が選択されていることを確認してExecuteボタンで本SQLを実行します。

f:id:kuwazzy:20210114192810p:plain

上記SQLを実行することで、DBAmpTableOptionsテーブル、および、SF_ から始まる数多くのストアドプロシージャが登録されます。

f:id:kuwazzy:20210114191716p:plain

DBAmp Configuration Programの設定

上記で作成したストアドプロシージャを使用するための設定を行います。Windowsのスタートメニューから「DBAmp > DBAmp Configuration Program」を開きます。DBAmp Configuration Programのアプリが起動するので、メニューから「Configration > Options」を開きます。

f:id:kuwazzy:20210114192127p:plain

DBAmpでBulkでデータ操作を行う時の一時領域であるDBAmp Work Directoryをセットします(本例では、C:¥wrk¥DBAmp)。次にSQLServerへのログインクレデンシャルをセットします。Windows統合認証の場合は「Trusted_Connection=Yes」、SQLServer独自認証の場合は「Trusted_Connection=No;UID=ユーザ名;PWD=パスワード」をセットします。これらをセットしたらOKボタンでDBAmp Configuration Programを閉じます。

xp_cmdshellの実行許可

DBAmpでは、SQLServerのクエリからWidnowsのコマンドを実行するためのxp_cmdshellの許可が必要となります。許可されているかを確認するためには以下のExec文をクエリエディタから実行してみてください。

Exec master..xp_cmdshell "dir"

下記のようにWindowsのdir(ディレクトリ一覧を表示)コマンドの結果がレコードとして表示されていれば許可されている状態になります。

f:id:kuwazzy:20210114193039p:plain

エラーとなる場合は、許可されていない状態ですのでクエリエディタから以下のコマンドを実行してxp_cmdshellを許可するように変更してください。詳細はMicrosoft社のこちらのドキュメントをご参照ください。

-- To allow advanced options to be changed.  
EXECUTE sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO  

上記構文を実行後に再度「Exec master..xp_cmdshell "dir"」を実行してレコードが返って来ることを確認してください。

SalesforceSQLServerのデータ同期

それでは実際に、SalesforceのデータをSQLServerに同期してみましょう。

f:id:kuwazzy:20210118104627p:plain

全件同期

本手順では、SalesforceのAccount(取引先)の全データをSQLServerの同名テーブルに全件同期してみます。クエリエディタで以下のSQLを実行します。

Use "salesforce backups"
Exec SF_Mirror 'SALESFORCE', 'Account'

f:id:kuwazzy:20210114200240p:plain

実行が完了するとMessagesに以下のような情報が出力されます。

--- Starting SF_Mirror for Account V4.1.8
19:59:52: Parameters: SALESFORCE Account  Version: V4.1.8
19:59:54: DBAmpAZ 4.1.8.0 (c) Copyright 2020 forceAmp.com LLC
19:59:57: Using settings from Registry.
19:59:58: Using FullCopy because the Account table does not exist.
19:59:58: Server: CDATANAS1\SQLEXPRESS, Database: salesforce backups, Linked Server: SALESFORCE
19:59:58: Using the Salesforce BulkAPI.
19:59:59: Create Account_Previous20210114195958604 with new structure.
19:59:59: Job 7500I00000TXGRAQA5 created on salesforce.
20:00:00: Using the bulkapi with polling every 60 seconds
20:00:00: Job still running.
20:00:15: Job Complete.
20:00:17: File downloaded successfully: C:\wrk\DBAmp\7500I00000TXGRAQA5-7510I00000gRUdeQAG-7520I00000IYnYW.csv
20:00:17: Files downloaded complete.
20:00:17: Loading file into SQL Server: C:\wrk\DBAmp\7500I00000TXGRAQA5-7510I00000gRUdeQAG-7520I00000IYnYW.csv
20:00:17: 85 rows loaded. - C:\wrk\DBAmp\7500I00000TXGRAQA5-7510I00000gRUdeQAG-7520I00000IYnYW.csv
20:00:17: File loaded successfully: C:\wrk\DBAmp\7500I00000TXGRAQA5-7510I00000gRUdeQAG-7520I00000IYnYW.csv
20:00:17: File load complete.
20:00:17: 85 rows copied.
20:00:17: Deleted file: C:\wrk\DBAmp\7500I00000TXGRAQA5-7510I00000gRUdeQAG-7520I00000IYnYW.csv
20:00:17: Drop Account if it exists.
20:00:17: Rename previous table from Account_Previous20210114195958604 to Account
20:00:17: Create primary key on Account
20:00:17: Drop Account_Previous20210114195958604 if it exists.
20:00:17: DBAmpAZ Operation successful.
--- Ending SF_Mirror. Operation successful.

Completion time: 2021-01-14T20:00:19.4486401+09:00

本メッセージを見ると以下のような処理の流れを確認出来ます。

  1. SQLServerの一時テーブル(Account_Previous20210114195958604)を作成
  2. SalesforceのBulkAPIを呼び出し
  3. Accountのデータを「DBAmp Configuration Program」の「DBAmp Work Directory」に指定したディレクトリにCSVファイルとして出力
  4. SQLServerの一時テーブル(Account_Previous20210114195958604)にCSVファイルをローディング
  5. CSVファイルと既存のAccountテーブルを削除
  6. 一時テーブル(Account_Previous20210114195958604)をリネーム(Account)
  7. PrimaryKeyをセット

salesforce backupsデータベースにAccountテーブルが作成されるのでクエリエディタにSELECT文をセットしてAccountテーブルのデータをみてみましょう。SalesforceのデータがローカルのSQLServerのテーブルに全件コピーされていれば成功です。

f:id:kuwazzy:20210114201017p:plain

ジョブでのスケジュール実行

SQLServer エージェントを使用してSF_Mirrorを定期的に実行することが出来ます。

f:id:kuwazzy:20210114201812p:plain

なお、SQLServerエージェントは、SQLServerExpress Editionには含まれないのでご留意ください。SQLServerエージェントからのストアドプロシージャの追加はMicrosoft社のこちらのドキュメントをご参照ください。

docs.microsoft.com

差分同期

以下の構文で「SF_Mirror 」ストアドプロシージャを再度実行します。

Exec SF_Mirror 'SALESFORCE', 'Account'

Messagesを確認すると「Identified 0 deleted rows.」と更新がないレコードはスキップされている記録が出力されています。これは、Salesforce側のデータが更新されたレコードのみ差分で更新されることを意味しています。

--- Starting SF_Mirror for Account V4.1.8
20:19:14: Parameters: SALESFORCE Account  Version: V4.1.8
20:19:14: DBAmpAZ 4.1.8.0 (c) Copyright 2020 forceAmp.com LLC
20:19:15: Using settings from Registry.
20:19:15: Using DeltaCopy to make changes to the Account table.
20:19:15: Server: CDATANAS1\SQLEXPRESS, Database: salesforce backups, Linked Server: SALESFORCE
20:19:15: Creating TableRefreshTime table because it does not exist.
20:19:15: Local table created at: 2021-01-14 19:59:59
20:19:15: Using last run time of 2021-01-14 18:59:59
20:19:16: Create Account with new structure.
20:19:16: Identified 0 updated / inserted rows.
20:19:16: Identified 0 deleted rows.
20:19:16: DBAmpAZ Operation successful.
--- Ending SF_Mirror. Operation successful.

Completion time: 2021-01-14T20:19:18.4340418+09:00

ローカルのSQLServerのテーブルリストを更新すると「TableRefreshTime」というテーブルが作成されており、テーブル毎に最後に更新された日時を保持することで、それ以降の更新があったデータのみ差分で更新する仕組みを実現しています。

f:id:kuwazzy:20210114203059p:plain

SQLServerSalesforceのデータ同期

今度は、SQLServerのデータをSalesforceに取り込んでみましょう。

f:id:kuwazzy:20210118104702p:plain

Salesforceへのデータ追加

ローカルのSQLServerのデータをSalesforceに追加する処理を実行してみます。ローカルのSQL Serverに以下のSalesforce追加データを格納するテーブル(Account_Load)を作成します。

Create Table Account_Load
(
Id nchar(18),
Name nvarchar(255),
AnnualRevenue decimal(18,0)
)

作成したローカルのAccount_Loadテーブルに以下のSQLでAccount(取引先)のName(取引先名)、及び、AnnualRevenue(年間売上)をセットした10レコードを追加します。

INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST001',10000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST002',20000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST003',30000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST004',40000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST005',50000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST006',60000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST007',70000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST008',80000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST009',90000)
INSERT INTO [dbo].[Account_Load]([Id],[Name],[AnnualRevenue]) VALUES('','TEST010',100000)

以下のSQLを実行して、ローカルSQLServerのAccount_Loadテーブルに上記で追加した10レコードが追加されていることを確認します。

SELECT * FROM [salesforce backups].[dbo].[Account_Load]

f:id:kuwazzy:20210118095205p:plain

ストアドプロシージャ「SF_TableLoader」を以下の構文(第一パラメータはInsert)で実行します。

Exec SF_TableLoader 'Insert', 'SALESFORCE', 'Account_Load'

実行時のメッセージが出力されます。最後に「Operation successful.」と出力されれば成功です。

--- Starting SF_TableLoader for Account_Load V4.1.8
20:19:51: Run the DBAmpAZ.exe program.
20:19:52: DBAmpAZ 5.0.1.0 (c) Copyright 2020 forceAmp.com LLC
20:19:52: Using settings from Registry.
20:19:53: Using TableLoader to push records to Salesforce.com.
20:19:53: Server: CDATANAS1\SQLEXPRESS, Database: salesforce backups, Linked Server: SALESFORCE
20:19:53: Using the Salesforce SOAP API.
20:19:53: Drop Account_Load_Result if it exists.
20:19:53: Create Account_Load_Result with new structure.
20:19:54: Add Error column in Account_Load_Result.
20:19:55: 10 rows read from SQL Table.
20:19:55: 10 rows successfully processed.
20:19:55: DBAmpAZ Operation successful.
20:19:57: Percent Failed = 0.000.
--- Ending SF_TableLoader. Operation successful.

Completion time: 2021-01-15T20:19:57.0816287+09:00

以下のSQLSalesforce側リンクサーバーのテーブル内のデータを確認します。

SELECT [Id],[Name],[AnnualRevenue] FROM [SALESFORCE]...[Account] ORDER BY [LastModifiedDate] DESC;

「Name」が「TEST」から始まる取引先が10件追加されていることを確認します。

f:id:kuwazzy:20210118090347p:plain

実行結果は「Account_Load_Result」というテーブルで確認できます。エラーとなった場合はこのテーブル内のError列の内容を確認してください。

f:id:kuwazzy:20210118091602p:plain

Salesforce側の画面でも確認してみます。Account(取引先)にNameがTESTから始まるレコードが追加されていれば成功です。

f:id:kuwazzy:20210118105836p:plain

Salesforceのデータ更新

次に、ローカルのSQLServerのデータでSalesforceのデータを更新(UPDATE)する処理を実行してみます。まずは、ローカルのSQL Serverに以下のSalesforce更新データを格納するテーブル(Account_Update)を作成します。

Create Table Account_Update
(
Id nchar(18),
Name nvarchar(255),
AnnualRevenue decimal(18,0)
)

SalesforceリンクサーバのAccountテーブル内の更新したいレコード(本例では、NameがTESTから始めるレコードのみ)を抽出してAccount_UpdateにINSERTします。

Insert into [salesforce backups].[dbo].[Account_Update] ([Id], [Name], [AnnualRevenue])
Select Id, Name, AnnualRevenue FROM [SALESFORCE]...[Account] WHERE Name Like 'TEST%' 

以下のUPDATE文でNameの末尾に「Edit」という文字列を追加します。

UPDATE [salesforce backups].[dbo].[Account_Update] SET [Name] = [Name] + 'Edit'

以下のSQLを実行して、ローカルSQLServerのAccount_LoadテーブルにNameの末尾に「Edit」という文字列が付いた10レコードが追加されていることを確認します。

SELECT * FROM [salesforce backups].[dbo].[Account_Update]

f:id:kuwazzy:20210118093215p:plain

ストアドプロシージャ「SF_TableLoader」を以下の構文(第一パラメータはUpdate)で実行します。

Exec SF_TableLoader 'Update', 'SALESFORCE', 'Account_Update'

実行時のメッセージが出力されます。最後に「Operation successful.」と出力されれば成功です。

--- Starting SF_TableLoader for Account_Update V4.1.8
09:33:23: Run the DBAmpAZ.exe program.
09:33:23: DBAmpAZ 5.0.1.0 (c) Copyright 2020 forceAmp.com LLC
09:33:23: Using settings from Registry.
09:33:24: Using TableLoader to push records to Salesforce.com.
09:33:24: Server: CDATANAS1\SQLEXPRESS, Database: salesforce backups, Linked Server: SALESFORCE
09:33:24: Using the Salesforce SOAP API.
09:33:24: Drop Account_Update_Result if it exists.
09:33:24: Create Account_Update_Result with new structure.
09:33:24: Add Error column in Account_Update_Result.
09:33:25: 10 rows read from SQL Table.
09:33:25: 10 rows successfully processed.
09:33:25: DBAmpAZ Operation successful.
09:33:26: Percent Failed = 0.000.
--- Ending SF_TableLoader. Operation successful.

Completion time: 2021-01-18T09:33:26.9019417+09:00

以下のSQLSalesforce側リンクサーバーのAccountテーブル内のNameが「Edit」付きに更新されたことを確認します。

SELECT [Id],[Name],[AnnualRevenue] FROM [SALESFORCE]...[Account] ORDER BY [LastModifiedDate] DESC;

f:id:kuwazzy:20210118093454p:plain

実行結果は「Account_Update_Result」というテーブルで確認できます。エラーとなった場合はこのテーブル内のError列の内容を確認してください。

f:id:kuwazzy:20210118093931p:plain

Salesforce側の画面でも確認してみます。Account(取引先)のNameが更新されていることを確認できれば更新は成功です。

f:id:kuwazzy:20210118093657p:plain

まとめ

本記事ではDBAmpを使用してSalesforceのデータとSQLServerのデータを双方向に同期する方法についてご紹介しました。DBAmpは21日間の無償評価版がございますので、ご興味のある方は、こちらのWebサイト、もしくは、CDataJapanのテクニカルサポート窓口よりお問い合わせください。