Migrate Azure SQL Database to availability zone support

Article
03/28/2024

This guide describes how to migrate Azure SQL Database from non-availability zone support to availability support.

Enabling zone redundancy for Azure SQL Database guarantees high availability as the database utilizes Azure Availability Zones to replicate data across multiple physical locations within an Azure region. By selecting zone redundancy, you can make your databases and elastic pools resilient to a larger set of failures, such as catastrophic datacenter outages, without any changes of the application logic.

Prerequisites

Before you migrate to availability zone support, refer to the following table to ensure that your Azure SQL Database is in a supported service tier and deployment model. Make sure that your tier and model is offered in a region that supports availability zones.

Service tier	Deployment model	Zone redundancy availability
Premium	Single database or Elastic Pool	All regions that support availability zones
Business Critical	Single database or Elastic Pool	All regions that support availability zones
General Purpose	Single database or Elastic Pool	Selected regions that support availability zones
Hyperscale	Single database	All regions that support availability zones

Downtime requirements

Migration for Premium, Business Critical, and General Purpose service tier is an online operation with a brief disconnect towards the end to finish the migration process. If you have implemented retry logic for standard transient errors, you won't notice the failover.

For Hyperscale service tier, zone redundancy support can only be specified during database creation and can't be modified once the resource is provisioned. If you wish to move to availability zone support, you'll need to transfer the data with database copy, point-in-time restore, or geo-replica. If the target database is in a different region than the source or if the database backup storage redundancy for the target differs from the source database, then downtime is proportional to the size of the data operation.

Migration (Premium, Business Critical, and General Purpose)

For the Premium, Business Critical, and General Purpose service tiers, migration to zone redundancy is possible.

Follow the steps below to perform migration for a single database or an elastic pool.

Migrate a single database

Go to the Azure portal to find your database. Search for and select SQL databases.
Select the database that you want to migrate.
Under Settings select Compute + Storage.
Select Yes for Would you like to make this database zone redundant?
Select Apply.
Wait to receive an operation completion notice in Notifications in the top menu of the Azure portal.
To verify that zone redundancy is enabled, select Overview and then select Properties.
Under the Availability section, confirm that zone redundancy is set to Enabled.

Open PowerShell as Administrator and run the following command (replace the placeholders in "<>" with your resource names). Note that <server_name> should not include .database.windows.net.

Connect-AzAccount
$subscriptionid = <'your subscription id here'>
Set-AzContext -SubscriptionId $subscriptionid

$parameters = @{
    ResourceGroupName = '<Resource_Group_Name>'
    ServerName = '<Server_Name>'
    DatabaseName = '<Database_Name>'
}
Set-AzSqlDatabase @parameters -ZoneRedundant

Use Azure CLI to run the following command (replace the placeholders in "<>" with your resource names):

    az sql db update --resource-group “<Resource_Group_Name>” --server “<Server_Name>” --name “<Database_Name>” --zone-redundant

Migrate an elastic pool

Important

Enabling zone redundancy support for elastic pools makes all databases within the pool zone redundant.

Go to the Azure portal to find and select the elastic pool that you want to migrate.
Select Settings, and then select Configure.
Select Yes for Would you like to make this elastic pool zone redundant?.
Select Save.
Wait to receive an operation completion notice in Notifications in the top menu of the Azure portal.
To verify that zone redundancy is enabled, select Configure and then select Pool settings.
The zone redundant option should be set to Yes.

Open PowerShell as Administrator and run the following command (replace the placeholders in "<>" with your resource names). Note that <server_name> should not include .database.windows.net.

Connect-AzAccount
$subscriptionid = <'your subscription id here'>
Set-AzContext -SubscriptionId $subscriptionid

$parameters = @{
    ResourceGroupName = '<Resource_Group_Name>'
    ServerName = '<Server_Name>'
    ElasticPoolName = '<Elastic_Pool_Name>'
}

Set-AzSqlElasticPool  @parameters -ZoneRedundant

Use Azure CLI to run the following command (replace the placeholders in "<>" with your resource names):

    az sql elastic-pool update --resource-group “<Resource_Group_Name>” --server “<Server_Name>” --name “<Elastic_Pool_Name>” --zone-redundant

Redeployment (Hyperscale)

For the Hyperscale service tier, zone redundancy support can only be specified during database creation and can't be modified once the database is provisioned. If you wish to gain zone redundancy support, you need to perform a data transfer from your existing Hyperscale service tier single database. To perform the transfer and enable the zone redundancy option, a clone must be created using database copy, point-in-time restore, or geo-replica.

Redeployment considerations

There are two modes of redeployment (online and offline):
- The Database copy and point-in-time restore methods (offline mode) create a transactionally consistent database at a certain point in time. As a result, any data changes performed after the copy or restore operation have been initiated won't be available on the copied or restored database.
- Geo-replica method (online mode) is a redeployment wherein any data changes from source are synchronized to target.
Connection string for the application must be updated to point to the zone redundant database.

Redeploy a single database

Database copy

To create a database copy and enable zone redundancy with Azure portal, PowerShell, or Azure CLI, follow the instructions in copy a transactionally consistent copy of a database in Azure SQL Database.

Point-in-time restore

To create a point-in-time database restore and enable zone redundancy with Azure portal, PowerShell, or Azure CLI, follow the instructions in Point-in-time restore.

Geo-replica

To create a geo-replica of the database:

Follow the instructions with Azure portal, PowerShell, or Azure CLI in Configure active geo-replication and failover (Azure SQL Database) and enable zone redundancy under Compute + Storage
The replica is seeded, and the time taken for seeding the data depends upon size of source database. You can monitor the status of seeding in the Azure portal or by running the following TSQL queries on the replica database:
```
    SELECT * FROM sys.dm_geo_replication_link_status;
    SELECT * FROM sys.dm_operation_status;
```
Once the database seeding is finished, perform a planned (no data loss) failover to make the zone redundant target database as primary. Use the sys.dm_geo_replication_link_status to view the status of the geo-replication state. The replication_state_desc is CATCH_UP when the secondary database is in a transactionally consistent state. In the sys.dm_operation_status dynamic management view, look for state_desc to be COMPLETED when the seeding operation has completed.
Update the server name in the connection strings for the application to reflect the new zone redundant database.
To clean up, consider removing the original non-zone redundant database from the geo replica relationship. You can choose to delete it.

Disable zone-redundancy

To disable zone-redundancy for a single database or an elastic pool, you can use the portal or ARM API.

To disable zone-redundancy for Hyperscale service tier, you can reverse the steps documented in Redeployment (Hyperscale).

Elastic pool
Single database

To disable zone-redundancy with Azure portal:

Go to the Azure portal to find and select the elastic pool that you no longer want to be zone-redundant.
Select Settings, and then select Configure.
Select No for Would you like to make this elastic pool zone redundant?.
Select Save.

To disable zone-redundancy with ARM, see Databases - Create Or Update in ARM and use the properties.zoneRedundant property.