DP-900 Objective 2.2: Describe Relational Azure Data Services

 • 34 min read • Microsoft Azure Data Fundamentals

Share:

DP-900 Exam Focus: This objective covers Azure SQL family including Azure SQL Database (fully managed PaaS), Azure SQL Managed Instance (near-complete SQL Server compatibility for lift-and-shift), and SQL Server on Azure Virtual Machines (IaaS with full control); and Azure database services for open-source systems including Azure Database for PostgreSQL, MySQL, and MariaDB. Understanding deployment options, features, use cases, and how to choose appropriate services is essential for the exam.

Understanding Azure Relational Database Services

Microsoft Azure provides comprehensive relational database services spanning fully managed Platform-as-a-Service (PaaS) offerings to Infrastructure-as-a-Service (IaaS) virtual machines. These services support both Microsoft SQL Server and popular open-source database systems, enabling organizations to run relational workloads in the cloud with varying levels of management, control, and compatibility. The Azure SQL family delivers SQL Server-compatible databases across different deployment models optimized for specific scenarios from new cloud-native applications to lift-and-shift migrations. Azure's open-source database services provide managed PostgreSQL, MySQL, and MariaDB offerings bringing enterprise-grade reliability to open-source databases without operational overhead.

Understanding Azure relational database services enables selecting appropriate options for specific requirements. New cloud applications often benefit from fully managed services minimizing operational overhead. Enterprise migrations might require specific compatibility features influencing deployment choices. Development teams familiar with open-source databases can leverage Azure managed services gaining cloud benefits while preserving familiar tools and skills. The variety of options ensures organizations can optimize for factors like management overhead, compatibility requirements, control needs, and cost considerations. Azure's managed services handle infrastructure concerns like patching, backups, high availability, and monitoring, allowing focus on applications rather than database administration. This democratizes enterprise-grade database capabilities making sophisticated features accessible without deep database expertise.

Azure SQL Database

Core Features and Capabilities

Azure SQL Database is a fully managed PaaS relational database based on the latest stable version of Microsoft SQL Server Database Engine. As a managed service, Microsoft handles all infrastructure including servers, storage, patching, and monitoring. You work with databases directly without managing underlying infrastructure. Azure SQL Database automatically provides high availability with 99.99% SLA through built-in replicas and automatic failover. Automated backups run continuously with point-in-time restore capability up to 35 days. Long-term retention extends backup periods up to 10 years for compliance requirements. Geo-replication creates readable secondary databases in different Azure regions for disaster recovery and read scale-out.

Intelligent features optimize performance automatically. Automatic tuning applies indexing recommendations and query plan improvements based on workload patterns. Query Performance Insight identifies problematic queries through execution statistics and visual insights. Intelligent Insights detects performance issues using AI analyzing telemetry. Security features include transparent data encryption (TDE) encrypting data at rest, always encrypted enabling client-side encryption, row-level security restricting data access based on user context, dynamic data masking obscuring sensitive data, Azure Active Directory authentication, and advanced threat protection detecting suspicious activities. Monitoring through Azure Monitor tracks resource utilization, query performance, and database health with customizable alerts and dashboards.

Deployment Options

Azure SQL Database offers flexible deployment options. Single databases provide dedicated resources for individual databases with predictable performance. You select compute and storage independently scaling each dimension. Service tiers include General Purpose for common workloads balancing performance and cost, Business Critical for high-performance applications requiring low latency and high availability, and Hyperscale for very large databases up to 100TB with fast backups and massive scale-out capabilities. Compute tiers include Provisioned with fixed resources and Serverless automatically scaling compute based on workload and pausing during inactivity to save costs—ideal for intermittent or unpredictable workloads.

Elastic pools share resources across multiple databases optimizing costs for multi-tenant SaaS applications. Instead of provisioning resources per database, elastic pools allocate shared resources distributed dynamically among pool databases based on utilization. This dramatically reduces costs for scenarios with many databases having varying usage patterns—some active while others idle. Elastic pools support same service tiers as single databases. You configure pool size (eDTUs or vCores) and number of databases sharing resources. Azure automatically balances resources across databases ensuring each gets what it needs within pool limits. Elastic pools simplify management at scale enabling creation and configuration of many databases through single resource while optimizing costs through resource sharing.

Use Cases and Best Fit Scenarios

Azure SQL Database excels for modern cloud-native applications prioritizing managed services over infrastructure management. New application development benefits from rapid provisioning, automatic scaling, and intelligent optimizations without database administration expertise. SaaS applications with multi-tenant architectures leverage elastic pools sharing resources across tenant databases while maintaining isolation. Microservices architectures use Azure SQL Database for individual service databases scaling independently. Development and testing environments benefit from quick database creation, serverless compute pausing when unused, and geo-replication for testing failover scenarios.

Variable workloads utilize serverless compute automatically scaling based on demand and pausing during inactivity—common for applications with sporadic usage patterns or batch processing. Applications requiring global distribution use geo-replication placing readable secondaries near users reducing latency while providing disaster recovery. Azure SQL Database suits applications prioritizing operational simplicity, automatic optimization, and built-in security over complete SQL Server feature parity. Organizations moving to cloud often choose Azure SQL Database for greenfield projects where compatibility with on-premises SQL Server features isn't critical, enabling full advantage of cloud-native capabilities without legacy constraints.

Azure SQL Managed Instance

Core Features and Capabilities

