DP-300 Sample Questions & Answers

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.

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.

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.

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.

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.

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.

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.

Cardinality Estimation (CE) feedback is a feature within the Intelligent Query Processing suite that specifically addresses problems arising from inaccurate cardinality estimates. It identifies queries where the estimated number of rows significantly differs from the actual number of rows and then attempts to adjust the model used for subsequent executions, leading to better execution plans. This is achieved by persisting the adjustments as part of a Query Store hint. The other options are also IQP features but address different performance problems.

Long-Term Retention (LTR) is the Azure SQL feature designed for this purpose. It allows you to configure policies to automatically retain specific full backups in separate Azure Blob storage for up to 10 years. This is the standard, managed way to meet long-term compliance and archival requirements. Manually copying backups is not a managed or automated solution. Geo-replication is for DR, not long-term backup. The base retention period cannot be extended beyond its maximum limit for the service tier.

The Serverless compute tier is specifically designed for workloads with intermittent, unpredictable usage patterns. It automatically scales compute based on workload demand and can be configured to pause the database during periods of inactivity, where you are only billed for storage. This auto-pausing feature makes it the most cost-effective option for development and test environments with long idle times. The Provisioned tier bills for compute resources continuously, regardless of usage. Hyperscale is designed for very large databases and is not the most cost-effective for this scenario.