Microsoft AZ-104: Azure Databases and Data Services — Study Guide

By Laurynas Brazys · 6 August 2026 · #microsoft #az-104 #certification #exam-prep #study-guide

Part of the Microsoft Azure Administrator Associate AZ-104 — Study Guide. Practice with verified answers in the Microsoft exam hub, or take timed practice tests on ExamRoll.io.

Overview

Azure’s database and data services span managed relational engines, globally distributed NoSQL, in-memory caching, large-scale analytics, and integration/orchestration. As an administrator, you must understand purchase models, service tiers, network and security topology, backup/DR semantics, and how to combine services for performance, cost, and resilience. This section focuses on operational choices and platform features you configure day-to-day: provisioning models (DTU vs vCore), elastic pools, backup and long-term retention, geo-replication and failover, VNet-injected managed instances, Cosmos DB distribution and consistency, open-source relational HA/read replicas, Synapse engines, and Data Factory runtimes.

Azure Relational Databases (SQL Database, Managed Instance, MySQL/PostgreSQL)

Azure SQL Database offers two purchasing models. The DTU model bundles CPU, memory, and IOPS into Database Transaction Units with Basic, Standard, and Premium tiers; it is simple but opaque, good for steady, predictable workloads and legacy sizing. The vCore model exposes CPU generation/count and memory, paired with storage and IOPS controls. vCore enables sizing transparency, Azure Hybrid Benefit, and Reserved Capacity discounts. Within vCore, service tiers map to workload and availability patterns: General Purpose uses remote Premium SSD or Azure Premium storage with a standard availability architecture; Business Critical places compute and storage on local SSD with multiple replicas, low latency, and integrated read scale-out; Hyperscale decouples compute and storage with page servers for near-instant scale and very large databases. For single databases, the serverless compute tier (vCore) elastically scales CPU and can auto-pause to cut idle cost.

Elastic pools share compute among multiple databases to absorb bursty, out-of-phase workloads at lower aggregate cost. Pools are available in DTU (eDTU) and vCore variants. You set per-database min/max caps to contain noisy neighbors and a pool max to control spend. Oversubscription is acceptable when bursts are short and uncorrelated. Pool sizing hinges on summed average consumption plus headroom for concurrency; monitoring per-database and pool metrics is critical to maintain SLOs.

Backups are automatic. Azure SQL maintains full, differential, and transaction log backups with point-in-time restore (PITR) to any second within the retention window (typically 7–35 days depending on tier and storage configuration). Long-term retention (LTR) persists weekly full backups for years in RA-GRS storage; you can restore an LTR backup as a new database on any server in the same subscription and region set, and cross-region restore is available if geo-redundant backup storage is enabled. Restores create a new database; they do not overwrite in place.

Geo-replication options include active geo-replication for single databases and pools (up to four readable secondaries with asynchronous replication) and auto-failover groups at the logical server scope. Failover groups bundle multiple databases (or an entire server) with geo-DR, a read-write listener endpoint, a read-only endpoint for offloading reads, health-based automatic failover, and DNS-based redirection. Business Critical also provides read scale-out via a local readable replica, enabling immediate read workload offload without cross-region complexity.

Azure SQL Managed Instance (MI) delivers near 100% SQL Server engine compatibility, including SQL Agent, cross-database queries, CLR, linked servers, Service Broker, and native backup/restore of .bak files from Azure Blob Storage. MI is VNet-injected: you deploy it into a dedicated, delegated subnet with private IPs and NSG/UDR controls; plan subnet size and address space up front because resizing subnets later is complex. Migration paths include the Azure Database Migration Service (online/offline cutovers), native backup/restore to URL into MI, and transactional replication from on-premises SQL Server to MI. Choose MI when you require surface-area parity or instance-scoped features that single databases do not expose.

Azure Database for MySQL and Azure Database for PostgreSQL (Flexible Server) provide managed OSS engines with control over maintenance windows, stop/start for cost savings, burstable and general-purpose compute, storage auto-grow, and VNet integration. Flexible Server offers high availability with synchronous replication; you can choose zone-redundant HA across Availability Zones for stronger fault isolation or same-zone HA for lower write latency. Read replicas are available for scale-out reads and can be provisioned within or across regions; they use asynchronous replication and are ideal for analytics, reporting, or read-heavy microservices. Promote a replica for failover or regional expansion when needed, acknowledging potential replication lag.