Azure SQL Managed Instance provides near 100% compatibility with SQL Server Enterprise Edition in a fully managed PaaS offering. Unlike Azure SQL Database focusing on individual databases, Managed Instance provides complete SQL Server instance experience including instance-level features. This enables lift-and-shift migrations of existing applications with minimal code changes. Microsoft manages infrastructure, patching, backups, and high availability similarly to Azure SQL Database, but you get SQL Server instance with familiar management model. Managed Instance supports features unavailable in Azure SQL Database like cross-database queries, SQL Agent for scheduling jobs, Service Broker for messaging, CLR integration for .NET code in database, linked servers, database mail, distributed transactions, and native virtual network integration.

Managed Instance deploys into customer's virtual network (VNet) providing network isolation and private IP addresses. This enables secure connectivity from on-premises networks through VPN or ExpressRoute, integration with existing virtual networks, and network security groups controlling traffic. Database sizes support up to 16TB per instance. Multiple databases within instance share instance resources. Managed Instance provides instance-level collation control, essential for applications requiring specific collation settings. Built-in high availability uses Always On availability groups automatically handling failover. Automated backups provide point-in-time restore and long-term retention. Geo-replication through failover groups enables disaster recovery across regions.

Migration and Compatibility

Azure SQL Managed Instance excels at lift-and-shift migrations from on-premises SQL Server. The high compatibility means most SQL Server applications work without code changes. Azure Database Migration Service provides assessment tools identifying compatibility issues and orchestrating migrations with minimal downtime. Data Migration Assistant analyzes on-premises databases reporting compatibility problems and feature parity. Azure provides migration guidance, scripts, and tools streamlining the process. For most databases, migration involves assessing compatibility, optionally fixing issues, creating Managed Instance, and migrating data through backup/restore, transactional replication, or Data Migration Service.

Common migration scenarios include migrating SQL Server 2008/2012 workloads approaching end-of-support gaining extended security updates in Azure, consolidating multiple SQL Server instances into Managed Instance reducing infrastructure costs, and modernizing data centers by moving workloads to cloud while preserving application compatibility. The compatibility bridge enables organizations to gain cloud benefits—automatic backups, geo-replication, built-in monitoring—without rewriting applications. Once migrated, applications can optionally refactor over time to leverage more cloud-native capabilities. This incremental approach reduces migration risk and accelerates cloud adoption compared to requiring extensive application changes.

Use Cases and Best Fit Scenarios

Azure SQL Managed Instance targets existing enterprise SQL Server workloads migrating to cloud. Applications requiring instance-level features like SQL Agent jobs for ETL processes, cross-database queries, CLR assemblies, or Service Broker benefit from Managed Instance providing these capabilities unavailable in Azure SQL Database. Enterprise applications built over years often depend on multiple interconnected databases—Managed Instance supports this architecture natively. Applications requiring private connectivity and virtual network integration for security compliance use Managed Instance's native VNet deployment. Organizations wanting managed service benefits—automatic patching, backups, high availability—without extensive application refactoring choose Managed Instance balancing compatibility and management.

Managed Instance suits scenarios where near-complete SQL Server compatibility is critical but full IaaS control isn't required. Database administrators familiar with SQL Server find Managed Instance provides familiar management experience while Microsoft handles infrastructure. The service bridges on-premises and cloud enabling hybrid architectures with on-premises connectivity. For organizations with substantial SQL Server investments, Managed Instance enables cloud migration preserving existing skills, tools, and applications while gaining cloud scalability and resilience. It's the recommended option for SQL Server Enterprise customers moving production workloads to Azure when database-level Azure SQL Database capabilities prove insufficient.

SQL Server on Azure Virtual Machines

Core Features and Capabilities

SQL Server on Azure Virtual Machines provides Infrastructure-as-a-Service (IaaS) deployment running SQL Server on Windows or Linux VMs. Unlike PaaS options where Microsoft manages infrastructure, you have complete control over virtual machine, operating system, and SQL Server installation. This maximum flexibility enables custom configurations, access to operating system, and support for all SQL Server versions and editions including older versions for legacy compatibility. Azure provides pre-configured SQL Server VM images from Azure Marketplace with SQL Server already installed and configured, or you can install SQL Server on standard VMs using bring-your-own-license (BYOL) or pay-as-you-go licensing.

You manage patching, backups, high availability, and monitoring similarly to on-premises deployments but leverage Azure infrastructure for rapid provisioning, flexible scaling, and cloud networking. Azure Site Recovery provides disaster recovery. Azure Backup integrates with SQL Server for managed backup services. SQL Server VMs support all SQL Server features without PaaS limitations—replication, distributed transactions, full-text search, and any third-party tools. You can install additional software on VMs, customize operating system settings, and configure SQL Server instances exactly as needed. Azure Virtual Machine scale sets enable multiple SQL Server VMs for scale-out scenarios. Premium SSD storage provides high IOPS and low latency for database files.

Deployment and Management

Deploying SQL Server on Azure VMs involves selecting VM size based on workload requirements (CPU, memory, storage), choosing SQL Server version and edition from marketplace images, configuring storage using managed disks with appropriate performance tiers, and setting up networking including virtual networks, network security groups, and load balancers. Azure provides SQL Server-specific optimizations like storage configuration recommendations, automated patching windows, automated backup to Azure Blob Storage, and Azure Key Vault integration for encryption key management. The SQL IaaS Agent Extension enables management features through Azure Portal.

Management responsibilities include applying SQL Server and operating system patches, configuring and testing backups, setting up high availability through Always On Availability Groups or failover cluster instances, monitoring performance through SQL Server tools and Azure Monitor, managing security including firewall rules and authentication, and capacity planning for resource scaling. While requiring more management than PaaS options, SQL Server VMs provide maximum control. Azure Hybrid Benefit allows using existing SQL Server licenses in Azure reducing costs. Reserved instances provide significant discounts for predictable long-term workloads. Organizations with existing SQL Server expertise can leverage those skills in Azure while gaining cloud infrastructure benefits.

