Every DBA loves a checklist in the morning, to try and speed up my checks I decided to mix a little policy based management and a SSRS subscription, this is a really easy way to set up a daily report (or even weekly / monthly reports) that can cover pretty much anything you want it to for your servers health.

I’ve split this into sections to go through the steps needed to set it up:

Daily Dashboard – 01 Setting up a Central Management Server
Daily Dashboard – 02 Setting up a Enterprise Policy Management Framework Server
Daily Dashboard – 03 Creating the Policies
Daily Dashboard – 04 Setting up the Daily EPM Agent Job
Daily Dashboard – 05 Create the Report and set up the subscription

The report ends up looking like:

DailyDashboardReport

Enterprise Policy Management Framework

Enterprise Policy Management Framework is a genius tool. It uses Policy Based Management, PowerShell and Central Management Servers to make PBM run against multiple servers and record the results to a central database. It comes with a set of SSRS reports to summaries results.
You can download it from: http://epmframework.codeplex.com/ , http://technet.microsoft.com/en-us/library/dd542632(v=sql.100).aspx

Lets Begin!

Setting up a Central Management Server

For more details on CMS look at the links below. but in summary it’s a registered server list that you can connect to from multiple machines and run queries against groups of servers that you define.

Open SSMS and View the Registered Servers (Ctrl + Alt + G)

dd1-01t

 

Right Click on the Central Management Servers and Select “Register Central Management Server”

dd1-02

Enter the details of your Server (remember this will be both our CMS and our EPM server)

dd1-03

 

Test and Save. (this will close the new window and return you to the Registered servers view)

Right click on your new CMS Server and Select “New Server Group..”  entering your details

dd1-04

Now right click on your new Group and select “New Server Registration” add as many servers you like to the group.

dd1-05

Once you’ve added the servers it should look something like:

dd1-06

 

Now you can simply right click on the Development Folder and do lots of  cool stuff, like run a T-SQL query against all the servers in a group. Or run a policy against all the servers in a group.

Run a Query against all the Servers in Development:

Right click on the Development folder and select “New Query”

dd1-07

Note the Query window shows a Connected. (2/2) – for DEV-SQL and DEV-SSIS at the top.

dd1-08

When we run the query the results show the Server Name that the query ran against and the results for the server. (Sad note you cant export or get access to these results to pipe into table, they are stand alone results.)

 dd1-09

 

Now we’ve set up a CMS we can move on to next step which is to set up the EPM Server. For more information on CMS look at the links below. especially  Pieter Vanhoves post on using Polices against a CMS to do your daily checks.

Links:

http://technet.microsoft.com/en-us/library/bb934126.aspx

http://www.sqlservercentral.com/articles/Central+Management+Server+(CMS)/88485/

http://pietervanhove.azurewebsites.net/?p=1151

Advertisements