Skip to main content
FluentFlash

Azure Administrator Databases: Complete Study Guide

·

Azure Administrator certification tests your database management skills across multiple platforms. You'll encounter Azure SQL Database, Azure Cosmos DB, and Azure Database for MySQL/PostgreSQL on the AZ-104 exam.

Databases represent a critical component, requiring expertise in deployment, configuration, management, and security. This guide breaks down essential concepts into manageable study units.

Key topics include backup operations, performance tuning, security configurations, and selecting the right solution for different workloads. Understanding when to use SQL Database versus Cosmos DB saves time during the exam.

Flashcards help you retain complex architecture details through active recall. You'll test yourself repeatedly until these concepts become second nature.

Azure administrator databases - study with AI flashcards and spaced repetition

Azure SQL Database Fundamentals and Deployment

Azure SQL Database is a fully managed relational database service. You don't manage servers, patches, or backups manually. As an Azure Administrator, you must understand the three main deployment options.

Deployment Options and Service Tiers

Single databases suit independent applications with predictable resource needs. Elastic pools optimize costs when multiple databases have variable workloads. Managed instances offer near-complete SQL Server compatibility, making migrations easier.

Each option supports different service tiers:

  • Single databases: Basic, Standard, Premium
  • Managed instances: General Purpose, Business Critical

Purchasing Models and Performance

Choose between DTU-based or vCore-based purchasing. DTU (Database Transaction Unit) bundles compute, memory, and I/O. vCore lets you select exact compute and storage separately.

Automatic backups occur with geo-redundancy by default. You can restore to any point within 35 days (standard tier) using point-in-time restore (PITR).

Security and Connectivity

Configure firewall rules to control IP access. Use virtual network endpoints for private connectivity. Azure AD integration enables single sign-on and reduces password management overhead.

Authentication methods include SQL authentication (username/password) and Azure AD. The exam tests your ability to implement both for different scenarios.

Scaling and Performance Recommendations

Scale databases up or down with minimal downtime. Built-in intelligence provides performance recommendations, threat detection, and vulnerability assessments.

Understanding the relationship between performance levels and costs ensures you select appropriate tiers for different workloads.

Azure Cosmos DB and NoSQL Database Solutions

Azure Cosmos DB is a globally distributed, multi-model database. It supports documents, key-value pairs, graphs, and column-family data formats. Knowing when to choose Cosmos DB over relational databases is crucial for exam success.

Global Distribution and Latency Guarantees

Cosmos DB guarantees single-digit millisecond latency at the 99th percentile worldwide. Data replicates automatically across regions you select. Instant, automatic scalability handles traffic spikes without manual intervention.

The platform operates on a consumption model where you provision throughput in Request Units (RUs). One RU represents the resources needed to read one 1-KB item.

Consistency Models

Understand five consistency levels and their trade-offs:

  • Strong: highest consistency, lowest performance
  • Bounded Staleness: consistent within time window
  • Session: consistent within single session
  • Consistent Prefix: ordered consistency
  • Eventual: highest performance, lowest consistency

Chosing the right consistency level balances performance against data accuracy for your application.

Partitioning and Query Optimization

Partition keys determine how data distributes across physical partitions. Poor selection creates hot partitions and uneven throughput distribution. Exam questions test your ability to identify effective partition keys.

Indexing strategies significantly impact query performance and RU consumption. Configure Time-to-Live (TTL) for automatic data expiration.

Advanced Features

Enable change feed for real-time data processing pipelines. Configure geo-replication for disaster recovery. Implement multi-region write capabilities for applications needing zero downtime during regional outages.

The exam tests your ability to migrate data using Azure Data Factory and optimize costs by analyzing consumption patterns.

Database Backup, Recovery, and Business Continuity

Backup and recovery strategies form a critical exam domain. Understand backup types, retention periods, and recovery procedures to design robust business continuity solutions.

Automatic Backup Types and Frequency

Azure SQL Database automatically maintains three backup types:

  • Full backups: weekly
  • Differential backups: daily
  • Transaction log backups: every 5-10 minutes

These backups enable restoration to any second within your retention window. Default retention is 7 days. Extend to 35 days for standard tier or longer using long-term policies.

