How to Set Up Replication of Dynamics CRM Data to SQL Azure

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

Creating Connections

In order to replicate data from Dynamics CRM to SQL Azure, first we need to create connections to Dynamics CRM and SQL Azure databases. If you have already created the necessary connections, you may skip these steps. To create a connection to Dynamics CRM, 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 Dynamics CRM. To quickly find it, you can either use the Type to filter box or filter connectors by categories using the All list (for Dynamics CRM, select the CRM category).
  4. The default name of a new connection is Untitled. Just click it to rename the connection, for example, to Dynamics CRM1.
  5. Enter the following connection parameters:
    • URL — url that you use to connect to Dynamics CRM. Usually it looks like: https://companyname.crm.dynamics.com
    • Username — your Windows Live ID.
    • Password — password for your Windows Live ID account.

      Connection Editor window

  6. Click Create Connection.

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 a new connection is Untitled. Just click it to rename the connection, for example, to SQL Azure.
  5. In the Server box, enter “TCP:". Replace "" 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 actions:

  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 Dynamics CRM 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 check boxes 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 Dynamics CRM 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 Dynamics CRM CRM since the last package execution, and then applies these changes to your database. It deletes records that were deleted in Dynamics CRM since the last package execution, updates records that were updated, and inserts the newly inserted records. This allows reducing Dynamics CRM 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 check boxes with all the workdays.

    Schedule screenshot 1

  4. Click Occur once at and select Recur every.

    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 09:00 and 18:00 of every workday.

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

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