75 of 239 questions · Page 2/4 · Identify Considerations For Relational Data On Azure topic · Answers revealed
Which TWO Azure services can be used to host a relational database that requires native support for JSON data and high availability with automatic failover?
Azure SQL Database supports JSON functions and provides built-in high availability with automatic failover.
Why this answer
A company uses Azure SQL Database for its order management system. The database has a table named Orders with columns OrderID (INT, PRIMARY KEY), CustomerID (INT), OrderDate (DATE), TotalAmount (DECIMAL). Queries that filter by OrderDate are slow. The database administrator observes that the nonclustered index on OrderDate has high fragmentation and many page splits. Which action will most likely improve query performance for these date-based queries?
Rebuilding with a lower fill factor reserves free space on pages, reducing page splits and fragmentation, thus improving performance.
Why this answer
Rebuilding the nonclustered index on OrderDate with a FILLFACTOR of 80 reduces page splits by leaving free space in each leaf-level page. This accommodates future insertions and updates that modify the OrderDate values, lowering fragmentation and improving query performance for date-based filters.
Exam trap
The trap here is that candidates may think changing the clustered index to OrderDate (Option C) is the best solution, but they overlook that this would disrupt the primary key and cause even more fragmentation for a table with frequent inserts, whereas rebuilding with a lower fill factor directly addresses page splits without altering the table's physical design.
How to eliminate wrong answers
Option B is wrong because changing TotalAmount from DECIMAL to FLOAT does not address fragmentation or page splits on the OrderDate index; it could introduce rounding errors and is irrelevant to date-based query performance. Option C is wrong because removing the clustered index on OrderID (the primary key) and creating a clustered index on OrderDate would reorganize the entire table by date, which might improve range scans but would severely degrade point lookups by OrderID and cause excessive page splits due to non-sequential date inserts; it is not the most targeted fix. Option D is wrong because a columnstore index is designed for analytical/aggregation workloads on large tables, not for improving point lookup or range filter performance on a single column in an OLTP system; it would add overhead without addressing fragmentation.
A company is designing a multi-tenant SaaS application. Each tenant has its own relational database, but the total number of tenants is expected to grow rapidly. The company wants to manage all databases efficiently and optimize costs by sharing resources among tenants with low usage. What should they use?
Elastic pools share resources among databases, ideal for multi-tenant SaaS.
Why this answer
Option A is correct because elastic pools in Azure SQL Database allow sharing resources among multiple databases, optimizing cost for low-usage tenants. Option B (Azure SQL Managed Instance) is for single large databases. Option C (SQL Server on Azure VM) requires separate VMs.
Option D (Azure Database for MySQL) does not have elastic pools.
A company has an existing on-premises SQL Server database that is 500 GB in size. The database uses SQL Server Agent jobs for scheduled maintenance and linked servers to query data from a remote SQL Server instance. The company wants to migrate to Azure with minimal application changes and needs automated backups and patching. Which Azure SQL service should they choose?
Azure SQL Managed Instance provides high compatibility with on-premises SQL Server, including support for SQL Agent jobs and linked servers. It also includes automated backups, patching, and high availability, meeting all requirements.
Why this answer
Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and linked servers, while offering automated backups and patching. This minimizes application changes during migration, unlike other Azure SQL options that lack these features.
Exam trap
The trap here is that candidates often choose Azure SQL Database for its simplicity, overlooking its lack of SQL Server Agent and linked server support, which are critical for the described workload.
How to eliminate wrong answers
Option A is wrong because Azure SQL Database does not support SQL Server Agent jobs or linked servers, requiring significant application changes. Option C is wrong because SQL Server on Azure Virtual Machines requires manual management of backups and patching, contradicting the need for automated maintenance. Option D is wrong because Azure Database for PostgreSQL is a different database engine, incompatible with SQL Server Agent jobs and linked servers.
Which TWO of the following statements about Azure SQL Database elastic pools are true?
Elastic pools allocate resources to a pool of databases.
Why this answer
Options A and C are correct. Elastic pools allow sharing of resources among multiple databases and are cost-effective for databases with variable usage patterns. Option B is wrong because elastic pools can be scaled up or down.
Option D is wrong because elastic pools are not limited to a single server. Option E is wrong because elastic pools do not store data; databases within pools do.
Match each Azure data security feature to its description.
Drag a concept onto its matching description — or click a concept then click the description.
Encrypts data at rest automatically
Encrypts data in use and in transit
Identity-based access control
IP-level network restrictions
Private IP connectivity over Azure backbone
Why these pairings
These are important data security mechanisms in Azure.
A financial application stores transactions in an Azure SQL Database table with columns: TransactionID (clustered index), AccountID, TransactionDate, Amount. Queries frequently filter on AccountID and TransactionDate together. The table contains millions of rows. Which index strategy will most improve query performance for these filters?
Correct. A composite nonclustered index on (AccountID, TransactionDate) enables an index seek on AccountID and a range scan on TransactionDate within that partition, minimizing I/O.
Why this answer
Option D creates a nonclustered index on (AccountID, TransactionDate) that acts as a covering index for queries filtering on both columns. SQL Server can seek directly to the matching rows using the composite key order, avoiding a full table scan or key lookup. This is the most efficient strategy because the index is sorted by AccountID first, then TransactionDate, matching the query predicate exactly.
Exam trap
The trap here is that candidates often choose Option A (changing the clustered index) because they think it will be faster for all queries, but they overlook the negative impact on the existing primary key and the fact that a nonclustered covering index is sufficient and less disruptive.
How to eliminate wrong answers
Option A is wrong because changing the clustered index to (AccountID, TransactionDate) would reorganize the entire table's physical order, potentially harming performance for other queries that rely on the existing TransactionID clustered index (e.g., range scans or joins on TransactionID). Option B is wrong because a nonclustered index on TransactionDate alone cannot efficiently filter on AccountID; it would require scanning all rows for each AccountID or performing a key lookup for each match. Option C is wrong because a nonclustered index on AccountID with TransactionDate as an included column only helps when filtering solely on AccountID; it does not support seeking on both columns together, as the included column is not part of the index key and cannot be used for range or equality filtering on TransactionDate.
A company runs an e-commerce application on Azure SQL Database. The application experiences heavy read traffic from reporting dashboards that query the same tables as the transactional workload. This causes performance degradation for the application. The company needs a solution that offloads reporting queries to a read-only copy that stays synchronized within minutes, without impacting transactional performance. Which Azure SQL Database feature should they use?
Correct. Active geo-replication provides a readable secondary database that can serve reporting queries while the primary handles transactional workloads. The replication lag is typically within minutes.
Why this answer
Active geo-replication creates a readable secondary replica of the Azure SQL Database that can be used for read-only workloads like reporting dashboards. The secondary replica stays synchronized with the primary database using asynchronous replication, typically within seconds to minutes, ensuring near-real-time data without impacting transactional performance on the primary.
Exam trap
The trap here is that candidates often confuse auto-failover groups with active geo-replication, not realizing that auto-failover groups do not make the secondary readable by default unless combined with active geo-replication, and that the primary purpose of failover groups is failover orchestration, not read offloading.
How to eliminate wrong answers
Option A is wrong because auto-failover groups provide high availability and disaster recovery by managing failover of multiple databases, but they do not offload read traffic to a read-only copy; the secondary in a failover group is not readable unless you use active geo-replication within the group. Option C is wrong because elastic pools are a pricing and resource management model for pooling resources across multiple databases, not a feature for creating read-only replicas. Option D is wrong because Query Performance Insight is a diagnostic tool for analyzing query performance and identifying bottlenecks, not a mechanism to offload read traffic to a separate copy.
A DBA runs the above KQL query in Azure Monitor for an Azure SQL Database. The query returns no results. What is the most likely reason?
QueryStoreRuntimeStatistics is populated only when Query Store is enabled.
Why this answer
Option B is correct because the Query Store must be enabled for each database to generate runtime statistics. Option A is wrong because AzureDiagnostics is a common table for SQL diagnostics. Option C is wrong because the category name is correct.
Option D is wrong because the syntax is valid.
A multinational e-commerce company uses Azure SQL Database active geo-replication to replicate a critical inventory database to a secondary region. During a regional outage, the application automatically fails over to the secondary database. After the primary region recovers, the administrator wants to make the original primary the main database again without losing any data modifications made on the secondary during the outage. What should the administrator do?
A planned failover (graceful failover) synchronizes all data between replicas before switching roles, ensuring zero data loss.
Why this answer
Option C is correct because a planned failover (also known as graceful failover) in Azure SQL Database active geo-replication is designed to switch roles between the primary and secondary databases without data loss. After the original primary region recovers, initiating a planned failover synchronizes all data from the current primary (the former secondary) to the original primary, making it the new primary while preserving all modifications made during the outage. This operation ensures zero data loss because it forces a final synchronization before the role swap.
Exam trap
The trap here is confusing a planned failover (graceful, no data loss) with a forced failover (unplanned, potential data loss), leading candidates to incorrectly choose Option B when they need to preserve all modifications made on the secondary during an outage.
How to eliminate wrong answers
Option A is wrong because dropping the geo-replication relationship and recreating the secondary from the current primary would discard the original primary's data modifications made during the outage, as the original primary would be overwritten by the current primary's data. Option B is wrong because a forced failover (also called unplanned failover) is intended for disaster scenarios and can cause data loss; it does not perform a final synchronization and would not guarantee that all modifications from the secondary are preserved when switching back. Option D is wrong because deleting the secondary database and restoring the original primary from a backup taken before the outage would lose all data modifications made on the secondary during the outage, defeating the purpose of geo-replication for high availability.
A company uses Azure SQL Database for an order management system. They have a table 'Orders' with columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. Queries that filter on OrderDate are slow. They create a nonclustered index on OrderDate. However, after many inserts, the index becomes fragmented and page splits occur frequently. Which action should the DBA take to maintain query performance?
Rebuilding the index defragments the index and can be done online to avoid blocking queries.
Why this answer
Option A is correct because rebuilding the index online eliminates fragmentation and page splits without blocking concurrent queries, which is critical for a production order management system. The ALTER INDEX REBUILD operation reorganizes the index B-tree structure, consolidating pages and reducing logical fragmentation, thereby restoring query performance on OrderDate filters.
Exam trap
The trap here is that candidates often confuse index maintenance actions, thinking a drop/recreate is simpler, or they incorrectly assume a clustered index on the filtered column always improves performance, ignoring the impact on write-heavy OLTP workloads.
How to eliminate wrong answers
Option B is wrong because dropping and recreating the index is a heavier operation that requires exclusive locks, causing downtime; it also loses any index metadata or statistics that might be in use, and the same effect can be achieved with a rebuild. Option C is wrong because adding a clustered index on OrderDate would physically reorder the entire table by that column, which could improve range scans but would also slow down inserts due to page splits on the clustered key, and it changes the table's physical structure unnecessarily. Option D is wrong because a clustered columnstore index is designed for large-scale analytical workloads (data warehousing) and is not suitable for an OLTP order management system with frequent inserts and point lookups; it would degrade performance for the typical order queries.
A company uses Azure SQL Database for an employee management system. The Employees table has 10 million rows and a clustered index on EmployeeID (the primary key). Queries that filter employees by Department and then sort by HireDate are very slow. Which indexing strategy will most improve performance for these queries?
This index supports both the filter (Department) and the sort order (HireDate). Using included columns makes it a covering index for the query, eliminating costly lookups to the clustered index.
Why this answer
A nonclustered index on (Department, HireDate) with included columns is optimal because it supports both the WHERE clause filter on Department and the ORDER BY on HireDate as a covering index. The index key order matches the query's filter and sort requirements, allowing SQL Server to perform a single index seek and avoid key lookups by including all needed columns. This eliminates the need to scan the clustered index or sort rows after filtering.
Exam trap
The trap here is that candidates often choose Option B because they think any index on both columns will help, but they overlook that the key column order must match the WHERE clause filter first to enable an efficient seek, not just the sort order.
How to eliminate wrong answers
Option B is wrong because the index key order (HireDate, Department) does not match the query filter on Department first, so SQL Server cannot efficiently seek on Department; it would require scanning or sorting. Option C is wrong because creating a clustered index on Department would reorder the entire table by Department, which is not the primary key and would break the existing clustered index on EmployeeID, likely degrading other queries and not directly optimizing the sort on HireDate. Option D is wrong because a clustered columnstore index is designed for large-scale analytical workloads (data warehousing) and not for point lookups or ordered retrieval in an OLTP employee management system; it would worsen performance for the described query pattern.
Refer to the exhibit. You create an external table in Azure SQL Database. Which data source is being used?
The location 'https://mystorageaccount.blob.core.windows.net/container' indicates Blob Storage.
Why this answer
The exhibit shows an external table referencing a data source with the LOCATION set to 'https://mystorage.blob.core.windows.net/...', which is the endpoint for Azure Blob Storage. In Azure SQL Database, external tables are created over external data sources that point to Azure Blob Storage or Azure Data Lake Storage, but the URL format 'blob.core.windows.net' specifically indicates Azure Blob Storage. The CREATE EXTERNAL TABLE statement uses this data source to read data stored as files (e.g., CSV, Parquet) in the blob container.
Exam trap
The trap here is that candidates confuse Azure Blob Storage with Azure Data Lake Storage Gen2 because both can store files, but the endpoint URL (blob.core.windows.net vs. dfs.core.windows.net) is the key differentiator in the exhibit.
How to eliminate wrong answers
Option B is wrong because Azure Files uses the file.core.windows.net endpoint and is accessed via SMB protocol, not the blob.core.windows.net URL shown in the exhibit. Option C is wrong because Azure SQL Database itself cannot be the data source for an external table in the same database; external tables reference external data sources like Blob Storage or Data Lake, not another SQL database. Option D is wrong because Azure Data Lake Storage Gen2 uses the dfs.core.windows.net endpoint (or a blob endpoint with a hierarchical namespace), not the standard blob.core.windows.net URL shown in the exhibit.
A company runs an e-commerce application on Azure SQL Database. The database experiences high transaction volume during business hours (9 AM to 6 PM) but very low activity at night and on weekends. They want to optimize costs by paying only for the compute resources used, while ensuring the database can automatically scale up during peak periods and scale down (or pause) during idle times. Which Azure SQL Database purchasing model and compute tier should they choose?
Serverless automatically scales compute based on load and pauses the database during inactivity, charging only for the compute used. This perfectly matches the requirement to minimize costs during low-usage periods while handling peak traffic.
Why this answer
The vCore-based purchasing model with serverless compute tier is correct because it automatically scales compute resources based on workload demand and can pause during idle periods, charging only for consumed compute and storage. This matches the requirement of high transaction volume during business hours and low activity at night/weekends, optimizing costs by eliminating charges for unused compute capacity.
Exam trap
The trap here is that candidates often confuse the Hyperscale service tier with serverless, but Hyperscale focuses on storage scalability and fast recovery, not compute auto-scaling or pausing, making it unsuitable for cost optimization during idle periods.
How to eliminate wrong answers
Option A is wrong because the DTU-based purchasing model uses a fixed bundle of compute, storage, and I/O resources, which cannot automatically scale up/down or pause based on demand, leading to over-provisioning during idle times. Option B is wrong because the vCore-based provisioned compute tier requires a fixed number of vCores allocated continuously, even during low-activity periods, and does not support auto-scaling or pausing, resulting in higher costs. Option D is wrong because the vCore-based Hyperscale service tier is designed for large databases with high scalability and fast recovery, not for cost optimization through auto-scaling and pausing; it uses provisioned compute and does not offer serverless capabilities.
A healthcare organization stores patient records in Azure SQL Database. To comply with HIPAA, they need to encrypt sensitive columns like Social Security Numbers (SSNs) at rest and ensure that only authorized users can decrypt them. Which feature should they implement?
Always Encrypted encrypts sensitive columns at the client side, keeping data encrypted in the database.
Why this answer
Option C is correct because Always Encrypted allows client-side encryption of sensitive columns, ensuring only authorized clients can decrypt. Option A is wrong because TDE encrypts the entire database at rest, not individual columns. Option B is wrong because Dynamic Data Masking obfuscates data but does not encrypt it.
Option D is wrong because Row-Level Security restricts row access but does not encrypt columns.
A company runs a customer-facing web application that uses an Azure SQL Database. The database experiences highly variable workloads: high traffic during business hours and low traffic at night and on weekends. The company wants to pay only for the compute resources consumed and automatically scale compute capacity based on demand, while maintaining the ability to pause during inactivity. Which Azure SQL Database service tier should they choose?
Serverless automatically scales compute based on demand and can pause when inactive, billing only for the vCores used per second. This is ideal for intermittent, variable workloads.
Why this answer
The Serverless tier is designed for workloads with variable traffic and idle periods, as it automatically scales compute resources based on demand and can pause the database during inactivity, charging only for consumed compute and storage. This matches the requirement to pay only for resources used and to pause when there is no traffic, such as at night and weekends.
Exam trap
The trap here is that candidates may confuse the Serverless tier's auto-scaling and pausing with the Hyperscale tier's storage scalability, but Hyperscale does not support compute pausing and is designed for continuous high-throughput workloads, not variable demand with idle periods.
How to eliminate wrong answers
Option A is wrong because Hyperscale is built for very large databases (up to 100 TB) with high throughput and fast scaling of storage, but it does not support auto-pausing and is not cost-effective for variable workloads with idle periods. Option C is wrong because Provisioned (General Purpose) uses a fixed compute size that must be manually scaled, and it cannot pause automatically, so you pay for allocated resources even during low traffic. Option D is wrong because Business Critical is a high-performance tier with low latency and built-in replicas, but it also uses fixed provisioning without auto-pausing, leading to higher costs during idle times.
A company runs an e-commerce application on Azure SQL Database. The Orders table has millions of rows. Queries that filter on CustomerID and order by OrderDate DESC are slow. The table currently has a clustered index on OrderID (the primary key). Which indexing strategy will most improve performance for these queries?
This covering index includes both the filter column and the sort column in the correct order, allowing efficient index seek and eliminating the need for a separate sort operation.
Why this answer
Option B is correct because creating a nonclustered index on (CustomerID, OrderDate DESC) with included columns allows the query to filter on CustomerID and sort by OrderDate in descending order using a single index seek, avoiding a sort operation. This leverages the index's key order to directly return rows in the desired order, which is critical for performance on large tables in Azure SQL Database.
Exam trap
The trap here is that candidates often think including a column in an index (as an included column) is sufficient for sorting, but only key columns determine the physical order of rows in the index, so a nonclustered index with OrderDate as an included column cannot eliminate the need for a sort operation.
How to eliminate wrong answers
Option A is wrong because including OrderDate as an included column does not make it part of the index key, so the index cannot provide sorted output for OrderDate DESC; the database would still need to perform a sort after filtering on CustomerID. Option C is wrong because rebuilding the clustered index on CustomerID would force the table to be physically ordered by CustomerID, which may help filtering but would not efficiently support ordering by OrderDate DESC, and it could degrade other queries that rely on the primary key OrderID. Option D is wrong because a nonclustered index on OrderDate alone does not support filtering on CustomerID, requiring a full scan or key lookup for each row, which is inefficient for millions of rows.
A startup is building a new application and needs a relational database that supports JSON data, automatic scaling, and a serverless compute tier to minimize costs during low usage periods. Which Azure data service should they choose?
Azure SQL Database serverless supports JSON, auto-scaling, and pauses when idle.
Why this answer
Option C is correct because Azure SQL Database serverless tier supports JSON, automatic scaling, and pauses during inactivity. Option A is wrong because Azure Cosmos DB is NoSQL, not relational. Option B is wrong because Azure SQL Managed Instance does not have a serverless tier.
Option D is wrong because Azure Database for PostgreSQL serverless does not support JSON as naturally as SQL Server.
A company needs to migrate a large on-premises SQL Server database to Azure. The migration must have minimal downtime and support ongoing replication. Which Azure service should they use?
Database Migration Service supports online migrations with continuous replication.
Why this answer
Azure Database Migration Service (DMS) is designed for online migrations with minimal downtime, supporting ongoing replication from SQL Server to Azure SQL Database. It uses the Data Migration Assistant (DMA) for assessment and the Azure DMS for continuous sync, enabling near-zero downtime during cutover.
Exam trap
The trap here is that candidates confuse the target service (Azure SQL Database) with the migration tool, or assume Data Factory can handle live replication, when in fact only DMS provides the necessary online migration and ongoing sync capabilities.
How to eliminate wrong answers
Option A is wrong because Azure Data Box is a physical data transfer appliance for offline bulk data migration, not suitable for minimal downtime or ongoing replication. Option B is wrong because Azure Data Factory is an ETL and orchestration service for data movement and transformation, not a dedicated migration tool with built-in replication and minimal downtime capabilities. Option C is wrong because Azure SQL Database is the target platform, not a migration service; it does not handle the migration process or replication itself.
A company uses Azure SQL Database for an e-commerce platform. The 'Orders' table has millions of rows with columns OrderID (primary key), CustomerID, OrderDate, and TotalAmount. Queries often filter by CustomerID (equality) and OrderDate (range). Currently, these queries are slow. Which index should be created to improve performance?
This composite index covers the query predicate perfectly. CustomerID is the equality column, and OrderDate is the range column. The index allows the database engine to efficiently locate rows for a specific customer and then scan a small range of dates.
Why this answer
The query pattern filters by CustomerID (equality) and OrderDate (range). A composite nonclustered index on (CustomerID, OrderDate) allows SQL Database to seek directly to the matching CustomerID rows and then efficiently scan the ordered OrderDate range within that partition, avoiding a full table scan or key lookup. This index order leverages the index's B-tree structure for both equality and range predicates.
Exam trap
The trap here is that candidates often choose a single-column index on OrderDate (Option C) thinking it covers the range filter, but they overlook that without CustomerID as the leading key, the index cannot efficiently narrow down to a specific customer, resulting in a full index scan instead of a seek.
How to eliminate wrong answers
Option A is wrong because indexing OrderID (the primary key) does not help queries filtering by CustomerID and OrderDate; the primary key is already clustered by default, and a nonclustered index on it would be redundant for this workload. Option C is wrong because a single-column index on OrderDate alone would require scanning all rows for each CustomerID, as it cannot narrow down by CustomerID first, leading to inefficient range scans. Option D is wrong because a clustered index on CustomerID would physically reorder the table by CustomerID, but it would not efficiently support range queries on OrderDate within a CustomerID group, and it would break the primary key's default clustering, potentially causing fragmentation and performance issues.
A company is migrating a legacy on-premises database to Azure. They require the ability to run cross-database queries within the same logical server, full control over database collation settings, and want to minimize management overhead for infrastructure patching. The database size is under 1 TB and they do not need instance-level features like SQL Agent jobs or linked servers. Which Azure SQL offering should they choose?
Azure SQL Database is a PaaS service that handles patching, supports elastic query for cross-database queries, and allows collation settings on a per-database level. It does not include SQL Agent or linked servers, which are not required here.
Why this answer
Azure SQL Database is the correct choice because it supports cross-database queries within the same logical server via elastic queries, allows full control over database-level collation settings, and is a fully managed Platform-as-a-Service (PaaS) offering that handles infrastructure patching automatically. With a database size under 1 TB and no need for instance-level features like SQL Agent jobs or linked servers, Azure SQL Database meets all requirements while minimizing management overhead.
Exam trap
The trap here is that candidates often confuse Azure SQL Database with Azure SQL Managed Instance, assuming that cross-database queries require instance-level features like linked servers, but Azure SQL Database supports this via elastic queries without the need for instance-level management.
How to eliminate wrong answers
Option B (Azure SQL Managed Instance) is wrong because it provides instance-level features like SQL Agent jobs and linked servers, which are not needed, and it introduces more management overhead than Azure SQL Database. Option C (SQL Server on Azure Virtual Machine) is wrong because it requires the customer to manage patching and infrastructure, contradicting the requirement to minimize management overhead. Option D (Azure Synapse SQL pool) is wrong because it is designed for large-scale data warehousing and analytics, not for cross-database queries within a logical server, and it does not offer the same level of control over database collation settings.
A company uses Azure SQL Database and needs to audit all data modifications (INSERT, UPDATE, DELETE) for compliance purposes. The audit logs must be stored for 7 years. Which feature should they enable?
Auditing logs data modifications and can be retained for years.
Why this answer
Option B is correct because SQL Database auditing logs database events to Azure storage, Log Analytics, or Event Hubs, and can be retained for long periods. Option A is wrong because TDE encrypts data, does not log modifications. Option C is wrong because vulnerability assessment scans for security issues.
Option D is wrong because threat detection alerts on suspicious activities.
A database designer wants to reduce data redundancy and improve data integrity by splitting a large table into multiple related tables based on functional dependencies. This process is known as:
Normalization reduces redundancy by breaking tables into smaller, related tables based on dependencies.
Why this answer
Normalization is the process of organizing a relational database into multiple related tables to reduce data redundancy and improve data integrity by eliminating functional dependencies that cause anomalies. This is a core concept in relational database design, directly aligning with the scenario described in the question.
Exam trap
The trap here is that candidates often confuse normalization with partitioning, because both involve splitting tables, but partitioning is a physical storage optimization, not a logical design technique for reducing redundancy.
How to eliminate wrong answers
Option A is wrong because denormalization is the opposite process—it intentionally adds redundancy by merging tables to improve read performance, often at the cost of data integrity. Option C is wrong because partitioning splits a table horizontally or vertically for performance or manageability, but it does not inherently reduce redundancy or address functional dependencies. Option D is wrong because indexing creates data structures to speed up query performance on existing tables, but it does not restructure tables to eliminate redundancy or enforce integrity.
A company operates a high-volume order processing system on Azure SQL Database. During peak hours, many concurrent transactions try to insert and update rows in the same table, causing contention on page latches. Indexing and query optimization are already tuned. Which feature should the company implement to reduce write contention while preserving ACID properties?
In-Memory OLTP improves write performance by eliminating latch contention through memory-optimized tables and optimistic concurrency, ideal for high-concurrency transactional workloads.
Why this answer
In-Memory OLTP is correct because it uses memory-optimized tables and natively compiled stored procedures to reduce latch contention by eliminating the need for page latches entirely. Transactions operate directly on in-memory data structures, using optimistic multi-version concurrency control (MVCC) to detect conflicts without blocking, which preserves ACID properties while allowing high concurrency.
Exam trap
The trap here is that candidates confuse In-Memory OLTP with caching or read optimization, but the question specifically targets write contention and ACID preservation, which In-Memory OLTP uniquely addresses through latch-free design and optimistic concurrency.
How to eliminate wrong answers
Option A is wrong because Read Scale-out is designed to offload read-only workloads to a read-only replica, not to reduce write contention on the primary database. Option C is wrong because Elastic Database Query enables cross-database querying across shards or databases, but does not address intra-table latch contention or improve write performance. Option D is wrong because Transparent Data Encryption (TDE) performs real-time encryption/decryption of data at rest and has no effect on concurrency, locking, or latch contention.
A company needs to migrate a large on-premises SQL Server database to Azure with minimal downtime. Which Azure service should they use for the migration?
Supports online migrations with minimal downtime.
Why this answer
Option B is correct because Azure Database Migration Service (DMS) is designed for online migrations with minimal downtime, supporting continuous data sync. Option A is wrong because Azure SQL Database is the target, not a migration tool. Option C is wrong because Azure Data Factory is for data integration, not database migration with minimal downtime.
Option D is wrong because Azure Backup is for backup, not migration.
A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows. Queries frequently aggregate data for the current month, but also need to retain historical data for 7 years. The company wants to improve query performance for the monthly aggregations and simplify data archiving. Which design should they implement?
Correct. Partitioning by month supports partition elimination for monthly queries and allows for easy partition switching to archive old data.
Why this answer
Partitioning the Transactions table by month allows SQL Server to perform partition elimination during queries that aggregate data for the current month, scanning only the relevant partition(s) instead of the entire table. Aligned indexes ensure that index structures follow the same partition scheme, maintaining efficiency for both queries and maintenance. This design also simplifies data archiving by enabling fast partition switching to move older months out of the table without costly delete operations.
Exam trap
The trap here is that candidates confuse performance features like columnstore indexes or elastic pools with the specific need for partition elimination and data archiving, overlooking that partitioning directly addresses both the query performance and data lifecycle requirements.
How to eliminate wrong answers
Option A is wrong because a clustered columnstore index is optimized for large-scale analytical workloads and data warehousing, not for transactional systems with frequent point lookups or updates; it would degrade performance for the financial system's mixed workload. Option C is wrong because an elastic pool is a resource management feature for scaling multiple databases, not a design choice to improve query performance or archiving for a single table. Option D is wrong because transparent data encryption (TDE) provides security at rest but has no impact on query performance or data archiving capabilities.
Which TWO factors should you consider when choosing between Azure SQL Database and SQL Server on Azure Virtual Machines?
Managed instance offers high compatibility; virtual machine offers full compatibility.
Why this answer
Options A and C are correct. Managed service vs. IaaS is a key differentiator.
Feature compatibility is also important because managed instance offers high compatibility but not 100%. Option B is wrong because both services support TDE. Option D is wrong because both support geo-replication (Azure SQL Database has active geo-replication; SQL Server on VM can use Always On AG).
Option E is wrong because both support auditing.
You are designing a relational database for a multi-tenant SaaS application. Each tenant's data must be isolated for security and compliance. Which design approach best ensures data isolation while keeping cost manageable?
Provides strong isolation.
Why this answer
Option A is correct because a separate database per tenant provides strong isolation. Option B is wrong because shared tables with a TenantID column require careful row-level security. Option C is wrong because a separate schema per database is not a standard Azure feature.
Option D is wrong because Azure Synapse Analytics is for data warehousing, not OLTP.
A startup is building a web application with a relational database backend. They expect variable traffic and want to minimize costs by scaling the database automatically based on demand. Which Azure service should they use?
Serverless compute auto-scales and pauses, reducing cost for variable workloads.
Why this answer
Option C is correct because Azure SQL Database serverless automatically scales compute resources and pauses during inactivity to save costs. Option A (Azure SQL Managed Instance) is not serverless. Option B (SQL Server on Azure VM) requires manual scaling.
Option D (Azure Database for MariaDB) does not have serverless compute.
A SaaS company manages hundreds of customer databases, each representing a tenant. Each tenant database has its own predictable usage pattern, but the aggregate workload across all tenants is variable. The company wants to optimize costs by pooling compute resources across tenants while still ensuring that each tenant benefits from resource isolation under normal loads. Which Azure SQL Database deployment model should they choose?
Elastic pools allow multiple databases to share a pool of resources, providing cost savings for multi-tenant SaaS applications while maintaining predictable performance per database.
Why this answer
Elastic pools are designed for SaaS multi-tenant scenarios where each tenant has a predictable, low average usage but the aggregate workload across tenants is variable. They allow pooling of compute resources (eDTUs or vCores) across multiple databases, providing resource isolation under normal loads via per-database min/max resource limits, while optimizing cost by sharing unused capacity among tenants.
Exam trap
The trap here is that candidates often confuse 'resource isolation' with 'dedicated resources' and choose Single Database, failing to recognize that Elastic Pools provide isolation via per-database resource limits while still pooling compute for cost efficiency.
How to eliminate wrong answers
Option A is wrong because a single database model allocates dedicated compute resources to each database, which would be cost-inefficient for hundreds of small, predictable tenant databases as it does not allow resource pooling. Option C is wrong because Managed Instance is a fully managed instance of SQL Server with fixed resources, designed for lift-and-shift migrations, not for pooling compute across many small tenant databases. Option D is wrong because SQL Server on Azure Virtual Machine requires manual management of the VM and SQL Server, and does not provide built-in resource pooling or isolation for multi-tenant databases.
A company has an Azure SQL Database and needs to run a weekly data aggregation job that takes several hours. They want to minimize cost and avoid impacting production workload. Which approach should they use?
Read-only replica offloads read workloads without affecting the primary.
Why this answer
Creating a read-only replica allows the weekly aggregation job to run against a separate copy of the database without affecting the production workload. Since the replica is read-only, it incurs additional compute costs only during the aggregation window, and you can scale it down or stop it when not in use, minimizing overall cost.
Exam trap
The trap here is that candidates may confuse Azure Elastic Jobs as a workload isolation tool, when in fact it only schedules jobs on the same database and does not provide a separate compute resource.
How to eliminate wrong answers
Option A is wrong because migrating to the Hyperscale service tier is designed for large databases and high throughput, not for cost-effective batch processing; it increases cost and complexity without addressing the need to avoid impacting production. Option B is wrong because Azure Elastic Jobs is a scheduling service for running T-SQL scripts across multiple databases, but it does not isolate the workload from the production database; the aggregation would still run on the same primary database, impacting performance. Option C is wrong because increasing DTU or vCore size on the primary database would temporarily improve performance but would significantly increase cost and still risk impacting production workload during the aggregation run.
A company runs an e-commerce application on Azure SQL Database. During seasonal promotions, traffic spikes significantly, but at other times traffic is low. They want to automatically adjust compute resources based on demand without manual intervention or provisioning. Which Azure SQL Database feature should they use?
Serverless compute automatically scales compute based on demand and pauses when idle, fitting the described requirement.
Why this answer
Serverless compute for Azure SQL Database automatically scales compute resources based on workload demand and pauses the database during inactive periods, charging only for storage and compute used per second. This matches the requirement for automatic adjustment without manual intervention or provisioning, especially for intermittent, unpredictable traffic spikes like seasonal promotions.
Exam trap
The trap here is that candidates confuse Elastic pools (which scale shared resources across multiple databases) with the single-database auto-scaling behavior of Serverless compute, or they assume Hyperscale's high scalability automatically includes dynamic compute scaling without manual intervention.
How to eliminate wrong answers
Option A is wrong because Geo-replication is a disaster recovery and business continuity feature that creates readable replicas in different Azure regions, not an auto-scaling mechanism for compute resources. Option B is wrong because Elastic pools are designed for managing and scaling multiple databases with shared resources in a predictable pattern, not for automatically adjusting compute of a single database based on demand spikes. Option D is wrong because Hyperscale is a service tier for very large databases (up to 100 TB) with fast scaling of storage and compute, but it requires manual scaling of compute replicas and does not provide the automatic pause/resume or per-second billing of serverless compute.
A company uses Azure SQL Database for a sales application. They need to replicate the database to a secondary region for disaster recovery. The secondary should be readable for reporting purposes and data should be synchronized within seconds. Which feature should they use?
Active Geo-Replication provides a readable secondary replica that stays synchronized within seconds, enabling both DR and read-scale out.
Why this answer
Active Geo-Replication is the correct choice because it creates a readable secondary replica in a different Azure region, with data synchronized within seconds via asynchronous replication. This meets the requirement for both disaster recovery and read-only reporting access, as the secondary can be queried directly without impacting the primary database.
Exam trap
The trap here is that candidates often confuse Auto-failover groups with Active Geo-Replication, assuming the group feature provides faster synchronization, when in fact both use the same asynchronous replication and the key difference is that Auto-failover groups add automatic failover and endpoint management, not lower latency.
How to eliminate wrong answers
Option B (Auto-failover groups) is wrong because while it supports readable secondaries and automatic failover, it is designed for group-level failover of multiple databases and does not guarantee sub-second synchronization; it uses the same underlying geo-replication but adds orchestration, not faster sync. Option C (Point-in-time restore) is wrong because it restores a database to a past state from backups, not a continuously synchronized readable secondary for disaster recovery. Option D (Long-term retention) is wrong because it preserves backups for years for compliance, not for real-time replication or readable secondaries.
A company stores customer data in an Azure SQL Database. To comply with data residency requirements, they need to ensure that all customer data remains within a specific Azure region. Which feature should they use?
Azure Policy can enforce that SQL Database and its replicas are only created in the required region.
Why this answer
Option B is correct because Azure SQL Database geo-replication allows configuring a secondary replica in a different region, but the question asks to keep data within a specific region; however, the scenario is about ensuring data does not leave the region. Actually, to keep data within a region, you should not use geo-replication. But the correct answer is to use a single-region deployment; but among options, 'Geo-replication' would allow cross-region, so not that. 'Failover groups' also. 'Azure Policy' can enforce resource location, but not data. 'Data masking' is for security.
Wait, re-evaluating: The best answer is 'Azure Policy' to restrict resource creation to a region, but data still could be replicated? Actually, to ensure data remains within a region, you can use 'Geo-replication' to replicate to same region? No. Let's think: The requirement is to keep data within a specific region. Geo-replication is used for disaster recovery across regions, so that would violate.
The correct approach is to use Azure Policy to enforce that resources are only created in allowed regions, and also to disable geo-replication. Among options, 'Azure Policy' is the most direct. However, the stem implies a feature of SQL Database itself.
Actually, Azure SQL Database allows configuring a 'geo-replication' secondary in the same region? No, geo means different region. So 'Failover groups' also cross-region. 'Data masking' is irrelevant. 'Transparent Data Encryption' (TDE) is for encryption at rest, not residency. So 'Azure Policy' is the correct answer.
A company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), Status (varchar), and TotalAmount (decimal). The most frequent queries retrieve orders for a specific CustomerID within a date range and order the results by OrderDate. Additionally, single order lookups by OrderID must remain fast. Which indexing strategy best satisfies both requirements?
Correct. The clustered index on OrderID optimizes single row lookups. The non-clustered index on (CustomerID, OrderDate) supports range queries filtering on those columns. This combination provides optimal performance for both query patterns.
Why this answer
Option B is correct because a clustered index on OrderID ensures fast single-row lookups by the primary key, while a non-clustered index on (CustomerID, OrderDate) efficiently covers the range query filtering by CustomerID and ordering by OrderDate. This design leverages the clustered index for point lookups and the non-clustered index as a covering index for the most frequent query pattern.
Exam trap
The trap here is that candidates often assume the clustered index must match the most frequent query pattern, but they overlook that a clustered index on a non-unique column like CustomerID can cause fragmentation and slower point lookups, whereas the correct design separates the point-lookup and range-query concerns into two distinct indexes.
How to eliminate wrong answers
Option A is wrong because creating a clustered index on (CustomerID, OrderDate) would make single-order lookups by OrderID slower, as the clustered index determines the physical order and a non-clustered index on OrderID would require a key lookup into the clustered index, adding overhead. Option C is wrong because creating separate non-clustered indexes on each column does not provide a covering index for the range query; SQL Server would need to combine indexes via index intersection or perform key lookups, which is less efficient than a composite index on (CustomerID, OrderDate). Option D is wrong because a clustered columnstore index is optimized for large analytical scans and aggregations, not for point lookups or small range queries; it would degrade performance for the transactional workloads described.
A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows with a TransactionDate column. Queries frequently aggregate sales totals for the current month, but historical data must be retained for 7 years. Currently, queries scan the entire table, causing performance issues. The company also wants to simplify archiving of old data. Which design should they implement?
Partitioning enables partition elimination for queries filtering on TransactionDate, reducing scan size. Old partitions can be switched out for easy archiving without impacting the live table.
Why this answer
Table partitioning by month on TransactionDate allows Azure SQL Database to efficiently manage and query large tables by splitting data into manageable segments. Queries that filter on TransactionDate for the current month will only scan the relevant partition(s), eliminating full table scans. Additionally, partitioning simplifies archiving by enabling swift partition switching to move old data to archive tables without complex ETL processes.
Exam trap
The trap here is that candidates often choose a non-clustered index (Option A) thinking it will speed up range queries, but they overlook that partitioning is specifically designed for both performance on large tables and simplified data lifecycle management, which the question explicitly requires.
How to eliminate wrong answers
Option A is wrong because a non-clustered index on TransactionDate would still require key lookups for non-indexed columns and does not eliminate scanning all partitions of historical data; it also does not simplify archiving. Option B is wrong because a materialized view for the current month's data would require manual maintenance and does not address the need to retain and efficiently query 7 years of historical data; it also does not simplify archiving of old data. Option D is wrong because a clustered columnstore index is optimized for analytical workloads on large tables but does not inherently partition data by month, so queries for the current month would still scan all column segments, and it does not provide a built-in mechanism for archiving old data.
A multinational corporation is deploying a global application using Azure SQL Database. They need to ensure that users in different geographic regions experience low latency reads. The application can tolerate slightly stale data for reads, but writes must be strongly consistent and must occur in a single primary region. Which feature should they implement?
Active geo-replication provides readable secondaries in different regions for low-latency reads.
Why this answer
Option C is correct because active geo-replication allows creating readable secondaries in other regions for low-latency reads, while writes go to the primary. Option A is wrong because failover groups provide automatic failover but not necessarily low-latency reads from multiple regions. Option B is wrong because Cosmos DB with multi-master is NoSQL and not relational.
Option D is wrong because read scale-out uses local replicas, not global.
A banking application processes fund transfers. When a transfer is executed, the system must either successfully debit one account and credit the other, or if any step fails, the entire operation must be rolled back so no partial changes remain. Which ACID property directly enforces this behavior?
Atomicity ensures the entire transaction is completed or fully rolled back, preventing partial updates.
Why this answer
Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this banking scenario, the debit and credit operations are part of one transaction; if either step fails, the entire transaction is rolled back, leaving no partial changes. This is the core property that enforces the 'all-or-nothing' behavior described.
Exam trap
The trap here is that candidates confuse Consistency with Atomicity, thinking that 'keeping data consistent' means the same as 'all-or-nothing rollback,' but Consistency only enforces rules like constraints and triggers, not the indivisible execution of a multi-step operation.
How to eliminate wrong answers
Option B (Consistency) is wrong because consistency ensures that a transaction brings the database from one valid state to another, enforcing integrity constraints (e.g., account balances must never go negative), but it does not guarantee the all-or-nothing rollback of the entire operation. Option C (Isolation) is wrong because isolation controls how concurrent transactions are executed to prevent interference (e.g., dirty reads), but it does not enforce the atomic rollback of a failed multi-step transfer. Option D (Durability) is wrong because durability guarantees that once a transaction is committed, its changes persist even after a system failure, but it has no role in rolling back a failed transaction.
A company stores customer orders in an Azure SQL Database. They need to ensure that the database can automatically scale to handle peak loads without manual intervention. Which Azure feature should they use?
Serverless compute automatically scales compute and pauses during inactivity.
Why this answer
Option B is correct because Azure SQL Database's serverless compute tier automatically scales compute resources based on workload demand, pausing during inactivity to save costs. Option A is wrong because elastic pools are for managing multiple databases with shared resources, not automatic scaling of a single database. Option C is wrong because read replicas are for read scalability, not compute scaling.
Option D is wrong because reserved capacity is a pricing model, not a scaling feature.
A global e-commerce company uses Azure SQL Database for its order management system. They need to ensure high availability with the ability to fail over to an Azure region in a different continent in case of a regional outage. They also want to use the secondary database for read-intensive reporting without affecting the primary's performance. Which Azure SQL Database feature should they enable?
Active geo-replication creates a readable secondary database in a different Azure region. It allows failover and offloads read-heavy workloads to the secondary. The secondary is readable and can be used for reporting.
Why this answer
Active geo-replication is the correct choice because it creates readable secondary replicas of an Azure SQL Database in a different Azure region (including a different continent). It supports manual failover to the secondary region during an outage, and the secondary can be used for read-only query workloads like reporting without impacting the primary database's performance.
Exam trap
The trap here is that candidates may confuse 'geo-replication' with 'failover groups' or assume that any backup feature (like long-term retention) can serve as a high-availability solution, but only active geo-replication provides a readable secondary in a different continent for both failover and read-scale.
How to eliminate wrong answers
Option B (Long-term backup retention) is wrong because it only preserves database backups for extended periods (up to 10 years) for compliance or recovery, not for real-time failover or read-scale. Option C (Automatic tuning) is wrong because it optimizes query performance through index and plan recommendations, not for high availability or geo-failover. Option D (Connection pooling) is wrong because it manages client-side database connections to reduce latency and resource usage, but does not provide any regional redundancy or read-scale capability.
Your company is migrating a legacy on-premises SQL Server database to Azure. The database is used by a mission-critical application that requires high availability with automatic failover to a secondary region. The database size is 2 TB and is expected to grow 20% annually. The application uses stored procedures, functions, and SQL Server Agent jobs. You need to select an Azure relational database service that meets the high availability requirements, supports the existing SQL Server features, and minimizes migration effort. What should you choose?
SQL Managed Instance supports SQL Server Agent, stored procedures, and provides automatic failover to a secondary region.
Why this answer
Option C is correct because Azure SQL Managed Instance provides high availability with automatic failover, supports SQL Server Agent, and offers near 100% compatibility. Option A is wrong because Azure SQL Database does not support SQL Server Agent. Option B is wrong because Azure SQL Database elastic pools are for multiple databases, not high availability.
Option D is wrong because Azure Database for SQL Server is not a recognized service.
An e-commerce application uses Azure SQL Database and stores user session data in a table called Sessions. The table contains millions of rows and queries often filter by UserID and LastActivityTime. The development team wants to improve query performance for these filters. What should they implement?
Nonclustered index on these columns directly supports the filter queries.
Why this answer
Option C is correct because an index on (UserID, LastActivityTime) speeds up filters on both columns. Option A is wrong because a clustered index organizes data physically but does not specifically optimize these filters. Option B is wrong because partitioning can help manage large tables but not directly optimize filter queries.
Option D is wrong because views are virtual and do not inherently improve performance without indexes.
A company is migrating a 3-TB on-premises SQL Server database to Azure. The database heavily uses cross-database queries with three-part names (e.g., db.schema.table) and relies on SQL Server Agent for scheduled maintenance jobs. They want a fully managed PaaS service with automatic backups and patching, while minimizing application code changes. Which Azure SQL service should they choose?
Azure SQL Managed Instance supports three-part cross-database queries and SQL Server Agent, offers high compatibility, and is fully managed.
Why this answer
Azure SQL Managed Instance is the correct choice because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries using three-part names (db.schema.table) and SQL Server Agent for scheduled maintenance jobs. As a fully managed PaaS service, it offers automatic backups, patching, and high availability while minimizing application code changes, unlike Azure SQL Database which lacks cross-database query support and SQL Agent.
Exam trap
The trap here is that candidates often choose Azure SQL Database (single or elastic pool) because it is the most well-known PaaS option, overlooking that it lacks critical on-premises features like cross-database three-part name queries and SQL Server Agent, which are essential for minimizing code changes in this migration scenario.
How to eliminate wrong answers
Option B (Azure SQL Database single database) is wrong because it does not support cross-database queries with three-part names (requires elastic query or external tables) and lacks SQL Server Agent for scheduled jobs. Option C (Azure SQL Database elastic pool) is wrong because it inherits the same limitations as single databases—no cross-database three-part name queries and no SQL Server Agent—and is primarily for resource pooling, not compatibility. Option D (Azure Synapse Analytics dedicated SQL pool) is wrong because it is a massively parallel processing (MPP) data warehouse service, not a general-purpose OLTP database; it does not support cross-database queries with three-part names or SQL Server Agent, and would require significant application rewrites.
A company is evaluating deployment options for a new business application that requires a fully managed relational database. The application must support high availability with automatic failover and horizontal scaling for read-heavy workloads. The development team wants to minimize administrative overhead and prefers a PaaS solution that offers built-in read scale-out. Which Azure SQL deployment option should they choose?
This is a fully managed PaaS service that, in the Premium or Business Critical tiers, provides up to four readable secondary replicas for read scale-out. It also supports automatic failover with zone-redundant configurations, meeting the high availability and read scaling needs with minimal administration.
Why this answer
Azure SQL Database (single database) is the correct choice because it is a fully managed PaaS relational database that supports high availability with automatic failover (99.99% SLA) and built-in read scale-out via read-only replicas (Active Geo-Replication and failover groups). It minimizes administrative overhead by handling patching, backups, and replication automatically, and it allows horizontal scaling for read-heavy workloads by offloading read traffic to secondary replicas without manual configuration.
Exam trap
The trap here is that candidates often confuse Azure SQL Managed Instance's high compatibility with full PaaS features, overlooking that it does not support built-in read scale-out, which is a key requirement for read-heavy workloads.
How to eliminate wrong answers
Option A is wrong because SQL Server on Azure Virtual Machines is an IaaS solution that requires manual configuration of high availability (e.g., Always On Availability Groups) and read scale-out, and it does not offer built-in automatic failover or PaaS-level administrative overhead reduction. Option B is wrong because Azure SQL Managed Instance provides near-100% SQL Server compatibility but lacks built-in read scale-out (read-only replicas are not supported for read-heavy workloads; it uses only a single primary replica). Option D is wrong because Azure SQL Database Elastic Pool is designed for managing multiple databases with shared resources and cost optimization, not for providing built-in read scale-out or automatic failover for a single database; it inherits the same read-scale limitations as single databases but does not add horizontal read scaling.
A company has both transactional and analytical workloads on the same SQL Server database. They want to move to Azure and separate these workloads to improve performance. They need a solution that supports both workloads without duplicating data. What should they do?
Synapse Link provides real-time replication without ETL.
Why this answer
Option D is correct because Azure Synapse Link for SQL enables real-time replication of transactional data from Azure SQL Database to Synapse Analytics for analytical queries without ETL. Option A is wrong because moving to Azure SQL Database alone doesn't separate workloads. Option B is wrong because using Azure SQL Database and SQL Data Warehouse separately would require data duplication.
Option C is wrong because Azure Data Factory is for ETL, not real-time replication.
A company runs an e-commerce application on Azure SQL Database. The database has a table named Orders with columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), TotalAmount (decimal). The application frequently runs the following query: SELECT * FROM Orders WHERE CustomerID = 12345 AND OrderDate BETWEEN '2025-01-01' AND '2025-01-31' ORDER BY OrderDate DESC. The table contains 10 million rows. Which index would best optimize this query?
This composite index covers both filter conditions (CustomerID equality, OrderDate range) and includes TotalAmount to avoid key lookups. The descending order helps with ORDER BY OrderDate DESC without additional sorting.
Why this answer
Option B is correct because the query filters on both CustomerID and OrderDate, so a composite nonclustered index on (CustomerID, OrderDate DESC) allows SQL Server to perform an index seek on CustomerID and then an ordered range scan on OrderDate, avoiding a sort operation. Including TotalAmount as an included column makes the index covering, so the query can be satisfied entirely from the index without key lookups to the clustered index.
Exam trap
The trap here is that candidates often think a single-column index on the most selective column (OrderDate) is sufficient, but they overlook that the query's equality filter on CustomerID must be the leading key column to enable an efficient seek, and that including the SELECT column avoids key lookups.
How to eliminate wrong answers
Option A is wrong because an index on OrderDate only would require scanning all rows for the given CustomerID, as the filter on CustomerID cannot use the index, leading to a full scan or inefficient partial scan. Option C is wrong because a clustered index on (OrderDate, CustomerID) would order the entire table by OrderDate first, making seeks on CustomerID inefficient and requiring a scan of all rows for that date range; also, changing the clustered index from the primary key (OrderID) could impact other queries and insert performance. Option D is wrong because an index on OrderDate DESC only suffers the same issue as Option A: it cannot efficiently locate rows for a specific CustomerID, resulting in a scan or bookmark lookup.
An e-commerce company runs a transactional database on Azure SQL Database. During peak shopping seasons, they experience performance degradation due to high read traffic on product catalog tables. The company wants to offload read queries to a read-only copy of the database without affecting write performance. What should they implement?
Active Geo-Replication provides readable secondaries that can handle read-only queries.
Why this answer
Option A is correct because Active Geo-Replication allows creating readable secondary replicas in the same or different region. Option B (Failover groups) is for disaster recovery, not read scaling. Option C (Read Scale-Out) is a feature of Azure SQL Database that automatically routes read queries to a secondary replica, but it is not available in all service tiers; the correct feature name is Active Geo-Replication for readable secondaries.
Option D (Azure Cache for Redis) adds caching but not a read-only copy of the database.
You are the database administrator for a global e-commerce company that uses Azure SQL Database. The company has a single database in the West US region. The database hosts a table named Orders with 500 million rows. The table is clustered on OrderId (uniqueidentifier). The most critical query is a daily report that aggregates total sales by product category for the previous day. This query currently takes over 30 minutes to run and causes performance degradation on the primary database. The report must be available by 6:00 AM local time each day, and the data must be no more than 24 hours old. You need to design a solution that minimizes impact on the transactional workload and improves report query performance. What should you do?
A readable secondary replica offloads the reporting workload from the primary, and you can create indexes on the replica without affecting the primary.
Why this answer
Option D is correct because creating a read replica offloads the reporting query from the primary and allows indexing specifically for the report. Option A is wrong because adding indexes on the primary may degrade write performance. Option B is wrong because partitioning alone won't isolate the reporting workload.
Option C is wrong because nonclustered columnstore index on the primary still impacts transactional workload.
A company is migrating a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance, relies on linked servers to query data from another SQL Server instance, and requires cross-database queries within the same instance. The company wants a fully managed PaaS service that minimizes application code changes and provides automatic backups and patching. Which Azure SQL service should they choose?
Azure SQL Managed Instance provides built-in support for SQL Server Agent, linked servers, and cross-database queries, with automatic backups and patching, while being fully managed PaaS.
Why this answer
Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. As a fully managed PaaS service, it offers automatic backups, patching, and high availability while minimizing application code changes, unlike Azure SQL Database which lacks instance-scoped features.
Exam trap
The trap here is that candidates often confuse Azure SQL Database (which is database-level PaaS) with Azure SQL Managed Instance (which is instance-level PaaS), overlooking that linked servers, Agent jobs, and cross-database queries require instance-scoped functionality not available in Azure SQL Database.
How to eliminate wrong answers
Option A is wrong because Azure SQL Database (Single Database) does not support SQL Server Agent jobs, linked servers, or cross-database queries within the same instance; it is a database-level PaaS service, not instance-scoped. Option B is wrong because Azure SQL Database (Elastic Pool) shares the same limitations as Single Database—it still lacks instance-level features like Agent jobs and linked servers, and only provides resource pooling for multiple databases. Option D is wrong because SQL Server on Azure Virtual Machine is an IaaS service, not fully managed PaaS; it requires the customer to manage patching, backups, and high availability, contradicting the requirement for a fully managed service.
A company has an Azure SQL Database with an 'Orders' table containing millions of rows. The table has a clustered index on OrderID (primary key). Queries frequently filter by CustomerID (equality) and OrderDate (range). These queries are slow and cause high logical reads. Which index strategy will most improve performance for these specific queries?
This composite index matches the query pattern exactly: it allows index seek on CustomerID and then range scan on OrderDate, providing optimal performance.
Why this answer
A non-clustered index on (CustomerID, OrderDate) is a covering index for queries filtering by CustomerID (equality) and OrderDate (range). It allows SQL Server to perform an index seek on CustomerID, then a range scan on OrderDate, retrieving all needed columns without touching the clustered index (if the query is covered). This dramatically reduces logical reads compared to a full clustered index scan or a key lookup.
Exam trap
The trap here is that candidates often think a filtered index or a single-column index is sufficient, but they overlook that the query has both an equality and a range predicate, requiring a composite index that supports both in the correct order (equality first, range second) to achieve optimal seek + range scan performance.
How to eliminate wrong answers
Option B is wrong because rebuilding the clustered index on (OrderDate, CustomerID) would change the physical order of the table, but the primary key (OrderID) must remain unique and clustered; altering the clustered index to a non-unique key violates best practices and would require a separate unique constraint. Option C is wrong because a single-column index on OrderDate only supports range scans on date, but still requires key lookups to filter by CustomerID, leading to high logical reads. Option D is wrong because a filtered index on OrderDate for recent dates only helps queries with a date predicate on recent rows; it does not support the CustomerID equality filter and misses older data, so it is not a general solution for the described workload.
A retail company uses Azure SQL Database to store inventory data. They notice excessive blocking and deadlocks during peak hours. Which design change would best reduce these issues?
Indexes reduce scan times, thereby shortening lock duration and reducing contention.
Why this answer
Adding appropriate indexes reduces the number of rows scanned during queries, which shortens lock duration and lowers the chance of blocking and deadlocks. In Azure SQL Database, indexes help queries become more efficient by using seeks instead of scans, minimizing the time locks are held on resources.
Exam trap
The trap here is that candidates often confuse scaling up (more resources) with performance tuning, but the DP-900 exam tests understanding that blocking and deadlocks are primarily caused by inefficient query execution, not insufficient hardware.
How to eliminate wrong answers
Option A is wrong because read replicas offload reporting traffic but do not reduce blocking or deadlocks on the primary database; they only separate read workloads. Option B is wrong because READ UNCOMMITTED avoids blocking by reading dirty data but does not reduce deadlocks or blocking for write operations, and it introduces data consistency issues. Option D is wrong because scaling up to a higher service objective increases resources (CPU, IO, memory) but does not address the root cause of inefficient queries that cause long-held locks.
A company is migrating a 1.5 TB on-premises SQL Server database to Azure. The database relies on SQL Server Agent jobs for daily ETL processes and uses linked servers to query data from another on-premises SQL Server database. The company wants a fully managed PaaS service that requires minimal application changes. Which Azure SQL service should they choose?
Correct. Azure SQL Managed Instance supports SQL Server Agent, linked servers, and provides high compatibility with on-premises SQL Server while being a fully managed PaaS service.
Why this answer
Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and linked servers, while being a fully managed PaaS service. This minimizes application changes, as the migration can leverage the existing database code and features without significant rework.
Exam trap
The trap here is that candidates often choose Azure SQL Database because it is the most well-known PaaS option, failing to recognize that its lack of SQL Server Agent and linked server support would require significant application changes, which the question explicitly wants to minimize.
How to eliminate wrong answers
Option A is wrong because Azure SQL Database is a PaaS service but lacks support for SQL Server Agent jobs and linked servers, requiring significant application changes to replace these features with alternatives like elastic jobs or external tables. Option C is wrong because SQL Server on Azure Virtual Machines is an IaaS service, not fully managed PaaS, requiring the company to manage the VM and SQL Server, including patching and backups, which contradicts the requirement for minimal management overhead. Option D is wrong because Azure Synapse Analytics is a distributed analytics service designed for large-scale data warehousing and big data workloads, not for transactional OLTP workloads with SQL Server Agent jobs and linked servers, and would require major application redesign.
A company uses Azure SQL Database for its e-commerce platform. During a traffic spike, queries against the Orders table become slow. The table has 10 million rows and is clustered on OrderId. The most common query filters by CustomerId and OrderDate range. Which index change would most improve performance?
This index supports efficient seek on CustomerId and range scan on OrderDate, directly addressing the slow query.
Why this answer
Option D is correct because a nonclustered index on (CustomerId, OrderDate) includes the columns used in the WHERE clause, enabling a seek operation. Option A is wrong because a clustered index on OrderId does not help queries filtering by CustomerId. Option B is wrong because a nonclustered index on (OrderDate, CustomerId) is less selective if OrderDate is high-cardinality.
Option C is wrong because partitioning by OrderId does not help queries filtering by CustomerId.
A company uses Azure SQL Database for an e-commerce application. They need to ensure that if the primary region fails, the database can be failed over to a secondary region with minimal data loss. Which feature should they enable?
Active geo-replication provides a readable secondary in another region with low RPO.
Why this answer
Option A is correct because active geo-replication creates readable secondary replicas in a different region and supports failover with a configurable recovery point objective (RPO) of up to 5 seconds. Option B is wrong because auto-failover groups build on active geo-replication and enable automatic failover, but the core feature for minimal data loss is geo-replication. Option C is wrong because backup storage redundancy (RA-GRS) is for backups, not live failover.
Option D is wrong because point-in-time restore restores to a specific time, not for region failover.
A company runs a financial application on Azure SQL Database. The Transactions table has a clustered columnstore index to support fast analytical queries on large historical datasets. However, the application also ingests a high volume of new transactions each second, and the columnstore index is causing performance degradation for these real-time inserts. The workload is hybrid (OLTP and OLAP). Which feature should the company implement to improve insert performance while still enabling efficient analytical queries on the table?
A nonclustered columnstore index on a rowstore table allows efficient OLTP inserts into the rowstore while the columnstore index periodically processes batches for analytical performance, achieving a balanced hybrid workload.
Why this answer
Option D is correct because converting the table to a rowstore heap with a nonclustered columnstore index allows the table to handle high-volume singleton inserts efficiently (rowstore heap) while still enabling fast analytical queries via the nonclustered columnstore index. This hybrid approach separates the OLTP insert path from the OLAP read path, avoiding the overhead of maintaining a clustered columnstore index during real-time ingestion.
Exam trap
The trap here is that candidates often assume a clustered columnstore index is always the best choice for analytical queries, overlooking the significant insert performance penalty it imposes on high-volume OLTP workloads, and fail to recognize that a nonclustered columnstore index on a heap can provide the same analytical benefits without the insert bottleneck.
How to eliminate wrong answers
Option A is wrong because In-memory OLTP is designed to accelerate OLTP transactions by storing tables in memory, but it does not directly address the performance degradation caused by a clustered columnstore index during inserts; it would require redesigning the table as a memory-optimized table and does not inherently support columnstore analytics. Option B is wrong because Elastic Query is used to query data across multiple Azure SQL databases or external data sources, not to improve insert performance on a single table. Option C is wrong because the Hyperscale service tier provides scalable storage and compute for large databases, but it does not change the fundamental behavior of a clustered columnstore index; inserts into a clustered columnstore index still incur overhead from delta store management and compression.
Your team is designing a database for a financial application that requires ACID transactions. You are considering Azure Cosmos DB and Azure SQL Database. Which service should you choose?
Provides full ACID transactions.
Why this answer
Option B is correct because Azure SQL Database provides full ACID transactions. Option A is wrong because Azure Cosmos DB provides limited transactional support. Option C is wrong because Azure Database for MySQL provides ACID but the question asks between Cosmos DB and SQL Database.
Option D is wrong because Azure Table Storage does not support ACID.
Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database deployment. What is the maximum size (in GB) of the database?
268435456000 bytes = 250 GB.
Why this answer
Option B is correct because 268435456000 bytes equals 250 GB (268435456000 / (1024^3) = 250). Option A is wrong because 268 GB would be 268435456000 bytes? Actually 268 GB is close but 250 GB is exact. Option C is wrong because 256 GB would be 274877906944 bytes.
Option D is wrong because 500 GB would be 536870912000 bytes.
An e-commerce application uses Azure SQL Database. The Orders table stores millions of rows with columns: OrderID (primary key, clustered index), CustomerID, OrderDate, OrderStatus, TotalAmount. Queries frequently filter on OrderDate and OrderStatus, and sort results by OrderDate DESC. Which indexing strategy will most improve query performance for these filters and sort?
A composite nonclustered index on (OrderDate, OrderStatus) allows the query to filter on both columns efficiently and provides the data already sorted by OrderDate (the leading key). Including other columns avoids key lookups, making the query even faster.
Why this answer
Option B is correct because a nonclustered index on (OrderDate, OrderStatus) supports both the filter and the sort in a single index seek/scan. SQL Server can use the index to locate rows matching both predicates and return them already sorted by OrderDate DESC without a separate sort operation, which is critical for performance on millions of rows.
Exam trap
The trap here is that candidates often think a single-column index on the most filtered column (OrderDate) is sufficient, overlooking that the second filter (OrderStatus) and the sort order require a composite index to avoid extra processing.
How to eliminate wrong answers
Option A is wrong because an index on OrderDate only does not cover the OrderStatus filter, forcing key lookups or a full scan to evaluate the status predicate, which is inefficient for large tables. Option C is wrong because a clustered columnstore index is optimized for analytical/aggregation workloads, not for point lookups or ordered retrieval of individual rows; it would degrade performance for the described transactional queries. Option D is wrong because an index on OrderStatus only does not help with the OrderDate sort, requiring a separate sort operation after filtering, and it does not support the date range filter efficiently.
A company plans to migrate an on-premises SQL Server database to Azure. The database currently uses SQL Server Agent jobs for scheduled maintenance tasks, cross-database queries, and query store for performance tuning. The database size is 500 GB and needs to scale to 10 TB eventually. They want a managed service that requires minimal application changes. Which Azure relational database service should they choose?
Azure SQL Managed Instance offers the highest compatibility with on-premises SQL Server, supporting SQL Agent, cross-database queries within the instance, and Query Store. It provides up to 16 TB of storage, meeting the size requirements.
Why this answer
Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, cross-database queries, and Query Store, while being a fully managed PaaS service. It allows scaling up to 10 TB (up to 16 TB with some configurations) with minimal application changes, as it uses the same T-SQL surface area and network configuration (VNet) as on-premises SQL Server.
Exam trap
The trap here is that candidates often confuse Azure SQL Database (single database) with SQL Managed Instance, assuming that Hyperscale's large storage capacity compensates for missing features like SQL Server Agent and cross-database queries, but the exam tests the specific feature requirements (Agent jobs, cross-database queries) that only Managed Instance fully supports.
How to eliminate wrong answers
Option B (Azure SQL Database single database) is wrong because it does not support SQL Server Agent jobs or cross-database queries (except via elastic queries or external tables), and its maximum size is 4 TB (or 100 TB with Hyperscale, but still lacks Agent and cross-database support). Option C (Azure SQL Database Hyperscale) is wrong because, while it supports large databases up to 100 TB, it does not support SQL Server Agent jobs or cross-database queries, and it requires application changes for connection strings and some T-SQL features. Option D (Azure Database for SQL Server) is wrong because this is not a real Azure service; the correct name is Azure Database for SQL Server (which is actually a marketing term for SQL Server on Azure VMs) or Azure SQL Database, but as a distinct service, it does not exist — the intended trap is confusing it with SQL Server on Azure VMs, which is IaaS, not a managed service.
A library management system uses Azure SQL Database. The Books table has 500,000 rows with columns: BookID (primary key, clustered), Title, Author, ISBN, PublishedYear, CopiesAvailable. Queries frequently filter by Author and then sort results by PublishedYear in descending order. The queries also return the Title and CopiesAvailable columns. Which indexing strategy will most improve query performance for these operations?
This covering index supports the filter and sort without accessing the base table.
Why this answer
Option A is correct because it creates a covering nonclustered index on (Author, PublishedYear DESC) that directly supports the filter (Author) and sort (PublishedYear DESC) operations. Including Title and CopiesAvailable as non-key columns makes the index covering, meaning all required columns are in the index leaf level, so SQL Server can satisfy the query entirely from the index without key lookups to the clustered index. This minimizes I/O and improves query performance.
Exam trap
The trap here is that candidates often think any index on the filtered column (Author) is sufficient, overlooking the need to also cover the sort order and include all returned columns to avoid key lookups.
How to eliminate wrong answers
Option B is wrong because an index on Author only would support the filter but not the sort on PublishedYear DESC, requiring a separate sort operation in the query plan. Option C is wrong because an index on PublishedYear DESC alone does not support the filter on Author, so SQL Server would still need to scan or seek on Author separately. Option D is wrong because the existing clustered index on BookID is not useful for filtering by Author or sorting by PublishedYear, leading to a full table scan and sort.
Which THREE are valid reasons to choose Azure SQL Managed Instance over Azure SQL Database?
Managed Instance supports SQL Agent and CLR, which are not available in Azure SQL Database single database.
Why this answer
Options A, B, and D are correct. Managed Instance offers near 100% compatibility with SQL Server, supports SQL Server Agent, and provides a virtual network for secure integration. Option C is wrong because both services support automated backups.
Option E is wrong because both can be configured for high availability; Managed Instance does not necessarily have a simpler HA setup.
A financial services company runs a critical application on Azure SQL Managed Instance. They need to ensure that in the event of a regional outage, the database can be failed over to a secondary region with minimal data loss and automatic failover. The secondary region should not be used for read traffic during normal operations. Which configuration meets these requirements?
Failover groups with automatic failover and readable secondary disabled meet the requirements.
Why this answer
Option A is correct because failover groups with automatic failover and readable secondary set to 'No' provide disaster recovery with minimal data loss and no read traffic on secondary. Option B is wrong because active geo-replication allows read-only access on secondary. Option C is wrong because automatic failover groups require readable secondary to be enabled for automatic failover.
Option D is wrong because backup restore does not provide automatic failover and has higher data loss.
Your organization is migrating a large on-premises SQL Server database to Azure SQL Database. The database is used by a critical line-of-business application that requires near-zero downtime during migration. The application uses a mix of read and write operations, and you need to minimize the cutover time. The database is about 2 TB in size. You plan to use Azure Database Migration Service (DMS) for the migration. The source SQL Server is version 2019 Enterprise Edition. The target is a Business Critical tier Azure SQL Database. You need to choose the appropriate migration method and configuration. Which of the following actions should you take?
Online mode provides near-zero downtime; Premium tier handles large volumes efficiently.
Why this answer
Option C is correct because using online migration mode with continuous sync allows near-zero downtime by replicating changes from source to target, and using the Premium tier for DMS provides better performance for large databases. Option A is wrong because offline migration causes downtime. Option B is wrong because using Basic tier DMS is too slow for 2 TB.
Option D is wrong because migrating to Managed Instance is not necessary and may not align with requirements.
A company has 12 SQL Server databases, each about 30 GB. The databases experience unpredictable load spikes during the day. The company wants to migrate to Azure SQL Database to reduce administrative overhead and optimize costs by sharing resources among the databases. Which deployment option should they choose?
Elastic pools allow databases to share resources, reducing cost and handling unpredictable spikes efficiently.
Why this answer
Elastic pools are designed to share resources (eDTUs or eVCores) among multiple databases with unpredictable, overlapping load spikes. By pooling resources, the company can optimize costs because databases do not all peak simultaneously, and the pool’s total resource allocation is lower than the sum of individual peak requirements. This reduces administrative overhead by providing a single management point for scaling and monitoring all databases in the pool.
Exam trap
The trap here is that candidates often choose Single Database (Option A) thinking it is simpler, but they miss that elastic pools are specifically designed for cost optimization when multiple databases have variable and overlapping load patterns, not for isolated workloads.
How to eliminate wrong answers
Option A is wrong because a single database with provisioned DTU would require each database to be sized for its peak load, leading to over-provisioning and higher costs, and it does not share resources across databases. Option C is wrong because Managed Instance is a full SQL Server instance in Azure, which still requires managing instance-level resources and does not provide the same resource-sharing efficiency as an elastic pool; it is also overkill for 12 small databases and incurs higher administrative overhead. Option D is wrong because SQL Server on Azure Virtual Machine requires full administrative control over the OS and SQL Server, negating the goal of reducing administrative overhead, and it does not offer built-in resource sharing or elastic scaling across databases.
A software company develops a multi-tenant SaaS application. They deploy a separate Azure SQL Database for each tenant. The databases are small (2-5 GB) and have highly variable loads — some tenants use the app heavily during the day, others at night. The company wants to maximize resource utilization and minimize costs by allowing databases to share a pool of resources, while still maintaining a predictable performance per database. Which Azure SQL Database deployment option should they choose?
Correct. Elastic pools share resources across multiple databases, ideal for variable, low-average usage patterns, and provide cost savings.
Why this answer
C is correct because an elastic pool allows multiple Azure SQL databases with variable and unpredictable usage patterns to share a fixed pool of resources (eDTUs or vCores), maximizing resource utilization and minimizing cost. The pool provides a predictable performance per database through per-database min/max resource limits, which is ideal for the described multi-tenant SaaS scenario with small databases and highly variable loads.
Exam trap
The trap here is that candidates often confuse elastic pools with single databases, thinking that the vCore model alone provides elasticity, but vCore single databases still allocate dedicated resources per database and lack the shared-pool cost benefit that elastic pools offer for multi-tenant SaaS workloads.
How to eliminate wrong answers
Option A is wrong because a single database with the DTU purchasing model allocates dedicated resources to one database, which cannot be shared across tenants, leading to wasted capacity and higher costs when loads are variable. Option B is wrong because a single database with the vCore purchasing model also provides dedicated resources per database, lacking the resource pooling and cost efficiency needed for many small databases with intermittent usage. Option D is wrong because Azure SQL Managed Instance is a fully managed instance of SQL Server with fixed resources, designed for lift-and-shift migrations or complex enterprise workloads, not for pooling many small databases with variable loads.
A company uses Azure SQL Database and needs to run complex analytical queries that scan large amounts of data. The queries are experiencing performance issues. Which Azure service should they use to offload the analytical workload?
It is designed for analytical workloads.
Why this answer
Option C is correct because Azure SQL Data Warehouse (now Synapse Analytics dedicated SQL pool) is optimized for analytical workloads. Option A is wrong because Azure Analysis Services is for semantic models, not direct query offloading. Option B is wrong because Azure SQL Database is transactional, not analytical.
Option D is wrong because Azure Data Lake Storage is for data lakes, not relational analytics.
A company uses Azure SQL Database for its e-commerce platform. The reporting team runs complex, long-running queries that join multiple tables and would degrade performance of the transactional workload if executed on the primary database. Which Azure SQL Database feature should the company enable to isolate the reporting queries while ensuring read-only access to the most current data?
Read scale-out provides a read-only replica in the same region that can be used for reporting without impacting the primary.
Why this answer
Read scale-out (read-only replicas) allows you to offload reporting queries to a read-only replica of the Azure SQL Database, ensuring that complex, long-running queries do not degrade the performance of the primary transactional workload. The replica provides access to the most current data because it uses snapshot isolation and is transactionally consistent with the primary database.
Exam trap
The trap here is that candidates often confuse Geo-replication (which also provides readable secondaries) with read scale-out, but Geo-replication is primarily for disaster recovery and involves asynchronous replication, whereas read scale-out is designed for read workload isolation within the same region with synchronous consistency.
How to eliminate wrong answers
Option A is wrong because Automatic tuning is a performance optimization feature that automatically adjusts index creation, query plan choices, and other settings based on workload patterns, but it does not isolate read-only workloads or provide a separate replica. Option B is wrong because Geo-replication creates readable secondary replicas in a different Azure region for disaster recovery and geographic distribution, but it is not designed for read-scale out within the same region and introduces latency for current data due to asynchronous replication. Option D is wrong because Elastic query enables cross-database queries across multiple Azure SQL databases, but it does not provide a dedicated read-only replica for offloading queries from a single database.
A SaaS provider hosts databases for hundreds of clients, each with light and sporadic usage. They notice that using separate single databases with provisioned DTUs is cost-inefficient. They need to consolidate the databases while optimizing cost and ensuring that a busy client does not monopolize resources. Which Azure SQL Database option should they use?
Elastic pools consolidate multiple databases into a shared resource pool. They are designed for SaaS applications with many databases having low average usage and unpredictable spikes, providing cost savings and performance isolation.
Why this answer
An Azure SQL Database elastic pool is designed to optimize cost for multiple databases with light and sporadic usage by sharing a fixed set of resources (eDTUs) across all databases in the pool. This prevents a busy client from monopolizing resources through per-database resource limits (min and max DTU), ensuring performance isolation while consolidating costs.
Exam trap
The trap here is that candidates often confuse elastic pools with single databases, thinking that provisioned DTUs are always cheaper for sporadic workloads, but they miss that elastic pools share resources across databases to eliminate over-provisioning waste.
How to eliminate wrong answers
Option A is wrong because a single database with provisioned DTUs allocates dedicated resources per database, which is cost-inefficient for hundreds of lightly used databases due to wasted capacity and higher per-database costs. Option C is wrong because Azure SQL Managed Instance is a fully managed instance with fixed resources, designed for lift-and-shift migrations, not for consolidating hundreds of small databases with sporadic usage; it lacks the elastic pooling feature for shared resource allocation. Option D is wrong because SQL Server on Azure VM requires manual management of OS and SQL Server, including resource governance, and incurs costs for the VM and licensing, making it more expensive and complex than an elastic pool for this scenario.
A company is migrating an on-premises SQL Server database to Azure. They want to ensure that database administrators (DBAs) can perform administrative tasks but cannot view sensitive customer data in query results. Which Azure SQL feature should they implement?
Always Encrypted encrypts data on the client side, so the database never sees plaintext. DBAs cannot access the encryption keys and therefore cannot view the sensitive data.
Why this answer
Always Encrypted ensures that sensitive data is encrypted at all times, including during query processing, and that the encryption keys are never revealed to the database engine. This allows DBAs to perform administrative tasks (e.g., backups, index maintenance) while being unable to view the plaintext data in query results, because the decryption occurs only on the client side.
Exam trap
The trap here is that candidates confuse Dynamic Data Masking with Always Encrypted, assuming masking prevents DBAs from seeing data, when in fact masking can be overridden by users with higher permissions, whereas Always Encrypted cryptographically prevents any server-side access to plaintext.
How to eliminate wrong answers
Option A (Dynamic Data Masking) is wrong because it only obfuscates data in query results for non-privileged users, but DBAs with elevated permissions (e.g., db_owner) can still view the unmasked data by running queries directly. Option C (Transparent Data Encryption) is wrong because it encrypts data at rest on disk but does not protect data in use or in query results; DBAs with access to the database can still read plaintext data. Option D (Row-Level Security) is wrong because it restricts which rows a user can see based on a predicate function, but it does not prevent DBAs (who typically have db_owner or higher privileges) from bypassing the policy or viewing all rows.
Which TWO Azure services can be used to host a relational database that is compatible with SQL Server?
Fully managed SQL Server database engine.
Why this answer
Options A and D are correct. Azure SQL Database and Azure SQL Managed Instance both provide SQL Server compatibility. Option B is wrong because Azure Cosmos DB is NoSQL.
Option C is wrong because Azure Database for MySQL is MySQL. Option E is wrong because Azure Database for PostgreSQL is PostgreSQL.
Your organization uses Azure SQL Database and needs to audit all database operations for compliance. The audit logs must be stored for at least five years and be easily searchable. What should you configure?
Auditing with Blob Storage allows long-term retention and searchability.
Why this answer
Option D is correct because Azure SQL Database auditing can store logs in Azure Storage or Log Analytics. Long-term retention is best achieved by sending logs to Azure Storage with a retention policy. Option A (Azure Blob Storage) is the target, but the configuration is auditing.
Option B (Azure Monitor) can be used but may be costly for long-term retention. Option C (Azure Sentinel) is for SIEM, not primary audit storage.
A retail company runs analytical reporting queries on a large Sales table in Azure SQL Database. The table contains over 100 million rows and is updated daily with new transactions. The queries aggregate data by product and month, scanning millions of rows per query. The company wants to significantly reduce query execution time without changing the queries. Which indexing strategy should they implement?
A clustered columnstore index is designed for analytical workloads. It compresses data and allows efficient scanning of columns, making aggregation queries much faster.
Why this answer
A clustered columnstore index is ideal for large data warehousing and analytical workloads because it stores data column-wise, enabling high compression and batch-mode processing. For queries that aggregate millions of rows by product and month, columnstore indexes dramatically reduce I/O and CPU by scanning only the necessary columns and using segment elimination, which directly addresses the requirement to reduce query execution time without changing the queries.
Exam trap
The trap here is that candidates often choose a nonclustered index (B) thinking it will speed up all queries, but they overlook that analytical aggregations on millions of rows require columnstore's batch processing and column elimination, not row-based index seeks.
How to eliminate wrong answers
Option B is wrong because a nonclustered index on ProductID would only speed up point lookups or small range scans, not large aggregations scanning millions of rows; it would likely cause key lookups and still require scanning most of the table. Option C is wrong because a filtered index for the most recent month's data would only benefit queries restricted to that month, but the existing queries aggregate across all months and would not use the filtered index, leaving the full scan overhead unchanged. Option D is wrong because a clustered rowstore index with compression reduces storage size but does not change the fundamental row-based storage and scan pattern; queries still scan all rows and columns, so execution time remains high for large aggregations.
A company has an Azure SQL Database that stores sensitive financial data. They need to ensure that database administrators (DBAs) cannot view the actual data but can still perform administrative tasks. Which feature should they implement?
Dynamic data masking can obfuscate sensitive data for specified users.
Why this answer
Option B is correct because dynamic data masking hides sensitive data from non-privileged users, including DBAs if they are not exempted. Option A is wrong because TDE encrypts at rest but does not hide data from DBAs. Option C is wrong because Azure RBAC controls access but doesn't mask data.
Option D is wrong because Azure SQL Database auditing logs actions but doesn't prevent viewing.
An organization stores sensitive customer data in Azure SQL Database. They need to encrypt the data at rest and ensure that only authorized applications can decrypt it. Which combination of features should they implement?
TDE encrypts the database at rest; Always Encrypted allows client-side encryption and decryption by authorized applications.
Why this answer
Option C is correct because TDE encrypts the database at rest, and Always Encrypted ensures that only authorized applications with the column encryption key can decrypt sensitive columns. Option A is wrong because row-level security controls access but does not encrypt data. Option B is wrong because dynamic data masking obfuscates data but does not encrypt.
Option D is wrong because auditing logs access but does not encrypt.
A startup has an application with unpredictable usage patterns on Azure SQL Database. They want to minimize cost by paying only for the compute they use and the database should automatically pause during idle periods. Which Azure SQL Database option should they choose?
Correct. Serverless pricing charges for compute per second used and auto-pauses during inactivity, making it ideal for sporadic workloads.
Why this answer
Azure SQL Database Serverless is designed for applications with unpredictable usage patterns, as it automatically scales compute resources based on demand and pauses the database during idle periods to eliminate compute costs. This model charges per second for the compute used, making it the most cost-effective choice for workloads that have periods of inactivity.
Exam trap
The trap here is that candidates often confuse 'serverless' with 'Hyperscale' or assume all Azure SQL tiers support auto-pause, but only the Serverless tier provides automatic compute pausing and per-second billing for idle periods.
How to eliminate wrong answers
Option B (Provisioned DTU or vCore) is wrong because it requires a fixed amount of compute resources to be allocated at all times, even when the database is idle, leading to continuous billing and no auto-pause capability. Option C (Hyperscale) is wrong because it is optimized for large databases with high scalability and fast recovery, not for cost savings through auto-pause; it uses a provisioned compute model with no idle pause feature. Option D (Business Critical) is wrong because it is a high-availability tier with multiple replicas and fast failover, designed for mission-critical workloads, and does not support auto-pause or pay-per-use compute billing.
Ready to test yourself?
Try a timed practice session using only Identify Considerations For Relational Data On Azure questions.