D365BC SaaS Read-Only environment

One of the greatest hidden features using Dynamics 365 Business Central on a SaaS environment is the availability of a read-only replica of the database.

Until latest versions it was not possible to manage the use of this database without Development/Technical skills. There is a great post from Demilliani about it -> https://demiliani.com/2020/05/15/read-scale-out-with-azure-sql-and-dynamics-365-business-central/.

But in recent version, MSFT bring this feature to a new friendly level!

All is possible to manage via Feature Management and Database Access Intent.

• Feature Management

Activate Feature: Report read-only data access.

• Database Access Intent List

Search and access to Database Access Intent List.

MSFT Learn -> https://learn.microsoft.com/en-GB/dynamics365/business-central/admin-data-access-intent

The page lists all reports, pages, and queries. The Access Intent column includes one of the following values:

Default - Indicates that the object uses the predefined database access intent.

Allow Write - Sets the object to use the primary database, allowing the user to modify data.

Read Only - Sets the object to use the database replica, which means that the user can only view data, not change data.

By default, all reports, pages and queries are with value: Default.

This is a Page over the system table AllObjWithCaption (2000000058).

Using standard filtering, it's not possible to filter by Access Intent column.

As a workaround, it's possible to use Open/Edit in Excel for bulk changes.

It seems to be a Best Practice to use this page for the exceptions! If a report/page/query is a Read Only and do not require online data refresh, it should be updated here for Read Only.

I made some tests changing a report to Read Only (Chart of Accounts report) and testing how long can take to refresh the Read-Only replica database.

My conclusions were:

• It's not real-time. With two sessions (one creating a new G/L Account and other running the report), it was possible to verify it's not real-time.

• It was few seconds delay. After 3/4 seconds, I tried again to run the same report and the new G/L Account was already included in the report.

Following an approach to manage Database Access Intent will definitely improve BC overall performance. And all with a self-service functionality and no extra costs in SaaS! I cannot quantify the effort in time and money to have a similar approach and solution on a On-Prem environment like on good old days :)

Thanks Microsoft to turn a challenging architecture into a self-service functionality!