How to Set 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 are 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.

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 +NEW in the top menu.
  2. Click the Connection button in the menu on the left.
  3. In the opened Select Connector page, select Salesforce. To quickly find it, you can either use the Type to filter box or filter connectors by categories using the All list (for Salesforce, select the CRM category).
  4. The default name of new connection is Untitled. Just click it to rename the connection, for example, to 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.

    Connection Editor window

  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.

    Log In window

  8. Click Create Connection.

    Connection Editor window 2

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

  1. Click +NEW in the top menu.
  2. Click the Connection button in the menu on the left.
  3. In the opened Select Connector page, select SQL Server. To quickly find it, you can either use the Type to filter box or filter connectors by categories, using the All list (for SQL Server, select the Database category).
  4. The default name of new connection is Untitled. Just click it to rename the connection, for example, to SQL Azure.
  5. In the Server box, enter “TCP:<server name>”. Replace “<server name>” with your actual server name.

  6. Specify your User Id, Password, and Database to connect to.

    Connection Editor window

  7. Click Create Connection.

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

Creating Package

To create a replication package, perform the following steps:

  1. Click +NEW in the top menu.
  2. In the Integration column, click Replication. The replication package details page will open.
  3. Rename your package by clicking and editing the package name. The default package name is Untitled. Please note, if you omit this step, the package name will remain Untitled in the list of created packages.

  4. Under Source, in the Connection drop-down list, select the Salesforce connection.

    Source Connection

  5. Under Target, in the Connection drop-down list, select the SQL Azure connection.

    Target Connection

  6. In the grid under Select Objects, select checkboxes for the objects you want to replicate. In our tutorial we will replicate Account and Contact.

    Select Objects table

  7. Click the Create button.
  8. Run the package by clicking Run.

Now you have your data replicated to a relational database. On the Monitor tab, you can check the Run History of the package.

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 were changed in your Salesforce CRM since the last package execution and apply 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. Click Schedule on the left side of the toolbar.
  2. Under Run every, select Week.

  3. Under Days of week, select checkboxes with all the workdays.

    Schedule screenshot 1

  4. Click Occur once at and then select Recur every in the list.

    Schedule screenshot 2

  5. Enter “1” (without quotes) into the Recur every box and click the Set time restrictions link.

    Schedule screenshot 3

  6. Enter 9:00 and 18:00 to the corresponding boxes.

    Schedule screenshot 4

  7. Click Now to put the schedule into action immediately or select At a specific time in the Starting list and specify the necessary date and time you want the schedule to be enabled from.

    Schedule screenshot 5

  8. Click Save to schedule package execution.

After this, our package will run automatically every hour between 9:00 and 18:00 of every workday.

You may check its execution on the Monitor tab in the Run History.

Run History

Click a package run to open the History Details window and see the detailed information on this run.

History Details window

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