Enhancing Resilience in Azure SQLDB Hyperscale Named Replicas with High Availability (HA) replica.

In this blog post, we will look at how to manage resiliency and high availability for read workloads offloaded to hyperscale, called replicas. There are many use cases for offloading read workloads to read replicas. We will discuss a scenario involving Contoso, an energy company that offloaded its read API workload to named replicas. Contoso uses named dedicated replicas for several API and reporting workloads to retrieve details such as outage status, energy usage, billing information, business reporting, and more.

We’ll also look at how to create a high availability (HA) replica for a named hyperscale replica using the Azure portal, PowerShell, and Azure CLI.

Brief about named replica: A named replica is a compute-only secondary replica available exclusively in the Azure SQL Database Hyperscale tier. It can be
added on-demand to offload read workloads from the primary Hyperscale replica. Since it uses the same storage as the primary Hyperscale
replica, it does not incur additional storage or license costs. Read applications can connect to a named replica via a dedicated connection 
endpoint, and isolated access can be configured for the users. Each named replica can have a different compute size or service level 
objective (SLO). It also has its own buffer pool and SSD cache to retain hot data pages in memory or local to the compute node, enhancing
read performance. Read more about named replica

script: Contoso rarely encounters application connectivity issues due to unplanned outages, such as process crashes or load shedding. Azure’s infrastructure can dynamically reconfigure servers when the SQL Database service is under heavy workload, which can cause applications to lose connectivity to the database. These errors, known as transient errors, can be investigated and managed by following the methods described in the documentation. Troubleshoot common connectivity issues.

However, Contoso’s problem persisted for a long time because it did not have a high availability (HA) secondary replica for its named replicas. If there is no HA secondary replica, a new named replica will be provisioned and automatically recover during an unplanned event, but the failover process can take seconds to minutes, during which applications will not be able to connect to the named replica. Because Contoso directed critical read operations to named replicas, even a brief outage could cause an incident for the customer.

To prevent these long-term incidents, Contoso added an HA secondary replica for the named replica. With this setup, automatic failover to the HA secondary replica minimizes downtime during unplanned outages. Contoso can also use HA secondary replicas to handle the read workload from the API, further improving system performance.

NOTE: The HA secondary replica will have the same compute size as the named replica and will scale accordingly when you scale the named replica's compute.
Application directing read workload to a named replica can load balance the workload on its HA secondary replica using the connection 
string parameter "ApplicationIntent=ReadOnly".

Let us discuss the steps to create an HA secondary replica for an existing named replica.

Azure portal

You can create an HA secondary replica for a named replica. You can refer to the following steps as shown in the animation:

1. Go to the Azure portal and select Hyperscale Database.
2. below data Management, choose Replica. Select an existing named replica.
3. below setting, Compute + Storage.
4. Add and select the desired number of high availability secondary replicas to your configuration. apply.

powershell

You can add an HA secondary replica for a named replica using the -HighAvailabilityReplicaCount input parameter of ‘Set-AzSqlDatabase’. For more information, see: Set-AzSQLDatabase.

To check if that property is enabled you can use PowerShell: ‘Get-AzSqlDatabase’. For more information, see: Get-AzSqlDatabase(Az.Sql).

yes:

Adding an HA Secondary Replica for a Named Replica contoso_named_replica_db Under Logical Server Contoso Server.

Set-AzSqlDatabase  -ResourceGroupName contosorg -DatabaseName contoso_named_replica_db -ServerName contososerver -HighAvailabilityReplicaCount 1

Verify that an HA secondary replica has been added for the named replica.

Get-AzSqlDatabase -ResourceGroupName contosorg -DatabaseName contoso_named_replica_db  -ServerName contososerver | Select-Object -ExpandProperty HighAvailabilityReplicaCount

Azure CLI

You can add an HA secondary replica for a named replica by using the –ha-replicas input parameter in the ‘az sql db replica create’ command. For more information about this CLI command, see: az sql db replica.

To check if the HA secondary replica has been added, you can use the CLI command ‘az sql db show’. For more information, see: az sql db show.

yes:

To add an HA secondary replica for a named replica contoso_named_replica_db.

az sql db replica create -g contosorg -n contoso_named_replica_db -s contososerver --secondary-type named --partner-database contoso_named_replica_db --partner-server contososerver --ha-replicas 1 

Verify that the HA secondary replica has been added.

az sql db show -g contosorg -n contoso_named_replica_db -s contososerver --query "highAvailabilityReplicaCount"

Restrictions:

1. Named replicas and their HA replicas are not currently automatically load balanced. To leverage an HA secondary replica for read operations, your application connection string must include the “ApplicationIntent = ReadOnly” parameter.

2. Named replicas do not currently support Geo failover or failover groups. When a geo failover occurs where the primary hyperscale database fails over to a Geo secondary replica, you have two options:

   1. To save compute costs by provisioning replicas only when needed, add a named replica to the Geo secondary replica when it becomes the primary replica.
   2. Pre-provision named replicas for the Geo secondary replica so that both the primary and Geo secondary environments have the same configuration and replica topology.

   The connection string or endpoint for each named replica is unique. Therefore, you need to update the application connection string for applications that point to the named replica of the Geo secondary replica using the new named replica endpoint provisioned for the Geo secondary replica.

FAQ: HA secondary replica for named replica:

question: If I scale a named replica from 40 to 80 cores, will the HA secondary replica also scale automatically?

answer: Yes, when a named replica scales up or down, its associated HA secondary replicas scale up automatically without any manual intervention.

question: If there is no HA secondary replica and there is a planned failover (e.g. maintenance), will a new named replica be created before the failover to minimize downtime?

answer: Yes, in case of a planned failover, a temporary secondary replica is automatically created and a hot failover is performed.

question: If there is an unplanned but manageable failover, such as load balancing, will the failover take more time because a new compute must be created, or will the system create a new compute before the failover?

answer: Load balancing is considered planned failover. In this situation, a temporary secondary replica is automatically created and a hot failover is performed.

question: If an unplanned failover occurs due to a compute error or issue, will the failover take more time because new compute must be created?

answer: Yes, if a failover is not planned (e.g. due to a SQL crash or replication node failure), it will take more time as a new SQL instance must be created and recovered on the other node.

question: Do log services and page servers have HA secondary replicas by default, independent of compute HA replicas?

answer: Yes, we maintain internal replicas to ensure resiliency regardless of whether you add HA replicas.

conclusion

Creating an HA replica for a hyperscale named replica is a simple process that can significantly improve the reliability and performance of your application. Whether you use the Azure portal, PowerShell, or Azure CLI, the steps are simple and effective. Implementing HA replicas can help ensure that your critical read workloads run smoothly and efficiently even when potential failures occur. Please share Leave a comment to ask your feedback and questions. You can also email us at: sqlhsfeedback to microsoft dot com. We’d love to hear from you all!