Courseiva

CCNA Plan and implement data platform resources Questions

75 of 165 questions · Page 1/3 · Plan and implement data platform resources · Answers revealed

Practice these questionsDomain overviewAll questions
1
MCQeasy

You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?

A.Always Encrypted with randomized encryption
B.Always Encrypted with deterministic encryption
C.Transparent Data Encryption
D.Dynamic Data Masking
AnswerB

Deterministic encryption supports equality searches.

Why this answer

Always Encrypted with deterministic encryption is correct because it encrypts the CreditLimit column at the client driver level, ensuring data remains encrypted at rest and in transit, while still allowing exact-match searches (e.g., WHERE CreditLimit = 5000) since deterministic encryption always produces the same ciphertext for a given plaintext. This meets the compliance requirement without requiring significant application code changes, as the Azure SQL Database driver handles encryption and decryption transparently for authorized users.

Exam trap

The trap here is that candidates confuse Dynamic Data Masking with encryption, thinking masking satisfies compliance requirements, but masking is a presentation-layer feature that does not protect data from privileged users or direct database access.

How to eliminate wrong answers

Option A is wrong because randomized encryption produces different ciphertext for the same plaintext each time, which prevents equality searches (e.g., WHERE CreditLimit = 5000) and thus fails the application requirement for exact-match queries. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level granularity or restrict access to specific columns; it protects against physical theft of files, not unauthorized viewing by database users. Option D is wrong because Dynamic Data Masking obfuscates data at query results but does not encrypt the underlying data; it can be bypassed by users with direct access to the database or through inference attacks, failing the compliance requirement for encryption.

Practice this question →
2
MCQhard

You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?

A.There is a blocking chain due to unoptimized queries.
B.The disk IOPS limit is being reached, causing queuing.
C.The DTU limit is being reached, causing CPU throttling.
D.The database is experiencing memory pressure due to concurrent queries exceeding available memory.
AnswerD

RESOURCE_SEMAPHORE specifically indicates waiting for memory grant; common in under-provisioned tiers.

Why this answer

RESOURCE_SEMAPHORE waits indicate that queries are waiting for memory grants to execute. In Azure SQL Database General Purpose tier with 100 DTUs, memory is shared between the buffer pool and query execution. During peak hours, concurrent queries can exhaust the available memory, forcing queries to wait for memory grants.

This is a classic sign of memory pressure, not CPU or IO throttling.

Exam trap

The trap here is that candidates often confuse DTU throttling (which affects CPU and IO) with memory pressure, but RESOURCE_SEMAPHORE is a memory-specific wait type that is not directly tied to DTU limits.

How to eliminate wrong answers

Option A is wrong because blocking chains typically manifest as LCK_M_* waits, not RESOURCE_SEMAPHORE waits. Option B is wrong because disk IOPS limits cause PAGEIOLATCH_* waits, not RESOURCE_SEMAPHORE waits. Option C is wrong because DTU limits being reached cause CPU throttling, which appears as SOS_SCHEDULER_YIELD waits, not RESOURCE_SEMAPHORE waits.

Practice this question →
3
MCQmedium

Refer to the exhibit. A DBA is creating an Azure SQL Managed Instance using the ARM template snippet shown. The DBA needs to ensure the instance can survive a regional outage without data loss. What change should be made to the template?

A.Set collation to SQL_Latin1_General_CP1_CS_AS
B.Set zoneRedundant to true
C.Set zoneRedundant to true and configure an auto-failover group with a secondary region
D.Change storageAccountType to RA-GRS
AnswerC

Failover group with a secondary region provides regional disaster recovery.

Why this answer

Option C is correct because Azure SQL Managed Instance does not natively support zone redundancy for regional outage protection; instead, it requires configuring an auto-failover group with a secondary instance in a paired region. Setting zoneRedundant to true alone only protects against zone failures within a single region, not a full regional outage. The auto-failover group ensures continuous replication and automatic failover to the secondary region, meeting the requirement of surviving a regional outage without data loss.

Exam trap

The trap here is that candidates often confuse zone redundancy (which protects against datacenter failures within a region) with regional disaster recovery, and incorrectly assume that setting zoneRedundant to true is sufficient for surviving a regional outage, when in fact Azure SQL Managed Instance requires an auto-failover group with a secondary region for that purpose.

How to eliminate wrong answers

Option A is wrong because changing collation to SQL_Latin1_General_CP1_CS_AS affects character set and case sensitivity, not high availability or disaster recovery; it has no impact on regional outage survival. Option B is wrong because setting zoneRedundant to true only provides zone-level redundancy within a single region, which does not protect against a complete regional outage; it is also not supported for Azure SQL Managed Instance (zone redundancy is available only for Azure SQL Database, not Managed Instance). Option D is wrong because storageAccountType is not a property of Azure SQL Managed Instance; it applies to Azure Storage accounts, and changing it to RA-GRS would not affect the managed instance's replication or failover behavior.

Practice this question →
4
Multi-Selecthard

Which THREE factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for a new workload?

Select 3 answers
A.Need for a static IP address
B.Lift-and-shift migration of on-premises databases
C.Requirement for cross-database queries
D.Both support automated backups
E.Use of SQL Server Agent jobs
AnswersB, C, E

Managed Instance offers higher compatibility for existing databases.

Why this answer

Option B is correct because Azure SQL Managed Instance is designed for lift-and-shift migrations of on-premises databases, offering near 100% compatibility with SQL Server features like instance-level configurations, linked servers, and SQL Server Agent. Azure SQL Database, as a Platform-as-a-Service (PaaS) offering, lacks these instance-scoped capabilities, making Managed Instance the preferred choice for migrating existing databases with minimal changes.

Exam trap

The trap here is that candidates often assume static IP addresses are a key differentiator, but Azure SQL Managed Instance does not provide a static public IP by default, and both services can use Private Link for stable private IPs, making this a red herring.

Practice this question →
5
Multi-Selecthard

Which THREE considerations are important when planning a migration from SQL Server on-premises to Azure SQL Managed Instance?

Select 3 answers
A.File tables and FILESTREAM.
B.Cross-database queries using three-part names.
C.SQL Server Integration Services (SSIS) packages.
D.Use of contained databases.
E.SQL Server Agent jobs that use PowerShell.
AnswersA, B, E

File tables are not supported in SQL Managed Instance.

Why this answer

Option A is correct because Azure SQL Managed Instance does not support FILESTREAM or FileTables. These features rely on the NTFS file system for storing BLOB data, which is not available in the managed instance environment. Any migration using FILESTREAM or FileTables must be refactored to use alternative storage, such as Azure Blob Storage with the remote BLOB store (RBS) or separate file storage.

Exam trap

The trap here is that candidates often assume SSIS packages are unsupported in Azure SQL Managed Instance, but they are actually supported via Azure Data Factory, while the real blockers are the less obvious features like FILESTREAM, cross-database three-part names, and PowerShell-based SQL Agent jobs that rely on on-premises Windows authentication.

Practice this question →
6
Multi-Selecteasy

Which TWO conditions must be met to use Azure SQL Database serverless compute tier?

Select 2 answers
A.The database must use the DTU purchasing model
B.The database must be in the Hyperscale service tier
C.The database must be a single database, not part of an elastic pool
D.The database must be in the General Purpose service tier
E.The database must be on a Gen5 hardware
AnswersC, E

Serverless is only for single databases.

Why this answer

Option C is correct because Azure SQL Database serverless compute tier is designed only for single databases, not for databases in elastic pools. The serverless tier auto-scales compute resources based on workload demand and pauses during idle periods, which is incompatible with the shared resource model of elastic pools where multiple databases share a fixed set of resources.

Exam trap

The trap here is that candidates often assume serverless is tied to a specific service tier (like Hyperscale) or purchasing model (like DTU), when in fact it requires the vCore model, General Purpose tier, single database, and Gen5 hardware—and the question asks for two conditions, so you must pick both C and E, not just one.

Practice this question →
7
MCQhard

You are deploying an Azure SQL Database using PowerShell as shown in the exhibit. The database will be used by a development team that works intermittently. You need to ensure the database is cost-effective while being available on demand. What is the purpose of the AutoPauseDelayInMinutes parameter?

A.It configures the database to pause during a disaster recovery scenario.
B.It controls the automatic pausing of the database after a period of inactivity to save costs.
C.It sets the maximum duration for which the database can be paused.
D.It determines how long the database takes to resume after a pause.
AnswerB

Serverless databases pause after inactivity to reduce costs.

Why this answer

The AutoPauseDelayInMinutes parameter is used with Azure SQL Database serverless compute tier. It specifies the number of minutes of inactivity (no CPU usage or active sessions) after which the database automatically pauses, stopping compute billing while storage remains billed. This makes the database cost-effective for intermittent development workloads because it eliminates compute costs during idle periods and automatically resumes on the first connection.

Exam trap

The trap here is that candidates confuse the auto-pause feature with a manual pause/resume operation or with a scheduled shutdown, and they incorrectly assume AutoPauseDelayInMinutes controls resume speed or maximum pause duration, when in fact it only defines the inactivity threshold before automatic pausing occurs.

How to eliminate wrong answers

Option A is wrong because AutoPauseDelayInMinutes does not relate to disaster recovery; disaster recovery is handled by geo-replication, failover groups, or backup/restore, not by pausing. Option C is wrong because the parameter does not set a maximum pause duration; the database remains paused indefinitely until a connection or activity triggers an automatic resume, and there is no configurable maximum pause time. Option D is wrong because the resume time after a pause is not controlled by AutoPauseDelayInMinutes; resume latency is determined by the underlying serverless infrastructure (typically 30-60 seconds) and is not configurable via this parameter.

Practice this question →
8
MCQeasy

You need to deploy an Azure SQL Database that complies with a regulatory requirement to encrypt data at rest using a customer-managed key stored in Azure Key Vault. The database will be used by a line-of-business application. Which feature should you enable?

A.Transparent Data Encryption (TDE) with customer-managed key
B.Azure Defender for SQL
C.Dynamic Data Masking
D.Always Encrypted
AnswerA

TDE encrypts data at rest and supports customer-managed keys.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key (CMK) in Azure Key Vault encrypts the database at rest, meeting the regulatory requirement for customer-controlled encryption keys. TDE performs real-time I/O encryption and decryption of data files, logs, and backups, and when configured with a CMK (also known as Bring Your Own Key or BYOK), the encryption key is stored and managed in Azure Key Vault, giving the customer full control over key rotation and revocation.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which encrypts data at rest and in transit but at the column level with client-side keys) with TDE (which encrypts the entire database at rest with server-managed or customer-managed keys), leading them to select Always Encrypted when the requirement is for full database-at-rest encryption with a customer-managed key.

How to eliminate wrong answers

Option B is wrong because Azure Defender for SQL provides advanced security monitoring, vulnerability assessments, and threat detection, but it does not encrypt data at rest or manage encryption keys. Option C is wrong because Dynamic Data Masking limits exposure of sensitive data by obfuscating it in query results to non-privileged users, but it does not encrypt data at rest. Option D is wrong because Always Encrypted protects sensitive data in transit and at rest by encrypting columns with client-side keys, but it does not use a customer-managed key stored in Azure Key Vault for the entire database at rest encryption; it is designed for column-level encryption with keys managed by the client application.

Practice this question →
9
MCQmedium

You are a database administrator for a large financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases, FinanceDB, is 2.5 TB in size and runs a critical application that requires high availability with automatic failover. The application uses database mail and SQL Agent jobs. During migration, you need to minimize downtime. Which deployment option should you choose?

A.Deploy an Azure SQL Database Hyperscale with geo-replication
B.Deploy an Azure SQL Managed Instance with zone-redundant configuration
C.Deploy a single Azure SQL Database with Active Geo-Replication
D.Deploy an Azure SQL Managed Instance in a failover group with a secondary in another region
AnswerD

Failover group provides automatic failover, supports all required features, and allows minimal downtime migration.

Why this answer

Option D is correct because Azure SQL Managed Instance supports database mail and SQL Agent jobs natively, and deploying it in a failover group with a secondary in another region provides automatic failover and high availability while minimizing downtime during migration. The 2.5 TB size is within Managed Instance limits, and the failover group enables a controlled, continuous-sync replication to the secondary, allowing a swift cutover with minimal data loss.

Exam trap

The trap here is that candidates may confuse Azure SQL Database with Azure SQL Managed Instance, overlooking that database mail and SQL Agent jobs are only supported in Managed Instance, not in single databases or Hyperscale.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Hyperscale does not support database mail or SQL Agent jobs, which are required by the application. Option B is wrong because zone-redundant configuration provides high availability within a single region but does not offer cross-region failover or minimize downtime during migration, as it lacks a secondary in another region for geo-replication. Option C is wrong because a single Azure SQL Database with Active Geo-Replication does not support database mail or SQL Agent jobs, and it is not a Managed Instance, which is needed for full compatibility with the on-premises SQL Server features.

Practice this question →
10
Multi-Selecteasy

You need to secure an Azure SQL Database. The requirements are: (1) encrypt sensitive columns such as credit card numbers, (2) prevent unauthorized users from seeing the full credit card number even if they have access to the database, and (3) allow authorized applications to decrypt the data. Which two features should you implement? (Choose TWO.)

Select 2 answers
A.Transparent Data Encryption (TDE)
B.Dynamic Data Masking
C.Always Encrypted
D.Row-level security
E.Column-level security
AnswersC, E

Encrypts specific columns and keeps data encrypted from DB engine.

Why this answer

Always Encrypted is correct because it encrypts sensitive columns like credit card numbers at the client-side, ensuring the data remains encrypted at rest, in transit, and during processing. This prevents unauthorized users, including database administrators, from viewing the plaintext data, while authorized applications with the column encryption key can decrypt it transparently.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with encryption, assuming obfuscation provides the same security as Always Encrypted, but masking does not protect data from privileged users or direct database access.

Practice this question →
11
MCQmedium

