Use Database Access Intent List to Boost Performance in Business Central

-

Introduction

For any Business Application, database replication is a necessity for the application to be highly available, fault tolerant and performant without any data throughput issues.
Business Central too follows the database replication utilizing a technique known as "Read Scale Out" or "Leader/Follower or Master/Slave Replication Architecture".

Basically, the business operations(Codeunits, Pages, POST/PUT/DELETE API calls) which create the data in the system are relatively quick as compared to Analytical operations (Reports, Queries, GET APIs calls)which read a whole bunch of data from a lot of tables at once.

So, in this case, performing both business and analytical operations on the same database can cause performance issues as tables can be locked by an analytical operation while a business operation tries or access or modify that data.

A solution for this is using multiple copies of the database in a leader follower architecture.
All the write transactions are directed towards the leader database which are then forwarded to the follower databases.
All the read transactions can be forwarded to either the leader or the follower database.

Please note that this all only happens for Production Environments.
Sandbox environments only have the primary database.

Side Note 

If you're wondering what happens when a User tries to read from a follower database before the leader database was able to send the updated information there (This is called a stale replica). 



This is an accepted risk when using this architecture.
According to CAP Theorem only two of the three properties, Consistency, Availability and Partition Tolerance can be guaranteed.
  • Consistency - Every read receives the most recent write or an error.
  • Availability - Every request receives a (non-error) response, without the guarantee that it contains the most recent write.
  • Partition Tolerance - The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes.
Out of these, partition tolerance has to be tolerated as network failures are inevitable so most systems have to choose between Consistency and Availability.
In most cases, RDBMS systems choose Consistency over Availability (as does Business Central) and most NoSQL databases choose Availability over Consistency.

Explanation

Setting the property DataAccessIntent to ReadOnly doesn't guarantee that all the operations that a particular object does are going to be routed via the "replica database".

For example, consider a case where we are using a processing report to update a field on the Item table based on the calculations done using a Query object.
Here, when the operation started, given that the processing report intents to update the Item table, the operation was forwarded into the Primary database, now when the Query is executed to fetch the generate the necessary value, the database is still going to be the Primary database.
To summarize, the database is not switched in the middle of a transaction.

For API Pages where we are only going to be fetching the data from Business Central, we have to set the API page's Editable property as false and only then we can set the DataAccessIntent to ReadOnly.
We don't have this property for any other page types.


For Reports, we can set the DataAccessIntent property directly and if it is a processing report that tries to make any modifications to the data then we end up with a run-time error.


For Queries, we can set the DataAccessIntent property directly as well with the same conditions as the Report object but in effect, the only time queries benefit from the "replica database" is if they are used directly as APIs.


Almost all the ODATA GET requests are directed to the "replica" database by default in Business Central on Cloud.
In the On-Premise Environment, we have a setting "ODataReadonlyGetEnabled" that controls this behaviour.

Further, there is a list page in Business Central "Data Access Intent List" which can be used to modify the Data Access List of any Page, Query or Report object.
The Default Value indicates that the object should use the pre-defined value defined in AL.
The same rules as above are followed when we update the "Data Access Intent" values in the Data Access Intent List page.

Conclusion:

Thus we saw how Business Central architecture uses the "read scale out" method to ensure consistency and availability and how we can leverage those to boost our application's performance.

Happy Coding!

Comments

Post a Comment

Popular posts from this blog

Using Notifications in Business Central via AL

-
Image
Introduction Notifications in Business Central are alerts that appears in the Notification bar based on User actions. Notifications stack up from top to bottom, lasting until the user dismisses them, including those from sub-pages.  Validation errors are prioritized and shown before other notifications. We can use this to alert the user regarding something without taking all the user's notification towards it, in the way messages or errors do. They also have the option to allow Users to make a corrective action by embedding the action button directly into the notification. Let's see how it works. Source Code Pre-requisites Business Central OnPrem/Cloud. References Notification - Business Central Docs Configuration Here, for an example, I've created one simple page which takes two inputs. 1. The message that is to be shown in the notification. 2. The message to be shown after the User clicks on the action embedded in the notification.     And I have two actions which I'l...
Read more

Actionable Error Messages in Business Central

-
Image
Introduction Error handling is an important concept in every technical field.  It helps programs deal with unexpected problems and mistakes smoothly.  It makes sure software works reliably and doesn't crash unexpectedly.  Error handling also helps developers find and fix issues quickly, making the software better for users. Plus, it gives users clear messages when something goes wrong, making their experience smoother. It shows that the team has considered the scenario and has measures in place for it indicating a well designed solution. Microsoft has an amazing document which lists the things to keep in mind for writing resilient code. In Business Central, we have try functions to handle errors and error function to show those errors to the Users.  In this blog, we'll learn how we can enhance the error messages so that the Users can resolve the errors themselves or at the very least we can point them towards where the error is. Pre-requisites Business Central OnPrem...
Read more

Configure Gmail SMTP service in Business Central without "Allow Less Secure" setting

-
Image
 Introduction Business Central has great email features like sending email, editing it with Rich Text, adding attachments, etc, but to send emails, you'll need to connect it with services like Outlook or Gmail. There is no outlook side configuration that is to be done if you are using Microsoft Outlook however if you are using Google's Gmail then there's a litte change that has happened on the configuration end. Starting May, 2022, Google has removed the "Allow Less Secure Apps" settings from the Google Accounts, which was essential is using Gmail to send emails from Business Central. However, to replace that we have "App Passwords" which we can use instead. Also, just something to keep in mind, Google is going to remove this from the "Google Workspace" accounts as well by Sept, 2024 so it would be wise for you to migrate your configurations to use app passwords. Reference 1.  How to Setup Email in Business Central 2.  Google Support - Less sec...
Read more