Policy-Based Management in SQL Server 2008

Policy-Based Management is one of the several nice features of SQL Server 2008. To explain how this feature is implemented, let me give you a real time scenario that I faced myself. The Manager I report to has been trying to implement naming standards across our firm. Two of his favorite naming standards are:

• The names of all user stored procedure should begin with usp.
• The names of all user created views should begin with Vwv.

Since a Manager doesn't have time to review all the code developed by every developer, it usually happens that Stored Procedures, Views, Functions etc written by developers get promoted to production even if the code is against the corporate naming standards. SQL Server 2008 makes it quite easy to handle such situations. You simply need to create policies with all your naming standards as conditions. That is it, its that simple. Let me walk you through the process of creating a policy. Lets create the first one - The names of all user stored procedure should begin with usp.
Connect to your SQL server 2008 instance, expand the Management folder, then expand the Policy Management folder, right-click the Policies folder and select New Policy as shown in the figure below.

The Create New Policy dialog box appears. Give an appropriate name for the policy and in the Check condition drop-down, select New Condition as shown in the figure below.

You can also select an existing condition that you have previously defined. (You can create a new condition by right-clicking the Conditions folder under Policy Management). Also, please note that you can only have one condition for a policy.
The Create New Condition dialog box appears as shown below. Type in an appropriate name for the condition, select Stored Procedure in the Facet drop-down and set the condition in the expression section as show in the figure below and click OK to return to the Create New Policy dialog box.

Your screen should look similar to the one shown in the figure below.

In the Against targets, you can either apply the condition to all the stored procedures or create a condition(by clicking the down arrow next to StoredProcedure) to apply it to only the required ones. The same applies to Database; you can apply the condition to stored procedures in all the database or create a condition to apply that to only the required ones.
In the Evaluation Mode drop-drown, you have 3 options to select:

• on demand - Use this option to evaluate the policy as and when required by the user. To evaluate a policy, right-click the Policy folder and select Evaluate.
• on schedule - Use this option to evaluate the policy automatically by creating a schedule using SQL Server Agent.
• on change: log only - This option uses event notification to evaluate a policy whenever the policy is violated.
• on change: prevent - This option automatically prevents the user from violating the policy. It uses DDL triggers behind the scenes.

In the Server restriction drop-down, you can select the servers that are appropriate for this policy by creating a condition or leave it as none to apply the policy to all servers.
Now lets try to create a stored procedure whose name doesn't begin with usp and see what happens.

As shown in the figure above, SQL Server prevented the creation of stored procedure since its violating the policy we just created.