Setting Up Replication of Salesforce Accounts and Contacts to SQL Azure

In this tutorial we will show how to configure replication of your Salesforce data to a relational database so that the replicated data were kept up-to-date with Salesforce automatically. In our tutorial we will demonstrate it with Salesforce Accounts and Contacts, however the same can be applied to any Salesforce object.

 

ExpandedToggleIcon        Creating Connections

In order to replicate data from Salesforce to SQL Azure, first we need to create connections to Salesforce and SQL Azure databases. If you have already created the necessary connections, you may skip these steps. To create a connection to Salesforce, perform the following steps:

1.Click Connections in the menu on the left side of the page to switch to the Connections page.
2.On the Connections page click the New button.
3.In the opened pane click Salesforce.
4.Specify the connection Name, for example, Salesforce1.
5.From the Environment drop-down list select the Salesforce environment type to import data to. Since this is just a sample walkthrough, the Sandbox environment is recommended.
 
salesforce-connection-oauth
6.From the Authentication drop-down list select the authentication method for connecting to Salesforce. If you don't mind storing your Salesforce credentials on Skyvia server, select User Name & Password. If you prefer not to store your credentials, select OAuth 2.0.
7.If you have selected User Name & Password, on the previous step, specify your Salesforce account e-mail, password, and security token. Otherwise, if you have selected OAuth 2.0 authentication, click the Log In with Salesforce button and login via the Salesforce website on the opened page. The result OAuth token will be stored in the connection data. Your Salesforce credentials will not be stored on our website.
 
Login-to-Salesforce    
8.Click the Save button to save the connection.
 
salesforce-connection-oauth-filled

To create a connection to SQL Azure, perform the following steps:

1.Click Connections in the page header to switch to the Connections page.
2.On the Connections page click the New button.
3.In the opened pane click SQL Server.
4.Specify the connection Name, for example, Azure.
5.In the Server box, specify enter "TCP:<server name>". Replace "<server name>" with your actual server name.
6.Specify your User Id, Password, and Database to connect.
 
azure-connection
7.Click the Save button to save the connection.

Now we have the necessary connections created. Let's create a package that performs the necessary data replication operation.

ExpandedToggleIcon        Creating Package

To create a Replication package, perform the following actions:

1.Click Integration in the menu on the left side of the page.
2.Click the New button in the top right corner of the page.
3.Click Replication.
4.Optionally click the automatically generated package name (on this step it's "Replication Package") and specify the package name. If you omit this step, the package name will be generated later based on the names of the replicated Salesforce objects.
5.In the Source header click Select and select Salesforce in the drop-down list.
6.In the Source Connection drop-down list select the Salesforce1 connection.
 
select-source-salesforce-connection
7.In the Target header click Select and select SQL Server in the drop-down list.
 
replication-select-target-type
8.In the Target Connection drop-down list select the Azure connection.
 
select-target-connection-replication-azure
9.In the grid under Select Objects, select check boxes for Account and Contact.
 
replication-select-objects
10. Save the package.
11. Run the package by clicking Run.
 
replication-created-package

Now you have your data replicated to a relational database.

 

ExpandedToggleIcon        Scheduling Package Execution

After we have replicated our data, we want to keep them up-to-date. For this, we will configure the package to run every hour during workdays automatically.

In order to keep down the number of Salesforce calls and speed up the recurring, we will use the Incremental Update feature. When using Incremental Updates, Skyvia will not copy all the data each time when the package is executed. Instead it will detect data that was changed in your Salesforce CRM since the last package execution, and then applies these changes to your database. It deletes records that were deleted in Salesforce since the last package execution, updates records that were updated, and inserts the newly inserted records. This allows reducing Salesforce calls and thus the cost of the replication operation.

Perform the following actions to set the schedule:

1.Under Schedule (at bottom left of the package editor) click the Set schedule button.
 
schedule-empty
2.In the Run every drop-down list select Week.
3.Click all the workdays in Days of week.
 
schedule-week-days
4.Click Occurs once at and select Recur every.
 
schedule-recur-every
5.Enter "1" (without quotes) into the Recur every box and click the Set time restrictions link.
 
schedule-every-hour
6.Set Start at to 8:00 and End at to 17:59.
 
schedule-time-restrictions
7.Save the package.

After this our package will run automatically every hour between 8:00 and 17:59 of every workday. You may monitor its execution using the package run history.

run-history-long

You can also visit Scheduling Packages to get more detailed information on setting a package schedule.