Read-scale out with Azure SQL and Dynamics 365 Business Central

In the last month I had the chance to test the read-scale out feature of an Azure SQL Database with Dynamics 365 Business Central. Others have talked about this opportunity in the past and I want to share here my experience.

The Read Scale-Out feature permits you to have a read-only replica of your Azure SQL database and use that replica to perform read-only queries instead of using the main instance and so without affecting its performance. This feature is enabled by default on Premium, Business Critical and Hyperscale service tiers. Read Scale-Out cannot be enabled in Basic, Standard, or General Purpose service tiers. Read Scale-Out is automatically disabled on Hyperscale databases configured with 0 replicas.

When you create an Azure SQL database with Premium tier, you can see that read-scale out is selected by default:

You can disable this feature also after db creation by selecting the database and then going to the Configure section:

By using Azure Powershell you can check if Read-Scale Out is enabled for a database with the following command:

Connect-AzAccount

Get-AzSqlDatabase -ResourceGroupName YourResourceGroup -ServerName YourServerName -DatabaseName YourDatabaseName| Format-List DatabaseName, Edition, ReadScale, ZoneRedundant;

As you can see, I have it enabled on my database:

You can also use Azure Powershell for enabling or disabling Read-Scale Out feature:

Set-AzSqlDatabase -ResourceGroupName YourResourceGroup -ServerName YourServerName -DatabaseName YourDatabaseName -ReadScale Disabled

To use the read-only replica of your database, you need to connect to that database with the ApplicationIntent property setted as Readonly (just append ApplicationIntent=ReadOnly to your connection string). If you’re using SQL Management Studio (SSMS) this can be done in the Additional Connection Parameters tab as follows:

When clicking on Connect, you are redirected to the read-only replica and you can perform here your queries.

What happens if you try to perform a write operation? Error! you’re on a read-only replica:

You can verify that you’re connected to a read-only replica by executing the following T-SQL command:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') as [Updateability];

and this is the output of this query:

If you insert a new data or update existing data from the primary connection, the read-only replica is updated (after a small delay).

Why this feature is extremely interesting for Azure SQL in general but for Dynamics 365 Business Central in particular? Because if you have lots of read-only workloads (like reports or datawarehouses ETL activities that pulls data) you can have a big performance benefit on your main instance and all without extra costs. Your primary database node will not be affected on long running read-only queries or processes because they are not sent to the primary node anymore and your production workloads (writing processes) can have a benefit.

How can you use this feature with Dynamics 365 Business Central?

Starting from Dynamics 365 Business Central 16, reports, queries and API pages have a new property called DataAccessIntent. This new property permits you to set whether to get data required by this object from a read-only replica of the database or from the primary database:

The ReadOnly value acts as a hint for the server to route the connection to a secondary (read-only) replica, if available. When a workload is executed against the replica, insert/delete/modify operations aren’t possible and if any of these operations are executed against the replica, an exception is thrown at runtime.

In Dynamics 365 Business Central SaaS, read scale-out feature is automatically enabled by default, so it’s highly recommended to use the DataAccesIntent property on your read-only queries.

In on-premise scenarios (Azure SQL or SQL Server) you need to enable this feature by your own. Read-only routing on SQL Server is available only from version 2016 and above. For on-prenise scenario, you need also to enable the Enable SQL Read-Only Replica Support (EnableSqlReadOnlyReplicaSupport) setting in the service tier configuration.

You can control the DataAccessIntent property for your objects also directly via user interface. For doing that, search for the Database Access Intent List page:

Here you can set the following values:

• Default: the object uses the predefined access intent.
• Allow Write: the object uses the primary database, allowing the user to modify data.
• Read Only: the object uses the database read-only replica, which means that the user can only view data (no insert/update/delete).

What’s my experience so far?

For large read-only reports or queries or external ETL processes that needs to query data from your Dynamics 365 Business Central database, this is absolutely recommended (you will gain in performances and on CPU loads).

If you have external application that uses direct SQL access to your on-premise database for read-only tasks, I suggest to change the database connection string by appending ApplicationIntent=ReadOnly.

If you have API pages that exposes entities only for reading data (GET), I suggest to use this property on the API page definition.

The only “problem” I’ve seen so far (if it’s a problem for your scenario) is that data from the primary instance is not immediately replicated to the read-only replica but there’s a small delay. If you have real-time reporting that could be affected of this, you need to remember this aspect. I think this is extremely interesting for statistics, analytic reports, periodic reports that analyzes data by periods and so on and I suggest to give it a chance.