Free Microsoft DP-300 Practice Questions

Correct answer: A, C, E. total size of all the databases; number of concurrently peaking databases * peak CPU utilization per database; total number of databases * average CPU utilization per database

To properly size an Azure SQL Database elastic pool, you need three key metrics: A) the total size of all databases to determine storage requirements, C) the number of concurrently peaking databases multiplied by peak CPU utilization per database to understand peak computational demand, and E) the total number of databases multiplied by average CPU utilization per database to understand baseline resource consumption. These three metrics together provide the storage, peak load, and average load dimensions needed for accurate pool sizing.

Why the other options are wrong

• B. Geo-replication support is a feature configuration, not a sizing metric for the elastic pool itself.
• D. While concurrent sessions matter for capacity planning, the specific metric of maximum concurrent sessions for all databases is less relevant than CPU utilization metrics for elastic pool sizing.

Correct answer: B. PolyBase

PolyBase must be removed before migration to Azure SQL Database because Azure SQL Database does not support PolyBase functionality. Clustered columnstore indexes, automatic tuning, and change tracking are all supported features in Azure SQL Database, so they can remain in place during migration.

Why the other options are wrong

• A. Clustered columnstore indexes are fully supported in Azure SQL Database.
• C. Change tracking is a supported feature in Azure SQL Database and does not need to be removed.
• D. Automatic tuning is supported in Azure SQL Database and can be migrated without modification.

Correct answer: D. Azure Database Migration Service

Azure Database Migration Service (DMS) is specifically designed to minimize downtime and data loss during database migrations to Azure SQL Database managed instances. It provides capabilities for online migrations with continuous synchronization, allowing the source database to remain operational during the migration process. Distributed availability groups, database mirroring, and Always On Availability Groups are SQL Server features that cannot directly migrate to managed instances and are not appropriate for this scenario.

Why the other options are wrong

• A. Distributed availability groups are on-premises SQL Server features and do not facilitate migration to Azure.
• B. Database mirroring is a legacy SQL Server feature that cannot be used to migrate to Azure managed instances.
• C. Always On Availability Groups are on-premises features; while they can be part of a migration strategy, Azure DMS is the proper tool to orchestrate the migration itself.

Correct answer: D. Azure Event Hubs Dedicated

Azure Event Hubs Dedicated tier allows you to change the partition count after creation, providing flexibility for variable data volumes. Event Hubs Standard tier has a fixed partition limit and does not allow partition count changes after creation. Stream Analytics and Data Factory are processing and orchestration services, not ingestion services for managing partition configuration.

Why the other options are wrong

• A. Azure Event Hubs Standard does not support changing the partition count after the event hub is created.
• B. Azure Stream Analytics is a stream processing service, not an ingestion service with partition management capabilities.
• C. Azure Data Factory is an orchestration service for data movement and transformation, not a streaming ingestion service with partition configuration options.

Correct answer: A. Load the data by using PySpark.

PySpark is the optimal choice for loading JSON files with variable structures and data types into Spark tables while maintaining source data types. PySpark provides schema inference and flexibility to handle varying JSON structures, and it natively preserves data types during the load process. OPENROWSET requires predefined schemas, Data Factory's Get Metadata is for metadata retrieval only, and Conditional Split is for row-level transformations, not data loading.

Why the other options are wrong

• B. OPENROWSET in a serverless SQL pool requires predefined schemas and cannot effectively handle variable JSON structures and types.
• C. Get Metadata activity in Data Factory retrieves metadata information only and does not load data into tables.
• D. Conditional Split transformation in data flows is used for row-level branching logic, not for loading JSON files with variable schemas into tables.

Correct answer: B. REPLICATE

REPLICATE distribution type is the best choice for a date dimension table that will be used by all fact tables. Replicating the dimension table to all compute nodes eliminates data movement during joins, as every node has a complete copy of the dimension table. This minimizes data shuffling and optimizes query performance across all joins with fact tables.

Why the other options are wrong

• A. HASH distribution on the date dimension would require data movement when joining with multiple fact tables distributed on different columns.
• C. ROUND_ROBIN distribution is inefficient for dimension tables that are repeatedly joined, as it causes excessive data movement during each join operation.

Correct answer: C. Parquet

Parquet format should be used for tables in the Spark pool database to ensure they are automatically available as external tables to the serverless SQL pool. Parquet is the optimal format for interoperability between Apache Spark pools and serverless SQL pools in Synapse Analytics, providing efficient columnar storage and seamless integration.

Why the other options are wrong

• A. JSON format is not the standard format for Spark-to-serverless SQL pool integration and lacks the performance benefits of columnar formats.
• B. CSV format is less efficient and does not provide the automatic external table availability that Parquet offers.
• D. ORC format, while columnar, is not the recommended standard format for Synapse integration; Parquet is the preferred choice.

Correct answer: C. Azure Stream Analytics

Azure Stream Analytics is the ideal choice for this solution. It has built-in anomaly detection capabilities for identifying spikes and dips in time series data, can send output directly to Azure Synapse, and requires minimal development effort with its declarative SQL-like query language. It is purpose-built for real-time streaming scenarios with anomaly detection functionality.

Why the other options are wrong

• A. Azure SQL Database is not suitable for processing streaming data from IoT hubs and lacks built-in anomaly detection capabilities for time series data.
• B. Azure Databricks, while powerful, requires more development and configuration effort compared to Stream Analytics for this specific anomaly detection scenario.

Correct answer: B. append

The 'append' mode is correct for this scenario because it writes only new rows that arrived in the current interval to the output sink. Since the requirement specifies reporting only events that arrive during each five-minute interval without updating previous results, append mode is the appropriate choice that minimizes data redundancy.

Why the other options are wrong

• A. Complete mode would rewrite the entire result set every interval, which is inefficient for this use case and contradicts the requirement to report only new events in each interval.
• C. Update mode is designed for stateful operations where existing rows need to be modified, but this scenario requires only appending new interval results.

Correct answer: D. sysadmin

User2 must be assigned the sysadmin role to create a SQL Server Agent proxy that uses credentials created by another user. Only sysadmin role members have the privilege to create proxies and reference credentials created by other users. This is a special permission required for proxy management that goes beyond the standard SQL Agent roles.

Why the other options are wrong

• A. SQLAgentUserRole does not have permissions to create proxies.
• B. SQLAgentReaderRole only allows reading SQL Agent information.
• C. SQLAgentOperatorRole allows managing existing proxies but cannot create new ones or access credentials from other users.

Correct answer: A. Implement memory-optimized tables.

Memory-optimized tables reduce tempdb contention because they store data in memory rather than requiring tempdb disk operations. For workloads with high tempdb contention, migrating qualifying tables to in-memory OLTP reduces pressure on tempdb and improves performance. This is particularly effective for temporary objects and frequently accessed data.

Why the other options are wrong

• B. DBCC FLUSHPROCINDB clears cached query plans, not related to tempdb contention issues
• C. Index key sequentiality does not directly address tempdb contention problems
• D. DBCC DBREINDEX rebuilds indexes on user tables, not a solution for tempdb contention

Correct answer: D. Modify the diagnostic settings for SQLMI1.