Point-in-Time Restore (PITR)

PITR recovers your database to any specific moment within retention. The process creates a new database rather than overwriting the original. This preserves your current database while letting you access historical states.

Recovery time depends on database size and restoration point. The exam tests PITR execution via Azure Portal, PowerShell, or CLI.

Geo-Redundancy and Disaster Recovery

Geo-redundant backup storage automatically replicates backups to a paired region. This protects against regional disasters. Long-term backup retention stores backups for up to 10 years, meeting compliance requirements.

Active geo-replication creates readable secondary replicas in different regions. Failover groups automate failover, directing traffic to secondaries with minimal downtime.

Cosmos DB Backup Strategy

Cosmos DB provides continuous backup with point-in-time restore to any timestamp within 30 days. Custom retention periods extend this window based on your requirements.

Testing recovery procedures regularly identifies issues before actual disasters occur. Understanding restore costs and time requirements ensures effective disaster planning.

Database Security, Access Control, and Compliance

Security is paramount in Azure database administration. Implement multiple layers of protection including authentication, encryption, network isolation, and auditing.

Authentication and Access Control

Azure SQL Database supports SQL authentication (username/password) and Azure AD authentication. Azure AD is preferred for enterprise environments. Add multi-factor authentication (MFA) for stronger security.

Row-level security (RLS) restricts data access based on user identity. This is essential for multi-tenant applications where different users need different data visibility.

Encryption Strategies

Transparent Data Encryption (TDE) encrypts data at rest by default. Column-level encryption protects sensitive columns. Always Encrypted provides client-side encryption where the database never accesses unencrypted values.

Choose encryption methods based on performance requirements and compliance obligations.

Network Isolation and Threat Protection

Firewall rules control IP-based access. Virtual network (VNet) service endpoints restrict database access to specific virtual networks. Private endpoints provide even stronger network isolation.

Threat Detection alerts you to suspicious activities like SQL injection attempts. Advanced Threat Protection and Advanced Data Security features provide vulnerability assessments and recommendations.

Audit and Compliance

Audit logging tracks database activities for compliance requirements. Azure Cosmos DB supports role-based access control (RBAC) and resource-specific consent (RSC) for fine-grained permissions.

The exam tests your ability to configure authentication, implement encryption, set firewall rules, and create audit policies matching organizational requirements.

Performance Tuning, Monitoring, and Query Optimization

Database performance optimization significantly impacts application speed and user satisfaction. Master monitoring tools, query analysis, and tuning techniques.

Query Analysis and Execution Plans

Query Performance Insight reveals slow-running queries and resource consumption. Execution plans show how SQL Server retrieves data, identifying missing indexes or inefficient joins.

Index selection balances query performance gains against maintenance overhead. The exam tests understanding of:

  • Clustered indexes (organizing table data)
  • Non-clustered indexes (separate structures pointing to data)

Statistics and Automatic Tuning

Statistics maintenance ensures the query optimizer makes informed decisions. Azure SQL Database includes automatic tuning recommending index creation or removal based on workload analysis.

This feature reduces manual tuning work and improves performance automatically.

Key Metrics and Alerting

Monitor these critical metrics:

  • DTU percentage (overall database usage)
  • CPU usage
  • Memory consumption
  • I/O percentages

Set up alerts for threshold violations enabling proactive issue detection. Azure SQL Database Advisor recommends scaling up when utilization consistently exceeds thresholds.

Connection and Query Management

Connection pooling reduces overhead of establishing new connections. Query timeouts prevent runaway queries from consuming resources. Elastic jobs enable parallel execution of maintenance tasks across multiple databases.

Cosmos DB Performance Optimization

Monitor RU consumption to identify queries needing optimization. Partitioning strategies and indexing paths significantly impact performance. Test different consistency levels to find optimal settings balancing performance and cost.

Understanding trade-offs between consistency, throughput, and cost helps design efficient solutions. The exam tests your ability to analyze metrics, identify bottlenecks, and use Azure tools for monitoring.

Start Studying Azure Administrator Databases

Master Azure database services with interactive flashcards covering SQL Database, Cosmos DB, backup strategies, security configurations, and performance tuning. Build confidence for your AZ-104 certification exam through active recall and spaced repetition learning.

