Microsoft Certified: Azure Database Administrator Associate — Practice Set 1

Question 1

Your organization needs a managed Azure SQL service that provides the highest compatibility with SQL Server on-premises, including support for SQL Server Agent, cross-database queries, and linked servers, while eliminating infrastructure management overhead. Which service should you deploy?

Accepted Answer

Azure SQL Managed Instance provides near-100% compatibility with the SQL Server database engine, including instance-scoped features like SQL Server Agent, cross-database queries, linked servers, and Service Broker. Azure SQL Database (single database) does not support all instance-scoped features. SQL Server on Azure VMs requires OS and SQL Server patching management. Azure Database for PostgreSQL is a different database engine.

Question 2

You need to migrate a SQL Server 2019 on-premises database to Azure SQL Database with minimal downtime for a business-critical application. The migration must keep the source database online during the migration and switch over only when fully synchronized. Which migration strategy should you use?

Accepted Answer

Online migration using Azure Database Migration Service (DMS) performs continuous change data capture (CDC) to keep the source and target synchronized while the source remains online. The migration completes with a brief cutover window. Offline migration (backup/restore) requires source downtime during the migration. BACPAC export creates a point-in-time snapshot requiring downtime. Detaching database files is an offline method.

Question 3

Your organization wants to manage Azure SQL Managed Instance servers across multiple on-premises datacenters using Azure management tools and policies without migrating the databases to the cloud. Which Azure Arc-enabled service supports this scenario?

Accepted Answer

Azure Arc-enabled SQL Managed Instance allows you to run SQL Managed Instance on any infrastructure — on-premises, at the edge, or in any cloud — while still using Azure management tools, Azure Policy, Microsoft Defender for Cloud, and Azure Monitor. This extends Azure SQL capabilities to non-Azure environments. Azure SQL Database hyperscale is a cloud-only service. Elastic pools manage multiple Azure SQL Databases. SQL on Azure VMs requires Azure infrastructure.

Question 4

You are a database administrator deploying a new Azure SQL Database for a SaaS application. The application handles millions of small rows per table and the database is expected to grow to several terabytes. You need to improve query performance on large tables by distributing data across multiple filegroups. Which feature should you configure?

Accepted Answer

Table partitioning divides large tables into smaller, more manageable pieces (partitions) based on a partition function and scheme. This improves query performance by allowing partition elimination (scanning only relevant partitions), improves data management operations like archiving old data, and distributes data across multiple filegroups. Always Encrypted and TDE are security features. Row-level security controls row visibility per user.

Question 5

Your organization requires that all Azure SQL Database connections use Microsoft Entra ID (formerly Azure Active Directory) authentication instead of SQL Server authentication. You need to configure this for a new Azure SQL Database. Which action should you take?

Accepted Answer

To enable Microsoft Entra ID authentication for Azure SQL Database, you must configure a Microsoft Entra admin (a user or group from your Entra ID tenant) for the Azure SQL logical server. This enables users to authenticate using their Entra ID credentials. To enforce Entra-only authentication, you can set the 'Entra-only authentication' option which disables SQL Server authentication. Creating SQL logins uses the legacy SQL authentication approach.

Question 6

You need to protect sensitive data in an Azure SQL Database so that the data is encrypted at rest and the encryption keys are managed by Azure. The application does not need to be modified to use the encrypted columns. Which Azure SQL security feature should you implement?

Accepted Answer

Transparent Data Encryption (TDE) encrypts the entire database at rest (data files, log files, and backups) without requiring any application changes. The encryption and decryption happen transparently at the storage layer, managed by Azure. Always Encrypted encrypts specific columns with client-side encryption requiring application changes. Dynamic Data Masking obfuscates displayed data without encrypting it. Row-level security controls row-level access.

Question 7

Your organization stores customer credit card numbers in Azure SQL Database. A requirement states that the database administrators must not be able to view the plaintext credit card numbers, but the application can still process them. Which Azure SQL feature encrypts specific columns so that only authorized clients can decrypt the data?

Accepted Answer