Use Cases and Best Fit Scenarios

SQL Server on Azure VMs suits scenarios requiring maximum SQL Server compatibility including all versions and editions. Legacy applications dependent on older SQL Server versions not available in PaaS options require IaaS. Applications needing operating system access for third-party monitoring tools, backup software, or security agents use VMs. Custom SQL Server configurations beyond PaaS capabilities—specific trace flags, custom startup parameters, or unconventional configurations—require IaaS control. Applications using SQL Server features unsupported in Managed Instance like certain replication topologies or distributed transactions across instances need IaaS deployments.

Lift-and-shift migrations prioritizing speed over optimization often choose VMs for rehosting without changes, planning future refactoring. Organizations preferring infrastructure control and direct database administration select VMs. Scenarios requiring specific performance characteristics like ultra-high IOPS or massive memory benefit from large Azure VMs. SQL Server VMs enable hybrid scenarios running some workloads on-premises and others in Azure with consistent management. While requiring most management overhead among Azure SQL family options, VMs provide most flexibility and compatibility making them appropriate choice when PaaS limitations prevent using managed services or when complete control outweighs managed service benefits.

Comparing Azure SQL Options

Service Comparison Matrix

Azure SQL Family Comparison:

  • Management Model: SQL Database (fully managed PaaS), Managed Instance (fully managed PaaS with instance), SQL VMs (customer-managed IaaS)
  • SQL Server Compatibility: SQL Database (database-level features), Managed Instance (near 100% instance compatibility), SQL VMs (100% all versions)
  • Management Overhead: SQL Database (lowest), Managed Instance (low), SQL VMs (high)
  • Deployment Unit: SQL Database (individual databases), Managed Instance (database instance), SQL VMs (virtual machines)
  • Virtual Network: SQL Database (optional with private link), Managed Instance (native VNet integration), SQL VMs (full VNet integration)
  • Maximum Database Size: SQL Database (100TB hyperscale), Managed Instance (16TB per instance), SQL VMs (limited by VM storage)
  • Scaling: SQL Database (automatic serverless or manual), Managed Instance (manual vertical scaling), SQL VMs (manual VM resize or scale-out)
  • High Availability: SQL Database (built-in 99.99% SLA), Managed Instance (built-in 99.99% SLA), SQL VMs (customer-configured Always On)
  • Pricing Model: SQL Database (vCore or DTU per database), Managed Instance (vCore per instance), SQL VMs (VM + SQL license or BYOL)

Decision Criteria

Selecting appropriate Azure SQL option depends on multiple factors. Management overhead considerations favor PaaS options (SQL Database or Managed Instance) for reduced operational burden versus IaaS requiring traditional database administration. Compatibility requirements drive choices—applications needing instance-level features require Managed Instance, while maximum compatibility including all SQL Server versions requires VMs. Azure SQL Database suits applications adaptable to database-level features. Control requirements influence decisions—organizations needing operating system access or custom configurations require VMs, while those accepting managed services benefit from PaaS automation.

Cost optimization varies by option. Azure SQL Database offers finest-grained pricing per database with serverless compute for variable workloads and elastic pools for multi-tenant scenarios. Managed Instance prices per instance shared by multiple databases, optimizing cost for consolidated workloads. SQL Server VMs combine VM costs with SQL Server licensing—Azure Hybrid Benefit significantly reduces costs using existing licenses. Migration timeline matters—VMs enable fastest rehosting, Managed Instance enables lift-and-shift with minimal changes, while SQL Database might require more refactoring but offers best long-term operational efficiency. Many organizations use multiple options deploying new applications on SQL Database, migrating enterprise workloads to Managed Instance, and using VMs for special cases requiring IaaS control.

Azure Database for PostgreSQL

Core Features and Capabilities

Azure Database for PostgreSQL is a fully managed PaaS offering for PostgreSQL, one of the most powerful and popular open-source relational databases known for reliability, feature robustness, and extensibility. Microsoft manages infrastructure, patching, backups, and high availability. It's based on community PostgreSQL preserving compatibility with PostgreSQL tools, libraries, and applications. Azure Database for PostgreSQL Flexible Server (the current deployment option with Single Server retiring) provides enhanced capabilities including zone-redundant high availability distributing replicas across availability zones for resilience against datacenter failures, customizable maintenance windows scheduling updates at convenient times, burstable compute tiers for cost optimization with intermittent workloads, and same-zone or cross-zone replication options.

Flexible Server supports PostgreSQL versions 11, 12, 13, 14, and 15 with ability to upgrade major versions. Automated backups with point-in-time restore protect data with retention up to 35 days. Encryption at rest using Azure Storage Service Encryption and in transit using TLS secure data. Azure Active Directory authentication provides centralized identity management. Virtual network integration enables private connectivity. Performance optimization features include connection pooling through PgBouncer reducing connection overhead, intelligent performance recommendations, and query performance insights. PostgreSQL extensions are supported including PostGIS for spatial data, pg_stat_statements for query statistics, timescaledb for time-series data, and many others enabling PostgreSQL's rich ecosystem.

Use Cases and Best Fit Scenarios

Azure Database for PostgreSQL suits organizations standardized on PostgreSQL seeking managed cloud hosting without operational overhead. Web applications commonly use PostgreSQL for reliability and feature richness. Geospatial applications leverage PostGIS extension for location-based services, mapping, and spatial analysis—popular in transportation, logistics, and geographic information systems. Data warehousing and analytics benefit from PostgreSQL's advanced query capabilities, materialized views, and analytical functions. Time-series applications use timescaledb extension for IoT sensor data, financial market data, or application monitoring metrics.

