Azure Geo Replication for Business Continuity

Azure Geo Replication is a powerful tool for ensuring business continuity, allowing you to replicate your data across multiple Azure regions for disaster recovery and high availability.

With Azure Geo Replication, you can replicate your data in real-time, with a maximum latency of 15 seconds, ensuring that your data is always up-to-date and accessible.

This capability is particularly useful for applications that require low-latency and high-availability, such as financial trading platforms and real-time analytics tools.

By replicating your data across multiple regions, you can ensure that your business remains operational even in the event of a regional outage or disaster.

Active Geo-Replication is a feature that provides automatic asynchronous replication, which means updates to the primary database are copied to the online secondary database after they're committed.

This feature is only supported for databases in the Premium service tier, and both the primary and online secondary databases must be configured to have the same or larger performance level.

With Active Geo-Replication, you can have multiple online secondary databases, which increases redundancy and protection for the primary database and application.

If multiple online secondary databases exist, the application remains protected even if one of them fails.

An online secondary database can be accessed for read-only operations using the same security principals as the primary database.

However, continuous copy operations on the online secondary database take precedence over application access, and prolonged table locking can cause transactions to fail on the primary database.

To failover an application to an online secondary database, you must terminate the continuous copy relationship with the primary database, which requires an explicit action by the application or an administrative script.

Here are the essential capabilities of Active Geo-Replication:

It's essential to configure the online secondary database to have the same or larger performance level as the primary database, and to ensure it has enough capacity to process the replicated transactions at the same speed.

To prepare for failover, validate that authentication and network access for your secondary server are properly configured. This includes setting up Azure SQL Database security for geo-restore or failover.

Ensure that the backup retention policy on the secondary database matches that of the primary. By default, the geo-secondary is configured with a default PITR retention period of seven days.

If your database is a member of a failover group, you cannot initiate its failover using the geo-replication failover command. Use the failover command for the group instead.

To initiate a planned failover, execute the following T-SQL script on the secondary server on the master database: ALTER DATABASE AzureGeoReplFAILOVER.

For a forced failover, click on the secondary database to which you want failover and click on Forced Failover. Alternatively, use the T-SQL script ALTER DATABASE AzureGeoReplFORCE_FAILOVER_ALLOW_DATA_LOSS on the master database at the secondary server.

Before doing failover, make sure the data synced from primary to secondary using sp_wait_for_database_copy_sync. This is especially helpful for unplanned replication to ensure the data is forced to written on secondary before committed to the application.

To prevent loss of critical data, call the sp_wait_for_database_copy_sync stored procedure immediately after committing the transaction. This blocks the calling thread until the last committed transaction has been transmitted and hardened in the transaction log of the secondary database.

Here is a summary of the steps to prepare for failover:

To configure a secondary database for geo-replication, you'll need to ensure the primary and secondary databases have the same service tier. This is a crucial step, as a mismatch can lead to replication lag and unavailability of the secondary database.

The secondary database should also have the same backup storage redundancy, compute tier, and compute size as the primary database. If the primary is experiencing a heavy write workload, a lower compute size on the secondary might not be able to keep up, causing replication lag.

You can use the Azure portal to set up active geo-replication across subscriptions, as long as both subscriptions are in the same Microsoft Entra tenant. However, creating a cross-subscription geo-secondary on a logical server in the same or different Microsoft Entra tenant is not supported when Microsoft Entra-only authentication is enabled.

To monitor log IO rate on the primary database, use the sys.resource_stats view to retrieve historical data, and the sys.dm_db_resource_stats view to retrieve recent data with higher granularity. This will help you estimate the minimal compute size of the secondary database required to sustain the replication load.

Here are some key considerations to keep in mind:

If you do decide to create the secondary database with a different configuration, be sure to monitor the log IO rate on the primary database over time to estimate the minimal compute size required for the secondary database.

You can save on licensing costs by designating a database for standby when configuring a new active geo-replication relationship, especially if your secondary replica is only used for disaster recovery.

Each replica created will add extra charges, so it's essential to review the App Configuration pricing page for details.

If you have a standard tier configuration store as your origin and five replicas, you'll be charged the rate of six standard tier configuration stores for your system.

Each replica's isolated quota and requests are included in this charge, so be sure to factor that in when calculating your overall costs.

This means you'll need to carefully consider the number of replicas you create to avoid unnecessary charges.

Active replication is a fundamental aspect of Azure Geo Replication, allowing for the automatic asynchronous replication of data between primary and secondary databases. This process ensures that transactions are committed on the primary database before being copied to the online secondary database.

Active Geo-Replication supports multiple online secondary databases, which increase redundancy and protection for the primary database and application. If multiple online secondary databases exist, the application remains protected even if one of the online secondary databases fails.

To manage active geo-replication programmatically, you can use T-SQL, Azure PowerShell, Azure CLI, or REST API. These APIs support Azure role-based access control (Azure RBAC) and provide a set of commands for managing active geo-replication.

The following table summarizes the T-SQL commands available for managing active geo-replication:

Active Geo-Replication provides automatic asynchronous replication, which means updates to the primary database are copied to the online secondary database automatically after seeding.

This feature is only supported for databases in the Premium service tier, and the online secondary must be configured to have the same or larger performance level as the primary.

With multiple online secondary databases, redundancy and protection for the primary database and application are increased, and the application remains protected even if one of the online secondary databases fails.

An application can access an online secondary database for read-only operations using the same security principals used for accessing the primary database.

Continuous copy operations on the online secondary database take precedence over application access, and prolonged table locking can cause transactions to eventually fail on the primary database.

User-controlled termination for failover is required before switching to an online secondary database, and termination of the continuous copy relationship requires an explicit action by the application or an administrative script.

Here are the essential capabilities of Active Geo-Replication:

Configuring Active Replication is a straightforward process that can be done using the Azure portal, T-SQL, Azure PowerShell, and REST API. You can also use the Azure CLI to manage active geo-replication.

To configure active geo-replication using the Azure portal, you need to connect to the Azure portal, select Azure SQL DB, and then select Geo-Replication. From there, you can select the region you need to replicate the Azure DB with, and up to 4 secondary regions.

You can also manage active geo-replication programmatically using T-SQL, Azure PowerShell, and REST API. The Azure CLI provides commands for creating, deleting, and listing geo-replication links.

To create a geo-secondary, both the primary and geo-secondary must have the same service tier. It's also recommended that the geo-secondary has the same backup storage redundancy, compute tier, and compute size as the primary.

Here are some key considerations for configuring a geo-secondary:

If the geo-secondary is at a lower compute size than the primary, look for the HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO wait type in sys.dm_exec_requests and sys.dm_os_wait_stats database views.