Edit

Azure Well-Architected Framework review - Azure SQL Database

  • Article

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions without user involvement. Management functions include upgrades, patches, backups, and monitoring.

The single database resource type creates a database in Azure SQL Database with its own set of resources and is managed via a logical server. You can choose between the DTU-based purchasing model or vCore-based purchasing model. You can create multiple databases in a single resource pool, with elastic pools.

The following sections include a design checklist and recommended design options specific to Azure SQL Database security. The guidance is based on the five pillars of architectural excellence:

  • Reliability
  • Security
  • Cost optimization
  • Operational excellence
  • Performance efficiency

Prerequisites

Azure SQL Database and reliability

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions without user involvement. Management functions include:

  • Upgrades
  • Patches
  • Backups
  • Monitoring

This service allows you to create a highly available and high-performance data storage layer for your Azure applications and workloads. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability.

For more information about how Azure SQL Database promotes reliability and enables your business to continue operating during disruptions, reference Availability capabilities.

The following sections include design considerations, a configuration checklist, and recommended configuration options specific to Azure SQL Database and reliability.

Design considerations

Azure SQL Database includes the following design considerations:

  • Azure SQL Database Business Critical tier configured with geo-replication has a guaranteed Recovery time objective (RTO) of 30 seconds for 100% of deployed hours.

  • Use sharding to distribute data and processes across many identically structured databases. Sharding provides an alternative to traditional scale-up approaches for cost and elasticity. Consider using sharding to partition the database horizontally. Sharding can provide fault isolation. For more information, reference Scaling out with Azure SQL Database.

  • Azure SQL Database Business Critical or Premium tiers not configured for Zone Redundant Deployments, General Purpose, Standard, or Basic tiers, or Hyperscale tier with two or more replicas have an availability guarantee. For more information about the availability guarantee, reference SLA for Azure SQL Database.

  • Provides built-in regional high availability and turnkey geo-replication to any Azure region. It includes intelligence to support self-driving features, such as:

    • Performance tuning
    • Threat monitoring
    • Vulnerability assessments
    • Fully automated patching and updating of the code base

  • Define an application performance SLA and monitor it with alerts. Quickly detect when your application performance inadvertently degrades below an acceptable level, which is important to maintain high resiliency. Use the monitoring solution previously defined to set alerts on key query performance metrics so you can take action when the performance breaks the SLA. Go to Monitor Your Database and alerting tools for more information.

  • Use geo-restore to recover from a service outage. You can restore a database on any SQL Database server or an instance database on any managed instance in any Azure region from the most recent geo-replicated backups. Geo-restore uses a geo-replicated backup as its source. You can request geo-restore even if the database or datacenter is inaccessible because of an outage. Geo-restore restores a database from a geo-redundant backup. For more information, reference Recover an Azure SQL database using automated database backups.

  • Use the Business Critical tier configured with geo-replication, which has a guaranteed Recovery point objective (RPO) of 5 seconds for 100% of deployed hours.

  • PaaS capabilities built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business.

  • Use point-in-time restore to recover from human error. Point-in-time restore returns your database to an earlier point in time to recover data from changes done inadvertently. For more information, read the Point-in-time restore (PITR) documentation.

  • Business Critical or Premium tiers are configured as Zone Redundant Deployments which have an availability guarantee. For more information about the availability guarantee, reference SLA for Azure SQL Database.

Checklist

Have you configured Azure SQL Database with reliability in mind?

  • Use Active Geo-Replication to create a readable secondary in a different region.
  • Use Auto Failover Groups that can include one or multiple databases, typically used by the same application.
  • Use a Zone-Redundant database.
  • Monitor your Azure SQL Database in near-real time to detect reliability incidents.
  • Implement Retry Logic.
  • Back up your keys.

Configuration recommendations

Explore the following table of recommendations to optimize your Azure SQL Database configuration for reliability:

Recommendation Description
Use Active Geo-Replication to create a readable secondary in a different region. 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.
Use Auto Failover Groups that can include one or multiple databases, typically used by the same application. 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. Learn more about AutoFailover Groups and DR design.
Use a Zone-Redundant database. 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. Learn more about Zone-redundant databases.
Monitor your Azure SQL Database in near-real time to detect reliability incidents. 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. Reference Azure SQL Analytics for more information.
Implement Retry Logic. 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. For more information, reference how to implement retry logic.
Back up your keys. If you're not using encryption keys in Azure Key Vault to protect your data, back up your keys.

Azure SQL Database and security

SQL Database provides a range of built-in security and compliance features to help your application meet various security and compliance requirements.

Design checklist

Have you designed your workload and configured Azure SQL Database with security in mind?

Recommendations