You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?

A.Use Azure Data Studio to import the certificate directly from the local machine store during migration.
B.Disable TDE on the source database, migrate the backup, then enable TDE on the target.
C.Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.
D.Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.
AnswerC

This ensures the TDE keys are available to decrypt the database after restore.

Why this answer

Option C is correct because TDE in SQL Server relies on a certificate (or asymmetric key) that must be present in the target instance to decrypt the database backup. By backing up the certificate and private key to a .pfx file and restoring it to Azure SQL Managed Instance, you ensure the target has the necessary encryption keys to read the backup. Azure SQL Managed Instance supports restoring TDE-protected backups only if the corresponding certificate is first restored into the master database.

Exam trap

The trap here is that candidates assume TDE certificates are automatically transferred or that disabling TDE is a safe shortcut, but in reality, the certificate must be explicitly backed up and restored to the target before the database restore can succeed.

How to eliminate wrong answers

Option A is wrong because Azure Data Studio cannot import a certificate directly from the local machine store during migration; TDE certificates must be manually backed up and restored to the target instance. Option B is wrong because disabling TDE on the source database would decrypt all data, which is unnecessary and risks exposing sensitive data during migration; TDE should remain enabled to maintain encryption at rest. Option D is wrong because creating a master key in the target does not automatically import the certificate; the certificate must be explicitly backed up from the source and restored to the target before the database restore.

Practice this question →
12
MCQhard

You are deploying a new Azure SQL Database for an application that will store sensitive financial data. The compliance team requires that the database be configured to automatically detect and alert on anomalous access patterns, and that all queries be logged for auditing. Which services should you enable?

A.Azure Purview and vulnerability assessment
B.Microsoft Sentinel and SQL Auditing
C.Azure Defender for SQL and SQL Auditing
D.SQL Server auditing and vulnerability assessment
AnswerC

Defender provides anomaly detection and auditing logs queries.

Why this answer

Option C is correct because Azure Defender for SQL provides anomaly detection and alerts for suspicious access patterns (e.g., SQL injection, brute force), while SQL Auditing captures all queries and events for compliance logging. Together, they meet the requirements for automatic detection and full query auditing without additional services.

Exam trap

The trap here is that candidates confuse Azure Defender for SQL with vulnerability assessment or Microsoft Sentinel, assuming a SIEM is required for detection, when Azure Defender for SQL already provides built-in anomaly detection for Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because Azure Purview is a data governance and catalog service, not a security monitoring tool, and vulnerability assessment alone does not provide real-time anomaly detection or query logging. Option B is wrong because Microsoft Sentinel is a SIEM that ingests logs but does not natively perform database-level anomaly detection or replace SQL Auditing for query logging; it would require additional configuration and cost. Option D is wrong because SQL Server auditing (on-premises style) is not directly available in Azure SQL Database—Azure SQL uses SQL Auditing—and vulnerability assessment does not detect anomalous access patterns or provide alerts.

Practice this question →
13
MCQhard

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a regional outage, you fail over to the secondary region. When the primary region recovers, you need to fail back with zero data loss. What should you do?

A.Delete the secondary database and recreate it from the primary
B.Add a new secondary in the original primary region and then fail over
C.Fail over the original primary to become the primary again
D.Perform a planned failover from the current primary to the original primary
AnswerD

Planned failover synchronizes data and ensures zero data loss.

Why this answer

Option D is correct because a planned failover in Azure SQL Database with Active Geo-Replication ensures zero data loss by synchronizing all committed transactions from the current primary (secondary region) to the original primary before switching roles. This process uses synchronous replication during the final phase, guaranteeing that no transactions are lost when failing back to the recovered primary region.

Exam trap

The trap here is that candidates confuse 'failover' (which can be unplanned and asynchronous) with 'planned failover' (which is synchronous and guarantees zero data loss), leading them to choose Option C thinking any failover will suffice.

How to eliminate wrong answers

Option A is wrong because deleting the secondary database and recreating it from the primary would cause data loss—any transactions committed on the current primary during the outage would be lost, and the new secondary would start from an outdated snapshot. Option B is wrong because adding a new secondary in the original primary region does not fail back; it merely creates an additional replica, and failing over to it would still require a separate failover step, potentially causing data loss if not synchronized. Option C is wrong because failing over the original primary to become the primary again (i.e., performing an unplanned failover) would not guarantee zero data loss—it could drop transactions that were committed on the current primary but not yet replicated to the original primary, as unplanned failover is asynchronous.

Practice this question →
14
Multi-Selecthard

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: - Minimal application changes. - Support for SQL Server Agent jobs. - Ability to scale storage independently from compute. - Native support for cross-database queries. Which THREE options meet these requirements? (Choose three.)

Select 3 answers
A.Azure SQL Database (single database)
B.Azure SQL Database elastic pool
C.Azure Synapse Analytics (dedicated SQL pool)
D.SQL Server on Azure Virtual Machine
E.Azure SQL Managed Instance
AnswersB, D, E

Supports cross-database queries within pool and independent scaling, but not SQL Server Agent.

Why this answer

Azure SQL Managed Instance (E) provides full SQL Server engine compatibility, including SQL Server Agent, cross-database queries, and independent storage scaling, with minimal application changes. SQL Server on Azure Virtual Machine (D) offers full control over the SQL Server instance, supporting Agent jobs and cross-database queries, and allows independent scaling of compute and storage via VM resizing and managed disks. Azure SQL Database elastic pool (B) supports SQL Server Agent jobs (via elastic jobs), independent storage scaling per database, and cross-database queries using elastic query, while requiring minimal application changes.

Exam trap

The trap here is that candidates often assume Azure SQL Database (single database) supports SQL Server Agent natively, but it does not—only Managed Instance and VM provide full Agent support, and elastic pools require elastic jobs as a workaround.

Practice this question →
15
MCQhard

You are a database administrator for a global e-commerce company. The company runs a critical transactional workload on an Azure SQL Managed Instance (MI) in the West US region. The database is approximately 1 TB in size and uses the Business Critical tier with zone redundancy enabled. The application requires read latency for reporting queries to be under 10 ms. The company is expanding to Europe and needs to deploy a secondary copy of the database in the North Europe region to serve read-only reporting traffic for European users. The secondary must be kept in sync with the primary with a recovery point objective (RPO) of less than 5 seconds. Additionally, the solution must support automatic failover if the primary region becomes unavailable. You need to design the disaster recovery and read scaling solution. Which approach should you take?

A.Create a geo-replica of the MI in North Europe and manually failover when needed
B.Deploy a separate managed instance in North Europe and use transactional replication to keep it in sync
C.Create an auto-failover group with the primary in West US and add a readable secondary in North Europe, using synchronous commit
D.Create an auto-failover group with the primary in West US and secondary in North Europe, but do not make the secondary readable
AnswerC

This meets all requirements.

Why this answer

Option C is correct because Azure SQL Managed Instance supports auto-failover groups with a readable secondary in a paired region. By configuring synchronous commit within the failover group, you achieve an RPO of less than 5 seconds (typically 0-2 seconds) while the secondary in North Europe can serve read-only traffic with low latency. The auto-failover group also provides automatic failover if the primary region becomes unavailable, meeting both the read scaling and disaster recovery requirements.

Exam trap

The trap here is that candidates may confuse geo-replication (which requires manual failover) with auto-failover groups (which support automatic failover and readable secondaries), or they may overlook that transactional replication cannot meet the strict RPO requirement due to asynchronous nature and potential lag.

How to eliminate wrong answers

Option A is wrong because a geo-replica of Azure SQL Managed Instance does not support automatic failover; it requires manual initiation, which violates the requirement for automatic failover. Option B is wrong because transactional replication introduces additional latency and complexity, and it cannot guarantee an RPO of less than 5 seconds due to potential replication lag and the need for manual failover. Option D is wrong because it creates a secondary that is not readable, failing to meet the requirement to serve read-only reporting traffic for European users with low latency.

Practice this question →
16
MCQeasy

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. The source databases use SQL Server Agent jobs, cross-database queries, and SQL Server Integration Services (SSIS) packages. Which migration approach minimizes the need to modify existing code or processes?

A.Migrate to SQL Server on Azure Virtual Machines and keep all existing functionality unchanged.
B.Migrate to Azure SQL Managed Instance and use Azure Data Factory to run SSIS packages with an Azure-SSIS integration runtime.
C.Migrate to Azure Database for PostgreSQL and rewrite all SQL Agent jobs as pg_cron tasks.
D.Migrate to Azure SQL Database and use elastic jobs to replace SQL Server Agent.
AnswerB

Correct: Managed Instance supports SQL Server Agent, cross-database queries, and SSIS via Azure Data Factory.

Why this answer

Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, including SQL Server Agent and cross-database queries. For SSIS packages, the Azure-SSIS integration runtime in Azure Data Factory allows you to lift-and-shift existing SSIS packages without rewriting them, minimizing code changes.

Exam trap

The trap here is that candidates often assume Azure SQL Database can replace SQL Server Agent with elastic jobs, but elastic jobs lack many Agent features (e.g., job schedules, operators, alerts) and do not support SSIS packages or cross-database queries, making Managed Instance the correct choice for minimal code changes.

How to eliminate wrong answers

Option A is wrong because migrating to SQL Server on Azure VMs keeps all functionality unchanged but requires you to manage the VM, patching, and backups, which is not the most minimal-effort approach for code changes—it's a valid option but not the best answer for minimizing code modifications. Option C is wrong because migrating to Azure Database for PostgreSQL requires rewriting SQL Agent jobs as pg_cron tasks and likely rewriting cross-database queries and SSIS packages, which contradicts the goal of minimizing code changes. Option D is wrong because Azure SQL Database does not support SQL Server Agent or cross-database queries natively, and elastic jobs only partially replace Agent functionality, requiring significant code modifications.

Practice this question →
17
MCQmedium

You are configuring an Azure SQL Database elastic pool for a SaaS application. The pool will host 50 databases with varying workloads. You need to minimize cost while ensuring performance meets baseline requirements. Which tier and configuration should you choose?

A.Hyperscale tier with 4 vCores
B.Provisioned tier with General Purpose and 2 vCores
C.Serverless tier with General Purpose and auto-pause enabled
D.DTU-based elastic pool with 200 DTUs
AnswerC

Serverless tier auto-scales and auto-pauses, reducing costs for variable workloads.

Why this answer

Option C is correct because the Serverless tier with General Purpose and auto-pause enabled is the most cost-effective choice for a pool of 50 databases with varying workloads. Serverless automatically scales compute based on demand and pauses databases during inactivity, charging only for storage and used vCores, which minimizes cost while meeting baseline performance requirements.

Exam trap

The trap here is that candidates often assume the Provisioned tier (Option B) is always cheaper for predictable workloads, but they overlook that the Serverless tier with auto-pause can dramatically reduce costs for variable or idle databases, which is the key requirement in this scenario.

How to eliminate wrong answers

Option A is wrong because the Hyperscale tier is designed for very large databases (up to 100 TB) with high throughput and rapid scaling, which is over-provisioned and unnecessarily expensive for 50 databases with varying workloads that only need baseline performance. Option B is wrong because the Provisioned tier with General Purpose and 2 vCores incurs continuous compute charges even when databases are idle, leading to higher costs compared to serverless for intermittent or variable workloads. Option D is wrong because a DTU-based elastic pool with 200 DTUs uses a fixed resource model that cannot scale down to zero during inactivity, and DTU pools are generally less cost-efficient than vCore-based serverless for workloads with significant idle periods.

Practice this question →
18
MCQhard

Your team is migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses Service Broker for cross-database messaging. The compliance requirement mandates that the migration must be performed with minimal downtime and that the target must support the Service Broker feature. What migration strategy should you recommend?

A.Perform a native backup and restore to Azure SQL Managed Instance
B.Use the Data Migration Assistant (DMA) to migrate to Azure SQL Database
C.Use Azure Database Migration Service (DMS) with offline migration to Azure SQL Database
D.Use Azure Database Migration Service (DMS) with online migration to Azure SQL Managed Instance
AnswerD

Azure SQL Managed Instance supports Service Broker and online migration minimizes downtime.

Why this answer

Option D is correct because Azure SQL Managed Instance fully supports Service Broker, and the Azure Database Migration Service (DMS) with online migration mode enables minimal downtime by continuously replicating changes from the source SQL Server to the target Managed Instance until a cutover. This satisfies both the Service Broker feature requirement and the compliance mandate for minimal downtime.

Exam trap

The trap here is that candidates may confuse Azure SQL Database with Azure SQL Managed Instance regarding Service Broker support, or assume that any offline migration method can achieve minimal downtime, but the key differentiator is that only online migration to SQL Managed Instance meets both the feature and downtime requirements.

How to eliminate wrong answers

Option A is wrong because a native backup and restore to Azure SQL Managed Instance is an offline method that requires the source database to be taken offline during the backup and restore process, causing significant downtime, and does not support minimal downtime. Option B is wrong because the Data Migration Assistant (DMA) can assess and migrate to Azure SQL Database, but Azure SQL Database does not support Service Broker for cross-database messaging, so it fails the feature requirement. Option C is wrong because using DMS with offline migration to Azure SQL Database also targets Azure SQL Database, which lacks Service Broker support, and offline migration inherently involves downtime, violating the minimal downtime requirement.

Practice this question →
19
MCQhard

You are deploying an Azure SQL Database that will be used by a critical financial application. The database must be protected against accidental deletion or modification of data by administrators. You need to implement a solution that allows point-in-time restore to any point within the last 35 days, even if an administrator accidentally drops a table. Which feature should you enable?

A.Configure long-term backup retention (LTR) policies.
B.Enable the Azure SQL Database recycle bin feature.
C.Enable automated backups and configure point-in-time restore with a retention period of 35 days.
D.Set up Active Geo-Replication with a failover group.
AnswerC

Point-in-time restore can recover from accidental data modification.

Why this answer

