Policy Based Management And Filtering

With policy based management, it is possible to define policies and evaluate the policies against lots of instances and againsts lots of objects. While evaluating policies, to exclude the object and the servers which are out of scope will optimize the management.

Let’s create a policy and see how to do and how to apply filtering.

Policy: Transaction logs must be backup  up.

As seen below we have 3 databases which have recovery models, simple, full and bulk_logged. And our aim is to evaluate the databases which has recovery models full and bulk_logged for transaction log buckup .

To create policies, we will use management studio. Object explorer has 3 items for Policy Based Management under “Policy Management”, “Policies”, “Conditions” and “Facets”.

First of all, we create “Condition” which defines the databases that we will apply policies. On the “Conditions”, clicking the right button of mouse and “New Condition”, a new window is opened. We name the conditon as “Databases to be checked for transaction log backup”. Then  “Database” is choosen as facet. And the expression part is completed as seen below.  Only full and bulk_logged recovery models are choosen.

The conditon is ready for databases which are to be evaluated.

Now, let’s create the conditon for the policy. To create a conditon with name “Transaction logs backup  time must be less than 10 hours”, first we choose “Database Maintenance” as “Facet”. The “Field” on the expression part must be @LastLogBackupDate. As an example, let’s make the conditon  the transaction logs must be backup up in last 10 hours. For this, “Advanced Edit” is used. When the elipses are pushed under value, “Advanced Edit” window is opened.

With the help of “Advanced Edit”, by using the functions under  “Functions and properties”,  we create the condition as “DateAdd('hh', -10, GetDate())”.

The policy condition is ready to use. Policy can be created now. Let’s give the name as “Transaction logs must be backed up”. After naming, let’s choose the conditon which we created previously as below.

The policy is created. This policy will be applied all databases on all intances. But we want to exclude the databases which have simple recovery model. Because of this, as seen below, on “Against targets”part, we choose the conditon that is created earlier.  If “every” option is not changed, the policy will be applied all user databases regardless of recovery model.

At this point, for filtering, it is possbile to create new conditon by clicking “New condition” item.
This way it is possible to filter every policy.

Now, we know how to filter objects. It is possible to filter servers too. We create a conditon to filter servers as below. The policy will be applied only the servers that are 2008 and 2008 R2 version. As facet, Server is choosen. The expression “@VersionMajor = 10” defines version 2008 and 2008 R2.

As “Server restriction”, we choose the condition we have created. This policy will be applied only sql servers that has verison 2008 and 2008 R2.

        

We have created 3 condition to be able to define the policy as seen below.

To run this policy, we click the right button and push “evaluate” option. The result is as seen below. There were 3 databases hosted on server. The one that has simple recovery model is not evaluated. While one of the other two is violating the policy, the other suited policy.

Conclusion: while creatin policies it is possbile filter servers and objects.