Export Data to Azure SQL!

Bring Your Own Database

Dynamics 365 For Finance and Operations, or Fin & Ops, has a feature that lets you export data entities into an external Azure SQL database. This can be any number of SQL databases which can be broken up by functional area or legal entity. You can also do full and incremental pushes to those databases on a schedule and get access to this data just like it was on any other SQL database, similar to 2009 and 2012. This is a very different solution than the Entity Store, however. BYOD is recommended for use in scenarios when you want to use reporting tools other than Power BI, you need SQL level access or would like integrate with other systems, potentially with SQL triggers. 

Prerequisites

You should already have an Azure SQL instance and database ready before getting started. Not sure how to that? Check out this video.

Getting Started

go to System Administration > Workspaces > Data Management > Configure entity export to database

Click "New"

If you have access to the Azure Portal Management blade for this particular Azure SQL instance, you can get the template for the connection string from there.

Assign a name, description, paste in your connection string and replace the user/password portions. Go ahead and save that and also be sure to validate it. Now that you have an export defined, go to "Publish". Find the entity we are looking for and click "Publish". In this instance, we just want to publish our customers. But wait! We got an error!

'SalesMemo' field is not supported for publish with CCI

Currently, the "Create clustered column store Indexes" option is only supported on Azure SQL Premium DBs. This option optimizes the target Azure SQL database for SELECT queries by creating clustered columstore indexes, or CCIs, for entities copied from Fin&Ops. In our instance, I am using a standard Azure SQL instance so we'll need to turn that off to proceed. Let's turn that option off and try again.

Success!

Now, let's look in SQL to see what we have.

As you can see, we only have the schema for our one entity that we published and nothing else. At this point, we do not have any data. We have the place where data will live but no data yet. Let's create an export job to get our customer data out in our database. We will create this like any other export. However, you'll notice we know have a new target data format in our list.

Select the Azure SQL target data format along with our entity. Also make sure that "incremental push" is selected. Despite the name, our first export will be all records. This is because Fin & Ops, and SQL underneath it, don't know which records have been tracked related to this entity and which ones haven't so its going to export everything the first time, keep track of what was exported and on later iterations only push out what it knows has changed. Whenever there is a change, that insert, update or delete will be tracked and pushed out to Azure SQL when this export job runs again. For now, we aren't going to schedule this on a batch job. That will be for another post. Let's go ahead and run our export.

We'll have to address that warning. In this environment, which is using standard demo data, change tracking for this entity is not enabled. We can address that by clicking on our entity name in the grid then going to "CHANGE TRACKING" and clicking on "Enable entire entity"

Now that we have change tracking enabled for this entity, let's give it a try and export it.

Looks like everything went well. Let's take a look at SQL.

Based on a quick review, we now have data in Azure SQL! Also, as a quick check, we can see that our row counts (cardinality) match between our export and a target Azure SQL database.

All of this is great and can help fill the need for ad-hoc reporting, unique demands or uses for data outside of the entity store. However, there are a few limitations. Microsoft has a section in this article that covers it in more detail but in short they are: the Target SQL box can't have any locking, exports are legal entity specific, composite entities are not currently supported and some entities that do not have unique keys may not be exported in an incremental fashion.