はじめに
CData技術ディレクター桑島です。本記事ではDBAmpを使用してSalesforceのデータとSQLServerのデータを双方向に同期する方法についてご紹介します。
本記事での手順では、DBAmpがインストール済でリンクサーバ経由でSalesforceデータにアクセス出来ていることが前提となりますので、まだの方はこちらの記事をご覧ください。
www.cdatablog.jp
手順
データベースの作成、および、ストアドプロシージャの登録
Salesforceのデータを同期するためのSQLServerのデータベースを作成します。SQLServerのManagementStudioから「salesforce backups」という名称でデータベースを作成します。
「C:\Program Files\DBAmp\SQL」配下の「Create DBAmp SPROCS.sql」をクエリエディタで開きます。
データベースに「salesforce backups」が選択されていることを確認してExecuteボタンで本SQLを実行します。
上記SQLを実行することで、DBAmpTableOptionsテーブル、および、SF_ から始まる数多くのストアドプロシージャが登録されます。
DBAmp Configuration Programの設定
上記で作成したストアドプロシージャを使用するための設定を行います。Windowsのスタートメニューから「DBAmp > DBAmp Configuration Program」を開きます。DBAmp Configuration Programのアプリが起動するので、メニューから「Configration > Options」を開きます。
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(ディレクトリ一覧を表示)コマンドの結果がレコードとして表示されていれば許可されている状態になります。
エラーとなる場合は、許可されていない状態ですのでクエリエディタから以下のコマンドを実行してxp_cmdshellを許可するように変更してください。詳細はMicrosoft社のこちらのドキュメントをご参照ください。
EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
上記構文を実行後に再度「Exec master..xp_cmdshell "dir"」を実行してレコードが返って来ることを確認してください。
Salesforce → SQLServerのデータ同期
それでは実際に、SalesforceのデータをSQLServerに同期してみましょう。
全件同期
本手順では、SalesforceのAccount（取引先）の全データをSQLServerの同名テーブルに全件同期してみます。クエリエディタで以下のSQLを実行します。
Use "salesforce backups"
Exec SF_Mirror 'SALESFORCE', 'Account'
実行が完了すると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
本メッセージを見ると以下のような処理の流れを確認出来ます。
- SQLServerの一時テーブル（Account_Previous20210114195958604）を作成
- SalesforceのBulkAPIを呼び出し
- Accountのデータを「DBAmp Configuration Program」の「DBAmp Work Directory」に指定したディレクトリにCSVファイルとして出力
- SQLServerの一時テーブル（Account_Previous20210114195958604）にCSVファイルをローディング
- CSVファイルと既存のAccountテーブルを削除
- 一時テーブル（Account_Previous20210114195958604）をリネーム（Account）
- PrimaryKeyをセット
salesforce backupsデータベースにAccountテーブルが作成されるのでクエリエディタにSELECT文をセットしてAccountテーブルのデータをみてみましょう。SalesforceのデータがローカルのSQLServerのテーブルに全件コピーされていれば成功です。
ジョブでのスケジュール実行
SQLServer エージェントを使用してSF_Mirrorを定期的に実行することが出来ます。
なお、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」というテーブルが作成されており、テーブル毎に最後に更新された日時を保持することで、それ以降の更新があったデータのみ差分で更新する仕組みを実現しています。
SQLServer → Salesforceのデータ同期
今度は、SQLServerのデータをSalesforceに取り込んでみましょう。
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]
ストアドプロシージャ「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
以下のSQLでSalesforce側リンクサーバーのテーブル内のデータを確認します。
SELECT [Id],[Name],[AnnualRevenue] FROM [SALESFORCE]...[Account] ORDER BY [LastModifiedDate] DESC;
「Name」が「TEST」から始まる取引先が10件追加されていることを確認します。
実行結果は「Account_Load_Result」というテーブルで確認できます。エラーとなった場合はこのテーブル内のError列の内容を確認してください。
Salesforce側の画面でも確認してみます。Account（取引先）にNameがTESTから始まるレコードが追加されていれば成功です。
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]
ストアドプロシージャ「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
以下のSQLでSalesforce側リンクサーバーのAccountテーブル内のNameが「Edit」付きに更新されたことを確認します。
SELECT [Id],[Name],[AnnualRevenue] FROM [SALESFORCE]...[Account] ORDER BY [LastModifiedDate] DESC;
実行結果は「Account_Update_Result」というテーブルで確認できます。エラーとなった場合はこのテーブル内のError列の内容を確認してください。
Salesforce側の画面でも確認してみます。Account（取引先）のNameが更新されていることを確認できれば更新は成功です。
まとめ
本記事ではDBAmpを使用してSalesforceのデータとSQLServerのデータを双方向に同期する方法についてご紹介しました。DBAmpは21日間の無償評価版がございますので、ご興味のある方は、こちらのWebサイト、もしくは、CDataJapanのテクニカルサポート窓口よりお問い合わせください。
関連コンテンツ
