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!