Option C is correct because Azure SQL Database's automated backups with point-in-time restore (PITR) allow you to restore a database to any point within the configured retention period, up to 35 days. This includes recovery from accidental table drops or data modifications, as PITR operates at the database level and can recover to a specific timestamp before the incident occurred.

Exam trap

The trap here is that candidates often confuse the 'recycle bin' feature from on-premises SQL Server with Azure SQL Database, or mistakenly think LTR or geo-replication can undo accidental data changes, when in fact only PITR with the correct retention period provides this capability.

How to eliminate wrong answers

Option A is wrong because long-term backup retention (LTR) is designed for archival purposes beyond 35 days (up to 10 years), not for short-term recovery from accidental deletion or modification within the 35-day window. Option B is wrong because Azure SQL Database does not have a 'recycle bin' feature; this concept exists in on-premises SQL Server (e.g., dropped objects go to the recycle bin) but is not available in Azure SQL Database. Option D is wrong because Active Geo-Replication with a failover group provides high availability and disaster recovery by maintaining readable replicas in different regions, but it does not protect against accidental data deletion or modification—it replicates all changes, including destructive ones, to the replica.

Practice this question →
20
MCQhard

You manage an Azure SQL Database that uses a Business Critical tier with a zone-redundant configuration. During a planned maintenance event, you notice a significant increase in query latency. You suspect that the read-only intent routing is not working as expected. What should you check first to ensure read-only connections are using the secondary replica?

A.Verify that the application connection string includes 'ApplicationIntent=ReadOnly' for read-only queries.
B.Review the maintenance window schedule to see if the primary replica was being updated.
C.Check the 'ReadScale' property of the database to ensure it is set to 'Enabled'.
D.Confirm that the database is in a zone-redundant configuration by checking the 'ZoneRedundant' property.
AnswerA

Correct: Read scale-out requires the application to specify read-only intent; otherwise, connections go to the primary replica.

Why this answer

Option A is correct because the read-only intent routing in Azure SQL Database relies on the client connection string including `ApplicationIntent=ReadOnly`. Without this parameter, all connections, including those intended for read-only workloads, will be directed to the primary replica, even if a readable secondary replica is available. This is the first and most fundamental check to ensure read-only queries are routed to the secondary.

Exam trap

The trap here is that candidates may confuse the `ReadScale` property (which is only for Hyperscale) with the Business Critical tier's built-in read-only routing, or they may think that zone-redundancy or maintenance schedules directly control read-only routing, when in fact the connection string's `ApplicationIntent` is the sole determinant.

How to eliminate wrong answers

Option B is wrong because reviewing the maintenance window schedule does not directly address the read-only routing issue; maintenance events may cause latency but do not change the routing behavior based on ApplicationIntent. Option C is wrong because the `ReadScale` property is only relevant for the Hyperscale service tier, not for Business Critical tier, where read-only routing is always enabled when a readable secondary exists. Option D is wrong because confirming zone-redundant configuration ensures high availability but does not affect the read-only routing mechanism, which depends on the connection string's ApplicationIntent parameter.

Practice this question →
21
MCQmedium

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical tier. The database is deployed in the West US region. You need to ensure that if the entire West US region becomes unavailable, the database can be failed over to a secondary region with minimal data loss. What should you implement?

A.Configure active geo-replication to East US
B.Configure an auto-failover group with a readable secondary in East US
C.Enable zone redundancy for the database
D.Configure geo-restore from the West US database
AnswerA

Active geo-replication with synchronous commit provides zero data loss.

Why this answer

Active geo-replication for Azure SQL Database Business Critical tier provides a continuous, asynchronous replication stream to a secondary region, ensuring minimal data loss (typically a few seconds) during a regional outage. Unlike auto-failover groups, active geo-replication allows you to manually initiate a failover to the secondary region with a Recovery Point Objective (RPO) of up to 5 seconds, which meets the requirement for minimal data loss. The Business Critical tier also supports readable secondaries, but the key here is the replication mechanism that prioritizes low RPO over automatic failover.

Exam trap

The trap here is that candidates often confuse auto-failover groups (which offer automatic failover but with the same RPO as active geo-replication) with active geo-replication, but the question's emphasis on 'failed over' (manual action) and 'minimal data loss' points to active geo-replication as the more precise choice for controlling failover timing and achieving the lowest possible RPO.

How to eliminate wrong answers

Option B is wrong because auto-failover groups use asynchronous replication with a default RPO of up to 5 seconds, but they are designed for automatic failover, not manual failover with minimal data loss; the question emphasizes 'failed over' (manual action) and 'minimal data loss,' which active geo-replication achieves more precisely. Option C is wrong because zone redundancy protects against failures within a single Azure region (e.g., a datacenter failure), not against a full regional outage, and it does not provide a secondary region for failover. Option D is wrong because geo-restore is a point-in-time restore from geo-replicated backups, which can have an RPO of up to 1 hour and an RTO of up to 12 hours, resulting in significant data loss and longer recovery time, not minimal data loss.

Practice this question →
22
MCQeasy

Your company is deploying a new application that uses an Azure SQL Database. The security policy requires that all connections use Microsoft Entra ID authentication and that no SQL authentication users are created. Which server-level setting should you enforce?

A.Set the database to use contained database authentication.
B.Configure a server-level firewall rule to allow only Entra ID IPs.
C.Enable 'Azure AD-only authentication' on the Azure SQL logical server.
D.Set the Entra ID admin for the server and disable SQL authentication.
AnswerC

This prevents SQL authentication connections.

Why this answer

Option C is correct because enabling 'Azure AD-only authentication' on the Azure SQL logical server enforces that all connections must use Microsoft Entra ID authentication and blocks any SQL authentication attempts, even if SQL logins exist. This setting directly meets the security policy requirement that no SQL authentication users are created and all connections use Entra ID.

Exam trap

The trap here is that candidates often assume setting the Entra ID admin and disabling SQL authentication manually is sufficient, but the 'Azure AD-only authentication' property is a separate, explicit enforcement mechanism that must be enabled to fully block SQL authentication at the server level.

How to eliminate wrong answers

Option A is wrong because setting the database to use contained database authentication allows contained database users to authenticate with SQL authentication, which would violate the policy that no SQL authentication users are created. Option B is wrong because configuring a server-level firewall rule to allow only Entra ID IPs does not enforce authentication method; it only restricts network access by IP address, and Entra ID authentication is not tied to specific IPs. Option D is wrong because setting the Entra ID admin and disabling SQL authentication via the portal or T-SQL is not a server-level setting that fully enforces the policy; the 'Azure AD-only authentication' property must be explicitly enabled to block all SQL authentication attempts, including those from the server admin.

Practice this question →
23
MCQmedium

You are deploying a new Azure SQL Database that will be used by an application requiring high availability and automatic failover across Azure regions. The RPO must be less than 5 seconds, and the RTO must be less than 1 minute. You also need to ensure that the secondary region can be used for read-only workloads during normal operation. Which solution should you implement?

A.Use active geo-replication with a readable secondary in a different Azure region.
B.Use an auto-failover group with a readable secondary in a different region.
C.Use Azure SQL Database backup to a second region and restore on demand.
D.Use an auto-failover group with a read-scale secondary in the same region.
AnswerA

Correct: Active geo-replication provides a readable secondary with RPO of 5 seconds and manual failover in seconds.

Why this answer

Active geo-replication with a readable secondary in a different Azure region meets the requirements because it provides an asynchronous replication mechanism with an RPO of less than 5 seconds and an RTO of less than 1 minute during failover. The secondary database is fully readable for read-only workloads, supporting the application's need for read-scale in the secondary region during normal operation.

Exam trap

The trap here is that candidates often confuse auto-failover groups with active geo-replication, assuming that auto-failover groups automatically provide a readable secondary, but in fact, the secondary in an auto-failover group is not readable unless you explicitly configure it as a readable secondary, which is not the default behavior.

How to eliminate wrong answers

Option B is wrong because auto-failover groups use the same underlying active geo-replication technology but add orchestrated failover; however, the secondary in an auto-failover group is not readable by default unless you explicitly enable it, and the question's requirement for a readable secondary is directly met by active geo-replication without the extra orchestration layer. Option C is wrong because using backups to a second region and restoring on demand results in an RPO of at least several minutes (due to backup frequency) and an RTO of hours, far exceeding the required 5-second RPO and 1-minute RTO. Option D is wrong because a read-scale secondary in the same region does not provide cross-region disaster recovery, failing the requirement for automatic failover across Azure regions.

Practice this question →
24
MCQhard

A company has an Azure SQL Database with a large table that is frequently updated. They notice performance degradation due to index fragmentation. Which maintenance strategy should you recommend to minimize impact on availability and performance?

A.Schedule an offline index rebuild during off-peak hours.
B.Use online index rebuild with resumable operation.
C.Disable and rebuild indexes manually during maintenance window.
D.Use online index reorganize with low priority locks.
AnswerB

Online rebuild allows concurrent access and resumable in case of failure, minimizing impact.

Why this answer

Option B is correct because online index rebuild with resumable operation allows the index to be rebuilt while the table remains available for reads and writes, minimizing downtime. The resumable feature lets you pause and resume the rebuild if it is interrupted, which is critical for large tables that are frequently updated. This approach reduces performance impact compared to offline rebuilds and avoids blocking issues associated with other online methods.

Exam trap

The trap here is that candidates often confuse 'online index reorganize' (which is less impactful but insufficient for high fragmentation) with 'online index rebuild' (which is the correct solution for severe fragmentation), or they assume offline operations are acceptable despite the availability requirement.

How to eliminate wrong answers

Option A is wrong because an offline index rebuild takes the table offline, blocking all access during the rebuild, which is unacceptable for a frequently updated table and violates availability requirements. Option C is wrong because disabling and rebuilding indexes manually during a maintenance window is essentially an offline operation that causes significant downtime and does not leverage Azure SQL Database's built-in online capabilities. Option D is wrong because online index reorganize with low priority locks is less effective for high fragmentation (it only defragments the leaf level) and may still cause blocking under heavy update workloads, whereas a rebuild is needed for severe fragmentation.

Practice this question →
25
Multi-Selecthard

You are configuring an Azure SQL Database for a financial application that requires high availability and disaster recovery across regions. The database is in the Premium service tier. Which TWO configurations should you implement to meet these requirements? (Choose TWO.)

Select 2 answers
A.Enable automatic tuning for the database.
B.Enable zone redundancy within the primary region.
C.Create a failover group that includes the primary and secondary.
D.Configure active geo-replication to a secondary region.
E.Enable Transparent Data Encryption (TDE).
AnswersC, D

Failover group enables automatic failover across regions.

Why this answer

Option C is correct because a failover group in Azure SQL Database provides automated, coordinated failover of multiple databases across regions, ensuring high availability and disaster recovery with a defined replication policy and a readable secondary endpoint. Option D is correct because active geo-replication allows you to create up to four readable secondary databases in different Azure regions, enabling manual or automatic failover for cross-region disaster recovery, which is essential for a financial application requiring regional resilience.

Exam trap

The trap here is that candidates often confuse zone redundancy (which is intra-region HA) with cross-region DR solutions like failover groups or geo-replication, leading them to select option B instead of the correct cross-region options.

Practice this question →
26
MCQhard

You are designing a disaster recovery solution for an Azure SQL Database in the Business Critical tier. You require automatic failover to a secondary region with zero data loss. Which configuration should you use?

A.Create a failover group with the Business Critical tier.
B.Configure geo-replication between two servers.
C.Enable read scale-out in a secondary region.
D.Use active geo-replication with manual failover.
AnswerA

Failover groups on Business Critical provide synchronous replication and automatic failover.

Why this answer

The Business Critical tier in Azure SQL Database supports failover groups, which provide automatic failover across regions with zero data loss by using synchronous replication at the primary region and asynchronous replication to the secondary region. Failover groups ensure that all committed transactions are preserved during a failover, meeting the zero data loss requirement for disaster recovery.

Exam trap

The trap here is that candidates often confuse active geo-replication with failover groups, assuming manual failover can achieve zero data loss, but active geo-replication uses asynchronous replication by default and does not support automatic failover with zero RPO.

How to eliminate wrong answers

Option B is wrong because geo-replication between two servers is a legacy feature that does not support automatic failover or zero data loss; it requires manual configuration and has potential data loss. Option C is wrong because read scale-out in a secondary region only offloads read-only workloads and does not provide failover capabilities or disaster recovery. Option D is wrong because active geo-replication with manual failover does not guarantee zero data loss, as it uses asynchronous replication and requires manual intervention to initiate failover.

Practice this question →
27
MCQmedium

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database. You need to ensure that tenant data is isolated and that performance is predictable. Cost efficiency is important. Which deployment model should you use?

A.Deploy a single Azure SQL Database per tenant
B.Use Azure SQL Managed Instance with multiple databases
C.Use a single large database with row-level security
D.Use an elastic pool with one database per tenant
AnswerD

Elastic pool isolates tenants while sharing resources efficiently.

Why this answer

Option D is correct because an elastic pool allows you to provision a shared set of resources (eDTUs or vCores) that is distributed across multiple databases, each representing a tenant. This provides logical isolation of tenant data (each tenant has its own database) while pooling resources to handle variable workloads cost-effectively. The elastic pool model is specifically designed for SaaS multi-tenant scenarios where predictable performance is achieved through resource governance, and cost efficiency comes from sharing resources among databases that do not all peak simultaneously.

Exam trap

The trap here is that candidates often confuse 'tenant isolation' with 'dedicated resources' and choose Option A, missing that elastic pools provide logical isolation (separate databases) with shared, cost-efficient resources, which is the exact requirement for predictable performance and cost efficiency in multi-tenant SaaS.

How to eliminate wrong answers

