Migration Best Practices: Microsoft Data Export Service to Azure Synapse Link for Dataverse

If you haven’t already migrated from Microsoft’s Data Export Service (DES) to Azure Synapse for Dataverse, now is the time to do it: the service reaches end-of-support and end-of-life in November 2022.

 

The AppSource add-on was officially deprecated in November 2021. November 2022 when end-of-support status goes into effect.

Data Export Service (DES) is a Microsoft data replication service that enables organizations to perform data discovery and build robust reports using the very popular T-SQL query language by replicating Dataverse data to Azure SQL, a widely used cloud database. 

Azure Synapse Link for Dataverse provides many of the same features as DES, including syncing data in near real-time and automatically updating schema changes when columns are added, dropped, or modified in Dataverse. It also supports many of the same connections from applications (ADO.NET, ODBC) and tools (Power BI, Tableau).

 

A key difference between them is that Azure Synapse Link for Dataverse sends data from selected tables to a target data lake rather than a target Azure SQL. Storing replicated data in a data lake comes with several benefits including increased cost-effectiveness, much larger capacity limits, and leveraging of Azure AD and SQL logins for security. 

1) Testing Environment

Every migration plan should include setting up Azure Synapse Link for Dataverse in an environment that closely matches Production. In this case, since both DES and Azure Synapse Link for Dataverse can run side by side, you could even set this up in Production without impacting the current DES replication process.

 

2) Connection Testing

Once Azure Synapse Link for Dataverse is set up, it's important to test your connection to the Serverless SQL endpoint, run your more complex and long-running queries or ETL jobs with the new connections, and analyze how long it takes to execute using the ready to run Serverless SQL connection. If everything checks out, we recommend moving forward with updating all connections and jobs to use Serverless SQL.

 

3) Serverless SQL vs. Azure SQL or Dedicated SQL
Update all connections and jobs to use Serverless SQL as this is the simplest transition plan from a setup, maintenance, and, in many cases, cost perspective. However, as Serverless SQL queries cannot be optimized with indexes to help complex queries execute faster, for some organizations it may make more sense to create dataflows and pipelines to move the Dataverse data from the data lake to Azure SQL or Synapse Dedicated SQL (Microsoft's modern cloud data warehouse technology).

 

Azure SQL and Dedicated SQL give organizations the ability to optimize queries to the Nth degree using indexes, partitioning, and data distribution. However, it's important to be aware that creating these dataflows and pipelines to copy the Dataverse data to Azure SQL or Dedicated SQL is no small task, especially if there are many tables to replicate. Microsoft does provide some generic Azure Data Factory templates and Synapse walkthroughs to copy the data as guidance, but a data engineer is needed to build out these pipelines, deploy, and maintain them. It is recommended to use the Azure Synapse Link for Dataverse append only mode to send incremental changes to Azure SQL or Dedicated SQL.

 

Hopefully, Microsoft will provide a service to copy data in a more automated fashion from the data lake to Azure SQL or Dedicated SQL before the November 2022 end-of-support date.

 

4) Synapse Workspace

Azure Synapse Link can be set up with just an Azure Storage Gen2 resource that supports folder hierarchies, but we recommend setting it up with Synapse Workspace which provides a ready-to-run Serverless SQL engine that runs on top of the replicated data in the data lake. Serverless SQL allows users to perform data exploration and discovery using T-SQL queries, similar to Azure SQL.

 

5) Start preparing now!

All Dataverse organizations need to start planning their transition from DES to Azure Synapse Link for Dataverse as soon as possible. Depending on the complexity of your environment, a well-orchestrated migration could take anywhere from 1 to 6 months.

Now is the time to come up with a project plan to transition from DES to Azure Synapse Link for Dataverse. With some setup and testing, organizations that relied on DES for their data needs can get a clear understanding of their data to use cases and be able to decide whether Serverless SQL or a copy to Azure SQL or Dedicated SQL is the appropriate transition plan for them.