Policy based management of SQL Server

Policy management allows you to create rules over your SQL Server, such as naming conventions. In SQL Server Management Studio, go to "Management" then "Policy Management".

Here's a quick step by step on how to create a policy.

Step 1 - Create a "condition"

• The key to creating a condition is knowing which "facet" to build your condition on. There are about 20 facets built into SQL Server and the names can be tricky to decipher. For example, to set a rule as to how tables are named, you create a condition using the facet called "Multipart Name".

• Build the expression.

Step 2 - Create the policy

• Select your condition you created in step1 from the drop-down box

• Against targets allows to you establish which databases and/or tables and/or views you want to apply policy again.

• Evaluation mode:

  • On demand

  • On schedule

  • On change: log only

  • On change: prevent

• You can optionally assign your policies to your own user-defined list of categories.

Other comments:

* Policies can be exported to a file and then imported on another server so that you can evaluate the other server's compliance with the policy.