Option A is wrong because deploying a single Azure SQL Database per tenant without pooling leads to over-provisioning and higher costs, as each database requires its own DTU/vCore allocation regardless of actual usage, and does not leverage shared resource benefits for variable workloads. Option B is wrong because Azure SQL Managed Instance is designed for lift-and-shift migrations of existing SQL Server workloads with instance-level features, not for multi-tenant SaaS isolation; it lacks the elastic pool resource-sharing model and is more expensive per database. Option C is wrong because using a single large database with row-level security (RLS) violates tenant data isolation at the database level (a single database is a shared failure domain), and performance is unpredictable as all tenants compete for the same resources without per-tenant resource governance, making it unsuitable for predictable performance and cost efficiency.

Practice this question →
28
MCQhard

Your company is migrating an on-premises SQL Server 2016 database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Database Mail, and Service Broker. The database is 500 GB. You need to minimize downtime during migration. Which migration approach should you use?

A.Set up transactional replication from on-premises to Azure SQL Managed Instance
B.Use Azure Database Migration Service with online migration
C.Export the database as a BACPAC file and import to Azure SQL Managed Instance
D.Back up the database to URL and restore to Azure SQL Managed Instance
AnswerB

DMS online migration supports minimal downtime and transfers SQL Agent jobs and other objects.

Why this answer

Option B is correct because Azure Database Migration Service (DMS) with online migration mode uses continuous data synchronization via change tracking or log shipping to minimize downtime. This approach supports SQL Server Agent jobs, Database Mail, and Service Broker, which are preserved during migration to Azure SQL Managed Instance. The online mode allows the source database to remain operational until the final cutover, reducing downtime to seconds.

Exam trap

The trap here is that candidates often confuse offline methods like BACPAC or backup/restore as 'minimizing downtime' because they are simple, but they fail to recognize that online migration via DMS is specifically designed to keep the source database operational during the bulk of the migration process.

How to eliminate wrong answers

Option A is wrong because transactional replication requires setting up publishers, distributors, and subscribers, and it does not natively support migrating all objects like SQL Server Agent jobs and Service Broker configurations without additional scripting; it also introduces complexity and potential latency. Option C is wrong because exporting a 500 GB database as a BACPAC file and importing it is an offline process that can take hours or days, causing significant downtime, and it may not preserve all metadata like SQL Server Agent jobs and Service Broker endpoints. Option D is wrong because backing up to URL and restoring to Azure SQL Managed Instance is an offline migration method that requires the source database to be offline during the backup and restore process, leading to extended downtime for a 500 GB database.

Practice this question →
29
Multi-Selectmedium

Which THREE of the following are valid options for migrating an on-premises SQL Server database to Azure SQL Database?

Select 3 answers
A.Use SQL Server Integration Services (SSIS) to migrate schema and data.
B.Use the Data Migration Assistant (DMA) to assess and migrate.
C.Export to a BACPAC file and then import to Azure SQL Database.
D.Use transactional replication to continuously replicate data to Azure SQL Database.
E.Use the Azure portal's 'Import database' wizard directly from on-premises.
AnswersB, C, D

DMA is a supported tool for assessment and migration.

Why this answer

Option B is correct because the Data Migration Assistant (DMA) is a Microsoft tool specifically designed to assess on-premises SQL Server databases for compatibility issues and then perform a seamless migration of schema and data to Azure SQL Database. DMA handles both assessment and migration in a single workflow, making it a valid and recommended option for migrating to Azure SQL Database.

Exam trap

The trap here is that candidates often confuse SSIS (an ETL tool) with a database migration tool, or assume the Azure portal can directly connect to an on-premises server, when in fact it only works with BACPAC files already uploaded to Azure Blob Storage.

Practice this question →
30
Multi-Selecthard

Which THREE actions should you take when planning to implement Azure SQL Database with Microsoft Defender for SQL and Microsoft Sentinel integration?

Select 3 answers
A.Enable Advanced Threat Protection for each database individually
B.Configure Microsoft Sentinel data connectors for Azure SQL Database
C.Enable Microsoft Defender for SQL at the server level
D.Stream SQL audit logs to a Log Analytics workspace
E.Create a diagnostic setting to stream metrics to Event Hub
AnswersB, C, D

Data connectors ingest logs into Sentinel.

Why this answer

Option B is correct because Microsoft Sentinel requires a data connector to ingest Azure SQL Database security events. The 'Azure SQL Database' data connector in Sentinel must be configured to pull diagnostic logs and alerts from the SQL resource into the Log Analytics workspace used by Sentinel, enabling advanced threat detection and incident response.

Exam trap

The trap here is that candidates often confuse enabling Microsoft Defender for SQL (server-level) with needing to enable Advanced Threat Protection per database, or they mistakenly think streaming metrics to Event Hub is the correct path for Sentinel integration instead of streaming audit logs to Log Analytics.

Practice this question →
31
Multi-Selectmedium

You are planning to migrate a large number of on-premises SQL Server databases to Azure SQL Managed Instance. The migration must be automated and support schema and data validation. Which TWO tools or services should you consider using? (Choose TWO.)

Select 2 answers
A.Azure SQL Migration tool
B.SQL Server Management Studio (SSMS)
C.Azure Database Migration Service
D.BACPAC export and import
E.Azure Data Studio with the SQL Migration extension
AnswersC, E

DMS supports automated online migrations with validation.

Why this answer

Azure Database Migration Service (DMS) is the correct choice because it is a fully managed service designed to automate the migration of on-premises SQL Server databases to Azure SQL Managed Instance with minimal downtime. It supports both schema and data validation through built-in assessment, data integrity checks, and cutover capabilities, making it ideal for large-scale automated migrations.

Exam trap

The trap here is that candidates often confuse the 'Azure SQL Migration tool' (a feature name) with the actual service 'Azure Database Migration Service,' or they mistakenly think SSMS or BACPAC can handle automated, large-scale migrations with validation.

Practice this question →
32
MCQeasy

You are configuring security for an Azure SQL Database. You need to ensure that only traffic from a specific virtual network and a specific set of public IP addresses can connect to the database. Which two features should you enable?

A.Microsoft Entra ID authentication and firewall rules
B.VNet service endpoints and firewall rules
C.Advanced Threat Protection and VNet service endpoints
D.Private endpoint and VNet service endpoints
AnswerB

Service endpoints restrict traffic from a VNet; firewall rules allow specific public IP addresses.

Why this answer

To restrict access to an Azure SQL Database to traffic from a specific virtual network and a specific set of public IP addresses, you need to combine VNet service endpoints with firewall rules. VNet service endpoints allow you to restrict inbound traffic from a specific subnet in a virtual network, while firewall rules (IP-based) allow you to specify allowed public IP address ranges. Together, they provide a layered network security approach that meets the requirement.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules, service endpoints) with identity/security monitoring features (Entra ID, ATP), leading them to pick options that address authentication or threat detection instead of network access restrictions.

How to eliminate wrong answers

Option A is wrong because Microsoft Entra ID authentication controls identity and access (who can connect), not network-level traffic filtering (where traffic originates). Option C is wrong because Advanced Threat Protection is a security monitoring and threat detection service, not a network access control mechanism. Option D is wrong because Private endpoint and VNet service endpoints are both network-level features, but private endpoint uses a private IP address from your VNet and does not support allowing a specific set of public IP addresses; it only allows traffic from the VNet, not from public IPs.

Practice this question →
33
Multi-Selecthard

You need to audit all successful and failed login attempts to an Azure SQL Database server for compliance. The audit logs must be stored in a Log Analytics workspace for analysis and retention. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Use SQL Server Audit to capture login events.
B.Enable auditing on the Azure SQL Database server and configure the audit destination as Log Analytics.
C.Configure diagnostic settings to send the SQL security audit events to Log Analytics.
D.Enable auditing on each database individually.
E.Enable login auditing by running ALTER SERVER CONFIGURATION SET AUDIT_LOGIN_EVENTS.
AnswersB, C

Auditing can send logs to Log Analytics.

Why this answer

Option B is correct because Azure SQL Database server-level auditing can be configured to send audit logs directly to a Log Analytics workspace, which is required for compliance analysis and retention. This captures both successful and failed login attempts at the server level, meeting the audit requirement without needing per-database configuration.

Exam trap

The trap here is that candidates confuse on-premises SQL Server audit commands (like ALTER SERVER CONFIGURATION) with Azure SQL Database's cloud-native auditing, which requires diagnostic settings or server-level audit configuration, not T-SQL commands.

Practice this question →
34
MCQeasy

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON. The source database 'db1' was created on 2025-03-01 and has a current backup retention of 7 days. The restore point is 2025-03-15T10:00:00Z. What will be the result of deploying this template?

A.The deployment will fail because the restore point is older than 7 days.
B.The deployment will succeed but the restore point will be ignored and the latest backup will be used.
C.The deployment will fail because the source database is not geo-replicated.
D.The deployment will succeed and create a new database restored to the specified point in time.
AnswerD

The restore point is valid and within retention.

Why this answer

Option D is correct because the ARM template specifies a restore point of 2025-03-15T10:00:00Z, which is within the 7-day backup retention period (the source database was created on 2025-03-01, so backups are available from 2025-03-01 to 2025-03-22). Azure SQL Database supports point-in-time restore (PITR) to any point within the configured backup retention window, and the deployment will create a new database restored to that exact timestamp. The restore point is valid and the template will succeed.

Exam trap

The trap here is that candidates mistakenly think the 7-day retention period is counted backward from the current date, when in fact it is counted forward from the database creation date, making a restore point 14 days after creation valid as long as it falls within the retention window.

How to eliminate wrong answers

Option A is wrong because the restore point 2025-03-15T10:00:00Z is only 14 days after the database creation date (2025-03-01), which is well within the 7-day backup retention period (backups are retained from creation date forward, not backward). Option B is wrong because the restore point is valid and will be honored; Azure SQL Database does not ignore a specified restore point within the retention window. Option C is wrong because geo-replication is not a prerequisite for point-in-time restore; PITR works on any single database regardless of geo-replication status.

Practice this question →
35
Multi-Selecteasy

Which TWO benefits does the Hyperscale service tier of Azure SQL Database provide?

Select 2 answers
A.Built-in in-memory OLTP support.
B.Zone-redundant configuration by default.
C.Up to 100 TB of database storage.
D.Fast provisioning of additional read replicas.
E.Zero data loss in all scenarios.
AnswersC, D

Hyperscale provides large storage capacity.

Why this answer

Option C is correct because the Hyperscale service tier of Azure SQL Database supports up to 100 TB of database storage, which is significantly larger than the 4 TB limit of the General Purpose tier and the 4 TB limit of the Business Critical tier. This massive storage capacity is achieved through a distributed architecture where data is stored in page servers with a shared disk model, allowing near-instantaneous scaling of storage without downtime.

Exam trap

The trap here is that candidates often confuse the Hyperscale tier's storage limit with the Business Critical tier's in-memory OLTP or zone-redundancy features, leading them to select options that are technically correct for other tiers but not for Hyperscale.

Practice this question →
36
MCQeasy

You are a database administrator for a healthcare organization. You need to deploy a new Azure SQL Database that stores protected health information (PHI). The database must be encrypted at rest using a customer-managed key in Azure Key Vault. Additionally, you need to ensure that backups are also encrypted with the same key. Which configuration should you use?

A.Use column-level encryption with a certificate
B.Enable Transparent Data Encryption (TDE) using a customer-managed key from Azure Key Vault
C.Enable Transparent Data Encryption with a service-managed key
D.Enable Always Encrypted with keys stored in Azure Key Vault
AnswerB

TDE encrypts the database and backups at rest, and customer-managed keys are supported.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault is the correct choice because it encrypts the database at rest, including data files and log files, using a key that you control and manage in Azure Key Vault. This meets the requirement for encrypting protected health information (PHI) at rest with a customer-managed key, and TDE automatically ensures that backups are encrypted with the same database encryption key (DEK), which is protected by the customer-managed key.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which encrypts data at the column level and requires application changes) with TDE (which encrypts the entire database at rest transparently), or they mistakenly think that service-managed keys satisfy a customer-managed key requirement.

How to eliminate wrong answers

Option A is wrong because column-level encryption with a certificate encrypts individual columns, not the entire database at rest, and does not automatically encrypt backups with the same key; it also requires application changes and does not meet the requirement for at-rest encryption of the whole database and backups. Option C is wrong because Transparent Data Encryption with a service-managed key uses a key managed by Microsoft, not a customer-managed key, so it fails the requirement for a customer-managed key in Azure Key Vault. Option D is wrong because Always Encrypted encrypts data in transit and at rest at the column level, but it does not encrypt the entire database or backups automatically; it also requires client-side key management and application changes, and does not fulfill the requirement for at-rest encryption of backups with the same key.

Practice this question →
37
Multi-Selecthard

You have an Azure SQL Database in the General Purpose tier. You need to implement high availability with automatic failover to a secondary database in a different Azure region. The solution must provide an RPO of 5 seconds and RTO of 1 hour. Which TWO components must you configure? (Select TWO.)

Select 2 answers
A.Change the service tier to Business Critical
B.Enable zone redundancy on the primary database
C.Configure active geo-replication
D.Create a secondary database in a different Azure region
E.Create an auto-failover group
AnswersC, D

Active geo-replication provides asynchronous replication to a secondary region.

Why this answer

Active geo-replication (Option C) creates a readable secondary database in a different Azure region, supporting cross-region disaster recovery. When combined with an auto-failover group, it provides automatic failover with an RPO of 5 seconds and an RTO of 1 hour, meeting the requirements for a General Purpose tier database.

Exam trap

The trap here is that candidates often confuse the auto-failover group (Option E) as a standalone component, not realizing it requires a pre-configured secondary database via active geo-replication to function, and they may incorrectly assume Business Critical tier (Option A) is necessary for cross-region failover when General Purpose with geo-replication suffices.

Practice this question →
38
MCQmedium

