Read only replica Azure SQL

In this Azure SQL tutorial, we will discuss various aspects of a read-only database in Azure SQL. We will discuss, what is a read-only database in SQL, how to make a database read-only in Azure and vice versa, advantages and disadvantages of a read-only database.

• Advantages of using read-only replica
• Disadvantages of using read-only replica
• Disable the read scale-out feature while creating the database
• Configuring the read scale-out feature using PowerShell
• Disable read scale-out using PowerShell
• Enable read scale-out using PowerShell
• Verify if you are connected to a read only replica
• Configuring the read scale-out feature using REST API
• Enable read scale-out using REST API
• Disable read scale-out using REST API
• Connect to a read-only secondary replica
• Conclusion

Introduction

In real-time scenarios, you have a primary database and some secondary databases i.e. replicas of the database with the same computing performance.

You can create some read-only replicas of the database. The intention behind this approach is to separate the read-write requests and the read-only requests to a database.

This helps to improve the overall performance of the database because the load on the primary database will decrease. Because it will handle only the read-write operations only. The rest of the read-only requests will be forwarded to the secondary read-only replica.

For implementing this architecture, you need to enable the read scale-out feature in Azure SQL.

This read scale-out feature is a feature under the High-Availability architecture of some specific databases. It is by default enabled when you create new Premium or Business Critical databases.

Also, if you create a new database under the Hyperscale tier, you will get this feature enabled once you add a secondary replica.

In the High Availability architecture of Basic, Standard, and General Purpose service tiers, there are no secondary replicas. Hence, the read scale-out feature is not available in these service tiers.

• How to Create a Single Database in Azure SQL

Advantages of using Azure SQL read-only replica

Load Balancing:

When you use a read-only replica, the read-only workloads are separated from the read-write workloads. As these read-only workloads are directed to the secondary replica, the workload on the primary database is decreased.

High performance and availability:

In normal scenarios, the read-write operations are slow and take some time. At the same time, it can affect the performance of the read-only operations.

Therefore, when we divert the read-only operations to the secondary replica, the computing power is properly utilized and the performance of the read-only operations is sustained.

Read Pause and resume Azure SQL database

Disadvantages of using Azure SQL read-only replica

Data Inconsistency:

The data is updated in the read-only replicas asynchronously. This means when a read-write operation is performed it may be updated in the read-only database after a certain latency.

As a result, if some data is written and read at the same time, the result may not be the same as the data is written. The latency is very minor. But it can cause some data inconsistency.

Disable read scale-out feature while creating Azure SQL database

As mentioned above, the read scale-out feature is enabled by default. But you can also disable it explicitly while creating the database.

For example, I am trying to create a Business Critical database. If I want to disable the read scale-out feature explicitly, I can check the Disabled option as shown in the below image:

After disabling this, your secondary replica will also accept read-write operations.

Read Cannot open server requested by the login

Configuring read scale-out feature using PowerShell

In this section, I will explain how to configure the read scale-out feature in your Azure SQL database for its replica.

Prerequisites: You need to install the Az module if you are using Windows PowerShell. Otherwise, you can also use the Azure PowerShell from the portal.

Execute the below command if you are using Windows PowerShell:

Connect-AzAccount

• This command is used to connect your Windows PowerShell to your Azure account. After executing this command, you will be asked to login into your Microsoft account.
• After logging into the Microsoft account, you can now execute the commands for enabling or disabling the read scale-out feature.

Disable read scale-out using PowerShell

By default, the read scale-out function is enabled i.e. your secondary replica will be a read-only database.

However, if you want to disable this feature, you can execute the Set-AzSqlDatabase command. The sample command to disable the read scale-out feature is:

Set-AzSqlDatabase -ResourceGroupName <resource group name> -ServerName <server name> -DatabaseName <database name> -ReadScale Disabled

For example, in my case the command will look like this:

Set-AzSqlDatabase -ResourceGroupName 'GroupAzure' -ServerName 'mysql10000' -DatabaseName 'HyperScaleDB' -ReadScale Disabled

Once you have disabled the read scale-out feature using the above PowerShell command, you will see an output like below in the image:

Thus, you might have learned how you can disable the read scale-out feature of a secondary replica of an Azure SQL database.

Read How to Connect to Azure SQL database

Enable read scale-out using PowerShell

For enabling the read scale-out feature you will use the same Set-AzSqlDatabase command. The sample command to enable the read scale-out feature will be:

Set-AzSqlDatabase -ResourceGroupName <resource group name> -ServerName <server name> -DatabaseName <database name> -ReadScale Enabled

For example, in my case the command for enabling the read scale-out feature will be:

Set-AzSqlDatabase -ResourceGroupName 'GroupAzure' -ServerName 'mysql10000' -DatabaseName 'HyperScaleDB' -ReadScale Enabled

If the command is executed successfully, you will see an output like in the below image:

You can see in the above image that the ReadScale parameter is set to Enabled depicting that the read scale-out feature is enabled.

Read How to rename a database in Azure SQL

Verify if you are connected to a read only replica in Azure SQL

To verify if you are connected to a read-only replica of your primary database, you can execute the below T-SQL query in your database:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS ReadScale;

