Read Data from Dynamics 365 Business Central Replica Database (Data Access Intent)

Around two years ago Microsoft released this feature and I noticed still not many uses this great feature due to a lack of understanding and knowledge about it. Therefore I thought to write a few blog posts explaining it as much as possible.

One beautiful thing about Microsoft Dynamics 365 Business Central is that it allows you to have a read-only replica of the Azure SQL database and use that replica database to perform read-only queries to retrieve data. Queries to retrieve data running on the replica database, your main database instance will run smoothly without any performance impacts. No more support calls from users complaining about the slowness of the system or the system hanging for a few seconds due to some user running a transaction report without date filters in the morning.

You might think it would take a lot of work to implement and use this. If you thought that I’m afraid I have to disagree with you. It is very simple and you have two ways to control how your objects can read data from Business Central.

Option 01: AL Code

From Business Central Version 16, Reports, Pages (API Pages) and Queries have a new property called “DataAccessIntent“. If replicas are enabled, you can use this property to reduce the load on the primary database. DataAccessIntent property set to ReadOnly works as a hint for the server to route the connection to a secondary (read-only) replica, if one is available. When a workload is executed against the replica, insert/delete/modify operations aren’t possible. An exception is thrown at runtime if any of these operations are executed against the replica.

Option 02: User Interface

Another option you have is to change the Data Access Intent property for the objects directly through the user interface. The property value set on the AL code can be overwritten by using page 9880 Database Access Intent List page.

Below are what each option does:

• Default: the object uses the property value DataAccessIntent set on the AL
• Read Only: the object uses the read-only replica, and that limits making any insert/update/delete to the data
• Allow Write: the object uses the primary database, allowing insert/update/delete

As soon as we start talking about database replication or data replication, one question that comes to everyone’s mind is the delay between the replication. The quote below is from the Microsoft Docs article “Use read-only replicas to offload read-only query workloads, ” which says it can be from tens of milliseconds to single-digit seconds.

“Typical data propagation latency between the primary replica and read-only replicas varies in the range from tens of milliseconds to single-digit seconds. However, there is no fixed upper bound on data propagation latency. Conditions such as high resource utilization on the replica can increase latency substantially.“

Kennie is also kind of 😉 unofficially confirmed on Twitter that in a typical application, the lag is around milliseconds. I think most of the Business Central implementations can live with this lag.

Is the read-only replica transactionally consistent? The same article says that within a session connected to a read-only replica, reads are always transactionally consistent. So it is a YES!

I think now you know a little bit more about reading data from the replica database and its benefits. I hope that in the future, you are developing your reports, pages and queries to use the replica database more and more to read data.

Thank you and Regards,
Tharanga Chandrasekara