Modifying diagnostic settings for SQLMI1 is the first step to stream performance metrics to Log Analytics. Diagnostic settings control what data is collected and where it is sent. Once diagnostic settings are configured to send metrics and logs to Workspace1, the streaming will be established. Private endpoints, Azure SQL Analytics configuration, and compute settings are either secondary configurations or not directly related to metric streaming.

Why the other options are wrong

• A. Private endpoints manage network connectivity but are not the first step for metric streaming.
• B. Azure SQL Analytics configuration follows after diagnostic settings are established.
• C. Compute and storage settings do not affect metrics streaming to Log Analytics.

Correct answer: B. Change vCores to 8.

To reduce memory pressure on an Azure SQL managed instance while minimizing costs, increasing vCores is the most cost-effective solution. More vCores directly increases available memory proportionally. Enabling Query Store (A) helps monitor but doesn't reduce pressure. Premium-series (C) and Business Critical tier (D) are more expensive upgrades that would increase costs rather than minimize them.

Why the other options are wrong

• A. Query Store aids monitoring but does not reduce memory pressure.
• C. Premium-series hardware is significantly more expensive and not necessary.
• D. Business Critical tier increases costs substantially without being required for this scenario.

Correct answer: D. sts.dm_db_index_physical_stats

sys.dm_db_index_physical_stats is the correct DMV for querying fragmentation information of data and indexes. It returns detailed physical fragmentation metrics for all indexes and heaps in the database. sys.dm_db_index_usage_stats shows usage patterns, DBCC CHECKALLOC checks allocation integrity, and DBCC SHOWCONTIG is a legacy command replaced by the modern DMV approach.

Why the other options are wrong

• A. sys.dm_db_index_usage_stats shows usage patterns and statistics, not fragmentation metrics.
• B. DBCC CHECKALLOC validates storage allocation integrity, not fragmentation.
• C. DBCC SHOWCONTIG is a legacy command superseded by modern DMVs like dm_db_index_physical_stats.

Correct answer: C. SQLAgentUserRole

SQLAgentUserRole is the least privileged SQL Server Agent role that lets a user create and schedule jobs, including a job that runs a full backup. Members can create, edit and run the jobs they own. The other Agent roles add rights the user does not need.

Why the other options are wrong

• A. db_owner grants full control of the database and does not by itself grant rights to create SQL Server Agent jobs.
• B. SQLAgentReaderRole includes SQLAgentUserRole rights plus viewing all jobs, which is more than needed.
• D. SQLAgentOperatorRole adds rights to manage and run other users' jobs, which exceeds least privilege.

Correct answer: C, E. a function; a security policy

A security policy (E) implements row-level security logic to restrict data access by company, and a function (C) defines the filtering logic that determines which rows each user can access based on their company context. Together, they enforce company-level data isolation in the dedicated SQL pool. Column encryption keys encrypt columns, asymmetric keys support encryption but don't define access logic, and custom RBAC roles control permissions but don't filter row-level data.

Why the other options are wrong

• A. Column encryption keys encrypt data at the column level but don't control which rows users can access.
• B. Asymmetric keys support encryption operations but don't define or enforce row-level access policies.
• D. Custom RBAC roles control object-level permissions but cannot filter rows based on company context at the data level.

Correct answer: A. sysadmin

Creating and configuring SQL Server Agent proxy accounts requires membership in the sysadmin fixed server role. The msdb SQL Server Agent roles can only use proxies they are granted, not create or configure them. So sysadmin is the least privileged role that meets the requirement.

Why the other options are wrong

• B. SQLAgentUserRole can use proxies it has been granted but cannot create or configure them.
• C. SQLAgentReaderRole can view jobs but cannot create or configure proxies.
• D. SQLAgentOperatorRole can manage jobs but still cannot create or configure proxy accounts.

Correct answer: D. columnstore archival compression

Columnstore archival compression provides the maximum compression ratio for partitioned columnstore indexes while maintaining the ability to index compressed data. It offers superior compression compared to regular columnstore compression by using additional CPU cycles during compression and decompression. This meets all three requirements: partition compression, maximized compression ratio, and indexability of compressed data.

Why the other options are wrong

• A. Page compression is for traditional rowstore tables, not for columnstore indexes.
• B. Standard columnstore compression provides less compression than archival compression.
• C. GZIP compression is not a native SQL Server option for columnstore indexes.

Correct answer: C. 8

SQL Server best practice recommends creating one tempdb data file per logical processor core to minimize contention and maximize performance. The scaled-up virtual machine has 8 vCPUs, so tempdb should have 8 data files. This configuration reduces allocation contention and provides the lowest latency for tempdb operations.

Why the other options are wrong

• A. 2 data files is insufficient for an 8 vCPU system and will cause contention.
• B. 4 data files does not match the number of available logical processors (8 vCPUs).
• D. 64 files far exceeds the number of logical processors and wastes resources without performance benefit.

Correct answer: B. No

While you can create an external table in a serverless SQL pool with an additional DateTime column, the issue is that this external table itself does not automatically populate that DateTime column from file metadata. The external table definition alone does not satisfy the requirement to store the file's DateTime as data is loaded; you would need additional logic in a pipeline to actually populate that column.

Correct answer: B. SQL Server on an Azure Virtual Machine

FileTables and FileStream are SQL Server-specific features that are not supported in Azure SQL Database or Azure SQL Managed Instance. SQL Server on Azure Virtual Machine provides a full SQL Server instance with all on-premises features intact, making it the only viable option for migrating databases that depend on these advanced features.

Why the other options are wrong

• A. Azure SQL Database does not support FileTables or FileStream features.
• C. Azure SQL Managed Instance does not support FileTables or FileStream features.
• D. Azure Database for MySQL is not compatible with SQL Server databases or these SQL Server-specific features.

Correct answer: A, D, E. Turn on auditing and write audit logs to an Azure Storage account; Apply sensitivity labels named Highly Confidential to the column; Turn on Azure Defender for SQL

To track access to confidential columns, you need: A) Auditing with storage account retention (365-day requirement); D) Sensitivity labels to classify the confidential data; and E) Azure Defender for SQL to provide threat detection and monitoring of the sensitive data access. Together these provide classification, detection, and long-term audit trail storage.

Why the other options are wrong

• B. Extended properties are metadata descriptors and do not track query execution or access patterns.
• C. While Event Hub is an auditing destination, it does not provide the 365-day retention capability that Azure Storage accounts offer.

Correct answer: B. Attach a stripe set that contains four 1-TB SSDs.

A stripe set (RAID-0) containing four 1-TB SSDs maximizes IOPs by distributing I/O operations across multiple disks in parallel. Each disk contributes its IOPs to the total, so four 1-TB SSDs in a stripe set provide better performance than a single 4-TB SSD. Striping is preferred over mirroring (which duplicates data) or RAID-5 (which includes parity overhead) when IOPs maximization is the primary goal.

Why the other options are wrong

• A. Mirrored SSDs duplicate data rather than stripe it, providing redundancy but not maximizing IOPs
• C. RAID-5 includes parity calculations that consume IOPs and reduce overall throughput compared to striping
• D. A single 4-TB SSD provides only one disk's worth of IOPs; multiple disks striped together aggregate their IOPs