Developers familiar with PostgreSQL prefer managed service preserving their skills while gaining enterprise features. Applications requiring PostgreSQL-specific features like JSON/JSONB support, full-text search, array types, or range types need PostgreSQL compatibility. Organizations migrating from on-premises PostgreSQL to cloud use Azure Database for PostgreSQL maintaining compatibility with existing applications and tools. The managed service handles operational complexity enabling focus on applications. PostgreSQL's strong reputation for data integrity, ACID compliance, and standards compliance makes it popular choice for applications where data correctness is critical. Azure's managed offering brings enterprise-grade availability, security, and performance to PostgreSQL workloads.

Azure Database for MySQL

Core Features and Capabilities

Azure Database for MySQL is a fully managed PaaS offering for MySQL, the world's most popular open-source relational database particularly widespread in web applications and content management systems. Microsoft handles infrastructure management including patching, backups, and high availability. It's based on MySQL Community Edition supporting MySQL versions 5.7 and 8.0, ensuring compatibility with MySQL applications, frameworks, and development tools. Azure Database for MySQL Flexible Server provides modern deployment architecture with zone-redundant high availability, same-zone replication for cost-optimized availability, automatic failover, and burstable compute tiers optimizing costs for intermittent workloads.

Automated backups with point-in-time restore protect against data loss with retention up to 35 days. Encryption at rest and in transit secures data. Virtual network integration enables private connectivity. Read replicas enable horizontal scaling for read-heavy workloads and geographic distribution placing read-only replicas in different regions. Connection pooling through ProxySQL improves application scalability. Monitoring through Azure Monitor tracks performance metrics, slow queries, and resource utilization. Integration with popular development stacks including PHP frameworks like Laravel, content management systems like WordPress and Drupal, and e-commerce platforms simplifies application deployment. MySQL's widespread adoption means extensive tooling, libraries, and community support.

Use Cases and Best Fit Scenarios

Azure Database for MySQL excels for web applications and content management systems commonly built on LAMP (Linux, Apache, MySQL, PHP) or MEAN (MongoDB, Express, Angular, Node.js with MySQL substitution) stacks. WordPress, Joomla, Drupal, and Magento commonly use MySQL as backend database. E-commerce platforms leverage MySQL for product catalogs, customer data, and transaction processing. SaaS applications use MySQL for multi-tenant data with database-per-tenant or shared-schema approaches. PHP applications typically pair with MySQL due to extensive framework support and developer familiarity.

Organizations standardized on MySQL benefit from managed service eliminating operational overhead while preserving MySQL compatibility. Migrations from on-premises MySQL or other hosting providers to Azure Database for MySQL typically require minimal application changes. Developers familiar with MySQL maintain productivity in Azure environment. The managed nature eliminates concerns about patching, backups, high availability, and scaling, allowing focus on application development. MySQL's performance characteristics, replication capabilities, and scaling options make it suitable for wide range of workloads from small applications to large-scale web properties. Azure's integration with app services, virtual networks, and monitoring tools creates comprehensive platform for MySQL-based applications.

Azure Database for MariaDB

Overview and Capabilities

Azure Database for MariaDB is a fully managed PaaS offering for MariaDB, a community-developed fork of MySQL created by original MySQL developers. Microsoft manages infrastructure, patching, backups, and high availability. MariaDB maintains MySQL compatibility while adding performance optimizations, new storage engines, and enhanced features. Azure Database for MariaDB supports MariaDB Community Edition versions 10.2 and 10.3. Features include built-in high availability with 99.99% SLA, automatic backups with point-in-time restore, encryption at rest and in transit, intelligent performance insights, connection pooling, and Azure Monitor integration.

However, it's important to note that Azure Database for MariaDB is on a deprecation path. Microsoft has announced retirement of Azure Database for MariaDB recommending migration to Azure Database for MySQL Flexible Server for ongoing support and feature development. Existing MariaDB deployments remain supported through scheduled retirement dates, but new deployments should consider MySQL or PostgreSQL options with clearer long-term roadmaps in Azure. MariaDB applications generally migrate to MySQL with minimal changes due to compatibility. Organizations planning new deployments should evaluate Azure Database for MySQL or PostgreSQL rather than MariaDB to ensure long-term support and investment in features.

Real-World Azure Database Scenarios

Scenario 1: SaaS Application Multi-Tenant Architecture

Business Requirement: SaaS company provides project management application to thousands of small business customers, each requiring isolated database for data privacy and customization.

Azure Solution: Azure SQL Database with Elastic Pools

  • Architecture: Each customer tenant gets dedicated Azure SQL Database providing data isolation and customization. All databases deploy in elastic pool sharing compute and storage resources.
  • Cost Optimization: Elastic pool dramatically reduces costs compared to provisioning resources per database. Most tenants use applications sporadically—during business hours with varied usage across time zones. Pool resources distribute dynamically to active databases while idle databases consume minimal resources.
  • Scaling: Adding new tenants provisions new databases instantly without capacity planning per database. Pool size scales based on aggregate usage as customer base grows.
  • Management: Centralized pool management simplifies operations. Updates, monitoring, and security policies apply across all databases. Automated backups and point-in-time restore protect customer data.
  • Customer Experience: Each customer perceives dedicated environment with predictable performance. Data isolation meets privacy requirements. Geographic replication enables disaster recovery and global performance.

Outcome: Elastic pools enable profitable SaaS business model providing isolated databases per customer while controlling costs through resource sharing. Operational simplicity of managed service allows small team to support thousands of databases.

