| title | description | ms.custom | ms.date | ms.reviewer | ms.topic | author |
|---|---|---|---|---|---|---|
Using Read Scale-Out for Better Performance |
Learn how to use read scale-out in Business Central to improve performance |
bap-template |
04/01/2021 |
jswymer |
overview |
jswymer |
[!INCLUDE2020_releasewave1.md]
The way [!INCLUDEserver] 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 [!INCLUDEprod_short] 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.
To start using read scale-out, do these three steps:
- Enable read scale-out on the [!INCLUDEprod_short] 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 [!INCLUDEprod_short] database
In the [!INCLUDEprod_short] Online service, read scale-out is readily available and automatically enabled on the databases.
For on-premises installations, see [Configuring your [!INCLUDEprod_short] database for read scale-out](database-read-scale-out-configuration.md) to learn how to enable read scale-out on the databases.
When you develop solutions for [!INCLUDEprod_short] 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.
[!INCLUDEdatabase_access_intent_note]
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.
In the [!INCLUDEprod_short] Online service, almost all OData GET requests use read-only intent for data access. If you're using read-scale out for [!INCLUDEprod_short] 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 [!INCLUDEserver]. 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.
[!INCLUDEdatabase_access_intent_note]
Reports, API pages, and query objects.
The [!INCLUDEserver] specifies database access intent as a parameter in the connection string to the database. The [!INCLUDEserver] doesn't know about primary/secondary replicas. So if there's a fail-over, the [!INCLUDEserver] will be redirected by the database.
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.
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.