Policy Based Managment - Part1

What DBA is overworked?   What does your role mean?  Do you Install and Maintain SQL, or do you monitor performance?  Do you maintain the indexes, stats, and backups? Or, do you write and review code?   Our role has change so much over the last decade.  I get to see all variations of the role DBA.   One thing I see consistently is inconsistency.   Here in PFE, we have an engagement called a Risk Assessment.   In this assessment, we look at 1, 2, or 3 instances and review them for performance, patching, configuration, etc...   What I see week in and week out are servers that are built different, configured different, and maintained different.   How can this be?   We all have the same build document don't we?   Yes, it starts that way, but depending on who built the OS, and who built SQL, some things are done and some are not.

How can we fix this?   Policy based management can help.   This is a take on the Policies in AD.  If you want something to be set on EVERY computer, you make a Policy; well the same is true in SQL now.   How do you know if a vender's install changes system settings?  Do you monitor every system setting on every sql instance every week, month?  My honest answer is I seriously doubt it.   At least I have not found a single customer that does in 3 years of working as a PFE.  I also did not do this when I was a DBA.

I'm going to take this post to talk about Policies, and how they can help you make some extra time.   Please don't think this is time you can put your feet up and sip a Latte, because there is plenty of work to do, trust me, I see it :)  

Just a bit if history... Back in my day (ha, just a few years ago) I also needed to do this "Enterprise Wide" stuff.   My job consisted of checking logs, backups, jobs, and moving objects from dev to stage, to prod.   The checking took 80% of my time as I had just around 100 servers that I was responsible for.   So what got dropped?  Well, I have to say it was the most fun stuff that did, TUNING.   During that move from dev to stage, we were supposed to review the code and try and tune it.   Re-write statements, add indexes, review the design, etc...   None of that got done, unless of course you had a performance problem, then that was done at that moment.   Usually with the boss standing behind you saying, "Is it up yet?".   

Why the history? The reason is how we all addressed the exact same issue.   I fixed it by writing LOTS of vbscripts trying to pull every detail of the first part of my job.  I did this for two reasons.  1. Who wants to do the same exact thing over and over and over? (NOT ME!). 2. I wanted to do the COOL stuff and make things faster and challenge myself.    I see these same kinds of scripts all over at my customers' sites now.   Here is the main problem.   There is usually one person who truly understands every little detail about them, and everyone else, just uses them.   No biggie, well tell that to them when that person leaves the company, and you are left holding scripts that need to have a new feature added, or a bug fixed.   Ok, ok, where is this leading?  To Policy Based management of course.

Policies, introduced in SQL 2008 are a way to take the custom scripts out and still get the work done.   All of the "Magic" happens in the policy itself, and then we can automate some things around it.  Don't worry, I'll explain it.  

Let's start very small.   Let's say we want to check the last backup date of each database.   Easy, we can use a policy.  This policy happens to be shipped with the install of SQL. So let's add that policy first, and then we will see how to use it.

Adding the Policy...

• Go to a central server, preferably the Server you built in the first post I wrote, "Central Management Server".
• Open Object Explorer, not Registered servers.
• Open Management -> Policy Management and right click on Policies    
• 
• Click Import Policy...
• Once that window opens, you will need to find the policies. Depending on your install path, this may be different, but here is mine. "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"
• In that folder there are 50 different policies you can import. I recommend you import them all so you can play with them and see what is possible.
• Now click import and in a few moments you will have them on the server.

 Ok, now the policy is here, we can go and evaluate it locally.   You could do this on every 2008 server you have, but that's not really very powerful is it?   First we must walk, later we will run.  

If you open the policy's, you will find one called "Last Successful Backup Date".   Lets do a quick run or Evaluation of this.  Right click on the policy and then click "Evaluate"

 When you do, a new window will open and you will see the policy run.  It will show you the results of the policy as well as the details for each database it touched.

 

 As you can see, I don't backup my local laptop instances very often, but that is good to show you this.    Click on one of those "View" links under details and you will get a window like this...

 

 As you can see, this policy is looking at the API Property of LastBackupDate.  If that date is < NOW() - 24 Hours, or older than 24 hours old, the policy will fail.

 Ok, the cool stuff is out of the way, you have "Evaluated" your first policy, let's go take a look at what a policy looks like.  Close all of those windows and get back to MSSMS, and look at all of the polices.   Double click on Last Successful Backup Date.  This will open a new window so you can see it in detail.

 

 Click on the ... next to Check condition.

In this window, you will see the code that is to be evaluated.

 

Note: Facet: is grayed out.  This is grayed out when you review or change the policy; this is not grayed out when you create a new one.

 Ok, just to recap, we now know how to import a policy and evaluate it.   All of this is being done locally for now, I don't want this blog post to turn into a book.   There are several other directions I can take this, but I want to show you how this can be powerful, so we are going to evaluate this policy on all of the servers listed in your Central Management Server.    We are going to do this in a manual way this time, and I'll show you how to automate it as well as expand polices in my next post.

Alright, it's time to click over to Registered servers and look at the Central Management Server.

 

• Right click on your server name, ebackschSSD in my case, and select Evaluate Polices...

 

• In the new Window click the ... box and a new window will open.

 

• Click the Server Radio button, and then find the name of the server you just Imported the polices too. Click Ok
• You should now see a list of Polices that are on that instance. Scroll down and find the Last Successful Backup Date one and check the checkbox, and then click the Evaluate button.

 

• 5. You will now see a new window open and the policy will be evaluated for each database for each instance in that list.

 

 NOTE: You may notice the Server Names.  We need to use SQL 2008 to evaluate the policies, but we can evaluate them on older versions of SQL.

In my next post, I'll walk through how to automate this process with Power Shell to show the full potential of Policy Based Management.   I hope that you can see how this will help you better monitor your SQL Server Environment as a whole.    I encourage you to go and play with this.  Create some new Policies of your own.

 

 Eric, @SQLPilot
Tweet