Scenario 2: Enterprise Lift-and-Shift Migration

Business Requirement: Large enterprise needs to migrate critical ERP system built on SQL Server 2016 Enterprise Edition from on-premises datacenter to Azure. Application uses SQL Agent jobs for nightly ETL, cross-database queries, and CLR assemblies.

Azure Solution: Azure SQL Managed Instance

  • Compatibility: Managed Instance provides near 100% compatibility with SQL Server Enterprise features. SQL Agent jobs, cross-database queries, and CLR assemblies work without code changes.
  • Migration Process: Azure Database Migration Service assesses on-premises database identifying any compatibility issues (minimal for Managed Instance). Data migrates using backup/restore or transactional replication minimizing downtime.
  • Networking: Managed Instance deploys in enterprise's Azure virtual network with VPN connection to on-premises network. Applications maintain existing connection strings. Gradual cutover reduces risk.
  • Management Benefits: Post-migration, Microsoft handles patching, backups, and infrastructure. Built-in high availability eliminates custom Always On setup. Geo-replication provides disaster recovery. Monitoring through Azure Monitor consolidates with other Azure resources.
  • Business Continuity: Automated backups with point-in-time restore protect against errors. Geo-replication to secondary region ensures business continuity. Recovery time objectives improve over on-premises.

Outcome: Successful migration with minimal application changes. Enterprise gains cloud benefits—automatic backups, built-in high availability, geographic replication—while preserving application compatibility and operational familiarity. Reduced datacenter footprint and transition to OpEx model.

Scenario 3: Open-Source Web Application Platform

Business Requirement: Media company operates portfolio of WordPress websites for different publications requiring reliable, scalable database platform without MySQL administration overhead.

Azure Solution: Azure Database for MySQL Flexible Server

  • Architecture: Each publication's WordPress installation connects to Azure Database for MySQL Flexible Server. Virtual network integration provides private connectivity from Azure App Service hosting WordPress.
  • High Availability: Zone-redundant deployment distributes replicas across availability zones. Automatic failover ensures minimal downtime during infrastructure issues. Read replicas handle read-heavy traffic from popular articles.
  • Performance: Burstable compute tiers optimize costs for smaller publications with variable traffic. Larger publications use dedicated compute. Connection pooling through ProxySQL improves scalability during traffic spikes.
  • Management: Automated backups with point-in-time restore protect against content errors. Maintenance windows schedule updates during low-traffic periods. Azure Monitor alerts on performance issues or resource constraints.
  • Security: Virtual network integration isolates databases from public internet. Encryption at rest and in transit protects data. Azure Active Directory authentication for administrative access. Private connectivity from content management applications.

Outcome: Reliable database platform for content publishing without MySQL administration burden. Managed service eliminates operational overhead. Automatic scaling handles traffic variability. Cost-effective burstable tiers for smaller sites and performance tiers for popular publications.

Exam Preparation Tips

Key Concepts to Master

  • Azure SQL Database: Fully managed PaaS, serverless compute, elastic pools, 99.99% SLA, automatic tuning
  • Azure SQL Managed Instance: Near 100% SQL Server compatibility, instance-level features, VNet integration, lift-and-shift migrations
  • SQL Server on Azure VMs: IaaS with full control, all SQL Server versions, maximum compatibility, customer-managed
  • Comparison: Management (Database lowest, VMs highest), compatibility (VMs highest, Database most limited)
  • Azure Database for PostgreSQL: Managed PostgreSQL, extensions like PostGIS, Flexible Server deployment
  • Azure Database for MySQL: Managed MySQL, web applications, LAMP stack, WordPress compatibility
  • Azure Database for MariaDB: Managed MariaDB, compatibility with MySQL, deprecation path to MySQL
  • Managed Service Benefits: Automated backups, high availability, reduced management, security features

Practice Questions

Sample DP-900 Exam Questions:

  1. Question: Which Azure SQL option provides the lowest management overhead?
    • A) SQL Server on Azure Virtual Machines
    • B) Azure SQL Managed Instance
    • C) Azure SQL Database
    • D) SQL Server on-premises

    Answer: C) Azure SQL Database - As a fully managed PaaS service, Azure SQL Database provides the lowest management overhead with Microsoft handling infrastructure, patching, and backups.

  2. Question: Which Azure SQL option should be used for migrating an application that requires SQL Agent and cross-database queries?
    • A) Azure SQL Database single database
    • B) Azure SQL Database elastic pool
    • C) Azure SQL Managed Instance
    • D) Azure Table Storage

    Answer: C) Azure SQL Managed Instance - Managed Instance supports instance-level features like SQL Agent and cross-database queries unavailable in Azure SQL Database.

  3. Question: What is the primary benefit of using Azure SQL Database elastic pools?
    • A) Providing complete SQL Server compatibility
    • B) Enabling operating system access
    • C) Sharing resources across multiple databases to optimize costs
    • D) Supporting older SQL Server versions

    Answer: C) Sharing resources across multiple databases to optimize costs - Elastic pools enable multiple databases to share compute and storage resources, reducing costs for multi-tenant scenarios.

  4. Question: Which deployment option provides 100% compatibility with all SQL Server versions and editions?
    • A) Azure SQL Database
    • B) Azure SQL Managed Instance
    • C) Azure Database for MySQL
    • D) SQL Server on Azure Virtual Machines

    Answer: D) SQL Server on Azure Virtual Machines - IaaS VMs support all SQL Server versions and editions with full compatibility.

  5. Question: Which Azure database service should be used for an open-source PostgreSQL application?
    • A) Azure SQL Database
    • B) Azure Cosmos DB
    • C) Azure Database for PostgreSQL
    • D) Azure SQL Managed Instance

    Answer: C) Azure Database for PostgreSQL - Azure Database for PostgreSQL is a managed service for PostgreSQL workloads.

  6. Question: What type of applications commonly use Azure Database for MySQL?
    • A) Machine learning workloads
    • B) Web applications and content management systems
    • C) Data warehouses
    • D) Real-time analytics

    Answer: B) Web applications and content management systems - MySQL is popular for web applications, WordPress, and LAMP stack applications.

  7. Question: Which Azure SQL option deploys into a customer's virtual network by default?
    • A) Azure SQL Database
    • B) Azure SQL Managed Instance
    • C) Azure Cosmos DB
    • D) Azure Table Storage

    Answer: B) Azure SQL Managed Instance - Managed Instance deploys into customer's virtual network providing network isolation and private IP addresses.

  8. Question: What is a key benefit of Azure managed database services compared to IaaS?
    • A) Lower cost in all scenarios
    • B) Support for older database versions
    • C) Automated patching and backups
    • D) Access to operating system

    Answer: C) Automated patching and backups - Managed services (PaaS) provide automated patching, backups, and high availability reducing operational overhead.

