Azure SQL Database — Managed Relational Database Service built on Microsoft SQL Server
Short summary (120 characters) — included in meta: Fully managed Azure SQL Database (PaaS): architecture, tiers, security, migration, backups, tuning & troubleshooting.
1. Introduction to Azure SQL Database
Azure SQL Database is a fully managed relational database service (PaaS) built on the proven Microsoft SQL Server engine. It aims to remove operational overhead — patching, backups, high availability, and infrastructure management — so teams can focus on data and application logic. It provides deep compatibility with SQL Server, making lift-and-shift and modernization scenarios straightforward.
- Fully managed PaaS with built-in HA, backups and scaling.
- Compatible with SQL Server T-SQL, tools, and drivers.
- Multiple service tiers for different performance and cost needs.
- Integration with Azure ecosystem: Power BI, Data Factory, Defender for Cloud.
Q: When should I choose Azure SQL Database over SQL Server on VM?
A: Choose Azure SQL Database when you want managed operations (auto-patching, backups, HA) and are comfortable with PaaS service limitations (e.g., some unsupported SQL Server features). Use SQL Server on VM when you require full OS-level control or unsupported features.
2. Architecture Overview
The service separates compute and storage for most tiers, enabling independent scaling and resilience. Key components include the SQL engine, control plane, data files stored on Azure storage, and a management plane that handles backups, upgrades, and failovers.
Service tiers
Primary tiers are General Purpose, Business Critical, and Hyperscale. General Purpose is cost-effective and uses remote storage; Business Critical provides local SSD-backed storage and higher I/O with synchronous replication; Hyperscale offers distributed storage and near-instant scaling.
- Compute–storage separation enables fast scale operations.
- Control plane handles management without user intervention.
- Tier choice impacts recovery time, I/O, and feature set.
Q: How does Hyperscale differ architecturally?
A: Hyperscale separates log service, page servers and compute. It horizontally scales storage across page servers which allows databases to grow beyond typical limits and supports very fast scale operations.
3. Deployment Models
Azure SQL Database offers multiple deployment models to meet different application needs:
- Single Database — isolated database with its own resources.
- Elastic Pool — multiple databases share resources (cost-effective for variable workloads).
- Managed Instance — offers near 100% compatibility with on-prem SQL Server and supports cross-database queries, SQL Agent, linked servers in a VNET.
- Single DB — best for single-tenant SaaS databases with predictable sizing.
- Elastic Pool — ideal when multiple DBs have unpredictable and varying usage.
- Managed Instance — best for lift-and-shift with minimal app changes.
Q: When to use Managed Instance over Elastic Pool?
A: Choose Managed Instance when you require SQL Server feature parity (SQL Agent, cross-database queries, DB scoped configurations) and native VNET support. Use Elastic Pool for many small databases where resource sharing reduces cost.
4. SQL Database vs. Managed Instance vs. SQL Server on VM
This decision depends on control, compatibility needs, and operational preferences. Below is a concise comparison:
| Feature | Azure SQL Database | Managed Instance | SQL Server on VM |
|---|---|---|---|
| Platform | PaaS | PaaS (near IaaS control) | IaaS |
| SQL Server feature parity | High but some features limited | Very high (near 100%) | Full |
| Maintenance | Managed | Managed | User-managed |
| VNET support | Private endpoints | Native VNET | Full control |
- Choose based on feature needs, control and cost.
- Managed Instance eases migration but costs more than single DB in many cases.
Q: Can I move back to on-prem later?
A: Yes—using backup/restore or export/import approaches, although schema or feature differences may require adjustments.
5. Compute and Storage Options
Azure SQL Database supports two main purchasing models: vCore-based and DTU-based. vCore offers clearer visibility into CPU, memory, and IO. DTU is a blended measure of compute, memory and IO for legacy scenarios.
Scaling
Compute can be scaled vertically (choosing larger vCore sizes) and, in some tiers, horizontally (adding read replicas or using Hyperscale page servers). Storage auto-growth handles capacity requirements automatically, subject to tier limits.
- vCore model maps better to on-prem comparisons and licensing (Azure Hybrid Benefit).
- DTU model useful for simple, bundled performance sizing.
- Hyperscale decouples storage to allow huge growth with minimal impact on compute.
Q: How do I check current vCore/DTU usage?
A: Use Azure Portal metrics, Query Store/SQL Insights, or the following PowerShell to fetch DTU/vCore metrics:
# Requires Az.Sql module
Connect-AzAccount
$rg = 'myResourceGroup'
$server = 'mySqlServer'
$database = 'myDatabase'
$metrics = Get-AzMetric -ResourceId "/subscriptions/$((Get-AzContext).Subscription.Id)/resourceGroups/$rg/providers/Microsoft.Sql/servers/$server/databases/$database" -MetricName 'cpu_percent','dtu_consumption_percent','storage_used'
$metrics | Format-List
6. Built-in High Availability
Azure SQL Database provides high availability automatically. Business-critical tier uses local synchronous replicas; General Purpose relies on remote storage with network-resilient design. Azure also offers zone-redundant deployments.
- SLA varies by tier — always check the current SLA before critical deployments.
- Zone redundant configurations reduce region-level risk.
Q: How do I verify the replica health?
A: Use Azure Portal SQL server monitoring and queries against sys.dm_hadr_... or review failover group status. For Managed Instance you can inspect sys.dm_hadr_database_replica_states.
7. Automated Backups and Point-in-Time Restore
Azure SQL Database automatically takes full, differential, and transaction log backups to enable point-in-time restore (PITR) within the configured retention window. Long-term retention (LTR) can export backups to Azure Blob Storage for compliance retention.
- Default backup retention varies by tier but can be extended.
- LTR snapshots are stored in Blob Storage and can be configured per database.
Q: How to restore to a specific point in time with PowerShell?
A: Example PowerShell to restore a DB from PITR:
Connect-AzAccount
$rg = 'myResourceGroup'
$server = 'mySqlServer'
$sourceDB = 'prodDB'
$targetDB = 'prodDB-restored'
$restorePoint = (Get-Date).AddHours(-2) # 2 hours ago
New-AzSqlDatabase -ResourceGroupName $rg -ServerName $server -DatabaseName $targetDB -RestorePointInTime $restorePoint -SourceDatabaseId "/subscriptions/$((Get-AzContext).Subscription.Id)/resourceGroups/$rg/providers/Microsoft.Sql/servers/$server/databases/$sourceDB"
8. Performance Optimization
Use Query Store, Automatic Tuning, and intelligent insights to detect plan regressions and index recommendations. Index maintenance, statistics updates, and careful query design remain essential.
- Enable Query Store to capture plans and runtime stats.
- Use Automatic Tuning for plan forcing and index recommendations.
- Monitor waits and top resource-consuming queries via SQL Insights.
Q: How to get top resource-consuming queries using T-SQL?
-- Example: top CPU queries from Query Store
SELECT TOP 20
qt.query_id, qs.avg_cpu_time, qs.avg_duration, qt.query_sql_text
FROM sys.query_store_runtime_stats qs
JOIN sys.query_store_query q ON qs.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
ORDER BY qs.avg_cpu_time DESC;
9. Security and Compliance
Azure SQL Database offers Transparent Data Encryption (TDE), Always Encrypted, TLS, and customer-managed keys (CMK) via Azure Key Vault. Authentication options include Azure AD, SQL authentication, and managed identities for Azure resources.
- Use Azure AD for centralized identity & RBAC management.
- Enable TDE by default; consider CMK for key control requirements.
- Use Private Link / Private Endpoint to remove public exposure.
Q: How to enable Azure AD authentication and test a connection?
A: You can create an Azure AD user in the DB and connect using Azure AD credentials or managed identity. Example T-SQL to create an AAD user:
-- Connect as an Azure AD admin
CREATE USER [app@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app@contoso.com];
From application connection string use Authentication=Active Directory Default or appropriate token-based auth.
10. Scalability Features
Elastic pools support many databases sharing resources. Vertical scaling up/down changes compute and memory. Some tiers support auto-scale or serverless autosuspend/resume to save costs.
- Use Elastic Pool for multi-tenant SaaS with variable per-DB usage.
- Perform controlled scale operations during maintenance windows to avoid transient performance impact.
Q: How to scale compute using PowerShell?
Set-AzSqlDatabase -ResourceGroupName 'myRg' -ServerName 'myServer' -DatabaseName 'myDB' -RequestedServiceObjectiveName 'GP_Gen5_4' # example vCore SKU
11. Geo-Replication and Disaster Recovery
Active geo-replication provides readable secondary databases in other regions. Failover groups simplify orchestrated failover of multiple DBs and DNS redirection. Cross-region replication is vital for BCDR planning.
- Test failover regularly to validate RTO and RPO assumptions.
- Consider data residency and compliance when choosing target regions.
Q: How to create a failover group via PowerShell?
New-AzSqlFailoverGroup -ResourceGroupName 'myRg' -ServerName 'primaryServer' -PartnerServerName 'secondaryServer' -FailoverGroupName 'myFailoverGroup'
12. Serverless vs. Provisioned Compute Tier
Serverless compute automatically scales based on workload and can pause during inactivity (pause incurs no compute cost). Provisioned compute is fixed and provides predictable performance for steady workloads.
- Serverless is cost-effective for intermittent usage with predictable pauses.
- Provisioned is better for steady, high-throughput workloads.
Q: Does serverless affect connection latency after resume?
A: Yes—first connection after resume experiences cold-start latency as compute spins up. Design apps with retries and transient handling.
13. Integration with Microsoft Entra ID (Azure AD)
Azure SQL Database supports Azure AD authentication for users and applications, enabling centralized identity and RBAC management. Managed identities simplify credential management for Azure services connecting to the database.
- Use Managed Identity for Azure Functions or App Service to connect securely without credentials.
- Leverage Azure RBAC for server-level management and database-level roles for granular permissions.
Q: How to grant database access to a managed identity?
-- on the SQL DB, connected as Azure AD admin
CREATE USER [myManagedIdentityName] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datawriter ADD MEMBER [myManagedIdentityName];
14. Networking and Connectivity
Choose between public endpoints secured by firewall rules and Private Link (private endpoint) for private VNET access. Use VNET Service Endpoints for Managed Instance-level integration. Always minimize public exposure.
- Private Endpoints are recommended for production to block public traffic.
- Ensure firewall and NSG rules on application subnets permit outbound to the DB endpoints.
Q: How to verify network latency from app to DB?
A: Use Network Performance Monitor, az network watcher, or simple T-SQL timing tests (e.g., measure round-trip time using client-side tools).
15. Data Migration Options
Common migration approaches include Azure Database Migration Service (DMS) for minimal downtime, BACPAC export/import for schema and data, and Data Migration Assistant (DMA) for compatibility assessments. Online migrations are preferred for critical systems.
- Run DMA to find compatibility issues before migration.
- Use DMS for near-zero downtime migration where possible.
Q: How to assess mission-critical features before migration?
A: Run DMA, test in a staging environment, and validate SQL Agent jobs, linked servers, and CLR assemblies for Managed Instance compatibility.
16. Monitoring and Alerts
Integrate Azure SQL with Azure Monitor, Log Analytics, and SQL Insights. Configure alerts for DTU/vCore consumption, long-running queries, deadlocks, and failed backups. Query Store provides query-level telemetry for troubleshooting.
- Use metric alerts for high-level health and activity monitoring.
- Use Log Analytics for detailed diagnostics and custom queries.
Q: Example Kusto query to find recent deadlocks in Log Analytics?
AzureDiagnostics
| where Category == 'Deadlock'
| sort by TimeGenerated desc
| take 50
17. Auditing and Logging
Configure auditing to send events to Azure Storage, Event Hubs, or Log Analytics. Auditing helps with compliance by capturing database-level events and tracking activity for forensic investigations.
- Choose Log Analytics for advanced analysis and retention flexibility.
- Audit retention policies should meet compliance requirements.
Q: How to enable auditing via PowerShell?
Set-AzSqlServerAudit -ResourceGroupName 'myRg' -ServerName 'myServer' -AuditActionGroup 'BATCH_COMPLETED_GROUP' -StorageAccountName 'mystorageaccount'
18. Cost Management and Optimization
Understand pricing models (vCore, DTU, serverless) and use Azure Cost Management to track spend. Use reserved capacity and Azure Hybrid Benefit where applicable. Optimize by right-sizing compute, using elastic pools, and automating scale where safe.
- Reserved capacity can save up to 65% for 1-3 year commitments.
- Use performance testing to size correctly before purchase commitments.
Q: How to estimate monthly costs for a vCore-based DB?
A: Use the Azure Pricing Calculator and include storage, backup storage, and networking egress costs.
19. Elastic Pools
Elastic Pools let multiple databases share a pool of resources measured in eDTUs or vCores. Pools reduce cost when individual databases have varying and unpredictable resource needs.
- Monitor per-database usage to avoid noisy-neighbor issues.
- Set per-database min/max to limit unexpected consumption.
Q: How to add a database to an elastic pool with PowerShell?
Set-AzSqlDatabase -ResourceGroupName 'myRg' -ServerName 'myServer' -DatabaseName 'myDb' -ElasticPoolName 'myPool'
20. Managed Instance Features
Managed Instance supports many SQL Server features such as SQL Agent, cross-database queries, linked servers, and native VNET placement, simplifying migrations and complex DB application scenarios.
- Use Managed Instance for near-transparent migrations from on-prem SQL Server.
- Network integration via VNET allows tighter security and control.
Q: How to run SQL Agent Jobs on Managed Instance?
A: SQL Agent is available — configure jobs via SSMS or T-SQL like on-premises.
21. Hyperscale Tier
Hyperscale provides a unique architecture with page servers, log service, and compute nodes. It supports rapid scale operations and storage growth to 100+ TB, making it ideal for very large OLTP workloads.
- Hyperscale decouples storage from compute to enable large sizes.
- Scale compute independently and add read replicas for scaling reads.
Q: Are all features supported on Hyperscale?
A: Most features are supported but verify specific features like cross-database queries or certain SQL Server extensions before migrating.
22. Advanced Data Security (ADS)
Advanced Data Security includes Threat Detection, Vulnerability Assessment, and a security posture assessment with recommendations. It integrates with Microsoft Defender for Cloud to provide threat visibility and automated response options.
- Enable Vulnerability Assessment and remediate prioritized findings regularly.
- Use Advanced Threat Protection to get alerts on anomalous activities.
Q: How to run vulnerability assessment scan results via PowerShell?
Get-AzSqlVulnerabilityAssessment -ResourceGroupName 'myRg' -ServerName 'myServer' -DatabaseName 'myDb'
23. Business Continuity and Disaster Recovery (BCDR)
Design your BCDR plan around geo-replication, failover groups, automated backups and regular failover testing. Document RTO/RPO goals and ensure your chosen architecture meets them.
- Run scheduled DR drills to validate failover and recovery procedures.
- Include application-level considerations (connection strings, DNS TTLs, config stores) in failover plans.
Q: How to test a failover without disrupting production?
A: Use read-only replicas, staging environments and scheduled maintenance windows. Validate with test databases and scripted failover/failback procedures.
24. Azure Policy and Governance
Enforce organizational standards using Azure Policy (e.g., require private endpoints, enforce SKU limits). Combine with RBAC, resource tagging, and cost reporting to maintain governance at scale.
- Define and audit policies that prevent insecure deployments.
- Use tags for cost allocation and lifecycle management.
Q: Can I enforce that all SQL DBs use private endpoints?
A: Yes—create an Azure Policy that audits or denies SQL DBs without Private Link configurations.
25. DevOps Integration
Use IaC (ARM, Bicep, Terraform) and CI/CD pipelines to provision SQL resources and database deployments. Integrate schema migrations into pipelines with Flyway, Liquibase or DACPAC deployments.
- Store schema changes in source control and apply via automated pipelines.
- Automate pre-deployment checks and performance tests in staging environments.
Q: Example Terraform snippet to create a SQL database?
resource "azurerm_sql_server" "example" {
name = "example-sqlserver"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
administrator_login = "sqladmin"
administrator_login_password = "H@Sh1CoR3!"
}
resource "azurerm_sql_database" "exampledb" {
name = "exampledb"
server_id = azurerm_sql_server.example.id
sku_name = "GP_Gen5_2"
}
26. Integration with Azure Ecosystem
Azure SQL Database integrates with Power BI, Azure Data Factory, Logic Apps, Functions and Synapse Analytics for analytics, ETL and serverless compute integration.
- Use Data Factory for scheduled ETL and Synapse for large analytical workloads.
- Power BI connects directly for reporting with appropriate gateway or service principal.
Q: How to connect Power BI to Azure SQL DB securely?
A: Use a service principal or managed identity and ensure the DB uses Private Link if required. Configure least-privilege access and use credentials stored in Key Vault where possible.
27. Maintenance and Updates
Azure performs automatic patching and updates to the SQL engine. Microsoft provides maintenance windows and notifications for planned updates. The PaaS model minimizes downtime exposure from patching.
- Keep an eye on Azure Service Health for planned maintenance and incidents.
- Design for transient connection resilience due to short maintenance interruptions.
Q: Can I control when maintenance occurs?
A: You can receive notifications and, in some cases, select maintenance windows for managed instances, but full control is not available as in IaaS.
28. Best Practices
Adopt least-privilege, use private endpoints, enable encryption and auditing, monitor with Query Store and Azure Monitor, and automate deployments with IaC. Regularly run vulnerability scans and DR tests.
- Use Private Link and restrict public access.
- Enable Azure AD authentication and RBAC.
- Monitor Query Store and configure Automatic Tuning.
- Schedule routine maintenance and backups testing.
29. Troubleshooting and Diagnostics
Troubleshooting commonly involves identifying slow queries, connection issues, or resource exhaustion. Use DMVs, Query Store and SQL Insights. Below are common diagnostics steps and tooling.
Common diagnostics checklist
- Check Azure metrics: CPU, DTU/vCore usage, storage latency.
- Review Query Store for regressed plans.
- Review deadlocks and long-running transactions.
- Check firewall and private endpoint configurations for connectivity failures.
PowerShell & Microsoft Graph troubleshooting examples
Below are actionable snippets for discovering issues and exporting diagnostic data.
Install-Module -Name Az.Sql -Scope CurrentUser -Force
Connect-AzAccount
$sqlServer = 'myServer'
dbName = 'myDb'
# Use Query Store via T-SQL through Invoke-AzSqlDatabaseQuery (or use SqlConnection in PowerShell)
$query = @"
SELECT TOP 20 qs.avg_cpu_time, qt.query_sql_text
FROM sys.query_store_runtime_stats qs
JOIN sys.query_store_query q ON qs.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
ORDER BY qs.avg_cpu_time DESC;
"@
Invoke-AzSqlDatabaseQuery -ResourceGroupName 'myRg' -ServerName $sqlServer -DatabaseName $dbName -Query $query | Out-File 'top_cpu_queries.txt'
GET https://management.azure.com/subscriptions/{subscriptionId}/providers/Microsoft.Sql/servers?api-version=2021-02-01-preview
Authorization: Bearer <access_token>
- Collect metrics & query plans before making changes.
- Test fixes in a staging environment first.
- When in doubt, capture an activity trace or extended events for deeper analysis.
Q: What to do if connectivity to DB suddenly fails?
A: Check firewall settings, private endpoint NIC status, NSG rules, and Azure service health. Validate server-level and DB-level auditing logs for denied connections and review recent configuration changes.
30. Real-World Use Cases
Azure SQL Database is used for multi-tenant SaaS platforms, enterprise ERP/CRM backends, hybrid analytics platforms, and web applications requiring elastic scalability. Hyperscale is used for very large OLTP stores, while serverless suits intermittent workloads or dev/test scenarios.
- SaaS apps use Elastic Pools to optimize costs across tenants.
- Enterprise apps use Managed Instance for near-transparent migration.
- Analytics and reporting often combine Azure SQL with Synapse and Power BI.
Q: Which tier is best for a new SaaS product?
A: For many new SaaS applications, start with Single DB or Elastic Pool in General Purpose; move to Business Critical or Hyperscale as performance or size needs grow. Use Managed Instance if legacy SQL Server features are required.
Shivam Tiwari
Website: http://Cloudknowledge.in
Leave a Reply