You are the DBA for a financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases is 2 TB in size and has a high transaction volume. The migration must have minimal downtime. The source environment is SQL Server 2019 running on Windows Server. The target is a General Purpose managed instance in the same region. The network bandwidth between on-premises and Azure is 500 Mbps. The DBA plans to use Azure Database Migration Service (DMS) for an online migration. However, during the initial sync, the migration is progressing slowly and the estimated time to completion is 48 hours. The DBA needs to reduce the migration time. What should the DBA do?

A.Switch to an offline migration using BACPAC export and import
B.Upgrade the DMS instance to a Premium SKU to increase performance
C.Request a higher bandwidth connection from the network team
D.Change the target to a different Azure region that is closer to the source
AnswerB

Premium SKU provides more CPU and memory for data movement.

Why this answer

Upgrading the DMS instance to a Premium SKU increases the underlying compute and I/O resources allocated to the migration service, which directly improves the throughput of data movement during the online migration. Since the bottleneck is the DMS processing speed rather than network bandwidth (500 Mbps is sufficient for 2 TB over 48 hours), a Premium SKU provides higher performance tiers that can process change data capture (CDC) and bulk inserts faster, reducing the overall sync time.

Exam trap

The trap here is that candidates often assume network bandwidth is the primary bottleneck and request a faster connection, but DMS performance is more commonly limited by its own SKU tier and the processing overhead of CDC, not the network pipe.

How to eliminate wrong answers

Option A is wrong because switching to an offline migration using BACPAC export/import would require taking the database offline, which contradicts the requirement for minimal downtime; BACPAC also does not support high transaction volumes well and would be slower than DMS online migration. Option C is wrong because the network bandwidth is 500 Mbps, which is already sufficient to transfer 2 TB in under 48 hours (theoretical max ~10.8 TB in 48 hours), so the bottleneck is not bandwidth but DMS processing capacity. Option D is wrong because changing the target region would increase latency and potentially reduce throughput due to longer network paths, and the source and target are already in the same region; a different region would not help and could violate compliance or latency requirements.

Practice this question →
39
MCQmedium

A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?

A.Transparent data encryption (TDE) and TLS 1.2
B.Dynamic data masking and row-level security
C.Azure Active Directory authentication and firewall rules
D.Always Encrypted and transparent data encryption (TDE)
AnswerA

TDE encrypts data at rest, TLS encrypts data in transit.

Why this answer

Option A is correct because Transparent Data Encryption (TDE) encrypts data at rest by performing real-time I/O encryption and decryption of the database, data files, and transaction logs, while TLS 1.2 encrypts data in transit between the client and Azure SQL Database. Together, they satisfy the requirement to encrypt all patient data both at rest and in transit.

Exam trap

The trap here is that candidates often confuse Always Encrypted with a complete encryption solution for both at rest and in transit, but it only encrypts specific columns and does not encrypt the entire transport channel, leaving the connection vulnerable without TLS.

How to eliminate wrong answers

Option B is wrong because Dynamic Data Masking (DDM) and Row-Level Security (RLS) control data visibility and access at the row level, but they do not encrypt data at rest or in transit. Option C is wrong because Azure Active Directory authentication and firewall rules manage identity and network access, but they provide no encryption of data at rest or in transit. Option D is wrong because Always Encrypted protects sensitive data in transit and at rest with client-side encryption, but when combined with TDE, it does not address the in-transit requirement for the entire connection (TLS is needed for the transport layer); moreover, Always Encrypted is not a replacement for TLS and is often overkill for the stated requirement, which is fully met by TDE + TLS 1.2.

Practice this question →
40
MCQmedium

You are configuring Azure SQL Database for a new e-commerce application that must support high read throughput for product catalog queries. The application uses Entity Framework Core and requires that read-only queries be offloaded to a secondary replica to reduce load on the primary. Which feature should you enable?

A.Enable read scale-out and configure the application to use read-only intent.
B.Enable Query Performance Insights and create indexes for frequent queries.
C.Configure Active Geo-Replication with a readable secondary in a different region.
D.Enable automatic tuning to force parameterization of queries.
AnswerA

Read scale-out uses a secondary replica for read-only queries.

Why this answer

Read scale-out in Azure SQL Database allows you to offload read-only workloads to a readable secondary replica by setting the application connection string's `ApplicationIntent=ReadOnly`. This reduces load on the primary replica, which is essential for high read throughput in an e-commerce catalog scenario. Entity Framework Core can use this by specifying `ReadOnly` in the connection string or via a custom interceptor.

Exam trap

The trap here is that candidates often confuse read scale-out with Active Geo-Replication, assuming that any readable secondary must be in a different region, but read scale-out works within the same region and is specifically designed for read-only workload offloading.

How to eliminate wrong answers

Option B is wrong because Query Performance Insights is a diagnostic tool for identifying query performance issues, not a feature to offload read traffic to a secondary replica. Option C is wrong because Active Geo-Replication with a readable secondary is designed for disaster recovery and regional read scaling, not for offloading read-only queries within the same region to reduce primary load; it also introduces replication lag and cross-region latency. Option D is wrong because automatic tuning with forced parameterization optimizes query plans by parameterizing non-parameterized queries, but it does not redirect read traffic to a secondary replica.

Practice this question →
41
MCQmedium

You are designing a disaster recovery solution for a critical Azure SQL Database that runs in the West US region. The database is 2 TB in size and requires a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds. Which deployment option should you recommend?

A.Use Azure SQL Database General Purpose with auto-failover group
B.Use Azure SQL Database Hyperscale with geo-replication
C.Use Azure SQL Database Business Critical with zone-redundant configuration
D.Use Azure SQL Managed Instance Business Critical with zone-redundant configuration
AnswerC

Business Critical with zone redundancy provides synchronous replication across zones, achieving RPO=0 and RTO~30s.

Why this answer

Azure SQL Database Business Critical with zone-redundant configuration is the correct choice because it provides synchronous replication across three availability zones within the same region, ensuring an RPO of 0 (no data loss) and an RTO of under 30 seconds for zonal failures. This meets the strict RPO of less than 5 seconds and RTO of less than 30 seconds for a 2 TB database, as Business Critical offers high-performance local SSD storage and automatic failover without data loss.

Exam trap

The trap here is that candidates often confuse Hyperscale's geo-replication with synchronous replication, but Hyperscale uses asynchronous replication with a typical RPO of 5-10 seconds, which does not guarantee the sub-5-second RPO required, while Business Critical zone-redundant provides synchronous replication within the same region.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database General Purpose uses asynchronous replication with an RPO of up to 5 minutes and an RTO of 1-2 hours, failing to meet the sub-5-second RPO and sub-30-second RTO requirements. Option B is wrong because Azure SQL Database Hyperscale with geo-replication uses asynchronous replication with an RPO of 5 seconds or more (typically 5-10 seconds) and an RTO of 1-2 hours, which does not guarantee the required RPO of less than 5 seconds and RTO of less than 30 seconds. Option D is wrong because Azure SQL Managed Instance Business Critical with zone-redundant configuration is designed for managed instances, not single Azure SQL Database deployments, and does not support the same auto-failover group capabilities for a single database; additionally, it targets a different service tier and deployment model.

Practice this question →
42
MCQhard

Your Azure SQL Managed Instance is experiencing performance degradation. You suspect a query plan regression caused by parameter-sensitive plan issues. Which feature should you use to identify and resolve the issue?

A.Intelligent Insights
B.Automatic tuning
C.Query Store with Query Store Hints
D.Database Advisor
AnswerC

Query Store tracks query performance and Query Store Hints allow forcing a specific plan to resolve regression.

Why this answer

Query Store with Query Store Hints is the correct feature because it allows you to identify parameter-sensitive plan regression by analyzing historical execution plans stored in Query Store, and then force a specific plan using a hint without changing application code. This directly addresses the root cause of parameter-sensitive plan issues, where different parameter values lead to suboptimal cached plans.

Exam trap

The trap here is that candidates often confuse Intelligent Insights or Automatic tuning as the solution for plan regression, but these features do not provide the ability to manually force a specific plan, which is required for parameter-sensitive plan issues where you need to pin a known good plan.

How to eliminate wrong answers

Option A is wrong because Intelligent Insights is a diagnostic feature that provides proactive health monitoring and root-cause analysis for Azure SQL databases, but it does not allow you to force or hint a specific query plan to resolve parameter-sensitive plan regression. Option B is wrong because Automatic tuning can automatically adjust query plans based on performance, but it does not provide the granular, manual control needed to identify and force a specific plan for parameter-sensitive issues; it may also change plans automatically without your explicit approval. Option D is wrong because Database Advisor provides recommendations for index creation, query performance, and schema changes, but it does not offer the ability to view historical execution plans or apply query hints to fix plan regression.

Practice this question →
43
Matchingmedium

Match each Azure SQL Database service tier to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Suitable for small databases with low performance requirements

Balanced performance for most production workloads

High performance and low latency for mission-critical workloads

Highly scalable storage and compute for large databases

Auto-scaling compute based on workload demand

Why these pairings

These are the main service tiers in Azure SQL Database, each designed for different performance and scalability needs.

Practice this question →
44
MCQhard

You have an Azure SQL Database with a table that stores historical data. To improve query performance, you create a nonclustered columnstore index on the table. However, after a large data load, you notice that query performance is worse than before. What is the most likely reason?

A.The columnstore index is fragmented and needs to be rebuilt.
B.The data was loaded in small batches, causing rowgroups to be smaller than optimal.
C.The table has too many columns included in the index.
D.The index was created with compression disabled.
AnswerB

Small rowgroups reduce compression and query performance.

Why this answer

Option B is correct because when data is loaded in small batches into a columnstore index, each batch may form its own rowgroup. Rowgroups with fewer than 102,400 rows are considered 'delta rowgroups' and are not compressed into the columnstore format, leading to poor compression and suboptimal query performance. The large data load likely consisted of many small batches, resulting in numerous small rowgroups that degrade scan performance.

Exam trap

The trap here is that candidates often assume any performance degradation after an index creation is due to fragmentation (Option A), but the DP-300 exam specifically tests the understanding that columnstore indexes require large batch loads to achieve optimal rowgroup size and compression.

How to eliminate wrong answers

Option A is wrong because columnstore index fragmentation is not the primary issue; fragmentation occurs from deletes and updates, not from small batch loads. Option C is wrong because including too many columns in a columnstore index does not inherently cause worse performance; columnstore indexes are designed to handle many columns efficiently by storing each column separately. Option D is wrong because columnstore indexes always use columnstore compression by default; compression cannot be disabled, and even if it could, disabling compression would not cause worse performance—it would reduce storage savings but not degrade query speed.

Practice this question →
45
MCQmedium

You are the database administrator for a large e-commerce company. The company has a SQL Server 2019 Enterprise Edition instance running on-premises with multiple databases. One of the databases, 'OrdersDB', is 2 TB in size and has a high transaction rate. The company plans to migrate 'OrdersDB' to Azure SQL Managed Instance to reduce operational overhead. The migration must have minimal downtime, and the target service tier must be Business Critical with 16 vCores. You have set up a test environment and performed a successful test migration using the Azure Database Migration Service (DMS) with online mode. During the final migration, the network bandwidth between on-premises and Azure is limited to 100 Mbps. The migration is taking longer than expected, and you are concerned about the log backup growth. What should you do to minimize the risk of the migration failing due to log space exhaustion?

A.Switch to offline migration mode to avoid log growth issues.
B.Pause the migration and request a higher network bandwidth from the ISP.
C.Reduce the Azure SQL Managed Instance storage performance tier to standard to reduce cost.
D.Increase the frequency of transaction log backups on the source database to keep the log small.
AnswerD

Frequent log backups prevent log file from growing too large during migration.

Why this answer

Option D is correct because increasing the frequency of transaction log backups on the source database reduces the size of the active log, preventing log space exhaustion during the long-running online migration. The Azure Database Migration Service (DMS) in online mode continuously captures and applies log changes, so a smaller log reduces the risk of the log file filling up before backups can be taken, especially under constrained network bandwidth.

Exam trap

The trap here is that candidates may think offline migration is the only way to avoid log growth, but the question explicitly requires minimal downtime, making online mode mandatory, and the correct solution is to manage the log backup frequency rather than changing the migration mode or bandwidth.

How to eliminate wrong answers

Option A is wrong because switching to offline migration mode would require taking the source database offline, causing significant downtime, which contradicts the requirement for minimal downtime. Option B is wrong because pausing the migration and requesting higher bandwidth does not address the immediate risk of log space exhaustion; it only addresses the slow transfer speed, and bandwidth upgrades are not a quick or guaranteed solution. Option C is wrong because reducing the Azure SQL Managed Instance storage performance tier to standard does not affect log growth on the source database and could degrade target performance, potentially causing migration failures due to throttling or timeouts.

Practice this question →
46
Multi-Selecthard

Which TWO features can help you monitor and troubleshoot a query performance issue in Azure SQL Database without making changes to the application code?

Select 2 answers
A.Using sp_setapprole to set application roles.
B.Enabling Automatic Tuning to force plan corrections.
C.Implementing Always Encrypted for sensitive columns.
D.Using Azure Data Migration Assistant for performance assessment.
E.Enabling Query Store and reviewing regressed queries.
AnswersB, E

Automatic Tuning works without code changes.

Why this answer

Option B is correct because Automatic Tuning in Azure SQL Database can automatically identify and force plan corrections for regressed query execution plans without requiring any application code changes. It uses the Query Store to detect plan regressions and applies the last known good plan, improving query performance transparently. Option E is correct because Query Store captures query execution statistics and plan history, allowing you to review regressed queries and manually force a better plan without modifying the application.

Exam trap

The trap here is that candidates may confuse features that improve security (Always Encrypted, application roles) with performance monitoring tools, or assume that migration tools (Data Migration Assistant) are used for ongoing performance troubleshooting.

Practice this question →
47
MCQmedium

You are analyzing the SQL script in the exhibit. This script is used to query data stored in Azure Blob Storage from Azure SQL Database. What is the primary purpose of the database scoped credential?