DP-900 Success Tip: Remember Azure SQL Database is fully managed PaaS with lowest management overhead suitable for new cloud applications; Azure SQL Managed Instance provides near-complete SQL Server compatibility for lift-and-shift migrations with instance-level features; SQL Server on Azure VMs provides maximum compatibility and control with most management responsibility. For open-source, Azure Database for PostgreSQL suits PostgreSQL applications with PostGIS for geospatial, Azure Database for MySQL serves web applications and CMS, and Azure Database for MariaDB is deprecated with migration path to MySQL. Choose based on management overhead, compatibility needs, and control requirements.

Hands-On Practice Lab

Lab Objective

Explore Azure relational database services by creating and comparing Azure SQL Database, examining Managed Instance and VM options, and working with open-source database services through Azure Portal and basic operations.

Lab Activities

Activity 1: Create Azure SQL Database

  • Navigate Azure Portal: Go to "Create a resource" and select "SQL Database"
  • Configure database: Select subscription, create new resource group, specify database name, create new server with admin credentials
  • Choose compute: Select service tier (Basic or General Purpose for testing), choose serverless or provisioned compute
  • Configure options: Review backup retention, geo-replication options, security settings
  • Create and connect: Deploy database, use Query Editor in portal to connect and run simple queries
  • Explore features: Review automatic backup settings, examine performance monitoring, check security recommendations

Activity 2: Explore Elastic Pools

  • Understand concept: Review elastic pool documentation explaining resource sharing across databases
  • Navigate portal: Go to SQL elastic pools and examine configuration options
  • Review pricing: Compare costs of individual databases vs elastic pool for multi-database scenarios
  • Identify use cases: Document scenarios where elastic pools provide cost benefits (SaaS, multi-tenant)
  • Understand pool metrics: Review how resources distribute across pool databases