Recommendation Benefit
Review the minimum TLS version. Determine whether you have legacy applications that require older TLS or unencrypted connections. After you enforce a version of TLS, it's not possible to revert to the default. Review and configure the minimum TLS version for SQL Database connections via the Azure portal. If not, set the latest TLS version to the minimum.
Ledger Consider designing database tables based on the Ledger to provide auditing, tamper-evidence, and trust of all data changes.
Always Encrypted Consider designing application access based around Always Encrypted to protect sensitive data inside applications by delegating data access to encryption keys.
Private endpoints and private link Private endpoint connections enforce secure communication by enabling private connectivity to Azure SQL Database. You can use a private endpoint to secure connections and deny public network access by default. Azure Private Link for Azure SQL Database is a type of private endpoint recommended for Azure SQL Database.
Automated vulnerability assessments Monitor for vulnerability assessment scan results and recommendations for how to remediate database vulnerabilities.
Advanced Threat Protection Detect anomalous activities indicating unusual and potentially harmful attempts to access or exploit databases with Advanced Threat Protection for Azure SQL Database. Advanced Threat Protection integrates its alerts with Microsoft Defender for Cloud.
Auditing Track database events with Auditing for Azure SQL Database.
Managed identities Consider configuring a user-assigned managed identity (UMI). Managed identities for Azure resources eliminate the need to manage credentials in code.
Microsoft Entra-only authentication Consider disabling SQL-based authentication and allowing only on Microsoft Entra authentication.

Policy definitions

Review the Azure security baseline for Azure SQL Database and Azure Policy built-in definitions.

All built-in policy definitions related to Azure SQL are listed in Built-in policies.

Review Tutorial: Secure a database in Azure SQL Database.

Azure SQL Database and cost optimization

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions without user involvement. Management functions include:

  • Upgrades
  • Patches
  • Backups
  • Monitoring

This service allows you to create a highly available and high-performance data storage layer for your Azure applications and workloads. SQL Database includes built-in intelligence that helps you dramatically reduce the costs of running and managing databases through automatic performance monitoring and tuning.

For more information about how Azure SQL Database provides cost-saving features, reference Plan and manage costs for Azure SQL Database.

The following sections include a configuration checklist and recommended configuration options specific to Azure SQL Database and cost optimization.

Checklist

Have you configured Azure SQL Database with cost optimization in mind?

Configuration recommendations

Explore the following table of recommendations to optimize your Azure SQL Database configuration for cost savings:

Recommendation Description
Optimize queries. Optimize the queries, tables, and databases using Query Performance Insights and Performance Recommendations to help reduce resource consumption, and arrive at appropriate configuration.
Evaluate resource usage. Evaluate the resource usage for all databases and determine if they've been sized and provisioned correctly. For non-production databases, consider scaling resources down as applicable. The DTUs or vCores for a database can be scaled on demand, for example, when running a load test or user acceptance test.
Fine-tune backup storage consumption For vCore databases in Azure SQL Database, the storage consumed by each type of backup (full, differential, and log) is reported on the database monitoring pane as a separate metric. Backup storage consumption up to the maximum data size for a database is not charged. Excess backup storage consumption will depend on the workload and maximum size of the individual databases. For more information, see Backup storage consumption.
Evaluate Azure SQL Database Serverless. Consider using Azure SQL Database serverless over the Provisioned Computing Tier. Serverless is a compute tier for single databases that automatically scales compute based on workload demand and bills for the amount of compute used per second. The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed. It automatically resumes databases when activity returns. Azure SQL Database serverless isn't suited for all scenarios. If you have a database with unpredictable or bursty usage patterns interspersed with periods of low or idle usage, serverless is a solution that can help you optimize price-performance.
Consider reserved capacity for Azure SQL Database. You can reduce compute costs associated with Azure SQL Database by using Reservation Discount. Once you've determined the total compute capacity and performance tier for Azure SQL databases in a region, you can use this information to reserve the capacity. The reservation can span one or three years. For more information, reference Save costs for resources with reserved capacity.
Elastic pools help you manage and scale multiple databases in Azure SQL Database Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price. For more information, see Elastic pools for managing and scaling multiple databases.

For more information, see Plan and manage costs for Azure SQL Database.

Azure SQL Database and operational excellence

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions without user involvement. Management functions include:

  • Upgrades
  • Patches
  • Backups
  • Monitoring

This service allows you to create a highly available and high-performance data storage layer for your Azure applications and workloads. Azure SQL Database provides advanced monitoring and tuning capabilities backed by artificial intelligence to help you troubleshoot and maximize the performance of your databases and solutions.

For more information about how Azure SQL Database promotes operational excellence and enables your business to continue operating during disruptions, reference Monitoring and performance tuning in Azure SQL Database.

The following sections include design considerations, a configuration checklist, and recommended configuration options specific to Azure SQL Database, and operational excellence.

Design considerations

