Creating A Policy With the GUI

In the previous posting we introduced the main PBM concepts through the use of a sample policy: All stored procedures in the database must start with "usp_". In this posting we will walk through the creation of that policy using the PBM GUI accessible from SQL Server Management Studio (SSMS).

The first step in creating the policy is to create a condition. To do this we use Object Explorer to navigate to the Policy Management node.

Right-clicking on the Conditions folder we select New Condition... which launches the New Condition dialog.

First we give our Condition a name. Conditions can be reused by multiple policies so we want to give it a meaningful name. Since conditions define the desired state of the system we'll give it a name in the positive. Something like SP Name Prefix usp_ should do. Next we have to pick which facet we want to build our expression over. Remember, facets define two things for us: target types and properties. We have a choice of three facets: Stored Procedure, Multi-Part Name, and Name. For this example we'll pick the Multi-Part Name facet.

 

Now that we have our condition created we right-click on the Policies node in OE and select New Policy... We give our policy a name and select the condition we created above. We also could have started from the New Policy dialog and launched into the New Condition dialog.

 

We want to ensure no stored procedures are created on the system which violate the policy, therefore, we choose the On change: prevent evaluation mode.

Next we need to choose which targets the policies applies to. Because we built our condition on the multi-part name facet we see all of the targets which support that facet. We select Stored Procedure since that's the only one we care about for this example.

On the Description tab we can enter a description for the policy and a link (http, https, and mailto) for where to go for additional help.

After we enter this information click OK to save the policy. The policy and condition show up in Object Explorer.

Now we can attempt to create a stored procedure which violates our policy. In this example we create a nonsensical SP but the important point is the name, MyProcedure. Since our policy states names must start with 'usp_' our procedure should not be created. And that's exactly what happens. Let's take a closer look at the information we get back. First, the message tells us which policy we violated along with which object violated the policy. We also see the condition, policy description, and the link for additional help. All of this is useful information if the person is not familiar with the policy. The statement which violated the policy is included in the output. If this was a long SQL script this can help us trouble shoot the offending code.

This simple example showed you everything you need to get started using Policy-Based Management. Though our condition was straightforward they can get quite complex. In future postings we'll explore building more complex conditions in addition to some of the other advanced capabilities of Policy-Based Management.

_____________

About the Author:
Dan Jones is a PM on the SQL Server Manageability team at Microsoft.