PBM(Policy-Based Management) – One of the most fancy terms which we(the fantastic SQL Server DBA’s) are listening starting SQL Server 2008. In this Blog post I’m not going to explain in detail what PBM is how exactly it works, instead I’m going to show how to create a Policy for checking Auto-Close, Auto-Shrink,Compatibility Level and Recovery Model of all the databases on a regular basis and how to setup email alerts if any of our databases are violating our policy.
For detailed explanation on PBM PS http://msdn.microsoft.com/en-us/library/bb510667.aspx
As i mentioned in my previous post, setting up DB Mail profile is mandatory for sending email alerts from SQL Server. Okay, enough blabbering..let’s get started!
As the first step, under “Management” Node in your OE(Obj Explorer) expand “Policy Management” and RC on “Policies” and choose create “new policy” as shown below.
Note: You can create conditions first and choose them accordingly for any future policies as well. In this post, I’m going to create everything from Create new policy Option it self, which avoids confusion for people who are new to PBM.
Name your Policy and select “create new condition” to create one as shown below.(you can pick from here if you’ve already created one in the past as mentioned in the above note)
Once, you click on “create new condition”, you’ll be welcomed with a new screen as shown below. Give any appropriate name for your condition and choose one facet from the drop down as shown below. I this case I’ve to choose Database Options Facet, because i’m creating a policy to evaluate DB Options.
select all your required options and create an expression using And/OR, Drop down Menus as shown below and Click OK. You can add your own description if you need any under “Description” tab.
Once done, now you have choose your targets(I selected against every Database) and evaluation mode(I’ve selected on schedule and created one to run/evaluate on daily basis) as shown below.
Once done..you can add your own description(optional) and click Ok. At this point we are all set with setting up a policy which runs on a daily basis to evaluate all our conditions. Let’s try to evaluate manually and see how it looks.
Go to your policy node and RC and select Evaluate(Enable the policy if it’s disabled). You’ll be welcomed with the below screen which is very much self explanatory…
You can click on View to see more details on the violation and also you can click on Apply to fix them all from right here with a single button click.
Configuring Email Alert:
Soo…we haven’t configured email alerts yet upto this point. To achieve this , the simple trick is to know the exact Error Number(s) which PBM generates in our Error logs and to setup alerts/actions for those Error Numbers in our SQL Server Agent. As per BOL the below are the error numbers which we’ve to look for when setting up alerts for PBM
| Execution mode |
Message number |
| On change: prevent
(if automatic) |
34050 |
| On change: prevent
(if On demand) |
34051 |
| On schedule |
34052 |
| On change |
34053 |
To double check, Let’s see our SQL Error Log and Event Viewer what has been logged…
Okay…..a message has been logged with eventID 34052. So…now let’s configure Alert to notify operator from SQL Agent for this Error Number.
FYI..I already created an Operator as you can see below
Okayyy…it’s time to create Alert. Follow the below Screen shots in the same order
Choose Error Number as shown below
DO NOT JUST CLICK OK now….Instead go to RESPONSE tab and choose an operator whom you want to notify for this Alert based on this Error Number as shown below.
Again, don’t just hit OK, instead go to Options and enter a customized message which you feel appropriate for you as shown below
Now click OK:) and let’s try to evaluate our policy manually as we did earlier(Go to your policy node under Management in your Object Explorer and RC and select Evaluate) for testing our policy initially and let us see what we’ve in our Inbox.
Perfectttttttttttt………!!! See how simple it is…to make yourself a hero with all these cool automation stuff 🙂
Hope this Helps!!
MSSQLTREK 