Azure SQL Replica Setup and High Availability

Setting up an Azure SQL replica is a crucial step in achieving high availability for your database.

To ensure high availability, you can create a replica in a different region, known as a geo-redundant replica, which can be used for disaster recovery purposes.

By setting up a read-only replica in a different region, you can ensure that your database is always available, even in the event of a regional outage.

With Azure SQL replica, you can choose from two types of replicas: read-only and read-write replicas.

Intriguing read: Azure Availability

You can designate a replica for standby when you configure a new active geo-replication relationship. This can be done using the Azure portal, PowerShell, the Azure CLI, or the REST API.

To create a new active geo-replication relationship and designate your secondary database for standby in the Azure portal, follow these steps: Go to your SQL database resource in the Azure portal, choose Replicas under Data management from the resource menu, and then select + Create replica to open the Create SQL Database - Geo Replica page.

If this caught your attention, see: Azure Sql Database Create

The secondary database must have the same service tier as the primary. You can select any region for your secondary server, but we recommend the paired region.

Here are the tools you can use to create a new active geo-replication relationship and designate your secondary database for standby:

You can also designate your databases for standby when you create a failover group, or add databases to an existing failover group in the Azure portal.

Adding a secondary database to your Azure SQL replica is a straightforward process. To create a new secondary database, you must be the subscription owner or co-owner.

You can add a secondary database in the Azure portal by selecting your database, scrolling to Data management, selecting Replicas, and then selecting Create replica. This will open the Create SQL Database - Geo Replica page.

Select your geo-secondary database Subscription and Resource group, and then select or create the server for the secondary database. You can select any region for your secondary server, but we recommend the paired region.

You might enjoy: Azure Sql Linked Server

If you want to add a secondary database to an elastic pool, you can do so by selecting Yes next to Want to use SQL elastic pool? and selecting a pool on the target server. However, this workflow doesn't create a pool, so a pool must already exist on the target server.

The secondary database must have the same service tier as the primary database. You can select Review + create to review the information, and then select Create to confirm your settings and create your new secondary database replica.

Here are the details you'll need to provide when creating a secondary database:

The tempdb database on the primary replica isn't replicated to the read-only replicas.

Each replica has its own tempdb database that is created when the replica is created.

This ensures that tempdb is updateable and can be modified during your query execution.

If your read-only workload depends on using tempdb objects, you should create these objects as part of the same workload, while connected to a read-only replica.

Geo-replication is a feature that allows you to create a readable secondary replica of your primary Hyperscale database in the same or a different Azure region. This provides an additional layer of redundancy and can be used for disaster recovery or geographic read scale-out scenarios.

A geo-replica doesn't share page servers with the primary, even if they are in the same region, and all data is copied from the primary to a different set of page servers. This ensures that your database remains transactionally consistent and available, even in the event of a disaster or outage.

There are some limitations to geo-replication, including the fact that only one geo-replica can be created, point in time restore of the geo-replica isn't supported, and creating a geo-replica of a geo-replica (also known as "geo-replica chaining") isn't supported.

High Availability is a key aspect of Geo-Replication, allowing your database to stay online even in the event of a failure. This is achieved through the use of High Availability (HA) replicas.

HA replicas use the same page servers as the primary replica, eliminating the need for data copying and making failover quick and automatic. They're essentially hot standby replicas that take over in case the primary becomes unavailable.

To add an HA replica, you don't need to change the connection string, and applications might only experience minimal downtime due to active connections being dropped. Proper retry logic is recommended, and some drivers already provide automatic retry logic.

HA replicas share the same server and database name as the primary replica, and their Service Level Objective (SLO) is also the same. They're not visible or manageable as standalone resources, but they're billed at the same compute rate as the primary replica.

You can specify the number of HA replicas during database creation or update, and there can be zero to four HA replicas. This can be done using common management endpoints and tools, such as Azure PowerShell or the Azure portal.

Cross-subscription geo-replication allows you to create a geo-secondary replica in a subscription different from the primary's subscription in the same Microsoft Entra tenant. This is possible through the Azure portal or by following the steps in this section.

You'll need to use SQL authentication and T-SQL to create a geo-secondary replica in a different Microsoft Entra tenant. Unfortunately, Microsoft Entra authentication for Azure SQL isn't supported in this scenario.

To get started, add the IP address of your client machine to the server firewalls of both the primary and secondary servers. You can do this by executing a query on the primary server to retrieve the client's IP address.

Here's the query to execute: SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Next, create a SQL authentication login dedicated to active geo-replication setup in the master database on the primary server. You'll need to adjust the login name and password as needed.

Explore further: Azure Auth Json Website Azure Ad Authentication

Here's the T-SQL command to create the login: CREATE LOGIN geodrsetup WITH PASSWORD = 'ComplexPassword01';

Create a user for the login and add it to the dbmanager role in the same database. You can do this by executing the following T-SQL commands: CREATE USER geodrsetup FOR LOGIN geodrsetup; ALTER ROLE dbmanager ADD MEMBER geodrsetup;

Take note of the SID value of the new login by executing the following query: SELECT sid FROM sys.sql_logins WHERE name = 'geodrsetup';

Create a user for the same login in the primary database, and add it to the db_owner role. This can be done by executing the following T-SQL commands: CREATE USER geodrsetup FOR LOGIN geodrsetup; ALTER ROLE db_owner ADD MEMBER geodrsetup;

Repeat the same steps on the secondary server, creating the same login and user, and adding it to the dbmanager role.

Once you've completed these steps, you can initiate geo-secondary creation on the secondary server by executing the following T-SQL command: alter database [dbrep] add secondary on server [servername];