Correct answer: A. Yes

A Get Metadata activity in an Azure Synapse pipeline can retrieve file metadata including the DateTime property of files in container1. This DateTime can then be captured and passed along as an additional column when the data is inserted into Table1, meeting the requirement to store the file's DateTime as a column.

Correct answer: A. Log Replay Service (LRS)

Log Replay Service (LRS) is the Azure SQL Managed Instance native migration tool specifically designed for minimal downtime migrations from on-premises SQL Server. It uses log shipping technology but is a fully managed Azure service that automates the process, reducing administrative effort while minimizing downtime during the cutover phase.

Why the other options are wrong

• B. Log shipping is a manual process requiring more administrative effort and doesn't provide the managed migration experience that LRS offers.
• C. Transactional replication is primarily for ongoing synchronization rather than initial large-scale database migration and requires more setup.
• D. SQL Data Sync is designed for bidirectional synchronization of specific tables, not for full database migration scenarios.

Correct answer: A. Create User1 in the master database.

Creating User1 in the master database is necessary because user connections to the logical server require authentication at the master database level first. Only after authenticating in master can User1 connect to and see the other databases. This is a fundamental requirement of SQL Server login architecture where the master database acts as the authentication gateway for the server instance.

Why the other options are wrong

• B. Assigning db_datareader to master alone doesn't provide the initial authentication mechanism needed for User1 to connect to the server.
• C. This assigns permissions only to databases User1 creates, which doesn't solve the connection problem for existing databases.
• D. Granting SELECT on sys.databases to public doesn't create the necessary user principal for User1 to authenticate to the server.

Correct answer: B. No

Shrinking the transaction log file does not meet the goal. The PAGELATCH_UP wait indicates contention on buffer pool pages, specifically a latch conflict on a data page (as shown by the wait_resource of 2:3:905856, which is database:file:page). This is a concurrency issue with data pages, not a transaction log capacity problem. Shrinking the transaction log would not reduce page latch contention.

Correct answer: D. Register the Azure subscription to the Microsoft.SqlVirtualMachine resource provider

Registering the subscription to the Microsoft.SqlVirtualMachine resource provider is the first step to enable the SQL IaaS Agent extension, which provides automated patching, backup, and management capabilities for SQL Server on Azure VMs. This registration must occur before you can leverage SQL Server-specific automation features within Azure, making it the logical first step.

Why the other options are wrong

• A. A system-assigned managed identity is useful for access control but is not required as the first step for SQL VM automation.
• B. The Microsoft.Sql provider is for Azure SQL Database/Managed Instance, not for SQL Server on VMs.
• C. DSC extensions are a lower-level configuration tool; the SqlVirtualMachine extension provides more integrated SQL-specific automation.

Correct answer: C. 8

The standard recommendation for tempdb data files is to create one secondary data file per logical CPU core to minimize contention. After scaling to 8 vCPUs, the best practice is to configure 8 secondary tempdb data files (matching the number of vCPUs) to allow each core to have dedicated allocation structures and reduce tempdb contention.

Why the other options are wrong

• A. 2 files is insufficient for 8 vCPUs and will not adequately reduce contention
• B. 4 files is half the number of vCPUs and does not follow the best practice guideline
• D. 64 files far exceeds the number of vCPUs and creates unnecessary overhead and management complexity

Correct answer: D. Watermark delay

Watermark Delay is the key metric to monitor when planning for increased device load. It measures how far behind the job's event processing time is compared to the current wall-clock time. As you double the device count, increasing Watermark Delay indicates the job cannot keep up with the input volume, signaling the need for more streaming units or job optimization.

Why the other options are wrong

• A. Input Deserialization Errors indicates malformed input data, not insufficient processing resources.
• B. Late Input Events refers to events with timestamps in the past, not resource capacity issues.
• C. Early Input Events refers to events with future timestamps, not resource capacity issues.

Correct answer: A. Generate a shared access signature (SAS).

A shared access signature (SAS) is the correct solution for maximizing security when backing up SQL Server databases to Azure Storage. SAS tokens provide time-limited, granular access control to storage resources without exposing account keys, and they are the recommended method for SQL Server backup operations. SAS tokens can be restricted to specific operations, containers, and time periods, which aligns with security best practices.

Why the other options are wrong

• B. Access policies are container-level constructs but are less secure than SAS for backup scenarios as they don't provide the same granular control.
• C. Rotating storage keys is a general security practice but doesn't address the specific secure authentication method needed for backup operations.
• D. Infrastructure encryption protects data at rest but doesn't solve the access authentication problem for the backup process itself.

Correct answer: C. --name, --resource-group, and --server

The three required parameters for deploying an Azure SQL database via Azure CLI are -- name (database name), --resource-group (the resource group where the database will be created), and --server (the SQL Server instance that will host the database). These are the minimal identifiers needed to provision a database.

Why the other options are wrong

• A. Edition and capacity parameters are optional for Azure SQL Database.
• B. Tier and min-capacity are not the standard parameter names; resource-group and server are required.
• D. License-type is not a required parameter for Azure SQL Database deployment via CLI.

Correct answer: B. Log on as a service

Log on as a service (B) is the correct user right because the SQL Server Agent needs this right to run as a Windows service that executes scheduled jobs automatically without user interaction. This is the specific operating system privilege required for service accounts that need to execute background tasks. This right allows the account to start and run SQL Server Agent as a service while following the principle of least privilege.

Why the other options are wrong

• A. Increase scheduling priority is not a required user right for SQL Server Agent operation.
• C. Profile system performance is a diagnostic right, not needed for basic SQL Server Agent job scheduling.
• D. Log on as a batch job is used for task scheduler jobs, not for SQL Server Agent service accounts.

Correct answer: A. Hyperscale

Hyperscale is the appropriate service tier for a 60-TB database because it is specifically designed to handle very large databases with virtually unlimited storage capacity (up to 100 TB), automatic storage scaling, and superior performance for large workloads, making it ideal for this 60-TB scenario.

Why the other options are wrong

• B. Business Critical tier has a maximum size of 4 TB and is not suitable for a 60-TB database.
• C. General Purpose tier has a maximum size of 4 TB and cannot accommodate a 60-TB database.

Correct answer: A. Always Encrypted with secure enclaves

Always Encrypted with secure enclaves meets all three requirements: it encrypts data in motion, supports comparison operators (through the secure enclave functionality which allows computations on encrypted data), and provides randomized encryption for improved security. Secure enclaves enable rich computations on encrypted data while maintaining encryption.

Why the other options are wrong

• B. Always Encrypted without secure enclaves does not support comparison operators on encrypted columns.
• C. Column-level encryption is not a specific Azure SQL encryption feature and doesn't provide the required functionality.
• D. Transparent Data Encryption (TDE) only encrypts data at rest and does not encrypt data in motion or support comparison operators.

Correct answer: C. a private link

Private Link creates a private endpoint for SqlDb1 on VNet1, allowing VM1 to connect via a private IP address while blocking all traffic to the public endpoint. This meets both requirements: it blocks public endpoint access and minimizes data exfiltration risk by keeping traffic within the private network. Service endpoints don't block public access, VPN gateways don't provide database-level connectivity controls, and ExpressRoute doesn't offer the granular database isolation needed.

