Administering Microsoft Azure SQL Solutions DP-300 Practice Test 21
Question 200
A database administrator must resolve this Azure SQL or SQL Server issue: A reporting table has many repeated values and storage cost dominates. What action BEST meets the requirement?
- Increase its supported maximum storage or service objective after checking usage
- Evaluate read scale-out capabilities and tier eligibility for suitable read-only endpoints
- Review supported Azure managed disk performance and striping or disk selection
- Evaluate row or page compression based on SQL Server and Azure SQL workload testing
Explanation
Correct Answer
D:Evaluate row or page compression based on SQL Server and Azure SQL workload testing. The deciding requirement in this SQL platform scenario is addressed because sQL data compression can reduce storage and I/O at the cost of additional CPU work. Without the change, enabling page compression universally may increase CPU bottlenecks on already busy servers. Confirm the outcome through a controlled validation where benchmarks show lower page reads while CPU and latency remain within tolerance.
Incorrect Answers
A:Increase its supported maximum storage or service objective after checking usage would instead apply to another SQL platform issue, such as when an Azure SQL Database reaches its configured data storage limit during nightly ingestion. The present requirement calls for evaluate row or page compression based on SQL Server and Azure SQL workload testing; this alternative changes a different control and does not directly resolve the stated database issue.
B:Evaluate read scale-out capabilities and tier eligibility for suitable read-only endpoints would instead apply to another SQL platform issue, such as when an Azure SQL Database is write-heavy and a reader workload causes contention with transactions. The present requirement calls for evaluate row or page compression based on SQL Server and Azure SQL workload testing; this alternative changes a different control and does not directly resolve the stated database issue.
C:Review supported Azure managed disk performance and striping or disk selection would instead apply to another SQL platform issue, such as when a SQL Server VM needs additional throughput but its single disk has a fixed IOPS ceiling. The present requirement calls for evaluate row or page compression based on SQL Server and Azure SQL workload testing; this alternative changes a different control and does not directly resolve the stated database issue.
Question 201
The team corrects "A large migration runs slowly only when an automatic platform backup overlaps it" by applying "Correlate migration throughput with service and storage metrics over the same timeline". Which validation is MOST convincing?
- A controlled test establishes whether storage saturation coincides with migration lag
- Dashboards identify which database drives the observed peak
- A query retrieves historical resource measurements through the required retention window
- A controlled deadlock produces a captured event with identifying session information
Explanation
Correct Answer
A:A controlled test establishes whether storage saturation coincides with migration lag. This observation tests the actual effect of correlate migration throughput with service and storage metrics over the same timeline instead of assuming that a configuration change alone was successful. The underlying database behavior is that time-correlated activity helps distinguish coincident operations from a specific bottleneck. The control matters because assuming every backup pauses all managed SQL writes can lead to unjustified conclusions.
Incorrect Answers
B:Dashboards identify which database drives the observed peak could validate a separate fix, for example when a software team uses an Azure SQL logical server and wants to compare CPU across five databases. Here the required proof is that a controlled test establishes whether storage saturation coincides with migration lag. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
C:A query retrieves historical resource measurements through the required retention window could validate a separate fix, for example when a DBA requests 30 days of historical performance for a database whose current dashboard shows only a week. Here the required proof is that a controlled test establishes whether storage saturation coincides with migration lag. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
D:A controlled deadlock produces a captured event with identifying session information could validate a separate fix, for example when a production SQL Server VM needs lightweight detailed event capture for intermittent deadlocks. Here the required proof is that a controlled test establishes whether storage saturation coincides with migration lag. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
Question 202
Given the Azure SQL condition "A small database estate runs SQL automation from Azure Functions using a managed identity", the proposed change is "Assign the function identity and scoped Azure SQL permissions and configure reliable triggers". Which explanation BEST supports the change?
- Azure SQL elastic jobs can execute scheduled T-SQL across multiple Azure SQL databases
- External schedulers can invoke SQL workloads when identity permissions and timeouts are handled
- Elastic jobs record target execution outcomes and can retry failed operations under configuration
- Elastic jobs can resolve target databases when jobs run under a supported target group definition
Explanation
Correct Answer
B:External schedulers can invoke SQL workloads when identity permissions and timeouts are handled. This is the mechanism that makes the proposed change, assign the function identity and scoped Azure SQL permissions and configure reliable triggers, appropriate to the database environment described. The key limitation is that storing SQL admin passwords directly in function code adds unnecessary credential exposure. A concrete confirmation is that a triggered run authenticates with managed identity and only the intended SQL operation succeeds.
Incorrect Answers
A:Azure SQL elastic jobs can execute scheduled T-SQL across multiple Azure SQL databases is a valid distinction for a different database situation, for example when an Azure SQL Database application needs nightly index and statistics updates on dozens of separate databases. Here the determining mechanism is that external schedulers can invoke SQL workloads when identity permissions and timeouts are handled. The alternative does not explain why the selected solution addresses this requirement.
C:Elastic jobs record target execution outcomes and can retry failed operations under configuration is a valid distinction for a different database situation, for example when a DBA needs to rerun only failed SQL elastic job targets after a network interruption. Here the determining mechanism is that external schedulers can invoke SQL workloads when identity permissions and timeouts are handled. The alternative does not explain why the selected solution addresses this requirement.
D:Elastic jobs can resolve target databases when jobs run under a supported target group definition is a valid distinction for a different database situation, for example when a platform team schedules one task across an elastic pool that gains new tenant databases weekly. Here the determining mechanism is that external schedulers can invoke SQL workloads when identity permissions and timeouts are handled. The alternative does not explain why the selected solution addresses this requirement.
Question 203
A SQL platform owner finds this gap: A SQL deployment script uses an old deprecated Azure PowerShell module command. Which supported remedy BEST addresses it?
- Write reviewed Bicep declarations for the SQL resources and deploy them through Azure Resource Manager
- Declare resource dependencies so partner SQL servers exist before dependent failover resources
- Update to supported Az modules or current Azure CLI commands and confirm service behavior
- Implement bounded retries with backoff and verify desired state after transient failures
Explanation
Correct Answer
C:Update to supported Az modules or current Azure CLI commands and confirm service behavior. The deciding requirement in this SQL platform scenario is addressed because azure management automation must match maintained command modules and resource provider APIs. Without the change, assuming obsolete AzureRM syntax is universally supported can halt repeat deployments. Confirm the outcome through a controlled validation where the updated toolchain passes a clean deployment in the intended subscription.
Incorrect Answers
A:Write reviewed Bicep declarations for the SQL resources and deploy them through Azure Resource Manager would instead apply to another SQL platform issue, such as when a deployment team repeats Azure SQL server and database configuration in multiple portal sessions. The present requirement calls for update to supported Az modules or current Azure CLI commands and confirm service behavior; this alternative changes a different control and does not directly resolve the stated database issue.
B:Declare resource dependencies so partner SQL servers exist before dependent failover resources would instead apply to another SQL platform issue, such as when a multi-region Bicep template creates a failover group before the partner server is deployed. The present requirement calls for update to supported Az modules or current Azure CLI commands and confirm service behavior; this alternative changes a different control and does not directly resolve the stated database issue.
D:Implement bounded retries with backoff and verify desired state after transient failures would instead apply to another SQL platform issue, such as when a PowerShell pipeline intermittently receives throttling errors from Azure SQL management APIs. The present requirement calls for update to supported Az modules or current Azure CLI commands and confirm service behavior; this alternative changes a different control and does not directly resolve the stated database issue.
Question 204
After remediating "A transaction database intermittently slows at noon but has no normal workload measurements", which evidence BEST shows the intended outcome was achieved?
- A query retrieves historical resource measurements through the required retention window
- A synthetic threshold test creates the expected alert and on-call notification
- The timeline identifies the connection-error spike separately from slow SQL execution
- Several days of comparable metrics reveal the recurring noon departure from baseline
Explanation
Correct Answer
D:Several days of comparable metrics reveal the recurring noon departure from baseline. This observation tests the actual effect of collect an operational baseline for CPU I/O waits duration and transaction throughput instead of assuming that a configuration change alone was successful. The underlying database behavior is that performance troubleshooting requires baseline comparison to distinguish anomalies from expected demand. The control matters because a single slow query sample cannot show whether the environment is abnormal at that time.
Incorrect Answers
A:A query retrieves historical resource measurements through the required retention window could validate a separate fix, for example when a DBA requests 30 days of historical performance for a database whose current dashboard shows only a week. Here the required proof is that several days of comparable metrics reveal the recurring noon departure from baseline. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
B:A synthetic threshold test creates the expected alert and on-call notification could validate a separate fix, for example when a Managed Instance team needs alerts when storage space approaches a configured ceiling. Here the required proof is that several days of comparable metrics reveal the recurring noon departure from baseline. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
C:The timeline identifies the connection-error spike separately from slow SQL execution could validate a separate fix, for example when a security incident requires distinguishing SQL connection failures from query latency errors. Here the required proof is that several days of comparable metrics reveal the recurring noon departure from baseline. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
Question 205
A database administrator selects "Use Microsoft Entra workload identity or secured secret references for SQL connections where supported" after observing "A database administrator wants to avoid storing sensitive passwords in Azure deployment scripts". What mechanism makes it appropriate?
- Secretless or managed-identity authentication reduces the exposure of reusable database credentials
- Database object-level GRANT and role membership support narrowly scoped SQL access
- External identity authorization can depend on effective token claims and existing session state
- Distinct principals permit access scoping and clearer audit trails of database activity
Explanation
Correct Answer
A:Secretless or managed-identity authentication reduces the exposure of reusable database credentials. This is the mechanism that makes the proposed change, use Microsoft Entra workload identity or secured secret references for SQL connections where supported, appropriate to the database environment described. The key limitation is that passing a SQL administrative password in script command-line history can leak the secret. A concrete confirmation is that deployment logs expose no reusable password and the approved identity can connect.
Incorrect Answers
B:Database object-level GRANT and role membership support narrowly scoped SQL access is a valid distinction for a different database situation, for example when a service identity must query one reporting view but not update the underlying finance table. Here the determining mechanism is that secretless or managed-identity authentication reduces the exposure of reusable database credentials. The alternative does not explain why the selected solution addresses this requirement.
C:External identity authorization can depend on effective token claims and existing session state is a valid distinction for a different database situation, for example when a DBA removes a developer from an Entra group but a long-lived connection retains current access. Here the determining mechanism is that secretless or managed-identity authentication reduces the exposure of reusable database credentials. The alternative does not explain why the selected solution addresses this requirement.
D:Distinct principals permit access scoping and clearer audit trails of database activity is a valid distinction for a different database situation, for example when a security auditor finds a shared SQL admin login used by multiple applications. Here the determining mechanism is that secretless or managed-identity authentication reduces the exposure of reusable database credentials. The alternative does not explain why the selected solution addresses this requirement.
Question 206
Given the Azure SQL condition "A SQL logical server permits a broad IP firewall rule used during a former migration", the proposed change is "Remove the obsolete rule and allow only documented source ranges if public access remains necessary". Which explanation BEST supports the change?
- TLS confidentiality without endpoint identity validation can permit interception by a spoofed server
- Azure SQL firewall rules evaluate incoming connection sources before database authentication
- External key and credential management decouples secret lifecycle from application binaries
- Secure enclaves support selected confidential computations on protected columns under supported conditions
Explanation
Correct Answer
B:Azure SQL firewall rules evaluate incoming connection sources before database authentication. This is the mechanism that makes the proposed change, remove the obsolete rule and allow only documented source ranges if public access remains necessary, appropriate to the database environment described. The key limitation is that leaving an emergency all-address rule creates unintended connection exposure after cutover. A concrete confirmation is that a negative external network test fails while the approved source still connects.
Incorrect Answers
A:TLS confidentiality without endpoint identity validation can permit interception by a spoofed server is a valid distinction for a different database situation, for example when a database connection encrypts packets but ignores server certificate validation. Here the determining mechanism is that azure SQL firewall rules evaluate incoming connection sources before database authentication. The alternative does not explain why the selected solution addresses this requirement.
C:External key and credential management decouples secret lifecycle from application binaries is a valid distinction for a different database situation, for example when a mobile app needs a secure secret that rotates without changing its compiled SQL client library. Here the determining mechanism is that azure SQL firewall rules evaluate incoming connection sources before database authentication. The alternative does not explain why the selected solution addresses this requirement.
D:Secure enclaves support selected confidential computations on protected columns under supported conditions is a valid distinction for a different database situation, for example when an analytics filter must compare sensitive Always Encrypted values without exposing keys to the engine. Here the determining mechanism is that azure SQL firewall rules evaluate incoming connection sources before database authentication. The alternative does not explain why the selected solution addresses this requirement.
Question 207
For the case "A PowerShell pipeline intermittently receives throttling errors from Azure SQL management APIs", the team chooses "Implement bounded retries with backoff and verify desired state after transient failures". Which database behavior justifies that choice?
- ARM dependency ordering ensures prerequisite resource creation before dependent operations
- Reusable IaC modules separate invariant SQL resource definitions from environment-scoped values
- Cloud management API operations can be asynchronous and subject to retryable rate limits
- Azure management automation must match maintained command modules and resource provider APIs
Explanation
Correct Answer
C:Cloud management API operations can be asynchronous and subject to retryable rate limits. This is the mechanism that makes the proposed change, implement bounded retries with backoff and verify desired state after transient failures, appropriate to the database environment described. The key limitation is that immediately retrying every request in a tight loop can increase throttling and prolong outages. A concrete confirmation is that the workflow completes with controlled retries and no duplicate incompatible resource changes.
Incorrect Answers
A:ARM dependency ordering ensures prerequisite resource creation before dependent operations is a valid distinction for a different database situation, for example when a multi-region Bicep template creates a failover group before the partner server is deployed. Here the determining mechanism is that cloud management API operations can be asynchronous and subject to retryable rate limits. The alternative does not explain why the selected solution addresses this requirement.
B:Reusable IaC modules separate invariant SQL resource definitions from environment-scoped values is a valid distinction for a different database situation, for example when a team maintains different hard-coded Azure subscription IDs in each environment template. Here the determining mechanism is that cloud management API operations can be asynchronous and subject to retryable rate limits. The alternative does not explain why the selected solution addresses this requirement.
D:Azure management automation must match maintained command modules and resource provider APIs is a valid distinction for a different database situation, for example when a SQL deployment script uses an old deprecated Azure PowerShell module command. Here the determining mechanism is that cloud management API operations can be asynchronous and subject to retryable rate limits. The alternative does not explain why the selected solution addresses this requirement.
Question 208
A database administrator must resolve this Azure SQL or SQL Server issue: A database administrator wants to avoid storing sensitive passwords in Azure deployment scripts. What action BEST meets the requirement?
- Correct the driver token resource and the Azure SQL authentication flow
- Create a supported instance login and map each database user with explicit permissions
- Review role membership and effective GRANT DENY and REVOKE assignments on the securable
- Use Microsoft Entra workload identity or secured secret references for SQL connections where supported
Explanation
Correct Answer
D:Use Microsoft Entra workload identity or secured secret references for SQL connections where supported. The deciding requirement in this SQL platform scenario is addressed because secretless or managed-identity authentication reduces the exposure of reusable database credentials. Without the change, passing a SQL administrative password in script command-line history can leak the secret. Confirm the outcome through a controlled validation where deployment logs expose no reusable password and the approved identity can connect.
Incorrect Answers
A:Correct the driver token resource and the Azure SQL authentication flow would instead apply to another SQL platform issue, such as when a service uses Microsoft Entra token-based SQL authentication but connects with a token for the wrong audience. The present requirement calls for use Microsoft Entra workload identity or secured secret references for SQL connections where supported; this alternative changes a different control and does not directly resolve the stated database issue.
B:Create a supported instance login and map each database user with explicit permissions would instead apply to another SQL platform issue, such as when an Azure SQL Managed Instance needs a login with instance scope for several approved databases. The present requirement calls for use Microsoft Entra workload identity or secured secret references for SQL connections where supported; this alternative changes a different control and does not directly resolve the stated database issue.
C:Review role membership and effective GRANT DENY and REVOKE assignments on the securable would instead apply to another SQL platform issue, such as when a compliance application needs a T-SQL permission audit of who can modify a critical table. The present requirement calls for use Microsoft Entra workload identity or secured secret references for SQL connections where supported; this alternative changes a different control and does not directly resolve the stated database issue.
Question 209
The team corrects "A software team uses an Azure SQL logical server and wants to compare CPU across five databases" by applying "Use per-database Azure SQL metrics with consistent aggregation and time windows". Which validation is MOST convincing?
- Dashboards identify which database drives the observed peak
- The affected statement and its memory grant change are identified in diagnostics
- Measurements isolate elevated log-write wait or storage latency during slow requests
- High-percentile request durations coincide with the reported error intervals
Explanation
Correct Answer
A:Dashboards identify which database drives the observed peak. This observation tests the actual effect of use per-database Azure SQL metrics with consistent aggregation and time windows instead of assuming that a configuration change alone was successful. The underlying database behavior is that metrics scoped to logical servers and databases represent different resource perspectives. The control matters because aggregating unrelated databases without labels can hide a noisy tenant.
Incorrect Answers
B:The affected statement and its memory grant change are identified in diagnostics could validate a separate fix, for example when an Azure SQL workload begins consuming more memory after a new query deployment. Here the required proof is that dashboards identify which database drives the observed peak. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
C:Measurements isolate elevated log-write wait or storage latency during slow requests could validate a separate fix, for example when an Azure SQL Database reports higher latency while CPU utilization remains low. Here the required proof is that dashboards identify which database drives the observed peak. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
D:High-percentile request durations coincide with the reported error intervals could validate a separate fix, for example when an application sees sporadic timeouts that do not appear in a daily average of database response time. Here the required proof is that dashboards identify which database drives the observed peak. The alternative tests another database behavior and cannot establish whether the stated issue was corrected.
Question 210
Given the Azure SQL condition "A compliance application needs a T-SQL permission audit of who can modify a critical table", the proposed change is "Review role membership and effective GRANT DENY and REVOKE assignments on the securable". Which explanation BEST supports the change?
- Secretless or managed-identity authentication reduces the exposure of reusable database credentials
- SQL authorization combines principal memberships and object-level permission rules
- Entra administrator configuration establishes a trusted administrative identity for Azure SQL authentication
- Database object-level GRANT and role membership support narrowly scoped SQL access
Explanation
Correct Answer
B:SQL authorization combines principal memberships and object-level permission rules. This is the mechanism that makes the proposed change, review role membership and effective GRANT DENY and REVOKE assignments on the securable, appropriate to the database environment described. The key limitation is that assuming every denied user is missing a login overlooks explicit object-level DENY. A concrete confirmation is that effective permission testing documents exactly which identity can modify the table.
Incorrect Answers
A:Secretless or managed-identity authentication reduces the exposure of reusable database credentials is a valid distinction for a different database situation, for example when a database administrator wants to avoid storing sensitive passwords in Azure deployment scripts. Here the determining mechanism is that sQL authorization combines principal memberships and object-level permission rules. The alternative does not explain why the selected solution addresses this requirement.
C:Entra administrator configuration establishes a trusted administrative identity for Azure SQL authentication is a valid distinction for a different database situation, for example when a database team needs a security administrator to configure the Entra administrator for a logical SQL server. Here the determining mechanism is that sQL authorization combines principal memberships and object-level permission rules. The alternative does not explain why the selected solution addresses this requirement.
D:Database object-level GRANT and role membership support narrowly scoped SQL access is a valid distinction for a different database situation, for example when a service identity must query one reporting view but not update the underlying finance table. Here the determining mechanism is that sQL authorization combines principal memberships and object-level permission rules. The alternative does not explain why the selected solution addresses this requirement.