Azure SQL Consulting and Managed Services: Enterprise Database Engineering on Microsoft Azure
Azure SQL Database and Azure SQL Managed Instance represent Microsoft’s most mature cloud-native relational database offerings, yet the gap between a correctly engineered deployment and a default-configured one is substantial. Licensing model selection, tier choice, geo-replication topology, and Intelligent Performance configuration each carry performance and cost implications that compound over time. At MinervaDB, our Azure SQL consulting and managed services practice has guided engineering teams through DTU-to-vCore migrations, Hyperscale tier adoptions, and Managed Instance migrations where default configurations were leaving 40–60% of available performance on the table. We bring practitioner-level depth to every engagement.
Performance Engineering for Azure SQL
DTU vs. vCore model selection: DTUs bundle compute, memory, and I/O into a single opaque unit — useful for predictable workloads with known resource ratios but limiting when you need to tune individual resource dimensions. The vCore model exposes CPU, memory, and storage independently and is the correct choice for production workloads where you need to reason about resource allocation explicitly.
For workloads migrating from on-premises SQL Server, the vCore model maps more naturally to existing capacity planning: 1 vCore ≈ 1 physical core on the source server, with memory ratios configurable per service tier. At MinervaDB, we default to vCore for all production migrations and reserve DTU tiers for development environments where simplicity outweighs tuning precision.
Intelligent Performance — Query Performance Insight and auto-tuning:
-- Review top resource-consuming queries via sys.dm_exec_query_stats
SELECT TOP 10
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time_us,
qs.execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_elapsed_time_us DESC;
Azure SQL’s built-in auto-tuning engine handles three categories of recommendations: CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN (via Query Store). We enable FORCE LAST GOOD PLAN in all production environments — it is the least invasive auto-tuning action and has the highest benefit-to-risk ratio. Automatic index creation and dropping require more careful evaluation; we review recommendations before enabling automatic application.
-- Enable auto-tuning for plan forcing only ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON); -- Review auto-tuning recommendations SELECT name, reason, score, JSON_VALUE(details, '$.implementationDetails.script') AS recommended_action FROM sys.dm_db_tuning_recommendations WHERE state = 'Active' ORDER BY score DESC;
Scalability: Hyperscale, Geo-Replication, and Elastic Pools
Hyperscale read scale-out: Hyperscale supports up to four named replicas that serve as dedicated read endpoints. Unlike standard read replicas, Hyperscale named replicas share the same distributed storage layer — there is no replication lag for reads against the storage tier itself. Application-tier read routing uses the replica’s connection string directly.
-- Hyperscale named replica connection Server: mydb-replica1.database.windows.net Database: mydb -- Named replicas appear as independent server endpoints
Geo-replication for cross-region DR and read distribution: Active geo-replication maintains up to four readable secondary databases in other Azure regions. Secondary databases are continuously synchronized and can serve read workloads while also serving as failover targets.
# Create geo-replication secondary via Azure CLI az sql db replica create \ --name mydb \ --resource-group prod-rg \ --server prod-sql-server \ --partner-server dr-sql-server \ --partner-resource-group dr-rg \ --service-objective GP_Gen5_4
Auto-failover groups provide a single connection endpoint that automatically redirects after regional failover — eliminating the need for application connection string updates during DR events. We configure failover groups with manual failover policy for production (to prevent spurious failovers on transient regional incidents) and automatic for pre-production environments.
Elastic Pools for multi-tenant architectures: Elastic Pools share a vCore or DTU budget across multiple databases — the correct architecture for SaaS applications with many tenant databases that do not peak concurrently. We size Elastic Pool capacity at 1.5× the sum of average individual database requirements, which provides headroom for concurrent peaks without over-provisioning for worst-case aggregate load.
High Availability Configuration
Built-in HA with local redundancy: All Azure SQL tiers above Basic provide built-in HA using Always On availability group technology at the infrastructure layer. The application connects to a single logical endpoint; failover is transparent and typically completes within 20–30 seconds.
Zone-redundant configuration: Zone redundancy distributes the database and replicas across Availability Zones within a region. This is the correct configuration for workloads with RTO requirements measured in seconds during a zone-level failure.
az sql db update \ --name mydb \ --resource-group prod-rg \ --server prod-sql-server \ --zone-redundant true
Long-term backup retention (LTR): Azure SQL automated backups cover 1–35 days. For compliance requirements mandating 7-year retention, LTR stores weekly, monthly, and yearly backups in Azure Blob Storage with geo-redundant replication:
az sql db ltr-policy set \ --resource-group prod-rg \ --server prod-sql-server \ --database mydb \ --weekly-retention P12W \ --monthly-retention P12M \ --yearly-retention P5Y \ --week-of-year 1
Maintenance window scheduling: Azure SQL allows maintenance window selection (weekday windows or weekend windows) to align patching with business off-hours. We configure this for all production instances and subscribe to Azure Service Health alerts for planned maintenance notifications.
Data Reliability Engineering
Query Store for plan stability: Query Store captures execution plans and runtime statistics across plan changes, providing the data to diagnose and remediate plan regression — a leading cause of unexplained performance degradation after index changes or statistics updates.
-- Find queries with highest variance in execution plan
SELECT q.query_id, qt.query_sql_text, COUNT(p.plan_id) AS plan_count,
MAX(rs.avg_duration) AS max_avg_duration_us
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(p.plan_id) > 1
ORDER BY max_avg_duration_us DESC;
Automated backups and restore validation: Azure SQL performs full, differential, and transaction log backups automatically. We instrument backup status monitoring through Azure Monitor and conduct quarterly PITR validation by restoring to an isolated test server.
Change tracking and CDC for data pipelines: For ELT pipelines consuming Azure SQL data, we configure SQL Server Change Tracking (lightweight, row-level change detection) or Change Data Capture (full before/after row capture) based on downstream consumer requirements.
Data Security
Advanced Threat Protection (Defender for SQL): Microsoft Defender for SQL monitors query patterns and flags anomalous behavior: SQL injection attempts, access from unusual locations, privilege escalation sequences, and bulk data extraction patterns. We enable Defender for SQL on all production instances and route alerts to the security team’s SIEM.
az sql db threat-policy update \ --name mydb \ --resource-group prod-rg \ --server prod-sql-server \ --state Enabled \ --email-addresses security@company.com \ --email-account-admins true
Azure AD authentication and managed identities: We configure Azure SQL to use Azure Active Directory as the primary authentication provider. Application services authenticate using managed identities — eliminating stored database credentials entirely. Interactive users authenticate through Azure AD with MFA enforcement.
-- Create Azure AD user in SQL Database CREATE USER [app-managed-identity] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [app-managed-identity]; ALTER ROLE db_datawriter ADD MEMBER [app-managed-identity];
Transparent Data Encryption (TDE) with customer-managed keys: TDE is enabled by default; for regulated workloads, we configure TDE with customer-managed keys in Azure Key Vault. This ensures that key rotation and revocation are fully under the organization’s control, not Microsoft’s default key management.
Private endpoint and network isolation: Azure SQL Private Endpoint assigns the database a private IP within the application VNet. We disable public endpoint access entirely and route all traffic through the private endpoint, eliminating exposure to internet-originated attacks.
Key Takeaways
- vCore model provides the explicit resource control needed for production workload tuning; DTU tiers are appropriate for development only.
- Hyperscale supports up to 100 TB with near-instantaneous backups and up to four named read replicas with no replication lag at the storage tier.
- FORCE_LAST_GOOD_PLAN auto-tuning is the lowest-risk, highest-return intelligent performance feature to enable in production.
- Auto-failover groups provide single-endpoint DR failover without application-level connection string management.
- Microsoft Defender for SQL provides behavioral threat detection without requiring an external SIEM agent on the database tier.
- Azure AD managed identity authentication eliminates static database credentials from application deployments.
- LTR policies extend backup retention beyond 35 days for compliance requirements up to 7 years.
How MinervaDB Can Help
MinervaDB provides Azure SQL consulting and managed services for organizations that need more than default configurations. Our engineering team handles DTU-to-vCore migrations, Hyperscale tier adoption, geo-replication and auto-failover group design, Intelligent Performance optimization, and SQL Managed Instance migrations from on-premises SQL Server. We also provide ongoing managed operations with defined SLAs covering query performance, HA monitoring, and security posture.
If you are planning an Azure SQL migration or need to improve an existing deployment’s performance or reliability, contact MinervaDB to discuss your requirements.
