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

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

Creating Connections

In order to replicate data from Dynamics 365 to SQL Azure, first we need to create connections to Dynamics 365 and SQL Azure databases. If you have already created the necessary connections, you may skip these steps. To create a connection to Dynamics 365, 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 365. To quickly find it, you can either use the Type to filter box or filter connectors by categories using the All list (for Dynamics 365, 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 3651.
  5. Enter the following connection parameters:
    • URL — url that you use to connect to Dynamics 365. 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 365 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 Dynamics 365 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 365 CRM since the last package execution, and then applies these changes to your database. It deletes records that were deleted in Dynamics 365 since the last package execution, updates records that were updated, and inserts the newly inserted records. This allows reducing Dynamics 365 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 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.