Why the other options are wrong

• A. VPN gateways provide site-to-site or point-to-site connectivity but don't block public endpoint access to SQL databases.
• B. Service endpoints allow traffic through public endpoints; they don't block public access like Private Link does.
• D. ExpressRoute provides dedicated network connectivity but doesn't prevent access to public endpoints or provide database-level isolation controls.

Correct answer: B. General Purpose

General Purpose service tier supports serverless compute with autoscaling of compute resources based on demand. This allows the database to automatically scale compute up or down within a configured range, optimizing costs and performance without manual intervention.

Why the other options are wrong

• A. Premium tier does not support serverless autoscaling of compute resources.
• C. Business Critical tier does not support serverless autoscaling of compute resources.
• D. Standard tier does not support serverless autoscaling of compute resources.

Correct answer: B, C. SQL Server audit; database audit specification

To audit table access in SQL Server, you must enable both SQL Server Audit (the server-level audit mechanism) and a database audit specification (which defines what actions on which objects to audit). The database audit specification targets the CustomerPII table within DB1 and specifies which operations to record, while SQL Server Audit provides the infrastructure to capture and log these events.

Why the other options are wrong

• A. Server audit specification is used for server-level events, not table-level operations on specific databases.
• D. A server principal is a login/user object, not an auditing mechanism.

Correct answer: B, E. a virtual machine; a Log Analytics workspace

SQL insights collects its data with a Telegraf agent that runs on a monitoring virtual machine and sends it to a Log Analytics workspace. You therefore create the monitoring virtual machine and the Log Analytics workspace first.

Why the other options are wrong

• A. A storage account is not a prerequisite for SQL insights.
• C. An Azure logic app is not a prerequisite for SQL insights.
• D. An Azure function is not a prerequisite for SQL insights.

Correct answer: B. No

Changing the autogrow setting for the master database does not address the PAGELATCH_UP wait issue. The wait type indicates contention on a specific data page in database 2 (which is tempdb or a user database, not master), caused by concurrent access patterns. Modifying master database file growth settings is unrelated to resolving page latch contention on data pages in other databases.

Correct answer: C. a private link

Private Link creates a private endpoint specifically for SqlSrv1, allowing VM1 to connect only to that server through a private IP address. This prevents VM1 from connecting to other SQL servers and restricts network connectivity to only SqlSrv1 via the private network. Service endpoints operate at the service level without per-server isolation, VPN gateways don't provide database-specific routing, and ExpressRoute doesn't offer granular SQL server-level access control.

Why the other options are wrong

• A. VPN gateways provide encrypted tunnels but don't restrict access to specific SQL servers.
• B. Service endpoints allow access to all SQL Database servers in a region, not just SqlSrv1.
• D. ExpressRoute provides dedicated connectivity but cannot enforce access to only a specific SQL server.

Correct answer: B. Microsoft Defender

Microsoft Defender (formerly Azure Defender for SQL) is the centralized solution for monitoring security vulnerabilities across both SQL Server on Azure Virtual Machines and Azure SQL databases. It provides unified threat detection, vulnerability scanning, and compliance reporting for hybrid SQL environments. Database audits track activities but don't scan for vulnerabilities, SQL Insights focuses on performance metrics, and Azure SQL Auditing is limited to Azure SQL databases only.

Why the other options are wrong

• A. Database audits track activities and compliance but do not identify security vulnerabilities.
• C. SQL Insights is designed for performance monitoring, not security vulnerability assessment.
• D. Azure SQL Auditing only applies to Azure SQL databases, not SQL Server on VMs.

Correct answer: B, D. Enable Resource Governor; Create a workload group.

To limit IOPs and resources for App2 queries in Azure SQL Managed Instance, you must first enable Resource Governor, which is the feature that controls resource allocation. Then you create a workload group to define the specific resource limits for App2's queries. Together, these allow you to cap CPU, memory, and IO operations generated by App2.

Why the other options are wrong

• A. Query optimizer fixes improve execution plans but do not control resource consumption
• C. Parameter sniffing relates to query optimization, not resource governance
• E. In-memory OLTP improves performance but does not govern or limit resource usage
• F. Database Engine Tuning Advisor optimizes queries and indexes, not resource allocation
• G. Reducing Max Degree of Parallelism affects CPU usage but is not the primary mechanism for limiting IOPs

Correct answer: B. Decrease by half the value of Data Flush Interval

Decreasing the Data Flush Interval by half ensures that Query Store data is written to storage more frequently, reducing the amount of data that accumulates in memory. This minimizes the possibility of Query Store transitioning to read-only state, which occurs when storage limits are exceeded. More frequent flushing prevents buildup.

Why the other options are wrong

• A. Doubling the Data Flush Interval would increase time between writes, causing more data to accumulate and increasing read-only risk.
• C. Statistics Collection Interval affects how often statistics are collected, not storage flushing.
• D. Decreasing Statistics Collection Interval doesn't address the storage capacity issue that causes read-only transitions.

Correct answer: A, B, C. General Purpose Standard; Business Critical Memory Optimized Premium; General Purpose Premium

A 14-TB database needs a tier whose storage limit exceeds 14 TB. General Purpose on standard and premium hardware supports up to 16 TB, and Business Critical on memory optimized premium-series hardware also supports up to 16 TB. Business Critical on standard-series or regular premium-series hardware has a much lower storage cap.

Why the other options are wrong

• D. Business Critical on regular premium-series hardware has a storage limit below 14 TB.
• E. Business Critical on standard-series hardware is limited to about 4 TB of storage.

Correct answer: C. Azure SQL Managed Instance

Azure SQL Managed Instance is the correct choice because it supports linked server connections to on-premises SQL Server instances like SQL2, allowing seamless communication between the migrated databases and the on-premises server. It also minimizes administrative effort by providing near-complete SQL Server compatibility while being a fully managed service.

Why the other options are wrong

• A. Single Azure SQL Database does not support linked server connections to on-premises SQL Server instances.
• B. While SQL Server on VMs would support linked servers, it requires significantly more administrative effort for patching, updates, and maintenance.
• D. An elastic pool does not support linked server connections and offers the same limitations as single Azure SQL Database.

Correct answer: D. M-series

The M-series virtual machines are specifically designed for memory-intensive and SQL Server workloads and are the only series that supports Write Accelerator, which provides ultra-low latency writes to premium storage for transaction logs and data files.

Why the other options are wrong

• A. E-series VMs are optimized for compute but do not support Write Accelerator.
• B. G-series VMs are legacy compute-optimized instances and do not support Write Accelerator.
• C. H-series VMs are high-performance compute optimized for HPC workloads, not SQL Server, and do not support Write Accelerator.

Correct answer: A. clusters

To log actions that relate to changes in compute for a Databricks resource, you should enable logging for the clusters service. Clusters represent the compute infrastructure in Databricks, and all cluster lifecycle events (creation, modification, scaling, termination) are logged in the clusters service logs.

Why the other options are wrong