Azure SQL Database includes the following design considerations:

  • Azure SQL Database Business Critical tier configured with geo-replication has a guaranteed Recovery time objective (RTO) of 30 seconds for 100% of deployed hours.

  • Use sharding to distribute data and processes across many identically structured databases. Sharding provides an alternative to traditional scale-up approaches for cost and elasticity. Consider using sharding to partition the database horizontally. Sharding can provide fault isolation. For more information, reference Scaling out with Azure SQL Database.

  • Azure SQL Database Business Critical or Premium tiers not configured for Zone Redundant Deployments, General Purpose, Standard, or Basic tiers, or Hyperscale tier with two or more replicas have an availability guarantee. For more information, reference SLA for Azure SQL Database.

  • Provides built-in regional high availability and turnkey geo-replication to any Azure region. It includes intelligence to support self-driving features, such as:

    • Performance tuning
    • Threat monitoring
    • Vulnerability assessments
    • Fully automated patching and updating of the code base

  • Define an application performance SLA and monitor it with alerts. Quickly detect when your application performance inadvertently degrades below an acceptable level, which is important to maintain high resiliency. Use the monitoring solution previously defined to set alerts on key query performance metrics so you can take action when the performance breaks the SLA. Go to Monitor Your Database for more information.

  • Use geo-restore to recover from a service outage. You can restore a database on any SQL Database server or an instance database on any managed instance in any Azure region from the most recent geo-replicated backups. Geo-restore uses a geo-replicated backup as its source. You can request geo-restore even if the database or datacenter is inaccessible because of an outage. Geo-restore restores a database from a geo-redundant backup. For more information, reference Recover an Azure SQL database using automated database backups.

  • Use the Business Critical tier configured with geo-replication, which has a guaranteed Recovery point objective (RPO) of 5 seconds for 100% of deployed hours.

  • PaaS capabilities built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business.

  • Use point-in-time restore to recover from human error. Point-in-time restore returns your database to an earlier point in time to recover data from changes done inadvertently. For more information, read the Point-in-time restore (PITR) documentation.

  • Business Critical or Premium tiers are configured as Zone Redundant Deployments. For more information about the availability guarantee, reference SLA for Azure SQL Database .

Checklist

Have you configured Azure SQL Database with operational excellence in mind?

  • Use Active Geo-Replication to create a readable secondary in a different region.
  • Use Auto Failover Groups that can include one or multiple databases, typically used by the same application.
  • Use a Zone-Redundant database.
  • Monitor your Azure SQL Database in near-real time to detect reliability incidents.
  • Implement retry logic.
  • Back up your keys.

Configuration recommendations

Explore the following table of recommendations to optimize your Azure SQL Database configuration for operational excellence:

Recommendation Description
Use Active Geo-Replication to create a readable secondary in a different region. 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.
Use Auto Failover Groups that can include one or multiple databases, typically used by the same application. 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. Learn more about Auto-Failover Groups and DR design.
Use a Zone-Redundant database. 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. Learn more about Zone-redundant databases.
Monitor your Azure SQL Database in near-real time to detect reliability incidents. 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. Reference Azure SQL Analytics for more information.
Implement Retry Logic. 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. For more information, reference how to implement retry logic and Configurable retry logic in SqlClient introduction.
Back up your keys. If you're not using encryption keys in Azure Key Vault to protect your data, back up your keys.

Azure SQL Database and performance efficiency

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions without user involvement. Management functions include:

  • Upgrades
  • Patches
  • Backups
  • Monitoring

The following sections include a design checklist and recommended design options specific to Azure SQL Database performance efficiency.

Design checklist

Have you designed your workload and configured Azure SQL Database with performance efficiency in mind?

Recommendations

Recommendation Benefit
Diagnose and troubleshoot high CPU utilization. Azure SQL Database provides built-in tools to identify the causes of high CPU usage and to optimize workload performance.
Understand blocking and deadlocking issues. Blocking due to concurrency and terminated sessions due to deadlocks have different causes and outcomes.
Tune applications and databases for performance. Tune your application and database to improve performance. Review best practices.
Review Azure portal utilization reporting and scale as appropriate. After deployment, use built-in reporting in the Azure portal to regularly review peak and average database utilization and right-size up or down. You can easily scale single databases or elastic pools with no data loss and minimal downtime.
Review Performance Recommendations. In the Intelligent Performance menu of the database page in the Azure portal, review and consider action on any of the Performance Recommendations and implement any index, schema, and parameterization issues.
Review Query Performance Insight. Review Query Performance Insight for Azure SQL Database reports to identify top resource-consuming queries, long running queries, and more.
Configure Automatic tuning. Provide peak performance and stable workloads through continuous performance tuning based on AI and machine learning. Consider using Azure Automation to configure email notifications for automatic tuning.
Evaluate potential use of in-memory database objects. In-memory technologies enable you to improve performance of your application, and potentially reduce cost of your database. Consider designing some database objects in high-volume OLTP applications.
Leverage the Query Store. Enabled by default in Azure SQL Database, the Query Store contains a wealth of query performance and resource consumption data, as well as advanced tuning features like Query Store hints and automatic plan correction. Review Query Store defaults in Azure SQL Database.
Implement retry logic for transient errors. Applications should include automatic transaction retry logic for transient errors including common connection errors. Leverage exponential retry interval logic.

Additional resources

For information about supported features, see Features and Resolving Transact-SQL differences during migration to SQL Database.

Migrating to Azure SQL Database? Review our Azure Database Migration Guides.

Watch episodes of Data Exposed covering Azure SQL topics and more.

Next steps