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.

By default, Skyvia creates and maintains a copy of the data. It also replicates record deletions from Salesforce, deleting the corresponding records in the target. In this tutorial, we don’t want to replicate deletions, and want to store all the history of replicated data, so we will use the History mode instead.

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 +Create New in the top menu.
  2. Click Connection in the menu on the left.
  3. 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. 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.

  7. If you have selected User Name & Password, on the previous step, specify your Salesforce account e-mail, password, and security token. These parameters are stored on Skyvia.

    Otherwise, if you have selected OAuth 2.0 authentication, click Sign In with Salesforce and sign in. 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 +Create New in the top menu.
  2. Click Connection in the menu on the left.
  3. 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. 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 an integration that performs the necessary data replication operation.

Creating Integration

To create a replication, perform the following steps:

  1. Click +Create New in the top menu.
  2. In the Integration column, click Replication.
  3. Rename your integration by clicking and editing the integration name. The default integration name is Untitled. Please note, if you omit this step, the integration name will remain Untitled in the list of created integrations.

  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. In the replication tasks, enable the History mode. For this, open the replication task by clicking the Edit button next to the Account object and, in the Mode list, select History. Skyvia will store all the history of replicated changes.

    List task editor

  8. Click Save task and repeat these actions for the Contact object.

  9. Let’s also assume that we don’t want to replicate the contacts’ emails. However, we still want to check for duplicate emails later. Here Skyvia’s Hashing feature comes in handy. To enable hashing for the emails fields, open the replication task for the Сontact object, point to the corresponding email field, click Unhashed, and then select Hashed.

    Hashing

  10. Click the Create button.
  11. Run the integration 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 integration.

Scheduling Integration Execution

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

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 - week

  4. Click Occur once at and then select Recur every in the list.
  5. Enter “1” (without quotes) into the Recur every box and click the Set time restrictions link.
  6. Enter 9:00 and 18:00 to the corresponding boxes.

  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 - final schedule

  8. Click Save to schedule integration execution.

After this, our integration 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 an integration run to open the History Details window and see the detailed information on this run.

History Details window

You can also visit Scheduling Integrations to get more information on setting schedule.