Using Read Scale-Out for Better Performance
INTRODUCED IN: Business Central 2020 release wave 1
The way Business Central Server interacts with the database can broadly be categorized in two patterns:
- Business processes that read and write data, such as codeunits that run from UI pages or web services.
- Analytical workloads that only read data, such as queries, reports, or API pages.
Where business process transactions typically are frequent and small, transactions from analytical workloads typically read many data and run for a long time. Mixing these two types of transactions in the same database often lead to performance problems. The analytical transactions can cause locking issues, which in turn impose waiting time for business processes. This condition also may disrupt the data cache in the database. Data will essentially be moved from the cache that speeds up business process transactions
If you run the Business Central database in a High Availability architecture, you can use the built-in Read Scale-Out feature in Azure SQL Database or SQL Server to load-balance read-only workloads. Read-Scale-Out uses the capacity of a read-only replica instead of sharing the read-write replica (also known as the primary database). This way, read-only workloads like reports, queries, and API pages, are isolated from the main read-write workload codeunits. So they won't affect the performance of business processes. As an added bonus, read-only workloads will run on a dedicated database and their performance will likely be better.
Get started using read scale-out
To start using read scale-out, do these three steps:
- Enable read scale-out on the Business Central database.
- As a developer, define the default database access intent (read-only or read-write) on selected reports or query objects.
- If needed, overwrite the default database access intent on reports, pages of the type API, and queries at runtime.
Enable read scale-out on the Business Central database
In the Business Central Online service, read scale-out is readily available and automatically enabled on the databases.
For on-premises installations, see Configuring your Business Central database for read scale-out to learn how to enable read scale-out on the databases.
Define the default database access intent in AL code
When you develop solutions for Business Central in AL, you can set the default intended database access intent (read-only or read-write) on reports and query objects that you create. For more information, see DataAccessIntent property.
Note
Setting the DataAccessIntent property to ReadOnly doesn't guarantee that your data access will be running on the secondary replica. It's merely stating that the code only requires read ability, so a read-only connection can be used.
For example, the property doesn't apply to queries running at arbitrary times during normal AL execution (that is, the server doesn't change to ReadOnly mode in the middle of a transaction). So for queries, the property only fully applies when they're exposed as an API/OData feed directly.
Overwrite the database access intent on reports, API pages, and queries
Read scale-out may introduce a slight delay when reading data from a database's secondary replica. The delay is caused by the way High Availability databases replicate data changes from the primary database to secondary replicas. If a delay isn't acceptable for an object, you can overwrite the default database access intent.
For more information, see Managing Database Access Intent.
Enable read-only intent on OData GET requests
In the Business Central Online service, almost all OData GET requests use read-only intent for data access. If you're using read-scale out for Business Central on-premises, you have the option to enable or disable read-only intent. To do so, change the Enable Read-Only Intent on GET Requests (ODataReadonlyGetEnabled) setting of the Business Central Server. This setting is enabled by default. If there are specific requests on which you don't want to use read-only intent, specify these requests in the Objects Exempt from Read-Only Intent on GET Requests (ODataReadonlyGetDisabledForObjects) setting. For more information, see Configuring Business Central Server - OData.
Note
Setting the DataAccessIntent property to ReadOnly doesn't guarantee that your data access will be running on the secondary replica. It's merely stating that the code only requires read ability, so a read-only connection can be used.
For example, the property doesn't apply to queries running at arbitrary times during normal AL execution (that is, the server doesn't change to ReadOnly mode in the middle of a transaction). So for queries, the property only fully applies when they're exposed as an API/OData feed directly.
FAQ about read scale-out
Which objects are supported?
Reports, API pages, and query objects.
What happens if there's a failover to the replica being used for reading?
The Business Central Server specifies database access intent as a parameter in the connection string to the database. The Business Central Server doesn't know about primary/secondary replicas. So if there's a fail-over, the Business Central Server will be redirected by the database.
Why is there a delay in when a data change is available on the secondary replicas?
When data is committed to the primary database, the transaction log entries are log-shipped to the transaction log for the secondary replicas. Then, an asynchronous transaction log "redo" operation makes the data available in the secondary databases.
What about Sandboxes?
Sandbox environments can't be enabled with read scale-out. Objects that use the DataAccessIntent property will compile and run. But they'll just access the primary database.