• B. Jobs logs track job execution and scheduling, not compute resource changes.
• C. DBFS logs track file system operations, not compute changes.
• D. SSH logs are not a standard Databricks audit service.
• E. Workspace logs track workspace object changes, not compute infrastructure changes.

Correct answer: A. Business Critical

The Business Critical service tier minimizes disk latency for Azure SQL Managed Instance by using high-performance storage and providing multiple synchronous replicas. It is designed for workloads requiring ultra-low latency and is the best choice when disk latency is a critical requirement.

Why the other options are wrong

• B. Hyperscale is not a service tier available for Azure SQL Managed Instance.
• C. General Purpose tier uses shared storage and does not optimize for minimal disk latency.
• D. Premium is not a valid service tier name for Azure SQL Managed Instance.

Correct answer: A. Configure service endpoints.

Service endpoints should be configured to allow Azure Database Migration Service to securely access the Azure SQL Managed Instance over the virtual network. Service endpoints create secure, direct connectivity from the migration service to SQLMI1. While NSGs and firewall rules may eventually be needed, service endpoints are the primary network configuration required to support the migration path through VNET1.

Why the other options are wrong

• B. Virtual network peering is not necessary since the on-premises network already connects via ExpressRoute and SQLMI1 is already on VNET1.
• C. Azure Firewall is not the primary requirement; service endpoints provide the necessary secure access.
• D. NSGs may play a supporting role but service endpoints are the core network configuration needed for DMS.

Correct answer: D. Create an alert rule that uses an Activity Log signal type.

Activity Log signal type alerts are used to monitor control plane changes such as database settings modifications (size, pricing tier, etc.). Activity Logs track administrative operations on Azure resources, making this the appropriate choice for detecting when database configurations are modified. Diagnostic settings are for logs/metrics storage, not alerting, and Metric signal types track performance data rather than configuration changes.

Why the other options are wrong

• A. Diagnostic settings store logs but do not directly notify; they must be paired with alerts to trigger notifications.
• B. Metrics measure performance data like CPU and memory, not configuration changes like pricing tier modifications.
• C. Metric signal types monitor numerical performance data, not administrative/control plane operations like database setting changes.

Correct answer: A. From the Azure portal, set a mask on the Email column.

Dynamic data masking (DDM) is configured in the Azure portal for Azure Synapse SQL pools. You set a mask directly on the Email column through the Azure portal, which will display values in the format aXXX@XXXX.com to non-administrative users while preserving the actual data in the database.

Why the other options are wrong

• B. Sensitivity classification is used for data discovery and governance but does not provide masking functionality to hide data values from users.
• C. While SQL Server Management Studio can be used for some SQL operations, dynamic data masking for Azure Synapse is configured through the Azure portal, not SSMS.
• D. Denying SELECT permission on a column is too restrictive and doesn't provide the required masked view; users need to see the masked format, not be blocked entirely.

Correct answer: C. Modify the automatic tuning settings for db1.

Azure SQL Database's Automatic Tuning feature includes automatic index management that can automatically create and drop indexes based on workload analysis. When Intelligent Insights identifies missing indexes, enabling automatic tuning with the create index option will automatically generate the recommended indexes without manual intervention.

Why the other options are wrong

• A. DBCC SQLPERF is used for performance monitoring, not index creation
• B. DBCC DBREINDEX is used to rebuild existing indexes, not create new ones
• D. Query Store tracks query performance and provides recommendations but does not automatically create indexes by itself

Correct answer: D. Modify the database scoped configuration of db1.

In Azure SQL Database, MAXDOP (Maximum Degree of Parallelism) is a database-scoped configuration setting, not a server-level configuration. Azure SQL Database does not support sp_configure for MAXDOP adjustments. Database scoped configurations are managed through ALTER DATABASE SCOPED CONFIGURATION commands and allow per-database tuning of query execution parameters including MAXDOP.

Why the other options are wrong

• A. sp_configure is for SQL Server on-premises server-level settings, not Azure SQL Database database-level settings
• B. sp_configure is a server-level command that cannot set database-specific MAXDOP in Azure SQL Database
• C. Extended properties are metadata containers, not the mechanism for configuring MAXDOP settings

Correct answer: B. data masking

Data masking hides sensitive data like credit card numbers from view while keeping the column visible and queryable, allowing salespeople to see all customer entries without accessing credit card information. This prevents both viewing and inferring sensitive values while maintaining data utility. Row-level security restricts rows, not columns; Always Encrypted prevents queries on encrypted data; column-level security prevents column access entirely rather than masking values.

Why the other options are wrong

• A. Row-level security controls which rows users can see, not the visibility of sensitive data within visible rows.
• C. Always Encrypted prevents queries on encrypted columns, making the data inaccessible rather than masked.
• D. Column-level security denies access to entire columns rather than masking values within accessible columns.

Correct answer: C, D. Backlogged Input Events; Watermark Delay

When monitoring SU % Utilization to ensure adequate streaming units, you should also monitor Backlogged Input Events (C) which indicates whether the job is falling behind processing input, and Watermark Delay (D) which measures how far behind the job is processing compared to wall-clock time. These two metrics directly indicate if insufficient streaming units are causing processing delays. Backlogged Input Events shows queue buildup, while Watermark Delay shows the actual lag in event processing.

Why the other options are wrong

• A. Late Input Events refers to events arriving after their application timestamp and is not a direct indicator of insufficient streaming unit capacity.
• B. Out of order Events relates to event ordering issues, not resource capacity problems.
• E. Function Events relates to custom function execution, not whether streaming units are adequately provisioned.

Correct answer: C. a private endpoint

A private endpoint is the correct solution because it creates a dedicated private connection from VM1 to SqlSrv1 within the Azure network, ensuring VM1 can only communicate with that specific SQL server and no others. Private endpoints restrict network connectivity by binding the SQL server to a specific virtual network interface, preventing access to other Azure SQL servers. This meets both requirements without requiring internet connectivity.

Why the other options are wrong

• A. A VPN gateway provides connectivity to on-premises networks or between virtual networks, not specific database-level isolation.
• B. A service endpoint allows access to Azure services but doesn't restrict access to a single SQL server instance.
• D. An ExpressRoute gateway provides dedicated hybrid connectivity and doesn't provide the database-level isolation required.

Correct answer: C. Sys.dm_db_index_operational_stats

sys.dm_db_index_operational_stats provides detailed information about the operations (seeks, scans, lookups, updates, inserts, deletes) being performed on indexes in real time. This DMV shows which operations are occurring on index1 and is ideal for diagnosing why queries using the index are slow. The physical stats DMV shows fragmentation, usage_stats shows historical aggregated usage, and query_plan_stats shows query execution plans.

Why the other options are wrong

• A. sys.dm_exec_query_plan_stats shows query execution plans, not index operation details.
• B. sys.dm_db_index_physical_stats reports fragmentation and page density, not operational activity.
• D. sys.dm_db_index_usage_stats shows historical aggregated usage, not current operational details.

Correct answer: B. No

Creating an external table in a dedicated SQL pool with an additional DateTime column does not automatically capture or populate the file's DateTime metadata when data is loaded. The external table structure alone is insufficient to meet the requirement; you need a pipeline process that actively retrieves file metadata and inserts it alongside the data.

