Free DP-300 sample questions

Correct answer: Elastic Jobs.

Elastic Jobs are specifically designed for running T-SQL scripts across a group of Azure SQL Databases, including all databases in an elastic pool. It provides native capabilities for defining target groups, creating job steps with T-SQL, scheduling, and monitoring execution, making it the most suitable and efficient tool for this scenario. Azure Automation is a more general-purpose automation service and would require more complex scripting to iterate through all databases. SQL Server Agent is not available in Azure SQL Database.

Correct answer: An Always On availability group with two replicas in synchronous-commit mode.

An Always On availability group with synchronous-commit mode ensures an RPO of zero by requiring the transaction to be hardened on the secondary before committing on the primary. This configuration also provides automatic failover capabilities for a low RTO and allows the secondary replica to be configured for read-access, meeting the reporting requirement. An FCI provides HA at the instance level but doesn't inherently provide a readable secondary. Asynchronous-commit mode would not guarantee an RPO of zero. Log shipping has a higher RPO and RTO.

Correct answer: Data Migration Assistant (DMA).

Data Migration Assistant (DMA) is the correct tool for this task. Beyond its primary function of identifying compatibility issues, DMA can run a performance data collection assessment. This assessment gathers detailed information about the source SQL Server's workload, which is then used to recommend an appropriate Azure SQL Database service tier and size. It collects the necessary metrics like CPU, memory, IOPS, and query statistics. DMS is for executing the migration, not for assessment. The SSMS Performance Dashboard is for real-time monitoring, not for long-term data collection for baselining.

Correct answer: A view that uses a CASE statement to show the Salary or a masked value, combined with granting SELECT on the view instead of the table.

While DDM and RLS are powerful security features, they don't natively support conditional unmasking based on the data in another column for the same user. DDM applies the mask to all non-privileged users, and RLS filters entire rows. The most direct and flexible way to implement this specific logic (unmask for your own department, mask for others) is to create a security view. The view's logic would contain a CASE statement that checks if the employee's department matches the analyst's department (e.g., using `USER_NAME()` or session context) and returns either the actual Salary or a masked value. The analyst is then granted permission only to the view.

Correct answer: `sys.dm_exec_requests` and `sys.dm_exec_sql_text`

The `sys.dm_exec_requests` DMV provides information about each request currently executing in SQL Server, including the `sql_handle` for the executing batch. To get the actual text of the SQL statement, you must pass this `sql_handle` to the `sys.dm_exec_sql_text` dynamic management function. Joining these two allows you to see the SQL text for a specific session ID. `sys.dm_exec_sessions` provides session-level information but not the currently executing statement text. `sys.dm_tran_locks` shows lock information but not the query text.

Correct answer: True

This is true. In an Azure environment, the availability group listener's IP address needs a mechanism to float between the VMs hosting the replicas. An Azure Load Balancer is used for this purpose. It is configured with a health probe to detect which node is the primary replica and directs traffic to that node's IP address accordingly.

Correct answer: uniqueString()

The `uniqueString()` function is designed for this purpose. It creates a deterministic 13-character hash string based on one or more seed values you provide, such as the resource group ID. Because it's deterministic, if you run the same template with the same seed values again, it will generate the same unique string, which is essential for idempotent deployments. The `guid()` function generates a new random GUID on every run, which would cause the deployment to fail on subsequent runs as it would try to create a new resource with a different name.

Correct answer: Increase the number of vCores for the primary compute replica.

In Azure SQL Database, including the Hyperscale tier, the maximum transaction log generation rate is directly tied to the number of vCores allocated to the compute replica. To increase the log rate limit beyond 100 MBps, the number of vCores must be increased. Adding read replicas helps with read scaling but does not affect the write or log generation capacity of the primary replica. Increasing max database size is irrelevant to the log rate limit. Switching to the DTU model is not an option for Hyperscale.

Correct answer: Always Encrypted with VBS Enclaves.

Always Encrypted is designed specifically for this use case. It ensures that sensitive data is encrypted on the client-side and remains encrypted at rest, in transit, and even in memory on the SQL Server instance. The decryption keys are never available to the database engine or to a DBA. This separation of duties prevents high-privileged users like DBAs from accessing the plaintext data. TDE encrypts the entire database at rest but does not protect from DBAs who have access to the running instance. DDM only masks data presentation and can be bypassed. SQL Audit tracks access but doesn't prevent it.

Correct answer: `sysmail_event_log`

The `msdb.dbo.sysmail_event_log` view contains one row for each message related to the Database Mail system. This includes detailed success or failure messages from the Database Mail external program. When an email fails to send, this is the primary location to look for specific error messages, such as SMTP server connection failures, authentication errors, or other delivery issues. The other views show the status of mail items but not the detailed error logs.