Always Encrypted is designed to protect sensitive data from high-privileged users like database administrators. Data is encrypted client-side before being sent to the database — the database engine only sees ciphertext and cannot decrypt it. Only applications with the column master key can decrypt the data. TDE encrypts data at rest but DBAs can still see plaintext. Dynamic Data Masking only masks display output, not the actual stored data. Ledger provides tamper-evident audit trails.

Question 8

You need to configure network security for an Azure SQL Database to restrict access to only your organization's virtual network. Which two approaches can you use to prevent public internet access to the database? Choose 2.

Accepted Answer

Private endpoints (Azure Private Link) and VNet service endpoints are the two network-level controls that restrict Azure SQL Database access to a virtual network. A private endpoint assigns a private IP address from your VNet to the SQL server. A VNet service endpoint extends your VNet identity to the Azure SQL service, restricting access to traffic from the specified subnet. Firewall rules alone don't prevent public internet access if the public endpoint is enabled. Always Encrypted and TDE are encryption features, not network controls.

Question 9

You are auditing an Azure SQL Database for compliance purposes. You need to ensure that changes to data in specific tables are tracked and that the audit trail cannot be altered or deleted by any user, including database administrators. Which Azure SQL feature provides a cryptographically verifiable, tamper-evident audit trail?

Accepted Answer

Azure SQL Ledger provides cryptographically verifiable, tamper-evident tables where data modifications are recorded in an immutable ledger. The ledger uses blockchain-like hash chaining to detect any unauthorized changes to historical records, even by database administrators. Azure SQL Auditing logs queries to storage but can be disabled. SQL Server Audit tracks events but is mutable. CDC tracks changes for data synchronization but is not tamper-evident.

Question 10

Your organization has a multi-tenant SaaS application built on Azure SQL Database. Each tenant's data must be completely isolated so that Tenant A can never query Tenant B's rows, even when both tenants' data exists in the same table. Which security feature provides row-level data isolation based on the executing user's identity?

Accepted Answer

Row-level security (RLS) enables you to control access to rows in a database table based on the characteristics of the user executing a query (such as group membership or execution context). For multi-tenant SaaS, a security policy with a predicate function filters rows so each tenant only sees their own data, transparently and without application changes. Dynamic Data Masking only masks column values. Column-level permissions control column access, not row access. Always Encrypted is for column encryption.

Question 11

You are a database administrator monitoring an Azure SQL Database that is experiencing slow query performance. You need to identify which queries are consuming the most CPU and I/O over the past week. Which built-in Azure SQL feature should you use?

Accepted Answer

Query Store persists query execution plans and runtime statistics over time, enabling you to identify top resource-consuming queries, track query performance regressions, and force query plans. It stores historical data (configurable retention) so you can analyze performance trends over the past week. Azure Monitor provides infrastructure-level metrics. DMVs show current in-memory statistics (reset when SQL Server restarts). SQL Server Profiler is not available in Azure SQL Database.

Question 12

Your Azure SQL Database has an application that experiences intermittent timeouts. You suspect the issue is caused by queries blocking each other. Which dynamic management view (DMV) should you query to identify the current blocking chain?

Accepted Answer

To identify blocking chains in Azure SQL Database, you query sys.dm_exec_requests (which shows the blocking_session_id column indicating which session is blocking each request) combined with sys.dm_os_waiting_tasks (which shows wait types and blocking task chains). sys.dm_exec_query_stats shows historical query statistics. sys.dm_exec_sessions shows active sessions. sys.dm_db_index_usage_stats shows index usage patterns.

Question 13

Azure SQL Database has detected a query that would benefit from an additional index. The automatic tuning feature is configured to CREATE INDEX recommendations. Which automatic tuning action should you enable to allow Azure SQL Database to automatically implement approved index recommendations?

Accepted Answer

Azure SQL Database automatic tuning has three built-in options: Create Index (automatically creates performance-improving indexes), Drop Index (automatically drops unused or duplicate indexes), and Force Last Good Plan (automatically forces the last known good query execution plan when a regression is detected). The 'Create Index' automatic tuning action allows Azure SQL Database to automatically create recommended indexes without manual DBA intervention.

Question 14

