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

Setting up the Enterprise Policy Management Framework.

so we have our CMS set up to run our policies against. Now its time to set up database to hold the results to and set up the fun stuff.

Download the EPM Framework from the link http://epmframework.codeplex.com/

Create the Folder / File structure:

If you have a drive for non system files use that (For me that’s the P drive) and create the below folder structure:

P:\Enterprise Policy Management Framework

P:\Enterprise Policy Management Framework\Install

P:\Enterprise Policy Management Framework\Results

clip_image002

Extract the codeplex zip to the Install folder. it contains 3 folders

clip_image003

Next open SSMS connect to the CMS / EMP Server and run the T-SQL Script in folder: “0Setup Scripts” File name: “EPM_Create_database_Objects.sql”

Make sure you have SSMS running in SQLCMD mode. (The script will highlight in grey the parts that use SQLCMD if its in the correct mode, so should look like below!)

Update the following:

:SETVAR ServerName

Should be the name of your CMS / EPM server.

clip_image004

After you run the script you will get some warnings which you can ignore.

Next we are into folder “1Down Version Evaluation PS” which contains a PowerShell Script. Open it in PowerShell ISE or NotePad to make the following edits:

Line 19:$CentralManagementServer = “WIN2008”

Change to your CMS / EPM server

Line 23: $ResultDir = “e:\Results\”

Change to “P:\Enterprise Policy Management Framework\Results” or the correct path to your Results folder

You don’t need to run this script now, just now place it in the route of the “P:\Enterprise Policy Management Framework”

 

Summary

There we go.. It doesn’t seem like much, but we have now set up the EPM part. and ready to get things moving to the next stage.

Advertisements