Create Free Flashcards

Frequently Asked Questions

What's the difference between Azure SQL Database and Azure SQL Managed Instance?

Azure SQL Database is a fully managed platform-as-a-service (PaaS) offering. You choose single databases or elastic pools. Microsoft handles all patching and updates automatically.

Azure SQL Managed Instance provides near 100% SQL Server compatibility. It supports features like SQL Agent jobs, Service Broker, and linked servers that single databases don't offer. Managed Instance operates within a virtual network, providing network isolation.

Use single databases for new cloud-native applications with predictable needs. Choose Managed Instance when you need SQL Server feature parity or simplified migration from on-premises systems.

Managed Instance typically costs more but reduces migration effort significantly. Understanding these differences helps you recommend appropriate solutions during exam scenarios.

How do I choose between Azure SQL Database and Azure Cosmos DB for my application?

Choose Azure SQL Database for structured, relational data requiring ACID transactions and complex queries. SQL Database suits applications with well-defined schemas, complex joins, and strong consistency needs.

Choose Azure Cosmos DB for unstructured or semi-structured data with rapidly evolving schemas. Cosmos DB excels at massive scale, global distribution, and eventual consistency acceptance. Use it for IoT applications, real-time analytics, and globally distributed content.

Consider these factors when deciding:

  • Data structure (structured vs. unstructured)
  • Query complexity (simple key lookups vs. complex joins)
  • Consistency requirements (strong vs. eventual)
  • Scale expectations (regional vs. global)
  • Geographic distribution needs

The exam tests your ability to analyze requirements and recommend appropriate services. SQL Database works better for enterprise applications, financial systems, and complex reporting scenarios.

What is a Request Unit (RU) in Azure Cosmos DB and how does it affect costs?

Request Units (RUs) measure throughput consumption in Azure Cosmos DB. One RU represents the resources needed to read one 1-KB item. Complex operations consume more RUs.

Operation costs vary by type:

  • Point reads (by ID): approximately 1 RU
  • Writes: 5-10 RUs
  • Stored procedures: 5-100 RUs
  • Queries: 10-1000+ RUs depending on complexity

Provisioned throughput means you reserve RUs per second and pay hourly. Serverless capacity means you pay per RU consumed. Choose provisioned for predictable workloads and serverless for unpredictable usage.

Factors affecting RU consumption include item size, indexing paths, consistency levels, and query complexity. The exam tests your ability to estimate requirements, provision appropriate throughput, and optimize queries to minimize consumption and reduce costs.

How does point-in-time restore (PITR) work in Azure SQL Database?

Point-in-time restore (PITR) enables recovering your database to any specific moment within the retention period. Default retention is 7 days but extends to 35 days for standard tier. Long-term retention policies extend this to 10 years.

Azure maintains three backup types enabling second-level precision:

  • Full backups (weekly)
  • Differential backups (daily)
  • Transaction log backups (every 5-10 minutes)

PITR creates a new database rather than overwriting the original. This preserves your current database while allowing parallel access to historical states.

Recovery time depends on database size and restoration point recency. Execute PITR via Azure Portal, PowerShell, or CLI commands.

For business continuity planning, PITR protects against accidental deletion or logical corruption. Geo-redundant backups protect against regional disasters. The exam tests understanding of backup retention configuration and recovery procedures.

Why are flashcards effective for studying Azure database concepts?

Flashcards leverage two proven learning techniques: spaced repetition and active recall. Azure database topics involve numerous services, options, and technical details that benefit from systematic review.

Flashcards break complex concepts into discrete questions preventing cognitive overload. Active recall (retrieving information from memory) strengthens neural pathways better than passive reading.

Spaced repetition optimizes review timing. You focus on challenging concepts while reducing redundant review of mastered material. This approach maximizes learning efficiency.

Flashcards enable efficient study during short windows like commutes or breaks. Digital apps like Fluent Flash track performance, identifying weak areas needing focus.

Testing yourself with flashcards simulates exam conditions, building confidence. The exam-style questions, practical scenarios, and technical terminology essential for Azure Administrator certification are ideally suited to flashcard-based learning.