DBA Checklist with Policy Based Management

In my job as a SQL Server Database Consultant, I have my “recurring” customers. This means that every week, I’m at the customer for 1 day to do day-to-day DBA tasks.
Since those customers don’t have a DBA, I have to perform the daily DBA checklist on a weekly basis J… Initially, I’ve started with executing some T-SQL scripts to verify the SQL Servers. But as more servers were installed, my DBA checklist became really time consuming.

So, I had to find a way to perform the same checks in a more efficient way. I’ve started playing with the policy based management feature and I’ve created my “DBA Checklist” policies that I’m checking against all the SQL Servers at once.
Less time consuming and it gives you a nice overview of failed checks.

The first step that you need to do is creating a Central Management Server (CMS) and register all your servers under that CMS.
I’m not going into detail on this but you can find more information on http://msdn.microsoft.com/en-us/library/bb895144.aspx

I’ve created the following DBA Checklist

  • Verify if there were any failed SQL Agent Jobs during the last week
  • Verify if all the SQL Server Services are running
  • Verify if all databases are in a “Normal” state (not suspect, restoring, offline, …)
  • Verify free disk space
  • Verify free space in the data files
  • Verify if a full backup was taken of the database in the last 24 hours
  • Verify if a transaction log backup was taken of the database in the last hour
  • Verify if there were errors in the error log with a severity > 17 for the last 7 days

This checklist is pretty basic, I know, but it gives me a good idea if my SQL Servers are fine or not. Feel free to give me any suggestion of checks that I can add to the list.

You can download the policies from my SkyDrive and import them into your Central Management Server.
Please note that the policies will not work against a SQL Server 2000 or lower.

Connect to your CMS – Go to Management – Policy Management – Policies
Right click Policies and select “Import Policy”

 

Select the xml –files you just have downloaded as the “Files to import” and click OK.

 

If all went fine, you should get a result like the one below

 

Now let’s do a check against all my servers.
Right click on your CMS and select “Evaluate Policies”

As a source, select again the CMS server, because that’s the one that where you have just imported the policies

Select the policies that you want to verify. You can easily find my policies because I’ve put them in the category “DBA Checklist”

Now, just click the Evaluate button. As a result, all the selected policies will be checked against the servers that are registered under your CSM.
When the checks are finished, you should see this kind of result

Below, in the target details area you can see which checks are failed. Click on the “View” link to see more details and correct them.
In the end, you want to see all you’re policy checks green 😀 !

If you should have suggestions or you find any improvements, please let me know so that I can adapt them.

Have fun!

 

4 comments on “DBA Checklist with Policy Based Management
  1. Hi,

    I want to implement your checks and I’m currently testing the policies. However the policy “Database is normal state” doesn’t seem to work correctly on my testserver: it doesn’t check offline and/or restoring databases, for some reason these databases aren’t considered being a target. Do you have any idea what is causing this?

    Kind regards,

    Anne

    • Hi Anne,

      You are correct. The database state doesn’t work correctly. It’s a bug. It looks like it can’t check the database if it has a state that is different than ONLINE…
      I thought I’ve opened a connect for this but I can’t find it anymore.
      As workaround you could implement another condition as follows:
      FACET = Server
      ExecuteSQL(‘String’),’select count(1) from sys.databases where state_desc<>”ONLINE”’)
      =0

      This will count all the databases that have another status than ONLINE. If it’s >0 you know that on that server “a database” has another status.
      You should then manually check the databases on that server.

      Regards
      Pieter

    • Hi,

      Not sure if I understand your question correctly but I created the checklist myself with PBM.
      You can download the policies and import them on your system.
      To import the policies, connect to your instance with SSMS – Select Management – Policy Management – Policies.
      Right click on the Policies folder and select Import Policy.
      After that, you can evaluate them against your instance.

      Regards
      Pieter

Leave a Reply

Your email address will not be published. Required fields are marked *