Microsoft Dynamics 365 for Finance and Operations is a popular ERP system used by medium to large organisations. D365 is a cloud based offering with no access to the backend DB.

In this blog I’ll provide you the key steps to export the data out of D365 onto an Azure SQL Server. The steps listed here are generally referred to as export to BYOD (Bring Your Own Database). The data in D365 is exposed to the users via entities.

Before you start you will need to have data management access to D365. Data management access gives you access to the data management page and enables you to publish/export entities.

Step 1 - Check Data management Access

Check you have access to data management page. The Data management option can be found under the Workspaces in the D365 side menu.

Step 2 - Create Azure SQL DB

Check you have access to data management page. The Data management option can be found under the Workspaces in the D365 side menu

Step 3 - Configure BYOD Connection

Go to D365 data management page and click “Configure entity export to database”. From the “Configure entity export to database” page click New to create the connection to BYOD.

The following fields are required

  • Source name – This will be the connection name
  • Connection string – Connection string to the BYOD

Connection string format

Data Source=[SQL Server Name];Initial Catalog=[SQL Database Name];Integrated Security=False;User ID=[SQL Login ID];Password=[Password];Connect Timeout=60;Encrypt=False;TrustServerCertificate=False

Click validate to validate the connection. On successful validation Save and exit the window.

Step 4 - Publish Entity Page

On exiting the window D365 will bring you back to the “Configure entity export to database” window. Select the new connection you created and click “Publish”. If you are not on the “Configure entity export to database” page, return back to “Data management” > “Configure entity export to database”.

Step 5 - Publish Entity

If you have followed Step 5 you will be on the Entity page. This page will list all available entities which you can export to the BYOD. Before exporting the entity, the entity needs to be published. Publishing the entity will create the table on the destination SQL Server to hold the data.

Select all entities to be published and click publish. You will receive a notification from D365 when the entities have been successfully published.

Step 6 - Export Job

To create the export job, return back to Data management > Export. Following field is required

  • Group name – This is the export job name

Click “Add entity” or “Add multiple”. Add multiple will enable you to select multiple entities at once to be added to the job.

  • Select entities to be exported
  • Target data format – This is the name of the BYOD connection you created on Step 3
  • Default refresh type – Make this full push only. In part 2 I’ll provide the steps to setup the incremental push
  • Export across all companies – Turn is on to export data across all companies in D365 not just the company you are logged in.

Click Add Selected

Note: The export across all companies might have to be turned on from the “Framework parameters” option on the Data management page. To turn this on go “Data management” > “Framework parameters” > “Bring your own database” > “Enable all company export”

Step 7 - Run Export Job

You can now schedule the export job or click "Export" to run the export