Correct answer: A. Business Critical

Business Critical tier is required because it supports up to 128 cores, provides up to 4 TB of storage, includes 124 GB of memory configurations, and most critically, supports more than 50,000 IOPS which is explicitly needed. Premium tier maxes out at around 4,000 IOPS, making it insufficient for the IOPS requirement. Hyperscale could technically work but is overkill for these specifications and much more expensive.

Why the other options are wrong

• B. Premium tier supports only up to around 4,000 IOPS, far below the required 50,000+ IOPS.
• C. Hyperscale is unnecessarily expensive and complex for these performance requirements, though it could technically meet them.

Correct answer: D. Change the column type to VARCHAR(200).

Changing the column type from CHAR(400) to VARCHAR(200) is necessary because page compression works best with variable-length data types, and the actual data never exceeds 150 characters. CHAR(400) wastes space with fixed-length padding. VARCHAR(200) allows for variable-length storage up to the actual data size while enabling page compression, which provides better compression ratios than row compression alone on variable-length columns.

Why the other options are wrong

• A. Sparse columns are designed for columns with many NULL values, not for this use case of oversized fixed-length fields.
• B. NVARCHAR(MAX) would worsen the situation by using more space per character and complicates compression.
• C. VARCHAR(MAX) is excessive when data only reaches 150 characters; VARCHAR(200) is more appropriate.

Correct answer: A. Yes

Reducing the use of table variables and temporary tables does meet the goal. PAGELATCH_UP contention on data pages can be caused by excessive tempdb usage, as table variables and temporary tables store data in tempdb. Heavy tempdb activity creates page latch contention due to the single-threaded nature of tempdb allocation. By reducing reliance on these objects, you decrease tempdb pressure and associated page latch waits.

Correct answer: B, D. Set the Alert logic threshold to Dynamic; Set Threshold Sensitivity to Low

To reduce alert fatigue while detecting significant sustained changes, set the Alert logic threshold to Dynamic (which learns normal baseline patterns) and set Threshold Sensitivity to Low (which raises the bar for what triggers an alert). Dynamic alerting adapts to usage patterns over time, and low sensitivity ensures only substantial deviations trigger alerts, reducing noise from minor fluctuations.

Why the other options are wrong

• A. High sensitivity increases alert frequency, worsening the problem of too many alerts
• C. Static thresholds require manual adjustment and don't adapt to normal usage pattern changes
• E. Force Plan is related to query plan forcing and does not affect alert thresholds or frequency

Correct answer: A. Premium SSD v2

Premium SSD v2 provides the best balance for the requirements: it supports custom configuration of IOPS, throughput, and capacity independently without being tied to VM size, and it offers cost optimization compared to Ultra SSD while still delivering high performance for SQL Server workloads.

Why the other options are wrong

• B. Premium SSD couples IOPS and throughput to the disk size, limiting custom performance configuration options.
• C. Standard SSD does not provide the performance characteristics required for SQL Server production workloads.
• D. Ultra SSD supports custom configuration but has significantly higher costs, making it less optimal for cost minimization.

Correct answer: C. SQL Insights

SQL Insights is the dedicated Azure monitoring solution specifically designed for SQL Server on Azure Virtual Machines, providing consolidated performance metrics and dashboards with minimal administrative overhead. Log Analytics (B) requires more configuration. Azure Monitor (A) is broader but requires additional setup. Azure SQL Analytics (D) is designed for Azure SQL databases, not on-premises SQL Server VMs.

Why the other options are wrong

• A. Azure Monitor is more general-purpose and requires more configuration effort.
• B. Log Analytics requires separate workspace setup and custom queries.
• D. Azure SQL Analytics targets Azure SQL databases, not SQL Server on VMs.

Correct answer: B. Azure Data Box

Azure Data Box is the optimal choice for migrating a 60-TB database over a 10-Mbps internet connection. Data Box is a physical appliance that allows you to transfer large amounts of data offline, which is far faster than attempting to transfer 60 TB over a 10-Mbps connection (which would take months). You ship the Data Box appliance to Microsoft, and they upload the data to Azure on your behalf.

Why the other options are wrong

• A. Azure Migrate is designed for VM and infrastructure migration, not large database transfers, and wouldn't be efficient for 60 TB over slow connectivity.
• C. Azure Database Migration Service requires network connectivity and would be impractical for 60 TB over a 10-Mbps link.
• D. Data Migration Assistant is a tool for assessing and migrating SQL Server databases but doesn't solve the bandwidth limitation problem.

Correct answer: A. Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) automatically encrypts data at rest in Azure Synapse Analytics databases without requiring application changes. TDE encrypts the entire database including backups, meeting the core requirement for encryption at rest. Advanced Data Security adds threat detection, Always Encrypted requires application-level key management, and Secure transfer required only protects data in transit over the network.

Why the other options are wrong

• B. Advanced Data Security provides threat detection and vulnerability scanning but doesn't encrypt data at rest.
• C. Always Encrypted requires application-level encryption management and isn't the default encryption at rest solution.
• D. Secure transfer required enforces HTTPS/TLS for data in transit, not encryption at rest.

Correct answer: B. the serverless compute tier

The serverless compute tier is the correct choice because it provides both dynamic CPU scaling based on workload demands and the ability to pause the database to reduce costs when inactive. These features directly align with both stated requirements.

Why the other options are wrong

• A. Business Critical tier does not support pausing to reduce costs.
• C. Elastic pools do not support pausing; they provide resource sharing but not auto-pause functionality.
• D. General Purpose tier does not support automatic pausing; it maintains consistent resource allocation.

Correct answer: A. Yes

The wait type PAGELATCH_UP on resource 2:3:905856 indicates tempdb contention, specifically on the Global Allocation Map (GAM) page or similar system pages in tempdb. Creating additional tempdb files reduces contention by distributing allocation activity across multiple files, each with its own allocation structures. This is a standard best practice for resolving tempdb allocation contention issues in SQL Server.

Correct answer: C. Create a pool in workspace1.

Creating a pool in workspace1 is the first step to reduce cluster startup and scale-up times while minimizing costs. Pools pre-allocate and reuse compute resources, eliminating the need to acquire new instances from scratch each time, which provides the fastest path to improved performance without the cost of upgrading to Premium tier.

Why the other options are wrong

• A. Upgrading to Premium tier would add cost and is not necessary; pools are available in Standard tier and address the performance requirement.
• B. Global init scripts help with cluster configuration but do not directly reduce startup or scale-up times.
• D. Cluster policies control configuration options but do not reduce startup time or improve scalability of existing clusters.

Correct answer: C. Assign a larger resource class to the automated data load queries.

Assigning a larger resource class to automated data load queries allocates more memory and CPU resources to those operations, ensuring they complete quickly even when adhoc queries are running concurrently. Resource classes in Synapse control memory allocation, and larger classes guarantee sufficient resources for data loads to succeed without contention.

Why the other options are wrong

• A. Assigning a smaller resource class would reduce memory availability and slow down the data loads, worsening the problem.
• B. Sampled statistics improve query optimization but do not address memory allocation or concurrent query resource contention.
• D. Hash distribution affects data placement but does not allocate additional memory resources to prioritize data load completion.