You need to perform regular index maintenance on an Azure SQL Database to maintain query performance. Which two index maintenance operations should you perform to address index fragmentation? Choose 2.

Accepted Answer

The two standard index maintenance operations in Azure SQL are: (A) ALTER INDEX REBUILD, which recreates the index from scratch and removes all fragmentation (online or offline), and (C) ALTER INDEX REORGANIZE, which defragments the leaf level of the index by physically reordering pages (always online, less resource-intensive). Dropping/recreating an index achieves the same result as REBUILD but is more verbose. UPDATE STATISTICS updates statistics but doesn't fix fragmentation. DBCC CHECKDB checks database integrity.

Question 15

You are a database administrator for an Azure SQL Database. Users report that queries that ran in 2 seconds yesterday now take 45 seconds. You suspect an execution plan regression occurred after statistics were updated. Which Azure SQL automatic tuning feature automatically detects and corrects execution plan regressions?

Accepted Answer

Automatic Force Last Good Plan is an Azure SQL Database automatic tuning feature that uses Query Store to detect query performance regressions. When a query's execution time suddenly increases significantly compared to its historical baseline, the system automatically forces the last known good execution plan. This resolves plan regressions without manual DBA intervention. Automatic Create Index creates new indexes. IQP improves plans through adaptive query processing features. Automatic statistics update refreshes statistics but doesn't force plans.

Question 16

You need to monitor the Azure SQL Database's performance in real time for current active queries, wait statistics, and resource consumption. Which Azure SQL feature provides intelligent performance insights by automatically analyzing performance patterns and surfacing recommendations?

Accepted Answer

Intelligent Insights is an Azure SQL Database built-in intelligence feature that uses a built-in intelligence baseline and AI to continuously monitor database usage patterns and automatically detect disruptive events (such as high wait times, plan regressions, resource exhaustion). It generates a diagnostic log with root cause analysis and recommendations. Azure Monitor Logs collects logs but doesn't automatically analyze SQL performance patterns. SQL Server Profiler is not available in Azure SQL Database. Azure Advisor provides general Azure optimization recommendations.

Question 17

You need to automate daily maintenance tasks for an Azure SQL Database, such as rebuilding indexes and updating statistics, using a managed scheduling service. For SQL Server on Azure Virtual Machines, which component should you use to schedule and manage these maintenance jobs?

Accepted Answer

SQL Server Agent is the built-in job scheduling service in SQL Server (available on SQL Server on Azure VMs and Azure SQL Managed Instance). It allows you to create, schedule, and manage T-SQL jobs, multi-step jobs with error handling, alerts, and notifications. Azure Automation runbooks can also automate tasks but are external to SQL Server. Azure Data Factory is for data integration pipelines. Azure Logic Apps are for workflow automation.

Question 18

Your organization needs to run T-SQL maintenance scripts across multiple Azure SQL Databases simultaneously on a schedule. SQL Server Agent is not available for Azure SQL Database. Which Azure service provides elastic job scheduling across multiple Azure SQL Databases?

Accepted Answer

Azure SQL Database elastic jobs is the Azure-native job scheduling service for Azure SQL Database that allows you to run T-SQL scripts across multiple databases, elastic pools, or shards on a schedule. It is the Azure SQL Database equivalent of SQL Server Agent for multi-database scenarios. Azure Automation can run PowerShell but is not SQL-native. Logic Apps can invoke SQL but are not designed for scheduled T-SQL maintenance. Azure Data Factory stored procedure activity is for data pipelines, not maintenance automation.

Question 19

You need to deploy an Azure SQL Database using infrastructure as code (IaC) so that the deployment is repeatable and consistent across development, staging, and production environments. Which Azure-native IaC approach should you use?

Accepted Answer

Azure Resource Manager (ARM) templates and Bicep are Azure's native IaC languages for declaratively defining Azure resources, including Azure SQL servers, databases, firewall rules, and configuration settings. They enable repeatable, version-controlled deployments across environments. ARM JSON is the original format; Bicep is a cleaner domain-specific language that compiles to ARM JSON. BACPAC is for schema and data migration, not infrastructure provisioning. The copy database feature creates a copy but is not IaC.