Activity 3: Compare Azure SQL Options

  • Review SQL Database: Document features, management model, compatibility, use cases
  • Review Managed Instance: Navigate to Managed Instance creation (don't deploy due to cost), examine configuration options, note instance-level features, review networking requirements
  • Review SQL VMs: Examine SQL Server VM images in marketplace, review VM sizes and SQL Server editions, understand IaaS management model
  • Create comparison: Build table comparing management, compatibility, networking, features across three options
  • Match scenarios: For sample scenarios (new app, migration, legacy system), identify appropriate Azure SQL option with justification

Activity 4: Explore Azure Database for PostgreSQL

  • Navigate portal: Go to "Create a resource" and select "Azure Database for PostgreSQL"
  • Review deployment: Examine Flexible Server options including compute tiers, storage, high availability
  • Understand features: Review PostgreSQL version support, extensions availability (PostGIS, timescaledb), backup options
  • Compare with SQL: Note differences between PostgreSQL and SQL Server offerings
  • Identify use cases: Document scenarios favoring PostgreSQL (geospatial, specific application requirements, developer preference)

Activity 5: Explore Azure Database for MySQL

  • Navigate portal: Go to "Create a resource" and select "Azure Database for MySQL"
  • Review deployment: Examine Flexible Server configuration options
  • Understand features: Review MySQL version support, replication, connection pooling
  • Identify use cases: Document MySQL use cases (web applications, WordPress, LAMP stack)
  • Compare open-source options: Create table comparing PostgreSQL and MySQL features, use cases, and capabilities

Activity 6: Decision Framework

  • Create decision tree: Build flowchart helping choose appropriate Azure database service
  • Consider factors: Include management overhead preferences, compatibility requirements, database system preference (SQL Server, PostgreSQL, MySQL)
  • Document criteria: List criteria for each option—new app → SQL Database, SQL Server migration → Managed Instance, maximum control → VMs, PostgreSQL app → PostgreSQL service
  • Practice scenarios: Given business requirements, select appropriate Azure database service with justification
  • Understand tradeoffs: Document tradeoffs between options (management vs control, compatibility vs simplicity)

Lab Outcomes

After completing this lab, you'll understand Azure relational database services including Azure SQL Database for managed PaaS databases, Azure SQL Managed Instance for high SQL Server compatibility, and SQL Server VMs for maximum control. You'll understand elastic pools for multi-tenant scenarios and serverless compute for variable workloads. You'll know open-source options including Azure Database for PostgreSQL and MySQL. You'll be able to select appropriate services based on requirements considering management overhead, compatibility, control, and use cases. This knowledge demonstrates Azure relational database understanding tested in DP-900 exam.

Frequently Asked Questions

What is Azure SQL Database and when should it be used?

Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database based on the latest stable version of Microsoft SQL Server Database Engine. Microsoft handles infrastructure management including patching, backups, high availability, and monitoring automatically. Key features include serverless compute automatically pausing during inactive periods and resuming on demand; elastic pools sharing resources across multiple databases; intelligent performance optimization through automatic tuning and indexing recommendations; built-in high availability with 99.99% SLA; point-in-time restore up to 35 days; geo-replication for disaster recovery; and advanced security including encryption, threat detection, and auditing. Azure SQL Database is ideal for modern cloud applications requiring managed databases without infrastructure overhead, SaaS applications needing multi-tenant databases with elastic pools, applications requiring automatic scaling based on demand, development and testing environments needing quick provisioning, and scenarios prioritizing reduced management over complete SQL Server feature compatibility. Single databases support predictable workloads while elastic pools optimize costs for multiple databases with varying usage patterns. Hyperscale tier enables databases up to 100TB with fast backups and restores. The managed nature makes Azure SQL Database excellent choice for new cloud-native applications where operational simplicity matters more than full SQL Server compatibility.

What is Azure SQL Managed Instance and how does it differ from Azure SQL Database?

Azure SQL Managed Instance provides near 100% compatibility with SQL Server Enterprise Edition in a fully managed PaaS offering, designed for lift-and-shift migrations of on-premises SQL Server workloads. Unlike Azure SQL Database focusing on individual databases, Managed Instance provides complete SQL Server instance including instance-level features like SQL Agent for scheduled jobs, CLR integration for .NET code, cross-database queries, Service Broker for messaging, linked servers, and database mail. This enables migrating existing applications with minimal code changes. Managed Instance deploys into customer's virtual network enabling private IP addresses, integration with on-premises networks through VPN or ExpressRoute, and network isolation. It supports larger database sizes up to 16TB, native virtual network integration, and instance-level collation. Like Azure SQL Database, Microsoft manages patching, backups, and high availability, but Managed Instance provides more control over instance configurations. Choose Managed Instance for migrating existing on-premises SQL Server applications requiring instance-level features, applications needing cross-database queries or SQL Agent jobs, applications requiring private IP addresses and virtual network integration, or scenarios where minimal application changes during migration are critical. The compatibility makes Managed Instance ideal bridge from on-premises SQL Server to cloud, preserving familiar management experiences while gaining cloud benefits like automated backups, geo-replication, and elastic scaling within instance limits.

What is SQL Server on Azure Virtual Machines and when should it be used?

SQL Server on Azure Virtual Machines provides Infrastructure-as-a-Service (IaaS) deployment running SQL Server on Windows or Linux virtual machines in Azure. Unlike PaaS options (Azure SQL Database and Managed Instance) where Microsoft manages infrastructure, IaaS gives complete control over operating system, SQL Server installation, and configurations. You manage patching, backups, high availability, and monitoring similarly to on-premises deployments but benefit from Azure infrastructure including rapid provisioning, flexible scaling, and pay-as-you-go pricing. SQL Server on Azure VMs supports all SQL Server versions and editions including older versions for legacy application compatibility, custom SQL Server configurations, operating system access for third-party software installation, and full administrative control. It's ideal for applications requiring specific SQL Server versions not available in PaaS options, applications needing operating system access or custom configurations, lift-and-shift migrations where rehosting without changes is priority, applications requiring features unsupported in Managed Instance like replication or distributed transactions across instances, and scenarios where complete control over database environment outweighs managed service benefits. While requiring more management than PaaS options, SQL Server on Azure VMs provides maximum flexibility and compatibility. Azure provides pre-configured SQL Server VM images simplifying deployment. Hybrid use benefit allows using existing SQL Server licenses reducing costs. SQL Server VMs suit organizations preferring infrastructure control or requiring capabilities beyond PaaS offerings.

How do you choose between Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs?

Choosing between Azure SQL family options depends on requirements for management overhead, compatibility, control, and features. Choose Azure SQL Database for new cloud-native applications, modern development with minimal database administration, serverless compute for variable workloads, elastic pools for SaaS multi-tenant scenarios, and when database-level features suffice without instance-level capabilities. SQL Database offers lowest management overhead with automatic patching, backups, and high availability, but has compatibility limitations with certain SQL Server features. Choose Azure SQL Managed Instance for migrating existing on-premises SQL Server applications with minimal changes, applications requiring instance-level features like SQL Agent or cross-database queries, applications needing private virtual network integration, scenarios requiring near-complete SQL Server compatibility, and when balancing managed service benefits with enterprise features. Managed Instance bridges on-premises and cloud providing familiar SQL Server experience in managed service. Choose SQL Server on Azure Virtual Machines for maximum SQL Server compatibility including all versions and editions, applications requiring operating system access or custom configurations, scenarios needing complete administrative control, legacy applications requiring older SQL Server versions, or when specific features unavailable in PaaS options are critical. SQL Server VMs provide most flexibility with most management responsibility. Consider management overhead (SQL Database lowest, VMs highest), compatibility requirements (VMs highest, SQL Database most limited), cost structure (PaaS predictable, IaaS depends on management), and feature needs when deciding. Many organizations use multiple options—SQL Database for new applications, Managed Instance for migrated enterprise workloads, VMs for specialized scenarios.

What is Azure Database for PostgreSQL?

Azure Database for PostgreSQL is a fully managed PaaS offering for PostgreSQL, one of the most popular open-source relational databases. Microsoft handles infrastructure including patching, backups, high availability, and monitoring. It's based on community PostgreSQL supporting standard PostgreSQL features, extensions, and tools, ensuring compatibility with existing PostgreSQL applications and skills. Azure Database for PostgreSQL offers two deployment options: Flexible Server and Single Server (retiring). Flexible Server provides enhanced control over database management, zone-redundant high availability, customizable maintenance windows, burstable compute tiers for cost optimization, and support for PostgreSQL versions 11 and above with major version upgrades. Features include point-in-time restore, automated backups, encryption at rest and in transit, Azure Active Directory authentication, and virtual network integration. Extensions like PostGIS for spatial data, pg_partman for partitioning, and timescaledb for time-series data are supported. Azure Database for PostgreSQL is ideal for organizations using PostgreSQL preferring managed service over self-managed deployments, applications built with PostgreSQL requiring Azure integration, developers familiar with PostgreSQL seeking cloud hosting, and scenarios needing enterprise-grade availability and security for open-source databases. It's popular for web applications, geospatial applications using PostGIS, and applications requiring PostgreSQL-specific features. The managed nature eliminates operational overhead while preserving PostgreSQL compatibility and extensions, making it attractive choice for PostgreSQL workloads in Azure.

What is Azure Database for MySQL?

Azure Database for MySQL is a fully managed PaaS offering for MySQL, another extremely popular open-source relational database particularly in web applications. Like other Azure database services, Microsoft manages patching, backups, high availability, and infrastructure. It's based on community MySQL editions supporting MySQL versions 5.7 and 8.0, ensuring compatibility with existing MySQL applications, frameworks, and tools. Azure Database for MySQL offers Flexible Server deployment providing customizable configurations, zone-redundant high availability, same-zone and cross-zone data redundancy, burstable compute options, and customizable maintenance windows. Features include automatic backups with point-in-time restore up to 35 days, encryption at rest and in transit, virtual network integration, replication for read scale-out, and monitoring through Azure Monitor. It integrates with popular development frameworks like WordPress, Drupal, and Laravel commonly using MySQL. Azure Database for MySQL is ideal for organizations standardized on MySQL seeking managed cloud service, web applications and content management systems using MySQL, PHP applications commonly paired with MySQL, migration from on-premises MySQL to cloud, and scenarios needing MySQL compatibility with enterprise availability and security. It's particularly popular for LAMP (Linux, Apache, MySQL, PHP) stack applications and content management systems. The managed service eliminates operational overhead of self-managing MySQL while preserving compatibility with MySQL ecosystem, tools, and applications. For organizations requiring MySQL in Azure, the managed offering provides reliability, scalability, and security without infrastructure management burden.

What is Azure Database for MariaDB?

Azure Database for MariaDB is a fully managed PaaS offering for MariaDB, an open-source relational database created by original MySQL developers as a community-developed fork of MySQL. Microsoft manages infrastructure, patching, backups, and high availability. MariaDB maintains MySQL compatibility while adding performance improvements, additional storage engines, and enhanced features. Azure Database for MariaDB is based on MariaDB Community Edition supporting versions 10.2 and 10.3, ensuring compatibility with MariaDB and generally with MySQL applications. Features include built-in high availability with 99.99% SLA, automatic backups with point-in-time restore, encryption at rest and in transit, intelligent performance insights, connection pooling through ProxySQL, and monitoring through Azure Monitor. It supports replication for read scale-out and disaster recovery. Azure Database for MariaDB is ideal for organizations standardized on MariaDB seeking managed cloud hosting, applications specifically developed with MariaDB, migrations from on-premises MariaDB to cloud, and MySQL users seeking MariaDB-specific enhancements in managed service. It's popular among organizations preferring community-governed open-source databases and existing MariaDB users moving to cloud. Note that Azure Database for MariaDB is on deprecation path with Microsoft recommending migration to Azure Database for MySQL Flexible Server for new deployments. Existing MariaDB workloads remain supported but future investment focuses on PostgreSQL and MySQL flexible servers. Organizations planning new deployments should consider MySQL or PostgreSQL options with clearer long-term Azure roadmaps.

What are the key benefits of using Azure managed database services?

Azure managed database services (PaaS offerings) provide significant benefits over self-managed IaaS or on-premises deployments. Reduced management overhead eliminates operational tasks like patching, backups, and infrastructure maintenance, allowing focus on applications rather than database administration. High availability is built-in with 99.99% or higher SLAs through redundant infrastructure, automatic failover, and zone redundancy options. Automated backups with point-in-time restore protect against data loss without manual backup management. Intelligent performance includes automatic tuning, indexing recommendations, and query performance insights optimizing databases without manual intervention. Security features like encryption at rest and in transit, advanced threat protection, auditing, and Azure Active Directory integration are built-in. Scalability enables easy vertical scaling of compute and storage, horizontal scaling through read replicas, and elastic pools for optimizing costs across multiple databases. Global distribution through geo-replication enables disaster recovery and reducing latency for globally distributed users. Monitoring and alerting through Azure Monitor provides insights into performance, resource utilization, and health. Cost optimization through serverless compute, pause/resume capabilities, and reserved capacity pricing. Integration with Azure ecosystem including virtual networks, Azure AD, Azure Key Vault, and Azure DevOps. Managed services reduce total cost of ownership through eliminated infrastructure management, reduced staffing needs, and consumption-based pricing. These benefits make Azure managed database services attractive for organizations wanting reliable, scalable, secure databases without operational overhead, enabling focus on applications and business value rather than infrastructure management.

Share:

Written by Joe De Coppi - Last Updated November 14, 2025

Previous: Objective 2.1 Describe Relational Concepts

Next: Objective 3.1 Describe Capabilities Azure Storage