Correct answer: D. Run dynamic management view-based queries

Dynamic management views (DMVs) are the proper method to diagnose tempdb usage and performance issues in Azure SQL Database. DMVs like sys.dm_db_task_space_usage and sys.dm_exec_sessions provide detailed tempdb allocation and usage metrics. Query Store focuses on query performance, SQL Profiler is not available in Azure SQL Database, and Query Performance Insight doesn't specifically diagnose tempdb contention.

Why the other options are wrong

• A. Query Store tracks query-level performance but not tempdb-specific diagnostics.
• B. SQL Server Profiler is not available in Azure SQL Database environments.
• C. Query Performance Insight shows overall database performance but not tempdb-specific issues.

Correct answer: D. Cache hit percentage

Cache hit percentage is the correct metric because commonly used queries benefit from result set caching in Synapse Analytics. When frequently executed queries show slow performance, a low cache hit percentage indicates the results are not being cached effectively, revealing a potential source of performance degradation. Increasing cache availability would improve performance for these repeated queries.

Why the other options are wrong

• A. Local tempdb percentage measures temporary storage, not query cache effectiveness.
• B. DWU percentage shows overall resource utilization but doesn't explain why frequent queries specifically are slow.
• C. DWU used measures resource consumption but doesn't identify cache-related performance issues.

Correct answer: A. transactional

Transactional replication is the correct choice for replicating from on-premises SQL Server to Azure SQL Database because it supports one-way replication from a publisher (on-premises) to a subscriber (Azure SQL Database), maintaining near real-time data consistency with minimal latency.

Why the other options are wrong

• B. Peer-to-peer replication requires bi-directional replication between nodes of the same SQL Server version and is not suitable for cloud scenarios.
• C. Snapshot replication takes periodic snapshots and is not appropriate for continuous synchronization of an active database.
• D. Merge replication is designed for distributed scenarios where multiple subscribers make changes to the same data, not for one-way replication to Azure.

Correct answer: D, E. Column4; Column5

Always Encrypted in Azure SQL Database supports encryption on columns with specific data types. Varchar (Column4) and Datetime2 (Column5) are both supported data types for Always Encrypted. Ntext (Column1) is a legacy data type that is not supported. Geometry (Column2) is a spatial data type that is not supported for encryption. Image (Column3) is a binary data type that is not supported for Always Encrypted.

Why the other options are wrong

• A. Ntext is a legacy data type and is not supported by Always Encrypted.
• B. Geometry is a spatial data type that cannot be encrypted with Always Encrypted.
• C. Image is a binary data type that is not supported for Always Encrypted encryption.

Correct answer: B, C. Create a private endpoint; Create a database-level firewall rule.

Creating a private endpoint (B) ensures each customer accesses the database through a private network connection, providing isolation. Creating a database-level firewall rule (C) allows you to control access at the individual database level, ensuring each customer can only access their own database. Together, these minimize administrative effort by providing granular access control without managing complex server-level rules for each customer.

Why the other options are wrong

• A. Denying public access alone doesn't solve the multi-customer isolation requirement.
• D. Network Security Groups operate at the infrastructure layer and are not the most efficient method for database-level access control.
• E. Server-level firewall rules apply to all databases on the server, not individual databases, making them unsuitable for isolating customers.

Correct answer: B. Deploy an Azure Active Directory Domain Services (Azure AD DS) domain and join the virtual machines to the domain.

Deploying Azure Active Directory Domain Services (Azure AD DS) and joining the virtual machines to the domain is the correct first step to enable centralized management of SQL Server instances with a single user account. Azure AD DS creates a managed domain that the VMs can join, allowing domain-based authentication and centralized user account management across all 10 SQL Server instances. This provides traditional domain administration capabilities while leveraging Azure AD.

Why the other options are wrong

• A. User-assigned managed identities are for service-to-service authentication, not for user account-based SQL Server management.
• C. System-assigned managed identities provide resource authentication, not centralized user account management across multiple instances.
• D. Simply joining VMs to Azure AD tenant alone doesn't provide the domain-joined capabilities needed for SQL Server instance management.

Correct answer: A. a contained database user for App1 on DB2

A contained database user is the correct solution because it allows App1 to authenticate directly to DB2 using SQL authentication without requiring a server-level login. This meets both requirements: App1 can only view DB2 (contained users are database-scoped), and it minimizes administrative effort by avoiding server-level login management. Contained database users are specifically designed for applications that need access to a single database.

Why the other options are wrong

• B. A server-level login would allow App1 to potentially access other databases on Server1, violating the requirement that App1 can view only DB2.
• C. Using an external provider adds unnecessary complexity when SQL authentication is simpler and meets the requirements.
• D. A Windows login requires Active Directory integration and increases administrative overhead compared to a contained database user.

Correct answer: A. DBCC SHRINKDATABASE

DBCC SHRINKDATABASE is the correct command to minimize space used by both data and log files of an entire database. It shrinks all files within the database to reclaim unused space. While DBCC SHRINKFILE works on individual files and the sp_clean procedures target specific free space cleanup, DBCC SHRINKDATABASE is the comprehensive solution for minimizing overall database file size.

Why the other options are wrong

• B. sp_clean_db_free_space cleans up free space but doesn't shrink file sizes themselves.
• C. sp_clean_db_file_free_space targets individual files and doesn't provide overall database shrinking.
• D. DBCC SHRINKFILE operates on single files, not the entire database at once.

Correct answer: C. shared access signatures (SAS)

Shared Access Signatures (SAS) provide time-limited access to storage resources with granular permissions, perfect for granting temporary access to specific applications. SAS tokens can be set to expire after a defined period, making them ideal for limiting access duration. Role assignments are permanent unless explicitly changed, account keys don't expire, and Azure AD identities don't inherently provide time-limited access.

Why the other options are wrong

• A. Role assignments are identity-based and don't have built-in time expiration mechanisms.
• B. Account keys are long-term credentials without expiration and provide full access, not limited permissions.
• D. Azure AD identities provide authentication but don't natively support time-limited access without additional token configuration.

Correct answer: C. SQL Server on Azure Virtual Machines

SQL Server on Azure Virtual Machines is the correct choice because it supports the latest SQL Server versions, includes SQL Server Agent service, and while requiring some administrative effort, it is the most appropriate for scenarios requiring full SQL Server feature parity. However, Azure SQL Managed Instance would also support these requirements with less administrative effort, making this question somewhat ambiguous in practice.

Why the other options are wrong

• A. Elastic pools do not support SQL Server Agent.
• B. Azure SQL Database does not support SQL Server Agent service.
• D. While Azure SQL Managed Instance does support SQL Server Agent and can use recent SQL versions, it may not always have the absolute latest version available as quickly as on-premises deployments.

Correct answer: C. Use Transact-SQL to enable the FORCE_LAST_GOOD_PLAN option.

For Azure SQL Managed Instance, automatic tuning is implemented using Transact-SQL by enabling the FORCE_LAST_GOOD_PLAN option through T-SQL commands. This is the native method for managed instances. The Azure portal automatic tuning configuration is primarily for Azure SQL Database, not managed instances, and the REST API approach is less direct than using T-SQL for this specific resource type.

