17.3 Read Scale-Out Architecture & DataAccessIntent Configuration

17.3 Read Scale-Out Architecture & DataAccessIntent Configuration

In high-volume enterprise environments, analytical reporting, heavy data extraction, and integration queries can contend with transactional business processing—such as sales order posting, warehouse picking, and payment processing. If long-running analytical queries execute against the primary transactional database, they acquire shared read locks (S locks) that can block write locks (X locks), leading to user wait times, lock timeouts (RT0012), and degraded system responsiveness.

To solve this concurrency challenge, Dynamics 365 Business Central cloud (SaaS) leverages Azure SQL Read Scale-Out architecture. By configuring the DataAccessIntent property on AL objects, developers instruct the platform to route heavy analytical and integration workloads to secondary read-only database replicas.

1. Read Scale-Out Architectural Principles in SaaS

Business Central SaaS instances run on Azure SQL databases configured with built-in high availability and replication. The database tier maintains two distinct compute nodes attached to the underlying database storage:

+-------------------------------------------------------------------------+
|                        BUSINESS CENTRAL CLOUD (SaaS)                    |
|                                                                         |
|    [Interactive Users / Posting]             [Power BI / ETL / APIs]    |
|                  │                                      │               |
|                  ▼                                      ▼               |
|   [DataAccessIntent = ReadWrite]          [DataAccessIntent = ReadOnly] |
+------------------┬--------------------------------------┬---------------+
                   │                                      │
                   ▼                                      ▼
+------------------------------------+  +---------------------------------+
|    PRIMARY READ-WRITE DATABASE     |  |   SECONDARY READ-ONLY REPLICA   |
|               NODE                 |  |              NODE               |
|                                    |  |                                 |
|  - Order Posting & Journal Check   |  |  - Analytical Queries & Reports |
|  - Real-Time CRUD Operations       |  |  - High-Volume API Data Streams |
|  - Exclusive Locks (X / IX)        |  |  - Zero Locking Contention with |
|  - Generates Transaction Log       |  |    Primary Transaction Engine   |
+------------------┬-----------------+  +-----------------▲---------------+
                   │                                      │
                   └──────────(Asynchronous Log Shipping)─┘
                              (Sub-Second Latency Stream)

Benefits of Read Scale-Out

1. Zero Lock Contention: Analytical queries on read replicas never acquire locks on the primary database, completely eliminating lock timeouts (RT0012) for transactional users.
2. Isolated Compute Capacity: Heavy data aggregation in queries and reports consumes CPU and memory on the secondary replica node without degrading transaction throughput on the primary node.
3. Near-Real-Time Data: Azure SQL replicates database changes asynchronously with sub-second replication latency (typically < 100 milliseconds), ensuring analytical queries reflect up-to-date data.

2. Configuring DataAccessIntent in AL Objects

Developers configure Read Scale-Out routing by setting the DataAccessIntent property directly in AL object definitions. DataAccessIntent is supported on three object types:

• API Pages (PageType = API)
• Query Objects (query)
• Report Objects (report)

Supported Property Values

• ReadWrite (Default): The runtime directs all database operations to the primary read-write database.
• ReadOnly: The runtime directs database read operations to the secondary read-only database replica.

1. API Page with DataAccessIntent = ReadOnly

API pages used for external reporting, Power BI semantic models, or third-party data warehouse ETL should always be configured with ReadOnly intent:

page 50135 "Cust Ledger Entries API"
{
    PageType = API;
    Caption = 'custLedgerEntries';
    APIPublisher = 'custom';
    APIGroup = 'analytics';
    APIVersion = 'v2.0';
    EntityName = 'custLedgerEntry';
    EntitySetName = 'custLedgerEntries';
    SourceTable = "Cust. Ledger Entry";
    DelayedInsert = true;
    ODataKeyFields = SystemId;
    Editable = false;
    DataAccessIntent = ReadOnly;

    layout
    {
        area(Content)
        {
            repeater(Group)
            {
                field(id; Rec.SystemId) { }
                field(entryNo; Rec."Entry No.") { }
                field(customerNo; Rec."Customer No.") { }
                field(postingDate; Rec."Posting Date") { }
                field(documentType; Rec."Document Type") { }
                field(documentNo; Rec."Document No.") { }
                field(amountLCY; Rec."Amount (LCY)") { }
            }
        }
    }
}

2. Query Object with DataAccessIntent = ReadOnly

query 50120 "Item Sales Analytics"
{
    QueryType = API;
    APIPublisher = 'custom';
    APIGroup = 'analytics';
    APIVersion = 'v2.0';
    EntityName = 'itemSale';
    EntitySetName = 'itemSales';
    DataAccessIntent = ReadOnly;

    elements
    {
        dataitem(Item; Item)
        {
            column(itemNumber; "No.") { }
            column(description; Description) { }
            dataitem(Value_Entry; "Value Entry")
            {
                DataItemLink = "Item No." = Item."No.";
                SqlJoinType = InnerJoin;
                column(salesAmountActual; "Sales Amount (Actual)")
                {
                    Method = Sum;
                }
                column(discountAmount; "Discount Amount")
                {
                    Method = Sum;
                }
            }
        }
    }
}

3. Report Object with DataAccessIntent = ReadOnly

report 50140 "Historical Sales Analysis"
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    DefaultRenderingLayout = ExcelLayout;
    DataAccessIntent = ReadOnly;

    dataset
    {
        dataitem(Customer; Customer)
        {
            column(CustomerNo; "No.") { }
            column(CustomerName; Name) { }
            dataitem(CustLedgEntry; "Cust. Ledger Entry")
            {
                DataItemLink = "Customer No." = field("No.");
                column(PostingDate; "Posting Date") { }
                column(AmountLCY; "Amount (LCY)") { }
            }
        }
    }
}

3. Runtime Routing Rules & Session Behavior

The Business Central Server tier evaluates several conditions before dispatching a request to the secondary replica:

1. Object Property Definition: The object must explicitly declare DataAccessIntent = ReadOnly (or be overridden by an administrator in the Database Access Intent List page).
2. Session Transaction Cleanliness: If the current AL session has previously written to the database (e.g., inserted, modified, or deleted records within the active transaction), the session is marked as "dirty". To ensure transactional consistency and prevent "dirty read" anomalies where code cannot see its own uncommitted writes, all subsequent queries in that transaction are pinned to the primary database, ignoring ReadOnly intent.
3. Independent Web Service & API Calls: Each incoming HTTP REST API request executes in its own isolated session. If an API page has DataAccessIntent = ReadOnly, that request is routed directly to the read replica from the very beginning of the HTTP pipeline.

4. Strict Constraints & Runtime Restrictions

Because secondary database replicas in Azure SQL are physically read-only, executing database write operations against a read replica will trigger fatal runtime exceptions.

What Operations Are Prohibited under DataAccessIntent = ReadOnly?

• Calling Rec.Insert(), Rec.Modify(), Rec.Delete(), Table.ModifyAll(), or Table.DeleteAll() on physical database tables.
• Calling database lock methods such as Rec.LockTable().
• Calling codeunits, procedures, or event subscribers that perform database modifications or state mutations.

Exam Watchout — Temporary Tables Are Allowed: Developers often ask whether temporary tables (SourceTableTemporary = true or var TempRec: Record Table temporary) can be used inside objects with DataAccessIntent = ReadOnly. Yes, temporary tables are fully supported. Temporary tables exist purely in-memory on the Business Central Server (NST) tier and do not issue SQL INSERT/UPDATE commands to the database server. Therefore, temporary buffer manipulation is completely valid on read-only replicas.

Summary Comparison Matrix