Distributed Data and Caching (Cosmos DB and Azure Cache for Redis)

Azure Cosmos DB is a globally distributed, multi-model database offering APIs for Core (SQL), MongoDB, Cassandra, Gremlin (graph), and Table. The choice of API determines client driver compatibility and data model semantics; operationally, you manage throughput (provisioned RUs or autoscale) and partitions regardless of API. Data is horizontally partitioned by a partition key that must exhibit high cardinality and even access distribution to avoid hot partitions; avoid monotonically increasing keys and consider hierarchical partition keys where compound access patterns exist. Cross-partition queries are supported but consume more RUs; co-locate related data by partition key when possible.

Consistency levels are tunable per account, database, or request: Strong guarantees linearizability; Bounded Staleness caps staleness by time or version; Session (default) provides read-your-writes for a session; Consistent Prefix guarantees order without full consistency; Eventual maximizes availability and performance. For multi-region writes, pick an appropriate conflict resolution policy (LastWriterWins or custom via stored procedures) and define failover priorities. Global distribution adds regions with a click; the service handles replication, failover, and latency-optimized routing with SLAs on throughput, latency, availability, and consistency.

Azure Cache for Redis provides sub-millisecond latency, backed by Redis. Tiers progress in capability: Basic (single node, dev/test), Standard (replicated two-node primary/replica with SLA), Premium (bigger sizes, clustering, persistence, VNet injection, geo-replication, and Redis modules like Bloom), Enterprise and Enterprise Flash (Redis Enterprise-based with advanced clustering, active geo-replication for multi-primary writes, and Flash-backed larger caches). Eviction policies define behavior under memory pressure: noeviction (errors on write), allkeys-lru/lfu/random (consider all keys), and volatile-lru/lfu/ttl/random (consider only keys with TTL). For session caching, use Premium or above for persistence if you cannot afford session loss, enable key TTLs to bound growth, and consider clustering for throughput and scale. Place the cache in the same region and virtual network as app servers to minimize latency; use Managed Identity or access keys and enforce network isolation via Private Link or VNet injection.

Analytics and Integration (Synapse Analytics and Data Factory)

Azure Synapse Analytics unifies data warehousing, big data, and data integration. The dedicated SQL pool (formerly SQL DW) is an MPP engine with hash/round-robin distributions, replicated tables, and result-set caching. You scale compute up or down to meet SLA windows and can pause to pay only for storage. Workload isolation can be achieved with workload groups and importance settings to protect critical queries. The serverless SQL pool provides on-demand T-SQL over data in Azure Data Lake Storage Gen2 without provisioning; you pay per TB scanned and can externalize schemas using views for semantic layers. Spark pools bring Apache Spark to Synapse with autoscale and on-demand clusters, enabling notebooks, Delta Lake, and machine learning with integrated security and data lineage; you can share lakehouse data between Spark and SQL engines.

Azure Data Factory (ADF) orchestrates data movement and transformation. Pipelines coordinate activities such as Copy, Data Flow (Spark-based mapping flows), and external compute (Databricks, Synapse, Functions). Datasets define the shape and location of data, while linked services encapsulate connection details (auth, endpoints) to sources/targets. Integration runtimes (IRs) provide the compute and network plane: Azure IR for cloud-native movement and transformation, Self-hosted IR for on-premises or private network sources over outbound HTTPS, and Azure-SSIS IR for lift-and-shift of SSIS packages. Triggers (schedule, tumbling window, event-based) enable repeatable orchestration; managed virtual network and private endpoints can be enabled for data exfiltration protection and compliant connectivity. Parameterization and Key Vault integration support reusable, secure patterns for environment promotion (dev/test/prod).

Business Continuity, Geo-Features, and Elastic Pools

Backup and restore strategies differ by service but share key themes: automate, test restores regularly, and separate PITR (operational errors) from LTR (compliance). In Azure SQL, use PITR for accidental drops or bad deployments; store LTR weekly fulls in RA-GRS for regulatory retention and cross-region disaster recovery restores. For MySQL/PostgreSQL Flexible Server, enable automated backups with geo-redundant storage where supported, set retention per policy, and validate point-in-time restores to alternate servers. Cosmos DB accounts with multiple regions enable automatic failover; combine with multi-region writes when RPO must be zero and the app can resolve conflicts deterministically.