A.To store the shared access signature token for accessing the blob storage.
B.To define the file format of the external data.
C.To specify the schema of the external table.
D.To provide the location of the external data source.
AnswerA

The credential holds the SAS token for authentication.

Why this answer

The database scoped credential in this context securely stores the Shared Access Signature (SAS) token required to authenticate and authorize access to Azure Blob Storage. When creating an external data source with the CREDENTIAL option, SQL Database uses this credential to pass the SAS token to the storage endpoint, enabling read operations for PolyBase or external table queries.

Exam trap

The trap here is that candidates confuse the credential's role with the external data source's LOCATION or the external table's schema, but the credential is strictly an authentication mechanism, not a metadata or location definition.

How to eliminate wrong answers

Option B is wrong because defining the file format of external data is the role of an external file format object (CREATE EXTERNAL FILE FORMAT), not a credential. Option C is wrong because specifying the schema of the external table is done via the CREATE EXTERNAL TABLE statement with column definitions, not a credential. Option D is wrong because providing the location of the external data source is the purpose of the CREATE EXTERNAL DATA SOURCE statement (which includes the LOCATION parameter), while the credential only supplies authentication secrets.

Practice this question →
48
MCQmedium

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses a SQL Server Agent job that runs a PowerShell script to process files. You need to ensure the job continues to run after migration with minimal changes. What should you do?

A.Migrate the database to Azure SQL Managed Instance and recreate the job using SQL Server Agent.
B.Migrate the database to Azure SQL Managed Instance and configure an Azure Automation runbook to run the script.
C.Migrate the database to Azure SQL Managed Instance and use Elastic Jobs to run the PowerShell script.
D.Migrate the database to Azure SQL Database and use Elastic Jobs to run the PowerShell script.
AnswerA

Azure SQL Managed Instance supports SQL Server Agent, so the job can run with minimal changes.

Why this answer

Azure SQL Managed Instance supports SQL Server Agent, including jobs that run PowerShell scripts via CmdExec or PowerShell job steps. By migrating the database and recreating the job with the same script, you preserve the existing automation with minimal changes. This is the only option that keeps the job execution environment identical to the on-premises setup.

Exam trap

The trap here is that candidates assume Azure SQL Database is a suitable target because it is the most common PaaS offering, but they overlook that SQL Managed Instance is required to retain SQL Server Agent and PowerShell job step support, which Azure SQL Database lacks entirely.

How to eliminate wrong answers

Option B is wrong because Azure Automation runbooks require additional configuration, credential management, and do not integrate directly with SQL Managed Instance's native job scheduling; this introduces unnecessary complexity and deviates from the minimal-change requirement. Option C is wrong because Elastic Jobs are designed for Azure SQL Database and SQL Managed Instance, but they execute T-SQL scripts, not PowerShell scripts, and would require rewriting the job logic. Option D is wrong because Azure SQL Database does not support SQL Server Agent or PowerShell job steps; migrating to Azure SQL Database would lose this functionality entirely, and Elastic Jobs still cannot run PowerShell scripts.

Practice this question →
49
Multi-Selecteasy

Which TWO are valid ways to secure access to an Azure SQL Database?

Select 2 answers
A.Use IPsec VPN from on-premises
B.Configure a Virtual Network service endpoint
C.Configure a Private Endpoint
D.Join the database to an on-premises Active Directory domain
E.Disable all firewall rules and rely only on authentication
AnswersB, C

Restricts traffic to a specific VNet.

Why this answer

Option B is correct because a Virtual Network service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection. This allows you to secure access by limiting connectivity to only traffic originating from a specific subnet in your VNet, effectively blocking public internet access while using the Azure backbone network.

Exam trap

The trap here is that candidates often confuse 'securing access' with 'authentication methods' and incorrectly assume that disabling firewall rules and relying solely on authentication (Option E) is valid, or they think an on-premises AD domain join (Option D) applies to Azure SQL Database, when in fact Azure SQL only supports Azure AD authentication and network-level controls like service endpoints or private endpoints are required for secure access.

Practice this question →
50
MCQeasy

Your company is deploying a new application that will use Azure SQL Database. You need to ensure that all connections to the database use Microsoft Entra ID authentication. Which step is required to enable this?

A.Configure an Entra ID administrator for the Azure SQL logical server.
B.Enable the Azure SQL Database firewall to allow only Entra ID IP addresses.
C.Create a contained database user mapped to an Entra ID identity.
D.Set the database to read-only mode.
AnswerA

Setting an Entra ID administrator enables Entra ID authentication for the server.

Why this answer

To enforce Microsoft Entra ID authentication for all connections to Azure SQL Database, you must first configure an Entra ID administrator at the logical server level. This step establishes the server’s trust relationship with the Entra ID tenant, enabling token-based authentication using OAuth 2.0. Without this administrator, Entra ID authentication cannot be used, and only SQL authentication would be available.

Exam trap

The trap here is that candidates often confuse the server-level Entra ID administrator configuration (a prerequisite) with the creation of contained database users (a downstream step), leading them to select Option C as the first required step.

How to eliminate wrong answers

Option B is wrong because the Azure SQL Database firewall controls network access by IP address or virtual network rules, not authentication methods; it cannot restrict authentication to Entra ID only. Option C is wrong because creating a contained database user mapped to an Entra ID identity is a step taken after the server-level Entra ID administrator is configured, not the prerequisite to enable Entra ID authentication. Option D is wrong because setting the database to read-only mode prevents write operations but does not enforce any authentication method; it is unrelated to authentication configuration.

Practice this question →
51
MCQeasy

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

A.TEXT
B.NVARCHAR
C.XML
D.VARCHAR
AnswerB

NVARCHAR supports JSON storage and querying.

Why this answer

B is correct because Azure SQL Database uses the NVARCHAR data type to store JSON documents as text. JSON is stored as a string in NVARCHAR columns, which supports Unicode characters and allows the use of built-in JSON functions like JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON for validation and querying. NVARCHAR(MAX) is recommended for large or variable-length JSON documents.

Exam trap

The trap here is that candidates often assume JSON requires a special data type like XML or a binary format, but Azure SQL Database stores JSON as plain text in NVARCHAR, leveraging the same string-based approach used for other semi-structured data.

How to eliminate wrong answers

Option A is wrong because TEXT is a deprecated data type in SQL Server and Azure SQL Database, and it does not support the JSON functions or Unicode characters needed for modern JSON processing. Option C is wrong because XML is designed for XML data and its associated XQuery functions, not for JSON; using XML would require conversion and lacks native JSON support. Option D is wrong because VARCHAR is a non-Unicode data type that cannot properly store JSON containing Unicode characters, and it is not optimized for the JSON functions in Azure SQL Database.

Practice this question →
52
MCQmedium

Your organization has multiple Azure SQL Databases in an elastic pool. You need to monitor the performance of the pool and identify which databases are consuming the most resources. You want to view historical resource usage for the past month. Which tool should you use?

A.Azure SQL Analytics (Azure Monitor)
B.Azure portal metrics for the elastic pool
C.SQL Server Profiler
D.Query Performance Insight
AnswerA

Provides historical metrics for elastic pools and databases.

Why this answer

Azure SQL Analytics (Azure Monitor) is the correct tool because it provides built-in monitoring and alerting for elastic pools, including historical resource usage (CPU, DTU, storage) for up to 30 days. It aggregates data across all databases in the pool and allows you to drill down into individual database consumption, making it ideal for identifying top resource consumers over the past month.

Exam trap

The trap here is that candidates often confuse Query Performance Insight (which is per-database and query-focused) with Azure SQL Analytics (which is pool-wide and resource-focused), leading them to choose D instead of A.

How to eliminate wrong answers

Option B is wrong because Azure portal metrics for the elastic pool only show real-time or near-real-time metrics (up to 30 days of data retention) but lack the pre-built historical analysis and per-database breakdown that Azure SQL Analytics offers; they are more suited for ad-hoc monitoring. Option C is wrong because SQL Server Profiler is a client-side tracing tool for capturing individual query events (e.g., stored procedure calls, deadlocks) and is not designed for historical resource usage analysis of an elastic pool. Option D is wrong because Query Performance Insight focuses on individual database query performance (e.g., top queries by CPU, duration) and does not provide aggregated historical resource usage across an entire elastic pool.

Practice this question →
53
MCQhard

You are migrating a large on-premises SQL Server database (5 TB) to Azure SQL Managed Instance. The network bandwidth between your on-premises data center and Azure is limited to 100 Mbps. You need to minimize the migration time. Which approach should you use?

A.Use Azure Data Box to physically transfer the data.
B.Use transaction log shipping to continuously sync changes.
C.Export as BACPAC and upload to Azure Blob Storage, then import.
D.Use Azure Database Migration Service (DMS) online migration.
AnswerA

Data Box bypasses network limitations.

Why this answer

Azure Data Box is the correct approach because it physically transfers the 5 TB database via a secure storage device, bypassing the 100 Mbps network bottleneck. At 100 Mbps, a 5 TB transfer would take approximately 5.5 days continuously, while Data Box can complete the transfer in a few days including shipping, making it the fastest option for such a large dataset over a limited bandwidth link.

Exam trap

The trap here is that candidates often overestimate the speed of online migration services like DMS or BACPAC, failing to calculate that at 100 Mbps, a 5 TB transfer takes over 5 days, making physical shipping via Data Box the faster choice despite the initial impression that 'online' is always quicker.

How to eliminate wrong answers

Option B is wrong because transaction log shipping requires an initial full backup transfer over the network, which would still be bottlenecked by the 100 Mbps link, and continuous log shipping does not minimize the initial migration time. Option C is wrong because exporting a 5 TB database as a BACPAC file is extremely slow due to the serial export process and the subsequent upload over 100 Mbps would take days, making it impractical for minimizing migration time. Option D is wrong because Azure Database Migration Service online migration relies on continuous data replication over the network, which is constrained by the 100 Mbps bandwidth and would take an excessively long time for the initial sync of 5 TB.

Practice this question →
54
MCQeasy

A company plans to deploy Azure SQL Database in a new region. They require that the database be accessible only from a specific set of Azure virtual machines in the same region. What should the DBA configure?

A.Use a private endpoint only
B.Configure a VNet service endpoint and a firewall rule for the VNet subnet
C.Configure a point-to-site VPN
D.Set the firewall to allow Azure services and resources to access the server
AnswerB

This restricts access to the specified VNet.

Why this answer

Option B is correct because a VNet service endpoint extends your virtual network private address space and the identity of your VNet to the Azure SQL Database service over a direct connection. By combining a service endpoint on the subnet with a firewall rule that allows traffic only from that specific VNet/subnet, you restrict access exclusively to Azure VMs in that subnet, meeting the requirement without exposing the database to the public internet.

Exam trap

The trap here is that candidates often confuse private endpoints with service endpoints, assuming a private endpoint alone provides network-level access control, when in fact it only provides a private IP connection and still requires explicit firewall rules to restrict which VMs can use it.

How to eliminate wrong answers

Option A is wrong because a private endpoint uses a private IP from the VNet to connect to Azure SQL Database, but it does not by itself restrict access to only a specific set of VMs; it still requires a firewall rule to block other traffic, and the question asks for a solution that ensures accessibility only from specific VMs, which a private endpoint alone does not enforce. Option C is wrong because a point-to-site VPN is designed for individual client machines connecting from outside Azure (e.g., on-premises or remote users) and does not restrict access to a specific set of Azure VMs within the same region; it also adds unnecessary complexity and latency. Option D is wrong because allowing Azure services and resources to access the server opens the database to all Azure IP addresses (including other subscriptions and regions), which violates the requirement of restricting access to only a specific set of Azure VMs.

Practice this question →
55
MCQmedium

You are designing a database migration from on-premises SQL Server 2019 to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local certificate store. The target is a General Purpose SQL Managed Instance. Which migration method should you use to minimize downtime and preserve TDE?

A.Use the SQL Server Import/Export Wizard to copy data.
B.Export the database as a BACPAC file and import it into the managed instance.
C.Use transaction log shipping from the source to the target.
D.Use Azure Database Migration Service (DMS) with online mode.
AnswerD

DMS online mode supports TDE-enabled databases and minimizes downtime.

Why this answer

Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal-downtime migrations by continuously replicating changes from the source SQL Server to the target Azure SQL Managed Instance using the transactional replication technology. DMS also preserves Transparent Data Encryption (TDE) by migrating the TDE certificate and key to the managed instance, ensuring the database remains encrypted throughout the process.

Exam trap

The trap here is that candidates often assume transaction log shipping (Option C) is viable for Azure SQL Managed Instance, but it is not supported; DMS online mode is the correct minimal-downtime method that also handles TDE preservation.

How to eliminate wrong answers

Option A is wrong because the SQL Server Import/Export Wizard only copies data at the table level, not the entire database, and it does not preserve TDE or support minimal downtime. Option B is wrong because exporting a BACPAC file does not preserve TDE; the exported data is decrypted, and the import process would require re-encrypting the database on the target, causing downtime and potential data exposure. Option C is wrong because transaction log shipping is not supported for Azure SQL Managed Instance as a target; it is a SQL Server on-premises feature and cannot be used to replicate logs to a managed instance.

Practice this question →
56
MCQhard

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

A.Disable and re-enable Query Store to reset the plans.
B.Execute sys.sp_query_store_set_hints to add a query hint.
C.Use sys.dm_db_tuning_recommendations to apply a plan.
D.Execute sys.sp_query_store_force_plan with the query_id and plan_id.
AnswerD

Forces Query Store to use a specific plan.

Why this answer

Option D is correct because `sys.sp_query_store_force_plan` is the dedicated system stored procedure in Azure SQL Database that forces the Query Store to use a specific execution plan for a given query. When a stored procedure's performance regresses, you identify the query_id and plan_id of the better-performing plan from Query Store views and execute this procedure to pin that plan, overriding the optimizer's choice.

Exam trap

