MCM SQL Server 3

Question 1

Which SQL Server feature enables you to encrypt data at rest by encrypting the entire database files, including log files, using a Database Encryption Key?

Accepted Answer

Transparent Data Encryption (TDE)

Answer

TDE encrypts the entire database at the file level using a Database Encryption Key, protecting data at rest without requiring application changes.

Question 2

In SQL Server, what happens to a transaction that encounters a deadlock?

Accepted Answer

The transaction with the lowest deadlock priority is chosen as the victim and rolled back

Answer

SQL Server's deadlock monitor selects a victim based on the lowest deadlock priority (SET DEADLOCK_PRIORITY) and, if equal, the transaction that is cheapest to roll back.

Question 3

Which extended event action captures the T-SQL text of a batch or statement currently executing when an event fires?

Accepted Answer

sql_text

Answer

The sql_text action captures the full text of the SQL batch or statement associated with the event at the time it fires.

Question 4

What is the function of a SQL Server 'Resource Governor' classifier function?

Accepted Answer

Routes incoming sessions to workload groups based on session attributes

Answer

The classifier function is a scalar T-SQL function that Resource Governor calls at session login to determine which workload group (and thus resource pool) the session belongs to.

Question 5

In SQL Server replication, which type of replication is best suited for scenarios where subscribers occasionally connect and need to synchronize accumulated changes?

Accepted Answer

Merge replication

Answer

Merge replication tracks changes at both publisher and subscriber and reconciles them during synchronization, making it ideal for occasionally-connected subscribers.

(MCM) Microsoft Certified Master Practice Test

(MCM) Microsoft Certified Master Practice Test

MCM SQL Server 3