The output after executing this command will look like this:

If you see the value as READ_ONLY, this means that you are connected to the secondary replica of the primary database.

Otherwise, if you see the value as READ_WRITE, this means that you are connected to the primary database.

Read How to create table in azure sql database

Configuring the read scale-out feature using REST API

Before you start enabling or disabling the read scale-out feature of a secondary replica using replicas, you should know about some prerequisites.

Firstly, you need to register your application with Microsoft in order to get an OAuth token. This OAuth token will be used to authenticate your requests from the application.

Once you get this authentication, you have to specify this token in the Authentication header of your request. In the below image, you can see a sample of this Authentication header:

Secondly, to interact with the Azure API, you need to send a PUT request to the below URL:

https://management.azure.com/subscriptions/{SubscriptionId}/resourceGroups/{GroupName}/providers/Microsoft.Sql/servers/{ServerName}/databases/{DatabaseName}?api-version= 2014-04-01-preview

Make sure to substitute your own database instance details in the above URL. In the body of your request, you will send the parameters to configure the database options.

Read Backup Azure database to local SQL Server

Enable read scale-out using Rest API

To enable the read scale-out feature, you have to send a PUT request to the Azure API with your instance details(URL of the API mentioned in the example below).

The body of the request will be:

{
   "properties": {
      "readScale":"Enabled"
   },
   "location": <your server location>
}

I am going to show you an example of how to enable the read scale-out feature of a secondary replica using REST APIs.

I have created a business-critical database named BusinessDB. I have also created its secondary replica on another server.

If I want to enable the read scale-out feature for this secondary replica, I will send a PUT request to the following URL:

https://management.azure.com/subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourceGroups/GroupAzure/providers/Microsoft.Sql/servers/mysql10000/databases/BusinessDB?api-version=2014-04-01-preview

I will add my authorization token in the request header and the body of the request will be:

{
   "properties": {
      "readScale":"Enabled"
   },
   "location": "East US"
}	

If your request is successful, you will get a 202 Accepted response code as shown below in the image:

Thus, you might have learned how you can enable the read scale-out feature using the REST APIs.

Read Backup and restore SQL Server to Azure Blob storage

Disable read scale-out using Rest API

To disable the read scale-out feature using REST API, you need to send a PUT request to the same URL with the same headers. But you have to change the readScale property to Disabled in the request body.

{
   "properties": {
      "readScale":"Disabled"
   },
   "location": <your server location>
}

Let me show you an example.

In my case, I will send a PUT request to the following URL:

https://management.azure.com/subscriptions/1cdf4300-dee5-4518-9c9c-feaa72a5cbd1/resourceGroups/GroupAzure/providers/Microsoft.Sql/servers/mysql10000/databases/BusinessDB?api-version=2014-04-01-preview

The body of this PUT request will be:

{
   "properties": {
      "readScale":"Disabled"
   },
   "location": "East US"
}

If your request is successful, you will get a 202 Accepted response code as shown below in the image:

Hence, in this way you can disable the read scale-out feature of a secondary replica of an Azure SQL database using REST APIs.

Read Cannot open backup device operating system error 50 azure

Connect to a read-only secondary replica

To connect to a secondary replica, you need to specify the ApplicationIntent parameter in the connection string that you will use to connect to the database.

You have to set this property to ReadOnly. For example, the below is a connection string that you can use to connect to a read-only replica:

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Remember to change the instance details in the above connection string.

Also, if you are using an IDE to connect to the database, there is always an option to specify this parameter.

For example, in the SQL Server management studio Connect to Server dialog box, you have to click on Options. Then navigate to the Additional Connection Parameters and specify the ApplicationIntent parameter as shown in the below image:

Similarly, if you want to connect to your read-only replica using Azure data studio, you have to select the Application Intent option to ReadOnly.

To do this, open Azure data studio and create a new connection. Click on Advanced and then you will see the Application Intent option. Change its value to ReadOnly. Look at the below image for reference:

Thus, you might have learned how to connect to a read-only replica using various methods.

You may also like the following Azure SQL tutorials:

• Bulk loading data to Azure SQL
• Connect to Azure SQL database using Python
• Azure SQL database configure firewall
• Azure SQL database column encryption
• Configuring email notifications in Azure SQL database
• Azure SQL database query history

Conclusion

The read-only secondary replicas should be used because they slightly improve the overall performance of the database architecture.

The data consistency can be maintained using multiple secondary read-only replicas. Also, the latency between the synchronization of the databases can be decreased by using enough resources. Because high utilization of resources results in high latency.

However, if you want the data to be highly consistent and do not want to compromise with the consistency, you should use the primary database.

• Advantages of using read-only replica in Azure SQL
• Disadvantages of using read-only replica
• Disable the read scale-out feature while creating an Azure SQL database
• Configuring the read scale-out feature using PowerShell
• Disable read scale-out using PowerShell in Azure SQL
• Enable read scale-out using PowerShell in Azure SQL
• Verify if you are connected to a read only replica
• Configuring the read scale-out feature using REST API
• Enable read scale-out using REST API for Azure SQL
• Disable read scale-out using REST API
• Connect to a read-only secondary replica