Question 20

You are a database administrator who needs to receive an email notification when the CPU utilization of your Azure SQL Database exceeds 90% for more than 5 minutes. Which Azure service should you configure to send this alert?

Accepted Answer

Azure Monitor alert rules allow you to configure metric-based alerts (such as CPU percentage > 90%) with a specified evaluation window (e.g., 5 minutes). When the alert fires, it triggers an action group which can send email, SMS, push notifications, or call a webhook/Azure Function. Query Store does not send alerts. SQL Server Agent alerts are for SQL Server on VMs/Managed Instance, not Azure SQL Database CPU metrics. Azure Service Health is for platform-level outages, not resource metrics.

Question 21

Your organization's Azure SQL Database must remain available for read operations even during a regional failover to a secondary Azure region. Which Azure SQL feature provides a readable secondary database in a different region for disaster recovery?

Accepted Answer

Active geo-replication is an Azure SQL Database feature that creates readable secondary databases in up to four other Azure regions using asynchronous replication. The secondary databases can serve read workloads, and during a disaster, you can initiate a manual or automatic failover to the secondary region. Long-term retention stores backup files but does not provide a standby database. Elastic pools manage multiple databases. Backup to blob storage enables restore, not live failover.

Question 22

Your organization requires a recovery point objective (RPO) of 1 hour and a recovery time objective (RTO) of 30 minutes for an Azure SQL Database. Which two HA/DR approaches together can meet these requirements? Choose 2.

Accepted Answer

Failover groups (A) provide automatic failover capability with minimal RTO and RPO (typically seconds to minutes for data loss) across Azure regions using a single connection string that automatically routes to the primary. Active geo-replication (C) provides a continuously-replicated secondary replica with near-zero RPO. Both can support the required RPO of 1 hour and RTO of 30 minutes. LTR with weekly backups would have an RPO of up to 7 days. Point-in-time restore has RPO of up to 7 days (backups every 5-10 minutes for SQL DB). Database copy creates a snapshot, not ongoing replication.

Question 23

You need to restore an Azure SQL Database to a specific point in time from 3 days ago because accidental data deletions occurred. Which restore option supports this scenario using Azure SQL Database automated backups?

Accepted Answer

Point-in-time restore (PITR) allows you to restore an Azure SQL Database to any point within the backup retention period (7 days by default for Standard, up to 35 days for Business Critical). Azure SQL Database automatically takes full, differential, and transaction log backups. PITR creates a new database from the backup chain. Geo-restore recovers from regional outages, not accidental deletions. Failover to geo-secondary provides the current state (with the deletions). LTR restores from weekly/monthly/yearly backups stored longer term.

Question 24

You manage an Azure SQL Managed Instance that hosts a critical business application. Your organization's HA policy requires an RPO of near-zero and an RTO of 1-2 minutes for automatic failover within the same Azure region. Which feature achieves this for Azure SQL Managed Instance?

Accepted Answer

Azure SQL Managed Instance uses a built-in Always On availability group under the hood to provide high availability within a region. Replica nodes are maintained in the same region, and in the event of a hardware or software failure, automatic failover occurs with near-zero data loss (RPO) and typically 1-2 minutes of downtime (RTO). This is built-in and does not require additional configuration. Active geo-replication is a cross-region DR feature, not an intra-region HA feature.

Question 25

Your organization requires that Azure SQL Database backups are retained for 3 years for compliance purposes. Standard automated backup retention is configured for 7 days. Which Azure SQL Database feature extends backup retention beyond 35 days?

Accepted Answer

Long-term backup retention (LTR) allows you to store Azure SQL Database full backups in Azure Blob Storage for up to 10 years, configured with weekly, monthly, and yearly retention policies. This is designed for compliance scenarios requiring backup retention beyond the maximum 35-day automated backup window. LTR backups are stored in geo-redundant Azure Blob Storage. Active geo-replication and failover groups are for live replication, not backup retention. Azure Backup for SQL in VMs applies to SQL Server on Azure VMs, not Azure SQL Database.

DP-300 — Azure Database Administrator Associate