Setting Up Dynamics CRM Replication 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.

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 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 Dynamics CRM.

4.

Specify the connection Name, for example, Dynamics.

5.

Enter the following connection parameters:

•

URL - the url that you use to connect to Dynamics CRM. Usually it looks like: https://companyname.crm.dynamics.com

•

Username - your Windows Live ID.

•

Password - the password for your Windows Live ID account.

6.

Click the Save button to save the connection.

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

1.

Click Connections in the page header to switch to the Connections page.

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 Dynamics CRM objects.

5.

In the Source header click Selectand select Dynamics CRM in the drop-down list.

6.

In the Source Connection drop-down list select the Dynamics connection.

7.

In the Target header click Select and select SQL Server in the drop-down list.

8.

In the Target Connection drop-down list select the Azure connection.

9.

In the grid under Select Objects, select check boxes for the objects to replicate. In our tutorial we will replicate account and contact.

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.

Under Schedule (at bottom left of the package editor) click the Set schedule button.

2.

In the Runevery drop-down list select Week.

3.

Click all the workdays in Days of week.

4.

Click Occurs once at and select Recur every.

5.

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

6.

Set Start at to 8:00 and End at to 17:59.

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.

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