Configure the SQL Database for zone resilient mode

This topic outlines the steps required to configure the Nerdio Manager SQL Database in zone resilient mode.

Nerdio Manager default deployment

When you install the Nerdio Manager application from the Azure Marketplace, the SQL Server and SQL Database use the S1 pricing tier on the DTU-based purchasing model.

Local redundancy

By default, Azure SQL Database achieves availability through local redundancy. Locally redundant availability is based on storing your database to locally redundant storage (LRS), which copies your data three times within a single datacenter in the primary region and protects your data in the event of local failure, such as a small-scale network or power failure. LRS is the lowest-cost redundancy option and offers the least durability compared to other options. If a large-scale disaster such as fire or flooding occurs within a region, all replicas of a storage account using LRS might be lost or unrecoverable. The default availability solution is designed to ensure that committed data is never lost due to failures, that maintenance operations have minimal impacts to your workload, and that the database isn't a single point of failure in your software architecture. However, to minimize impact on your data in the event of an outage to an entire zone, you can achieve high availability by enabling zone redundancy. Without zone redundancy, failovers happen locally within the same datacenter, which might result in your database being unavailable until the outage is resolved.

High availability through zone-redundancy

Zone-redundant availability ensures that your data is spread across three Azure availability zones in the primary region. Each availability zone is a separate physical location with independent power, cooling, and networking.

Zone-redundant availability is available to databases in the Business Critical, General Purpose and Hyperscale service tiers of the vCore-based purchasing model, and only the Premium service tier of the DTU-based purchasing model - the Basic and Standard service tiers don't support zone redundancy. While each service tier implements zone-redundancy differently, all implementations ensure a Recovery Point Objective (RPO) with zero loss of committed data upon failover. The following table shows the availability options based on service tiers:

Premium and Business Critical service tiers

When zone redundancy is enabled for the Premium or Business Critical service tier, replicas are placed in different availability zones within the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW). The routing to a specific gateway ring is controlled by Azure Traffic Manager. Because the zone-redundant configuration in the Premium or Business Critical service tiers uses its existing replicas to place in different availability zones, you can enable it at no extra cost. By selecting a zone-redundant configuration, you can make your Premium or Business Critical databases and elastic pools resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes to the application logic. You can also convert any existing Premium or Business Critical databases or elastic pools to zone-redundant configuration.

The zone-redundant version of the high availability architecture is illustrated by the following diagram:

Consider the following when configuring your Premium or Business Critical databases with zone-redundancy:

• All Azure regions that have availability zone support, support zone redundant Premium and Business Critical databases.

• For zone redundant availability, choosing a maintenance window other than the default is currently available in selected regions. For more information, see Maintenance window availability by region for Azure SQL Database.

Azure SQL Server zone redundant availability

In Azure SQL Database, a server is a logical construct that acts as a central administrative point for a collection of databases. At the server level, you can administer logins, authentication method, firewall rules, auditing rules, threat detection policies, and failover groups. Data related to some of these features, such as logins and firewall rules, is stored in the master database. Similarly, data for some DMVs, for example sys.resource_stats, is also stored in the master database.

When a database with a zone-redundant configuration is created on a logical server, the master database associated with the server is automatically made zone-redundant as well. This ensures that in a zonal outage, applications using the database remain unaffected because features dependent on the master database, such as logins and firewall rules, are still available. Making the master database zone-redundant is an asynchronous process and will take some time to finish in the background.

When none of the databases on a server are zone-redundant, or when you create an empty server, then the master database associated with the server is not zone-redundant.

You can use Azure PowerShell or the Azure CLI or the REST API to check the ZoneRedundant property for the master database:

Use the following example command to check the value of ZoneRedundant property for master database.

Get-AzSqlDatabase -ResourceGroupName "myResourceGroup" -ServerName "myServerName" -DatabaseName "master"

Scale Azure SQL Database for zone redundancy

In this example, we’ll scale the default Nerdio Manager Azure SQL Database from S1 to Premium, and then enable zone-redundancy. For more information about scaling the database, see Scale single database resources in Azure SQL Database.

To scale up the SQL Database to Premium:

1. In the Azure portal, navigate to the Nerdio Manager Azure SQL Database.

   Tip: The default naming convention for the SQL Database is nmw-app-db.

2. Go to Settings > Compute + storage, and from the Service tier drop-down list, select Premium (Highest availability and performance).

   

   A new section at the bottom is now displayed.

3. Under Would you like to make this database zone redundant?, select Yes.

   

   After a few minutes, the Azure SQL Database will be on the Premium P1 tier with zone redundancy enabled.