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

• Data export service
• Replicate data to Azure SQL Database using Data Export Service
• Enable change tracking to control data synchronization
• Use change tracking to synchronize data with external systems
• Data Export Service Installation and Configuration
• Full capabilities

Pro’s

• It’s free
• integrates with Dynamics 365 and Azure SQL with configuration
• It has an API but it can work with configuration
• Monitoring and diagnostics on sync status
• Full initial syncronisation
• Metadata changes are fully syncronised

Con’s

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

• Restore an environment.
• Copy (either full or minimal) an environment.
• Reset an environment.
• Move an environment to a different country or region.
• You need to turn on change tracking, which can slow down the system a bit I believe

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

• How to Retrieve Incremental Changes from CRM in your ETL Process
• Dynamics CRM: Data Migration with SSIS (overview)

Pro’s

• KingswaySoft is powerful and you can transform the data.
• There are lots of developers with experience and the learning Kingswaysoft is quick
• Free developer edition
• Cheaper than Scribe

Con’s

• Purchase a KingswaySoft licence
• Developer to write the packages (it’s not code but does need a professional approach)
• You need to create an SQL server with SSIS and configure firewalls
• You need visual studio downloaded

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.

• Quickly Copy Dynamics 365 Online Data with Scribe Insight
• Scribe Online Replication Service: Part 1 — Getting Things Started
• How to Set Up Scribe Online with Microsoft Dynamics CRM Online or CRM On-Premises

Pro’s

• It’s configuration
• online service, so now need to download or have servers
• easy to learn and use
• no knowledge of SQL or SSIS needed

Con’s

• Purchase licence
• Developer to write the packages (it’s not code but does need a professional approach)
• no knowledge of SQL or SSIS needed
• Can be slow sometimes and no way to speed this up
• Costly. At least 4 times more expensive than kingswaysoft

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.

• Tibco Scribe vs Kingswaysoft and where lies PowerAutomate aka MS Flow
• Dynamics CRM: Scribe vs 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

• Quickstart: Use the Copy Data tool to copy data
• 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.