Learn About Microsoft's Exam 70-450

  By Certification Magazine —

You need to ensure that no databases are created that violate company policy.

What should you do?

A.    Create a DML INSTEAD OF trigger.
B.    Configure the server to use the same collation as the master database.
C.    Import the Database Collation policy.
D.    Create an alert that runs a job to roll back a CREATE DATABASE or ALTER DATABASE that uses a different collation.

Answer:
C

Tutorial:
You should import the Database Collation policy. You can import or create policies to ensure that Data Definition Language (DDL) statements, such as CREATE DATABASE and ALTER DATABASE, do not violate company policy. The imported policies are enforced by Policy-Based Management. The Database Collation policy is a default policy included with SQL Server that verifies that all databases have the same collation as master and model. When a policy is configured as an On change: prevent policy and an action occurs that violates the condition, it uses DDL triggers to roll back changes that should be prevented.

You should not create a DML INSTEAD OF trigger. A Data Modification Language (DML) trigger executes when a DML statement such as INSERT, UPDATE or DELETE executes. Creating a database or modifying the collation of a database requires a DDL statement.

Configuring the server to use the same collation as the master database will not meet the requirements. The master database is configured to use the same collation as the server when it is created. Its collation can only be changed by rebuilding master. However, the collation of the server instance (and therefore the master database) does not prevent users from creating a database with a different collation.

You should not create an alert that runs a job to roll back a CREATE DATABASE or ALTER DATABASE that uses a different collation. An alert fires in response to an error condition or a SQL Server event that is logged to the event log. An alert does not fire in response to a DDL statement unless an error occurs. You can, however, create an alert that fires in response to an error raised by policy management. For example, you might import the Database Collation policy and create an alert that notifies an administrator if a user attempts to create a database with the wrong collation.

References:
COLLATE (Transact-SQL)
MSDN
http://msdn.microsoft.com/en-us/library/ms184391.aspx

Set the Collation of User-defined Databases to Match Those of the master and model Databases
MSDN
http://msdn.microsoft.com/en-us/library/bb402915.aspx

Monitoring and Enforcing Best Practices by Using Policy-Based Management
MSDN
http://msdn.microsoft.com/en-us/library/cc645723.aspx

Administering Servers by Using Policy-Based Management
MSDN
http://msdn.microsoft.com/en-us/library/bb510667.aspx