Dynamics 365 and replicating to an SQL database for reporting

Image for post
Image for post

Data migration takes at least 4 times longer than everyone estimates #HoskWisdom

It’s a common requirement to create a different data source for reporting this allows data to be aggregated and reduces the load on your production instance of Dynamics 365.

This post will look at the options to replicate your Dynamics 365 database to a SQL database.

Azure SQL database

Azure SQL is common database that is familiar with many report writers. The common requirement is to copy your Dynamics 365 database to an Azure SQL server.

What are the options for you to do this

The Data export service

The Data export service is a good choice because it’s free and can works with Dynamics 365. Choosing what entities you copy is setup with configuration with no code needed.

The Data export service is an add on that replicates your Dynamics 365 database (or CDS) into an Azure SQL Database. After the initial copy it syncronises the changes by using changing tracking.

It copies to Azure SQL subscription, or SQL Server on an Azure VM

Here are some links to learn more

Pro’s

Con’s

If you do any of the below in an environment, you the export profile must be deleted and recreated

KingswaySoft

KingswaySoft is a popular tool for data migration, it works with SQL server and has a connector with Dynamics. You can use KingswaySoft to synronise data to an SQL database.

Some useful articles

Pro’s

Con’s

Scribe online

Scribe is tool to integrate and syncronise business applications and it has an out of the box connector for Dynamics 365. Scribe online is a service, so you don’t need to download anything.

I haven’t used Scribe or been on a project with Scribe but people who have used it, usually recommended it.

Pro’s

Con’s

Scribe versus KingswaySoft versus Data Export service

It seems the Data Export service is the best choice and it is if you only want to replicate your Dynamics 365 database. Kingswaysoft and Scribe can do it and a whole lot more. KingswaySoft and Scribe can integrate between systems with powerful transformation functionality.

In the scenarios where integration has been needed Kingswaysoft has been chosen over Scribe because Kingswaysoft is a lot cheaper and the project has had developers on it. The big benefit of Scribe is you don’t need to know SQL or SSIS.

I haven’t used Scribe or been on a project with Scribe but anyone who has usually recommended it.

Data migration and integration always take longer than you think and is more important than most realise.

Other options

Microsoft Power Automate and Logic apps are no code tools to integrate Microsoft business applications. These are power no code solutions with Azure capacity. The connectors and improvements Microsoft have made in Power Automate has made it a powerful tool.

What use to take C# code and a week, can now be done in a few hours with Power Automate and Logic apps. If there are a lot of integrations, this choice might lead to more maintenance and there is some missing functionality which could make it unsuitable.

Azure Data Factory

Azure data factory is a is Azure cloud ETL service that can run SSIS packages. It’s a service so you don’t need to worry about any infrastructure. It has a code free UI, it’s configure rather than code.

It’s offers Azure scale and connects with Microsoft services out of the box. The pricing seems competitive and based on compute/usage.

The downside is few developers have experience using it, so there would be upskilling involved.

Azure Data lakes

Microsoft are investing in Data lakes, making it easy to export to and promoting it. Azure Data Lakes are the direction of travel and I have the feeling Microsoft are going to invest and make this attractive for people to use.

Exporting Common Data Service data to Azure Data Lake

Azure Data lakes are made for large datasets and as we capture more data, it’s likely we will need to upgrade the tools we use.

I haven’t used a Data Lake yet but I believe this will change going forwards, so it’s something to watch and investigate if you have time.

Written by

Have been working with Dynamics 365 since version 4 and enjoy reading and delivering enterprise projects

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store