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 Daily EPM job

So at this point we should be ready to set up the agent job. All we need to do is create a step that calls the ps1 file we put in the root of the “Enterprise Policy Management” folder and put some parameters to it and it will do the rest! That means it will run each of our 5 polices against the 2 SQL Servers and hold the results in a table in the MDW database..

One of the main things that can go wrong here, is that you run the PowerShell step without using a Proxy account that has access to all the Servers it will run against! To set up a Agent Proxy account look at http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/ NOTE! the example included is for SSIS. Not PowerShell, everything is the same except you will need to enable it for PowerShell instead of SSIS. For the steps below I will expect you to have a EPM Proxy set up

Open SSMS and connect to your CMS / EPM Server.

Open the SQL Server Agent -> Jobs and Right Click “New Job”

Fill in the General Details

clip_image002

In Steps Create a new PowerShell Step (Again remember to use a Proxy account that can access all the CMS servers and run all the Polices you create

clip_image003

Lets break down the PowerShell code:

SL "<a href="///\\DEV-BERRY\p$\Enterprise%20Policy%20Management%20Framework\">\\DEV-BERRY\p$\Enterprise Policy Management Framework\</a>"
.\EPM_EnterpriseEvaluation_3.0.0.ps1 -ConfigurationGroup "Development" -PolicyCategoryFilter "PROD_DAILY" –EvalMode “Check”
SL "<a href="///\\DEV-BERRY\p$\Enterprise%20Policy%20Management%20Framework\">\\DEV-BERRY\p$\Enterprise Policy Management Framework\</a>"

SL is an Alias for Set-Location, so all we are doing is moving Powershell to the root of our EPM folder.

.\EPM_EnterpriseEvaluation_3.0.0.ps1

We call the ps1 script that we edited earlier.

-ConfigurationGroup "Development"

This is the CMS folder we want to run our Policies against.

-PolicyCategoryFilter "PROD_DAILY"

Set which category we want to run (We will run every Policy that is set to that Category)

–EvalMode “Check”

some polices can “fix” the issue if it results in a fail. (Ie change a database from Simple Recovery mode to Full Recovery Mode) as we don’t care about that we leave it as “Check”

So for now lets click ok and just run the Job without setting up a Schedule. Depending on how many servers you added to your CMS will depend on how long it takes.

Once the job runs and completes successfully. we can go to the MDW database and open the policy.PolicyHistory table.

Use [MDW]
GO
SELECT * FROM [policy].[PolicyHistory]

You will now see 10 results (5 for DEV-SQL 5 for DEV-SSIS)

clip_image004

If you see the results then set up a schedule for your job and we are now down to the final hurdle! the Reports.

 

Summary:

After the previous posts of setting things up we finally started running things and seeing results. In the real world you could now start expanding on categories and schedules creating a in-depth analysis of your environment via polices! fun times 🙂

Advertisements