SQL DB
The presented resiliency recommendations in this guidance include Azure Database Services
Summary of Recommendations
| Recommendation | Category | Impact | State | ARG Query Available |
|---|---|---|---|---|
| SQLDB-1 - Use Active Geo Replication to Create a Readable Secondary in Another Region | Disaster Recovery | High | Preview | Yes |
| SQLDB-2 - Use Auto Failover Groups that can include one or multiple databases, typically used by the same application | Disaster Recovery | High | Preview | Yes |
| SQLDB-3 - Use a Zone-Redundant database | Availability | Medium | Preview | Yes |
| SQLDB-4 - Implement Retry Logic | Application Resilience | High | Preview | Yes |
| SQLDB-5 - Monitor your Azure SQL Database in near-real time to detect reliability incidents | Monitoring | High | Preview | Yes |
| SQLDB-6 - Back up your keys | Disaster Recovery | Medium | Preview | Yes |
Recommendations Details
SQLDB-1 - Use Active Geo Replication to Create a Readable Secondary in Another Region
Category: Disaster Recovery
Impact: High
Guidance
If your primary database fails, perform a manual failover to the secondary database. Until you fail over, the secondary database remains read-only. Active geo-replication enables you to create readable replicas and manually failover to any replica if there is a datacenter outage or application upgrade. Up to four secondaries are supported in the same or different regions, and the secondaries can also be used for read-only access queries. The failover must be initiated manually by the application or the user. After failover, the new primary has a different connection end point.
Resources
Resource Graph Query
// Azure Resource Graph Query
// Provides a list of SQL databases that are not configured for Geo-redundant storage.
resources
| where type == "microsoft.sql/servers/databases"
| where (properties['currentBackupStorageRedundancy'] ) <> 'Geo'
| project recommendationId = "sqldb-1", name, id, tags, param1=strcat("CurrentGeoRedudancy=", properties['currentBackupStorageRedundancy'] )
SQLDB-2 - Use Auto Failover Groups that can include one or multiple databases, typically used by the same application
Category: Disaster Recovery
Impact: High
Guidance
You can use the readable secondary databases to offload read-only query workloads. Because autofailover groups involve multiple databases, these databases must be configured on the primary server. Autofailover groups support replication of all databases in the group to only one secondary server or instance in a different region.
Resources
Resource Graph Query
// Azure Resource Graph Query
// Provides a list of SQL databases that are not configured to use a failover-group.
resources
| where type =~'microsoft.sql/servers/databases'
| where isnull(properties['failoverGroupId'])
| project recommendationId = "sqldb-2", name, id, tags, param1= strcat("databaseId=", properties['databaseId'])
SQLDB-3 - Use a Zone-Redundant Database
Category: Availability
Impact: Medium
Guidance
By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in 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 (ATM). Because the zone redundant configuration in the Premium or Business Critical service tiers doesn’t create extra database redundancy, you can enable it at no extra cost.
Resources
Resource Graph Query
Resources
| where type =~ 'microsoft.sql/servers/databases'
| where tolower(tostring(properties.zoneRedundant))=~'false'
|project recommendationId = "sqldb-3", name, id, tags
SQLDB-4 - Implement Retry Logic
Category: Application Resilience
Impact: High
Guidance
Although Azure SQL Database is resilient when it concerns transitive infrastructure failures, these failures might affect your connectivity. When a transient error occurs while working with SQL Database, make sure your code can retry the call.
Resources
Resource Graph Query
// under-development
SQLDB-5 - Monitor your Azure SQL Database in Near Real-Time to Detect Reliability Incidents
Category: Monitoring
Impact: High
Guidance
Use one of the available solutions to monitor SQL DB to detect potential reliability incidents early and make your databases more reliable. Choose a near real-time monitoring solution to quickly react to incidents.
Resources
Resource Graph Query
// Azure Resource Graph Query
// Provides a list of SQL databases that are not configured for monitoring.
resources
| where type == "microsoft.insights/metricalerts"
| mv-expand properties.scopes
| mv-expand properties.criteria.allOf
| project databaseid = properties_scopes, monitoredMetric = properties_criteria_allOf.metricName
| where databaseid contains 'databases'
| summarize monitoredMetrics=make_list(monitoredMetric) by tostring(databaseid)
| join kind=fullouter (
resources
| where type =~ 'microsoft.sql/servers/databases'
| project databaseid = tolower(id), name, tags
) on databaseid
|where isnull(monitoredMetrics)
|project recommendationId = "sqldb-5", name, id=databaseid1, tags, param1=strcat("MonitoringMetrics=false" )
SQLDB-6 - Back Up Your Keys
Category: Disaster Recovery
Impact: Medium
Guidance
It is highly recommended to use Azure Key Vault (AKV) to store encryption keys related to Always Encrypted configurations, however it is not required. If you are not using AKV, then ensure that your keys are properly backed up.
Resources
Resource Graph Query
// under-development