Why the other options are wrong

• A. REST API calls for AutomaticTuningServerMode are not the standard approach for managed instances.
• B. Azure portal automatic tuning configuration is designed for Azure SQL Database, not managed instances.

Correct answer: D. a user-assigned managed identity

Elastic jobs should use a user-assigned managed identity to access DB1 when location-based Conditional Access policies are required. User-assigned managed identities are fully compatible with Azure AD Conditional Access policies, including location-based policies, providing secure authentication without credential management while supporting advanced access controls.

Why the other options are wrong

• A. System-assigned managed identities don't provide the same flexibility for Conditional Access policy application across elastic job scenarios.
• B. Azure SQL sign-in credentials cannot participate in Conditional Access policies.
• C. Database-scoped credentials don't support Conditional Access policy integration.

Correct answer: C. SQL Server on Azure Virtual Machines

SQL Server on Azure Virtual Machines is required because merge replication is not supported by Azure SQL Database or Azure SQL Managed Instance. Only SQL Server running on virtual machines provides full compatibility with all SQL Server replication types, including merge replication.

Why the other options are wrong

• A. Azure SQL Edge does not support merge replication.
• B. Azure SQL Database does not support merge replication; only transactional and snapshot replication are partially supported.
• D. Azure SQL Managed Instance does not support merge replication.

Correct answer: A. Azure Private Link

Azure Private Link is the solution that meets all three requirements: it creates a private endpoint for the Azure SQL database, assigns it a private IP address on the virtual network, and restricts connections to only that specific virtual network. This provides secure, private connectivity between the web app and database.

Why the other options are wrong

• B. Network security groups control traffic flow between subnets and resources but do not assign private IP addresses to the SQL database or create the private connectivity required.
• C. Database-level firewalls control access at the database level but do not provide private IP assignment or enforce virtual network-only connectivity as effectively as Private Link.
• D. Server-level firewalls are less granular than Private Link and do not assign private IP addresses to the SQL database.

Correct answer: B. Remove the linked service from df1.

Before encrypting a Data Factory with a customer-managed key from Key Vault, linked services must be removed because they contain credentials and connection strings that complicate the encryption process. Removing the linked service first ensures the Data Factory can be properly encrypted with the key without conflicts. Purge protection and soft delete settings on the Key Vault are separate from this requirement, and a self-hosted integration runtime is unrelated to Data Factory encryption.

Why the other options are wrong

• A. Purge protection settings on the Key Vault don't affect Data Factory encryption capability.
• C. Self-hosted integration runtime creation is unrelated to enabling customer-managed key encryption on the Data Factory itself.
• D. Soft delete on the Key Vault doesn't prevent or require changes before encrypting a Data Factory.

Correct answer: C. 8

For optimal tempdb performance with 16 vCPUs, the recommended number of data files is 8. SQL Server best practice recommends one tempdb data file per CPU core up to 8 files, even when you have more than 8 cores. With 16 vCPUs, 8 data files is the appropriate configuration to provide the lowest latency and minimize contention.

Why the other options are wrong

• A. 2 data files is too few for 16 vCPUs and will cause contention.
• B. 4 data files is below the recommended threshold for 16 vCPUs.
• D. 64 data files far exceeds the recommended configuration and would create excessive overhead.

Correct answer: B. From SQLVM1, uninstall the SQL Server instance.

To change from Enterprise to Standard edition on a SQL Server deployed from Azure Marketplace, you must first uninstall the SQL Server instance. This is necessary because a direct in-place downgrade from Enterprise to Standard is not supported. After uninstalling, you can reinstall using the Standard edition license to ensure compliance.

Why the other options are wrong

• A. SQL Server does not provide an Edition Upgrade/Downgrade wizard; downgrading requires uninstallation and reinstallation.
• C. The Repair wizard is used to fix corrupted installations, not to change editions.
• D. Reconfiguring the VM in the Azure portal does not change the SQL Server edition; the instance itself must be modified.

Correct answer: B. SQL Servers

Creating the alert rule at the SQL Server resource level ensures the alert applies to all databases on that server, including any new databases created in the future. This approach provides automatic coverage for new databases without requiring manual alert reconfiguration. Selecting the SQL Server as the resource type enables metric thresholds to be evaluated across all its child databases.

Why the other options are wrong

• A. Resource Groups are too broad and would not provide database-specific CPU monitoring.
• C. Creating alerts on individual SQL Database resources would require manual configuration for each new database and is not scalable.
• D. SQL Virtual Machines are not applicable; these databases run on Azure SQL Database managed service, not VMs.

Correct answer: B. Azure Databricks

Azure Databricks is a managed Apache Spark platform that minimizes administrative effort while providing built-in support for intrusion detection analytics and security monitoring. It offers fully managed clusters, integrated machine learning capabilities, and is purpose-built for large-scale data analysis with minimal operational overhead.

Why the other options are wrong

• A. Data Lake Storage is a data repository, not an analytics platform; it does not provide Spark compute or analysis tools.
• C. HDInsight requires more administrative effort than Databricks as it requires cluster management and configuration.
• D. Data Factory is an ETL/data movement service, not an analytics platform for running Spark jobs.

Correct answer: A. Always On availability groups

Always On availability groups provide the best solution for minimizing downtime and preventing data loss during migration to Azure SQL Managed Instance. They enable continuous synchronization of data and allow for a controlled failover with minimal disruption, making this the replication technology that meets the stated requirements of minimal downtime and zero data loss.

Why the other options are wrong

• B. Backup and Restore causes significant downtime during the restore process.
• C. Log shipping is older technology with longer RTO/RPO compared to Always On availability groups.
• D. Database Migration Assistant is an assessment and planning tool, not a migration execution method.

Correct answer: B, D. Create a contained database user; Assign the database user the db_ddladmin role.

A contained database user (B) is required because User1 has an Azure AD account, and contained database users support Azure AD authentication in Azure SQL Database. The db_ddladmin role (D) grants the minimum necessary permissions to add and remove columns (DDL operations) without granting broader administrative privileges. Together, these options implement least privilege: a contained database user scoped to the database, with only the DDL permissions needed for column modifications.

Why the other options are wrong

• A. db_owner grants excessive administrative permissions beyond the least-privilege requirement for column management.
• C. Creating a login is not applicable for Azure AD accounts in Azure SQL Database; only contained database users with Azure AD authentication are supported.

Correct answer: C. Configure transactional replication.

Transactional replication is the optimal method for migrating to Azure SQL Database while minimizing downtime. It allows continuous replication of data changes from the source to Azure SQL Database, enabling a cutover with minimal downtime by keeping the source and target in sync until the final switch. Always On availability groups require both instances to be SQL Server, transaction log shipping is older technology, and importing a BACPAC requires a full backup and restore which causes significant downtime.

Why the other options are wrong

• A. Transaction Log Shipping is a legacy technology that causes longer downtime compared to replication.
• B. Always On availability groups don't work between on-premises SQL Server and Azure SQL Database directly.
• D. Importing a BACPAC requires stopping the source database, resulting in significant downtime.