Geo-replication and auto-failover groups in Azure SQL provide DR and read offload. Use active geo-replication for a single database/pool when you want to explicitly manage secondaries; use auto-failover groups to group many databases and get DNS-based listeners plus automatic failover. Where low-latency reads matter but DR is not the goal, use Business Critical read scale-out or Hyperscale named replicas to keep analytics and reporting off the primary. Monitor replication lag and failover health signals, and test failover drills to validate RTO/RPO.

Elastic pools are cost-optimization levers for multi-tenant SaaS and fleets of small databases. In DTU-based pools, allocate eDTUs with per-database caps; in vCore pools, allocate vCores, memory, and IO throughput with per-database max vCores and IO governance. Right-size by measuring per-database 95th percentile usage and aligning pool capacity to concurrency patterns; raise per-database caps for tenants with higher SLOs and consider splitting pools by workload class (e.g., heavy vs light tenants). Use alerts on pool and per-database limits to detect saturation early. When a handful of databases consistently hit max caps, move them to dedicated compute or a separate pool to maintain predictability.

Practical Problem Scenario

Starbucks needs to modernize its global loyalty platform to meet peak traffic during promotions, reduce operational overhead, and support analytics without disrupting store operations worldwide.

Partition the operational data store:

Choose Azure Cosmos DB (Core SQL API) for customer interactions and rewards events to achieve global distribution with low latency. Configure multi-region writes in regions near major customer populations and set Session consistency to balance read-your-writes with performance. Select a high-cardinality partition key such as customerId or a composite hierarchical key (customerId, eventMonth) to distribute throughput and support common query patterns.

Implement transactional account and catalog data:

Deploy Azure SQL Managed Instance for account balances, redemptions, and SKU/catalog because near 100% SQL Server compatibility is needed for existing stored procedures and cross-database logic. Place MI in a dedicated, delegated subnet with NSGs and route tables as required by VNet injection, enabling private access from app subnets and ExpressRoute.

Provide global read scale and DR for relational workloads:

For new microservices using Azure SQL Database, use vCore Business Critical for low latency and read scale-out. Create an auto-failover group to a paired region with read-only listener endpoints for localized reads and automatic failover to meet DR objectives.

Add low-latency session management:

Deploy Azure Cache for Redis Premium with clustering and data persistence for web and mobile session tokens. Set an allkeys-lfu eviction policy to keep frequently accessed sessions resident. Integrate the cache within the same VNet and region as the app tier to minimize latency.

Orchestrate data movement and build analytics:

Use Azure Data Factory to copy operational data (Cosmos DB change feed and SQL MI) into Azure Data Lake Storage Gen2. Employ a Managed VNet IR with private endpoints to prevent data exfiltration. Parameterize pipelines and use tumbling window triggers to guarantee ordered processing.

Enable enterprise analytics with elastic cost control:

In Azure Synapse Analytics, use serverless SQL pool for ad hoc exploration over parquet data and a dedicated SQL pool for curated, high-concurrency BI models with predictable performance. Create Spark pools for feature engineering on loyalty behavior and write Delta tables in the lake for interoperability between Spark and SQL.

Governance, backups, and retention:

Configure Cosmos DB automatic failover priorities and monitor conflicts using LastWriterWins with a timestamp field. For Azure SQL Database and MI, verify PITR windows and enable LTR to meet compliance retention. For Flexible Server instances supporting ancillary OSS services (e.g., regional store telemetry in PostgreSQL), enable zone-redundant HA and configure read replicas for reporting.

Why these services: Cosmos DB’s global distribution and tunable consistency address worldwide latency-sensitive interactions; MI preserves complex SQL Server features while providing managed operations; Business Critical databases supply local read scale without cross-region latency; Redis ensures sub-millisecond session access during traffic spikes; ADF provides secure, governed movement from private networks; Synapse mixes on-demand and provisioned analytics for cost-effective, scalable insights. This composition meets performance SLOs during promotions, reduces admin toil through managed PaaS, and enforces clear RTO/RPO and compliance boundaries.

← Azure App Service and PaaS Compute · All domains · Azure Monitor →

Practice these questions → · Timed practice on ExamRoll.io →