Microsoft Dynamics 365 for Finance and Operations (D365FO) provides Bring Your Own Database (BYOD) feature, which helps you to export data entities to your own external databases serving businesses different needs.

Overall, with BYOD, you can complete the following tasks:
● Configure Microsoft Azure SQL databases so that you can export entity data from D365FO into it.
● Export either all the records (full push) or only the records that have changed or been deleted (incremental push).
● Export on regular schedule with recurrence option of the Finance and Operations batch framework.
● Access the entity database by using Transact-SQL (T-SQL), and even extend the database by adding more tables.
● Export entities into multiple databases.

Some common scenarios that BYOD is utilized are:

● Export data from Finance and Operations into your own data warehouse.
● Get data for other analytical purposes that use other tools requiring T-SQL access to data.
● Perform batch integration with other systems.
● Any other purposes of testing, validating, or integrating data.

If you have integrated solutions that require direct T-SQL access to the database, BYOD is the recommended upgrade path.

To use BYOD, you will need to complete several steps as below:
1. Create a SQL database to connect with your D365FO environment
2. Configure the BYOD connection in D365FO client.
3. Identify data entities that you want to use and publish them (publishing a data entity makes them available to export to BYOD)
4. After the above set up steps, you can create an export in Data Management Workspace with target format is your configured external database.

In this article, we will go over how to configure BYOD. In the next articles, I’ll provide more examples of exporting to BYOD and some common errors.

1. Create a SQL database to connect with your D365FO environment

You can create an Azure SQL Database easily on Azure portal.

● Go to Azure portal -> SQL databases -> Add

You should also create a SQL user account for sign-in to the database. Please remember to note and save the server name, database name, and the SQL user account and password. These details are required when configuring the database on D365FO client.

● After selecting the new server as above, choose the appropriate Resource group and pricing tier as you want

Here, for example, I’m using a minimum basic SQL database. However, tiers affect functionality options. The basic tier does not support clustered columnstore indexes.

For integration with a business intelligence (BI) tool, premium databases with the option of clustered columnstore indexes (CCIs) can meet your performance needs. Since CCIs are in-memory indexes that enhances the performance of reading queries that are the major part of analytical and reporting tasks.
For general storing or integration purposes, you may find a standard database sufficient, however, depending on your data size and performance requirements, you can always go for a premium.
● Azure DB Connection String format we use here is ADO.NET

2. Configure the BYOD connection in D365FO client.

Go to System administration > Workspaces > Data management > Configure entity export to database.

To configure a new database, select New, and then enter a unique name and a description for the new database. Then, you can paste the connection string in the Azure portal here and remember to replace the appropriate authentication details (highlighted in screenshots).
Also, as explained above, here I use the basic database tier, so I have to turn off clustered columnstore indexes option. Otherwise, you will get an error.

Select Validate, and make sure that the connection is successful. One common error in this step is an error about not being able to access Azure server

The solution for the above error is enabling access in the Azure portal. So you need to go to the Azure DB > Firewall settings > Add client IP to allow access.

3. Publish data entities

To export to Azure DB, data entities must be published to it. Publishing a data entity to Azure DB creates tables and necessary structures of the data entity in Azure DB.
If data entities are not published, when exporting data entities to Azure DB, an error will show up.
● Go to System administration > Workspaces > Data management > Data entities
On default, you can see that the value of published field is No. To publish a data entity, you can simply select it and click Publish.

4. After successfully configuring the BYOD connection, we can now export data entities to it.

After entities are published to the destination database, you can use the Export function in the Data management workspace. You can create an export project with target format is now selected as your newly added BYOD.

In the next article, we will go through examples of exporting full push an incremental push to BYOD and some important behaviors of these options. If you face any issues importing your own database and require assistance, I-Net Dynamics is a certified Microsoft Dynamics re-seller with a experienced team of local support professionals who can assist you!