The trap here is that candidates confuse the DMV for recommendations (`sys.dm_db_tuning_recommendations`) with the actual stored procedure to enforce a plan, or they think resetting Query Store is a valid troubleshooting step when it actually destroys historical data.

How to eliminate wrong answers

Option A is wrong because disabling and re-enabling Query Store would clear all historical plan data and force statistics, losing the ability to identify and force a previous good plan, which is counterproductive. Option B is wrong because `sys.sp_query_store_set_hints` is used to add query-level hints (e.g., `RECOMPILE`, `MAXDOP`) to influence future plan generation, not to force an existing historical plan. Option C is wrong because `sys.dm_db_tuning_recommendations` is a dynamic management view that surfaces automated tuning recommendations (e.g., create/drop indexes, force plan), but applying a plan force requires executing `sys.sp_query_store_force_plan` directly; the DMV itself does not apply changes.

Practice this question →
57
MCQhard

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

A.Business Critical tier with 8 vCores
B.Serverless tier with auto-pause
C.General Purpose tier with 2 vCores
D.Hyperscale tier with 4 vCores
AnswerD

Hyperscale supports high INSERT throughput and RCSI is enabled by default.

Why this answer

The Hyperscale tier with 4 vCores is correct because it supports up to 100 TB of storage, provides fast scaling for high-volume INSERT workloads (10,000 rows/second), and natively supports read-committed snapshot isolation (RCSI) without additional configuration. It minimizes cost compared to Business Critical while offering the necessary performance and isolation level for a 500-million-row table.

Exam trap

The trap here is that candidates may choose General Purpose (C) due to its lower cost, overlooking that it cannot sustain 10,000 INSERTs/second and does not natively support read-committed snapshot isolation without explicit configuration, while Hyperscale (D) is designed for such large-scale, high-throughput workloads with built-in RCSI.

How to eliminate wrong answers

Option A is wrong because Business Critical tier with 8 vCores is over-provisioned and more expensive than necessary; it provides high availability and low latency but is not cost-minimizing for this workload. Option B is wrong because Serverless tier with auto-pause is unsuitable for sustained high-throughput INSERT operations (10,000 rows/second) as it can cause performance throttling and auto-pause delays during peak hours. Option C is wrong because General Purpose tier with 2 vCores cannot handle 10,000 INSERTs per second due to its limited IOPS and log write throughput, and it does not natively support read-committed snapshot isolation without enabling READ_COMMITTED_SNAPSHOT; it would also struggle with a 500-million-row table size.

Practice this question →
58
MCQhard

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that the database is experiencing high I/O latency during peak hours. After analyzing the query performance, you determine that the primary bottleneck is due to log write throughput. You need to reduce log write latency without changing the service tier. What should you do?

A.Configure Query Store hints to force parameterization.
B.Enable read scale-out to offload read queries.
C.Scale up to a higher Hyperscale service objective.
D.Increase the MAXDOP setting for the database.
AnswerC

Higher SLO increases the log rate limit and reduces log write latency.

Why this answer

Option C is correct because scaling up to a higher Hyperscale service objective increases the log write throughput by provisioning more log I/O capacity and faster storage. Since the bottleneck is specifically log write latency, and you cannot change the service tier, increasing the service objective within Hyperscale directly addresses the issue by allocating more resources to the log write path.

Exam trap

The trap here is that candidates may confuse scaling up with changing the service tier, or think that read scale-out or query hints can solve a write-specific bottleneck, when in fact only increasing the service objective within the same tier addresses log write throughput.

How to eliminate wrong answers

Option A is wrong because Query Store hints to force parameterization help with plan reuse and query performance, but they do not affect log write throughput or I/O latency. Option B is wrong because enabling read scale-out offloads read queries to a secondary replica, which does not reduce log write latency on the primary replica. Option D is wrong because increasing MAXDOP can reduce parallelism and potentially lower CPU pressure, but it does not improve log write throughput or reduce log I/O latency.

Practice this question →
59
Multi-Selectmedium

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Select 2 answers
A.Create contained database users mapped to Microsoft Entra ID identities
B.Assign a Microsoft Entra ID user or group as the Azure SQL Database admin
C.Set the database's authentication mode to 'Azure AD Only Authentication'
D.Deploy the database in Azure SQL Managed Instance
E.Register the application in Microsoft Entra ID
AnswersB, C

Required to enable Entra ID authentication.

Why this answer

Assigning a Microsoft Entra ID user or group as the Azure SQL Database admin (Option B) is required because this establishes the administrative identity that can manage authentication for the database. Without this step, Microsoft Entra ID identities cannot be granted access or used to authenticate. Setting the database's authentication mode to 'Azure AD Only Authentication' (Option C) is also required because it disables SQL authentication, ensuring that only Microsoft Entra ID identities can connect, which is a key requirement for enabling Microsoft Entra ID authentication.

Exam trap

The trap here is that candidates often think creating contained database users (Option A) is the first step, but the correct order is to first assign an Entra ID admin and set the authentication mode, then create users mapped to Entra ID identities.

Practice this question →
60
MCQeasy

You need to migrate an on-premises SQL Server database to Azure SQL Database. The database uses SQL Server Integration Services (SSIS) packages for ETL. You want to continue using SSIS in the cloud with minimal changes. Which Azure service should you use?

A.Use Azure Data Factory with Azure-SSIS Integration Runtime
B.Deploy SSIS on Azure SQL Database
C.Migrate SSIS packages to Azure Databricks
D.Use Azure SQL Managed Instance to run SSIS
AnswerA

Azure-SSIS IR in ADF allows running existing SSIS packages in the cloud.

Why this answer

Azure Data Factory with Azure-SSIS Integration Runtime is the correct choice because it provides a fully managed service that lifts and shifts existing SSIS packages to the cloud with minimal changes. The Azure-SSIS IR runs a dedicated cluster of Azure VMs that host the SSIS engine, allowing you to deploy, execute, and manage your existing SSIS packages directly in Azure without rewriting code.

Exam trap

The trap here is confusing Azure SQL Managed Instance with a full SSIS host; candidates often assume Managed Instance runs SSIS natively, but it only supports SQL Server Agent jobs, not the SSIS runtime itself.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not support running SSIS packages; it is a PaaS database service without an SSIS runtime. Option C is wrong because Azure Databricks is a Spark-based analytics platform, not designed to run SSIS packages, and migrating would require significant re-engineering of ETL logic. Option D is wrong because Azure SQL Managed Instance does not natively run SSIS; it supports SQL Server Agent but not the SSIS runtime, and you would still need Azure-SSIS IR for SSIS execution.

Practice this question →
61
MCQmedium

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

A.Deploy each tenant's database as a single database with reserved capacity
B.Use a single Hyperscale database with schema per tenant
C.Use a single Azure SQL Managed Instance with multiple databases
D.Use an elastic pool and add databases as needed
AnswerD

Elastic pool allows sharing resources and scaling based on aggregate load.

Why this answer

Elastic pools are designed for multi-tenant SaaS scenarios where each tenant has its own database but usage patterns are unpredictable. They provide performance isolation through per-database resource limits (e.g., min/max DTUs or vCores) while sharing a fixed pool of resources, which minimizes cost by allowing idle databases to borrow from others. This matches the requirement of fluctuating tenant counts and cost efficiency.

Exam trap

The trap here is that candidates confuse 'performance isolation' with 'dedicated resources' and choose single databases (Option A), not realizing that elastic pools provide isolation via per-database resource caps while sharing a common pool for cost efficiency.

How to eliminate wrong answers

Option A is wrong because deploying each tenant's database as a single database with reserved capacity locks in fixed resources per database, leading to over-provisioning and higher costs when tenant activity fluctuates. Option B is wrong because a single Hyperscale database with schema per tenant breaks performance isolation — a noisy tenant can consume shared resources (e.g., log throughput or page server I/O) and impact others, plus Hyperscale is optimized for large, single databases, not multi-tenant isolation. Option C is wrong because Azure SQL Managed Instance is a single-instance deployment with shared resources across all databases; it lacks the per-database resource governance and elastic scaling of an elastic pool, and it is more expensive for many small databases.

Practice this question →
62
Multi-Selectmedium

You are planning to deploy Azure SQL Database for a new application. The application requires high availability and the ability to scale out read workloads. You need to choose the appropriate deployment option and feature. Which TWO options should you select? (Choose two.)

Select 2 answers
A.Use an elastic pool to group databases for resource sharing.
B.Enable zone redundancy for the database.
C.Configure a failover group with a readable secondary.
D.Deploy a single Azure SQL Database with locally redundant backup storage.
E.Configure active geo-replication with a readable secondary.
AnswersC, E

Provides high availability and read scaling.

Why this answer

Option C is correct because a failover group with a readable secondary provides both high availability and read-scale capabilities. The readable secondary allows read-only workloads to be offloaded, while automatic failover ensures business continuity during an outage.

Exam trap

The trap here is that candidates often confuse zone redundancy (which provides HA within a region but no read scaling) with geo-replication or failover groups (which provide both HA and read scaling), leading them to select option B instead of C or E.

Practice this question →
63
MCQhard

Your company has an Azure SQL Database configured with Active Geo-Replication between two regions. The primary database is experiencing performance degradation due to a sudden increase in write-intensive workloads. You need to minimize downtime and ensure no data loss. What should you do?

A.Enable read scale-out on the primary database
B.Initiate a forced failover to the secondary region
C.Increase the service tier of the primary database
D.Initiate a planned failover to the secondary region
AnswerD

Planned failover synchronizes all data before switching, ensuring zero data loss.

Why this answer

A planned failover (Option D) is correct because Active Geo-Replication supports a graceful, synchronous failover that promotes the secondary database to become the new primary without data loss. This is achieved by first replicating all pending transactions to the secondary, ensuring zero data loss, and then switching roles with minimal downtime. In contrast, a forced failover (Option B) would cause data loss because it does not wait for pending transactions to be replicated.

Exam trap

The trap here is that candidates confuse 'forced failover' (which causes data loss) with 'planned failover' (which ensures zero data loss), and they overlook that scaling the service tier does not address the need to minimize downtime during an active degradation.

How to eliminate wrong answers

Option A is wrong because enabling read scale-out on the primary database only offloads read-only queries to a secondary replica, but does not address write-intensive performance degradation or provide a failover mechanism. Option B is wrong because initiating a forced failover (also called an unplanned failover) will cause data loss, as it does not wait for pending transactions to be replicated to the secondary, violating the 'no data loss' requirement. Option C is wrong because increasing the service tier of the primary database (e.g., from S2 to S3 or from Standard to Premium) can improve performance but does not minimize downtime during an ongoing degradation; it requires a scaling operation that may take time and does not leverage geo-replication for high availability.

Practice this question →
64
MCQeasy

You are designing a new Azure SQL Database for an e-commerce application. The database will contain sensitive customer data and must be isolated from other tenants. Which deployment option provides the highest level of resource isolation?

A.Elastic pool
B.Hyperscale
C.Managed Instance
D.Single database
AnswerD

Single database provides dedicated resources and full isolation.

Why this answer

A single database in Azure SQL Database runs on a dedicated logical server and, when configured with the appropriate service tier and compute size, provides the highest level of resource isolation because it does not share CPU, memory, or IO resources with any other database. This ensures that the sensitive customer data is isolated from other tenants, meeting strict compliance and performance requirements.

Exam trap

The trap here is that candidates often confuse 'resource isolation' with 'scalability' or 'manageability,' leading them to choose Elastic Pool or Managed Instance, which offer shared resources or broader management features but not the highest level of isolation.

How to eliminate wrong answers

Option A is wrong because an elastic pool shares resources (DTU or vCore) among multiple databases, which can lead to resource contention and reduced isolation between tenants. Option B is wrong because Hyperscale is a service tier designed for large, scalable databases but still operates within a shared infrastructure model (e.g., page servers and log service) that does not guarantee the same level of resource isolation as a single database. Option C is wrong because Managed Instance provides instance-level isolation but is a Platform-as-a-Service (PaaS) offering that still shares underlying hardware resources with other instances in the same virtual cluster, whereas a single database can be provisioned on a dedicated logical server with no resource sharing.

Practice this question →
65
MCQhard

Refer to the exhibit. The exhibit shows a partial ARM template for deploying an Azure SQL Database server. You run the deployment but get an error stating the server cannot be created because of a missing firewall rule. The template has public network access disabled and no firewall rules defined. What is the most likely cause?

A.The firewallRules array must include at least one rule when publicNetworkAccess is Disabled.
B.The Entra ID admin SID is invalid (all zeros).
C.The tenantId is missing in the Entra ID admin configuration.
D.The Entra ID admin type should be 'Group' not 'User'.
AnswerB

A zero GUID is not a valid SID for a user in Microsoft Entra ID.

Why this answer

The error occurs because the Entra ID admin SID is set to all zeros ('00000000-0000-0000-0000-000000000000'), which is an invalid GUID. Azure SQL Database requires a valid, non-zero SID (object ID) for the Entra ID admin to establish authentication. When public network access is disabled, the server can still be created without firewall rules, but the invalid SID prevents the deployment from succeeding.

Exam trap

The trap here is that candidates assume public network access disabled requires a firewall rule, but the actual cause is the invalid Entra ID admin SID, which is a subtle validation check that Azure performs regardless of network settings.

How to eliminate wrong answers

Option A is wrong because when publicNetworkAccess is Disabled, no firewall rules are required; the server can be created with an empty firewallRules array. Option C is wrong because the tenantId is not required in the Entra ID admin configuration; the tenant is inferred from the subscription context during deployment. Option D is wrong because the Entra ID admin type can be either 'User' or 'Group'; 'User' is valid and does not cause a deployment failure.

Practice this question →
66
MCQhard

You are the Azure DBA for a global e-commerce company. The company uses Azure SQL Database for its product catalog. The database is in the Business Critical tier with 16 vCores and 1 TB of storage. During a major sales event, the application experiences severe performance degradation. Analysis reveals that the database is experiencing high write latency on the secondary replicas due to heavy read workload routed to them. The primary replica is not overloaded. The application uses read-only routing to direct queries to readable secondaries. The DBA needs to reduce write latency on the secondaries without affecting the read workload or incurring additional cost. What should the DBA do?