A unique perspective: Sql Azure Add User

Data consistency is a crucial aspect of geo-replication. Data changes made on the primary replica are persisted on read-only replicas synchronously or asynchronously depending on replica type.

For all replica types, reads from a read-only replica are always asynchronous with respect to the primary. This means that data changes made on the primary may not be immediately visible on the read-only replica.

Within a session connected to a read-only replica, reads are always transactionally consistent. This ensures that the data you see is up-to-date and consistent within the session.

However, because data propagation latency is variable, different replicas can return data at slightly different points in time relative to the primary and each other. This can cause issues if an application requires guaranteed data consistency across sessions.

Typical data propagation latency between the primary replica and read-only replicas varies in the range from tens of milliseconds to single-digit seconds. However, there is no fixed upper bound on data propagation latency.

Related reading: Azure Data Studio vs Azure Data Explorer

Conditions such as high resource utilization on the replica can increase latency substantially. To minimize latency, it's essential to monitor and troubleshoot read-only replicas regularly.

To ensure data consistency, changes are not visible until the transaction commit log record is applied. This means that even if you make changes to data on the primary replica, they won't be visible on the read-only replica until the log record is applied.

To monitor data propagation latency, see Monitor and troubleshoot read-only replica.

On a similar theme: Azure Data Studio Connect to Azure Sql

Hyperscale named replicas can be configured for zone redundancy to distribute compute nodes across different physical locations within an Azure region.

This setup enhances the resilience of all layers of your Hyperscale databases to a wider range of failures, including datacenter outages, without any modifications of the application logic.

To troubleshoot zone redundant Hyperscale named replicas, start by ensuring at least one high availability replica is specified when creating a zone redundant named replica, in PowerShell and CLI.

For example, see Create a Hyperscale named replica for a demonstration of this process.

Additionally, the Hyperscale database should have zone redundancy already enabled as a prerequisite to enable this feature for named replicas.

If you're looking to test application fault resiliency, check out Test application fault resiliency for more information.

You can monitor and troubleshoot read-only replicas using Dynamic Management Views (DMVs). These views provide a wide variety of monitoring data.

DMVs such as sys.dm_db_resource_stats, sys.dm_os_wait_stats, and sys.dm_database_replica_states are commonly used for replica monitoring and troubleshooting. These views expose metrics like CPU, data IO, and log write utilization, as well as replica health state and synchronization statistics.

To get a better understanding of your replica's performance, you can also use views like sys.dm_os_performance_counters and sys.dm_io_virtual_file_stats, which provide database engine performance counters and storage IOPS, throughput, and latency statistics for all database files.

Here are some commonly used DMVs for replica monitoring and troubleshooting:

To remove a secondary database, you must be the subscription owner or co-owner. You can do this by stopping the replication to the secondary database, which changes its role to a regular read-write database. This process permanently stops the replication to the secondary database.

You can stop replication to the secondary database in the Azure portal by selecting the database you want to remove from the geo-replication partnership, then selecting Stop replication. A confirmation window will open, and you must select Yes to remove the database from the geo-replication partnership.

Alternatively, you can use the az sql db replica delete-link command to remove the secondary database. This command can be used in combination with other tools, such as PowerShell or the Azure CLI.

After stopping replication, your secondary database will become a standalone database, and you will start incurring licensing costs. You can check the status of the secondary database by running the az sql db replica list-links command.

To troubleshoot zone redundant Hyperscale named replicas, you need to ensure at least one high availability replica is specified when creating a zone redundant named replica. This can be done using PowerShell and CLI, and an example can be found in the documentation.

For application fault resiliency testing, see the separate documentation on testing application fault resiliency. Zone redundancy is a prerequisite for enabling this feature on named replicas.

If you're experiencing issues with your zone redundant Hyperscale named replicas, check the documentation on creating a Hyperscale named replica for an example of how to specify a high availability replica.

To troubleshoot zone redundant Hyperscale named replicas, follow these steps:

Some common troubleshooting steps for zone redundant Hyperscale named replicas include:

Incorrect database data can occur when querying sys.databases for named replicas, where row values in columns other than name and database_id may be inconsistent and incorrect.

When getting the compatibility level of a named replica, the DATABASEPROPERTYEX() function can be used as a workaround to get correct data.

Incorrect data may be reported in the compatibility_level column for a named replica, even if the primary database corresponding to the named replica is set to a different compatibility level.

For example, compatibility level 140 might be reported for a named replica when the primary database is actually set to compatibility level 150.

DATABASEPROPERTYEX() function returns correct data, making it a reliable alternative for retrieving database properties.

Long-running queries can cause issues on read-only replicas, especially when object metadata is modified on the primary replica. This can lead to blocking and out-of-sync replicas.

In rare cases, a query on a read-only replica can block the process applying changes from the primary replica, causing significant data latency and potential database availability impact. If this happens, the query may be automatically terminated to avoid further issues.

Error 1219, "Your session has been disconnected because of a high priority DDL operation", or error 3947, "The transaction was aborted because the secondary compute failed to catch up redo. Retry the transaction." may be received if a long-running query causes blocking. If you encounter either of these errors, simply retry the query.

A unique perspective: Azure Sql Cross Database Query

To avoid operations that modify object metadata, such as schema changes, index maintenance, and statistics updates, on the primary replica while long-running queries execute on secondary replicas. This can help prevent blocking and ensure data consistency.

For SQL Managed Instance, read-scale out is automatically enabled on the Business Critical service tier, and isn't available in the General Purpose service tier. Disabling and reenabling read scale-out isn't possible, so it's essential to plan accordingly.