Managing SQL Servers in multi-server environment via Central Management Server, Policy Management, Power Shell and Reporting Services - 2

Collection of Evaluated Policies:

To collect results, firstly of course we need a database. But, don’t worry about that actually it is the easiest part since we have a ready script for that. The only thing you need to do is downloading EPMFramework from the link http://epmframework.codeplex.com/releases/view/28621 I haven’t changed the default download path and have just gone to C:\Users\isilefe\Downloads\CodePlex 3.0.zip\CodePlex 3.0\0Setup Scripts and opened EPM_Create_Database_Objects.sql script.

And now, you should configure the variables below according to your environment.

:SETVAR ServerName "ISILEFE02" – ISILEFE02 is the name of central management server in my environment

:SETVAR ManagementDatabase "PSDatabase" – PSDatabase is the name of the database that I am going to use, it could be either and existing one or a new one, I just preferred a new one.

GO 

After changing these two variables, you can run the script in SQL CMD mode. 

 

 

Now, our database is ready to store the data.

The next step is collecting data:

It is also an easy step since we have a ready powershell script. Don’t worry if you are not familiar with PowerShell, you can just review my blogpost http://blogs.msdn.com/b/isilefe/archive/2012/07/23/how-to-manage-your-sql-servers-with-powershell.aspx to understand the logic better.

I could find the downloaded script under C:\Users\isilefe\Downloads\CodePlex 3.0.zip\CodePlex 3.0\1Down Version Evaluation PS. Firstly, you should edit this script in notepad, by declaring centralmanagement server name, database name. and also you should define a results directory.

# Declare variables to define the central warehouse

# in which to write the output, store the policies

$CentralManagementServer = "ISILEFE02"

$HistoryDatabase = "PSDatabase"

# Define the location to write the results of the

# policy evaluation.  Delete any files in the directory.

$ResultDir = "c:\Results\"

And now, the thing to do is just going to SSMS and right click on your central management server name and choose StartPowershell to try if the script can be run successfully.

 

 

The script asks for configuration group and category filter as a parameter, to be able to run the script in all configuration groups and for all categories, I made a small edit in the “parameters” part as seen below:

# Uses the Invoke-PolicyEvaluation Cmdlet

 

#param([string]$ConfigurationGroup=$(Throw `

#"Paramater missing: -ConfigurationGroup ConfigGroup"),`

#[string]$PolicyCategoryFilter=$(Throw "Parameter missing: `

#-PolicyCategoryFilter Category"), `

#[string]$EvalMode=$(Throw "Parameter missing: -EvalMode EvalMode"))

 

[string]$ConfigurationGroup=""

[string]$PolicyCategoryFilter=""

[string]$EvalMode="check"

 

And now you can run the script without any parameters, if you would like to define categories and configuration group you can use the original script and run the script by passing parameters:

SL "C:\Users\isilefe\Downloads\CodePlex 3.0\CodePlex 3.0"

.\EPM_EnterpriseEvaluation_3.0.0.ps1 -ConfigurationGroup

After you press “Enter” you see informational text as below meaning that you successfully run your policies:

 

And now, let’s check if we have the data in our database. I have just run the scripts below on my central management server:

select * from PSDatabase.[policy].[v_PolicyHistory]

select * from PSDatabase.[policy].[v_PolicyHistory_LastEvaluation]

 

 

So far so good, I can see the results of evaluated policies for my both sql instances that I have registered under Central Management Server.

However, still not perfect J Why not?

Since I don’t want to run policies each and every time manually. How to automate it? Please review my next blog post on this topic.