A.Scale up the service tier to Hyperscale to offload writes
B.Disable read-scale out for non-critical queries and use the primary for read-write
C.Add more replicas to distribute the read load
D.Remove the secondary replicas and use only the primary
AnswerB

Reducing read load on secondaries decreases write latency on replicas.

Why this answer

Option B is correct because disabling read-scale out for non-critical queries forces those queries to use the primary replica, reducing the read workload on the secondaries. This directly lowers the write latency on secondaries, as fewer reads compete for resources with the log apply process. The change does not affect the read workload for critical queries that still use read-only routing, and it incurs no additional cost.

Exam trap

The trap here is that candidates may assume adding more replicas or changing tiers is necessary, when the real solution is to selectively route read traffic away from secondaries to reduce contention on the log apply process without incurring cost.

How to eliminate wrong answers

Option A is wrong because scaling up to Hyperscale does not offload writes; Hyperscale uses a different architecture with a log service and page servers, but write latency on secondaries is not addressed by this tier change, and it would increase cost. Option C is wrong because adding more replicas would increase cost and, while it distributes the read load, it does not reduce the write latency on existing secondaries caused by heavy reads competing with log apply. Option D is wrong because removing secondary replicas eliminates read-scale out entirely, forcing all read workload to the primary, which could overload it and does not address the requirement to maintain the read workload.

Practice this question →
67
Multi-Selecteasy

Your company is deploying Azure SQL Database for a new application. The security policy requires that all queries be encrypted in transit and that the database be protected against accidental deletion. Which THREE configurations should you implement? (Select THREE.)

Select 3 answers
A.Set the minimum TLS version to 1.2
B.Configure firewall rules to allow only specific IP addresses
C.Enable Microsoft Entra ID authentication
D.Enable Transparent Data Encryption (TDE)
E.Enable a resource lock on the Azure SQL Database logical server
AnswersA, D, E

Ensures encrypted connections.

Why this answer

Setting the minimum TLS version to 1.2 ensures that all connections to Azure SQL Database use a secure, encrypted channel, enforcing encryption in transit as required by the security policy. TLS 1.2 is the minimum recommended version, as older versions (1.0, 1.1) are deprecated and vulnerable to attacks like POODLE and BEAST.

Exam trap

The trap here is that candidates often confuse encryption in transit (TLS) with encryption at rest (TDE) or access control (firewall rules, Entra ID), and overlook the resource lock as a separate, required configuration for deletion protection.

Practice this question →
68
MCQmedium

You are reviewing an ARM template snippet for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' in resource group 'rg1'. The source database 'db1' is in the same server 'server1' and is currently active. What will be the result of deploying this template?

A.The deployment will fail because sourceDatabaseId is specified but not used.
B.It will create a copy of db1 in the same server.
C.It will create a new empty database with the specified service objective.
D.It will perform a point-in-time restore of db1 to the current time.
AnswerC

Default createMode creates an empty database.

Why this answer

The ARM template snippet specifies `createMode: Default` and a `requestedServiceObjectiveName`, but does not include a `sourceDatabaseId` or `restorePointInTime` property. With `createMode: Default`, Azure SQL Database creates a new empty database using the specified service objective, regardless of the presence of a `sourceDatabaseId` in the template (which is ignored when `createMode` is not set to `Copy`, `PointInTimeRestore`, or `Secondary`). Therefore, the deployment succeeds and creates a new empty database named 'db2' with the defined performance tier.

Exam trap

The trap here is that candidates assume specifying a `sourceDatabaseId` automatically triggers a copy or restore operation, but Azure SQL Database only uses that property when the `createMode` is explicitly set to `Copy`, `PointInTimeRestore`, or `Secondary`; otherwise, it is silently ignored.

How to eliminate wrong answers

Option A is wrong because `sourceDatabaseId` is not used only when `createMode` is `Default`; the deployment does not fail—it simply ignores the property. Option B is wrong because creating a copy requires `createMode: Copy` and a valid `sourceDatabaseId`; with `createMode: Default`, no copy operation occurs. Option D is wrong because a point-in-time restore requires `createMode: PointInTimeRestore` and a `restorePointInTime` property; neither is present in the template.

Practice this question →
69
MCQhard

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

A.Azure SQL Managed Instance
B.Azure SQL Database
C.SQL Server on Azure Virtual Machine
D.Azure SQL Database with elastic query
AnswerA

SQL Managed Instance is a PaaS offering that supports SQL Server Agent, Service Broker, and cross-database queries.

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, Service Broker, and cross-database queries within the same instance. These features are not available in Azure SQL Database, which is a PaaS offering with a more restricted surface area. SQL Server on Azure VM is IaaS and requires manual management, while elastic query in Azure SQL Database only supports cross-database queries across different databases, not the full Service Broker or Agent functionality.

Exam trap

The trap here is that candidates often confuse Azure SQL Database with Azure SQL Managed Instance, assuming that all PaaS SQL offerings support the same features, but Azure SQL Database deliberately omits instance-scoped features like Agent and Service Broker to maintain a multi-tenant architecture.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not support SQL Server Agent jobs, Service Broker, or cross-database queries within the same instance; it uses elastic jobs and has limited cross-database query capabilities via elastic query. Option C is wrong because SQL Server on Azure Virtual Machine is an IaaS solution, not a PaaS option, and requires you to manage the underlying OS and SQL Server instance, including high availability and backups. Option D is wrong because Azure SQL Database with elastic query only enables cross-database queries across different databases in the same logical server, but it still lacks SQL Server Agent and Service Broker support, and is not a separate PaaS offering.

Practice this question →
70
MCQmedium

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

A.Azure SQL Database with active geo-replication
B.Azure SQL Managed Instance with failover groups
C.Azure SQL Database with failover groups
D.Azure SQL Database Hyperscale with geo-replication
AnswerD

Hyperscale geo-replication supports multiple readable secondaries across regions.

Why this answer

Azure SQL Database Hyperscale with geo-replication is the correct choice because it provides a single writable primary endpoint while supporting geo-distributed readable secondaries with low-latency reads for global users. Hyperscale's distributed architecture allows fast scaling and read scale-out, and geo-replication enables asynchronous replication to multiple regions, meeting the OLTP workload's low-latency read requirement without compromising the single write endpoint.

Exam trap

The trap here is that candidates often confuse active geo-replication (Option A) with failover groups (Option C), not realizing that Hyperscale's geo-replication (Option D) is the only option that combines a single write endpoint with the distributed read scale-out needed for global low-latency reads.

How to eliminate wrong answers

Option A is wrong because active geo-replication for Azure SQL Database supports readable secondaries but does not provide automatic failover groups with a listener endpoint, making it less suitable for a global application requiring managed failover and low-latency reads. Option B is wrong because Azure SQL Managed Instance with failover groups supports geo-replication but is not optimized for the Hyperscale distributed storage architecture that provides the fastest read scale-out for global OLTP workloads. Option C is wrong because Azure SQL Database with failover groups (non-Hyperscale) uses the standard tier, which has limited storage and performance scaling compared to Hyperscale, and does not offer the same level of read replica distribution for global low-latency reads.

Practice this question →
71
MCQmedium

A company uses Azure SQL Managed Instance for its line-of-business applications. They need to ensure that backups are retained for 35 days for compliance purposes. Which configuration should the DBA set?

A.Create a backup policy in Azure Backup Vault
B.Enable geo-redundant storage (GRS)
C.Set the backup retention period to 35 days in the backup settings
D.Configure long-term retention (LTR) policy
AnswerC

The backup retention can be set up to 35 days.

Why this answer

Option C is correct because Azure SQL Managed Instance allows you to configure the backup retention period directly in the backup settings, with a maximum of 35 days for point-in-time restore (PITR). This meets the compliance requirement without needing additional services or policies.

Exam trap

The trap here is that candidates confuse long-term retention (LTR) with the standard backup retention period, assuming LTR is required for any retention beyond the default 7 days, when in fact the 35-day maximum is configurable directly in the backup settings.

How to eliminate wrong answers

Option A is wrong because Azure Backup Vault is used for backing up Azure VMs, files, and workloads, not for managing native SQL Managed Instance backups, which are handled internally by the SQL engine. Option B is wrong because geo-redundant storage (GRS) is a storage redundancy option for backup files, not a retention period setting; it does not control how long backups are kept. Option D is wrong because long-term retention (LTR) is for retaining backups beyond 35 days (e.g., years), not for setting a 35-day retention, and it requires additional configuration and costs.

Practice this question →
72
MCQhard

You are migrating a 10-TB SQL Server database to Azure SQL Database Hyperscale. The source database has multiple filegroups and uses full-text indexing. The migration must be completed with minimal downtime. Which migration method should you use?

A.Set up transactional replication from on-premises to Azure
B.Use Azure Data Factory to copy data
C.Use the Azure SQL Migration extension in Azure Data Studio with online migration mode
D.Export a BACPAC file and import it to Azure
AnswerC

Supports online migration to Hyperscale with minimal downtime.

Why this answer

The Azure SQL Migration extension in Azure Data Studio with online migration mode is the correct choice because it supports minimal-downtime migrations for large databases (10 TB) with complex features like multiple filegroups and full-text indexing. It uses the Data Migration Service (DMS) to continuously replicate changes from the source to the target Azure SQL Database Hyperscale, allowing you to cut over with only a brief pause.

Exam trap

The trap here is that candidates often choose transactional replication (Option A) because it is associated with minimal downtime, but they overlook that it is not designed for one-time migrations of databases with full-text indexing and multiple filegroups, and it requires ongoing synchronization that is unnecessary for a migration cutover.

How to eliminate wrong answers

Option A is wrong because transactional replication requires schema changes (e.g., adding replication triggers) and does not natively support full-text indexing or multiple filegroups without complex manual configuration, and it introduces ongoing replication overhead that is not ideal for a one-time migration. Option B is wrong because Azure Data Factory is a data integration tool for bulk copy operations, not a migration service; it cannot perform online (minimal-downtime) replication of a live database with transactional consistency, and it lacks support for full-text indexes and filegroup structures. Option D is wrong because exporting a BACPAC file for a 10-TB database would take an extremely long time, and the import process requires the database to be offline or in read-only mode, causing significant downtime; BACPAC also does not preserve full-text indexes or multiple filegroups in Azure SQL Database Hyperscale.

Practice this question →
73
MCQmedium

You are designing a disaster recovery plan for an Azure SQL Database that is used by a critical application. The database is currently in the West US region. You need to ensure that if a regional outage occurs, the database can be failed over to another region with minimal data loss. The solution must also minimize costs for the secondary replica. Which deployment option should you recommend?

A.Configure active geo-replication to a secondary in East US using a lower service tier.
B.Deploy an auto-failover group with a secondary in East US using the same service tier.
C.Deploy a zone-redundant database in West US with a failover group to East US.
D.Use a failover group with a secondary in East US and enable zone redundancy on both.
AnswerA

Active geo-replication allows different service tiers for secondary, reducing costs.

Why this answer

Active geo-replication allows you to create a readable secondary database in a different region (East US) with a lower service tier, which reduces costs while still providing a disaster recovery target. In the event of a regional outage, you can manually initiate a failover to the secondary, and because replication is asynchronous, data loss is limited to the replication lag (typically a few seconds). This meets the requirement of minimal data loss and cost minimization.

Exam trap

The trap here is that candidates assume auto-failover groups are always the best choice for disaster recovery, but they fail to recognize that auto-failover groups require the secondary to have the same service tier, which conflicts with the cost minimization requirement.

How to eliminate wrong answers

Option B is wrong because deploying an auto-failover group with the same service tier on the secondary does not minimize costs; the secondary must match the primary's service tier for auto-failover groups, eliminating any cost savings. Option C is wrong because zone redundancy in West US only protects against zonal failures within the same region, not a regional outage; it does not provide a secondary in a different region for cross-region failover. Option D is wrong because enabling zone redundancy on both the primary and secondary increases costs unnecessarily and does not allow a lower service tier for the secondary, contradicting the cost minimization requirement.

Practice this question →
74
Multi-Selecthard

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

Select 3 answers
A.Create a server-level firewall rule on the secondary server to allow client IPs.
B.Add the primary database to the failover group.
C.Configure a grace period of at least 1 hour.
D.Create a secondary server in the target region with the same administrative login.
E.Set the failover group's read/write failover policy to 'Automatic' or 'Manual'.
AnswersB, D, E

Correct: The database must be added to the group.

Why this answer

Option B is correct because adding the primary database to the failover group is the essential step that associates the database with the group, enabling automated replication and failover to the secondary server. Without this step, the failover group has no database to replicate, and the readable secondary cannot be created or maintained.

Exam trap

The trap here is that candidates may think creating firewall rules on the secondary server is required for failover group setup, but those rules are only needed for direct client connections to the secondary, not for the replication or failover process itself.

Practice this question →
75
Multi-Selectmedium

Which THREE are features of Azure SQL Database Hyperscale?

Select 3 answers
A.Zone redundant compute by default
B.Unlimited log rate
C.Storage up to 100 TB
D.Near-instantaneous backups
E.Fast scaling of compute resources
AnswersC, D, E

Hyperscale supports up to 100 TB.

Why this answer

Azure SQL Database Hyperscale supports storage up to 100 TB by using a distributed architecture where data is stored across multiple page servers, each managing a subset of pages. This allows the database to scale beyond the 4 TB limit of other service tiers, making it suitable for very large databases.

Exam trap

The trap here is that candidates often assume 'unlimited' or 'by default' features based on marketing terms, but Azure SQL Database Hyperscale has specific limits and optional configurations that are not universally applied.

Practice this question →
Page 1 of 3 · 165 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Plan and implement data platform resources questions.

Start 20-question session