Policy Based Managment - Part2
Just to recap what I talked about before. In Part1 I showed you how to setup a Central Management Server and what that meant. We then covered the start of Policies. How to import them, evaluate them, and then evaluate them on a Central Management Server.
This time we are going to cover how to automate the checking of several policies on a folder in the Central Management Server.
For this I am going to use a little help. I like to use the Enterprise Policy Management Framework on Codeplex. I think this is a great start to demo what is possible. It also provides us with a great little PowerShell script. Thanks @SQLGal for putting it up there to use. All of the things below can be downloaded so you can do this in just a short time.
This demo assumes you have SQL 2008, and SQL Server Reporting Services installed. It also assumes that you have imported all of the policies in Part 1.
First download all of the files from codeplex, once unzipped you should have 3 folders.
- 0Setup Scripts
- 1Down Version Evaluation PS
- 2Reporting
STEP 1:
The first thing we need to do is to setup a database to collect all of this data. The demo assumes you have a MDW database for the Management Data Warehouse. You can use any database you like, but go ahead and create it now.
Open EPM_Create_Database_Objects.sql located in 0Setup Scripts. We need to modify a few lines, but first we need to make sure we are in Command Mode. In MSSMS click on Query -> SQLCMD Mode.
When that is done you will see 3 lines gray out. Those are the lines you need to edit.
Set :SETVAR ServerName "WIN2008" to your server name :SETVAR ServerName "<YourServerName>"
Set :SETVAR ManagementDatabase "MDW" to your database name. :SETVAR ManagementDatabase "<Your Database Name>"
Once those two things are done, you can execute the script. This will create the tables to accept the results from the PowerShell script.
STEP 2:
The next step is to put the PS1 file somewhere on the Central Management Server. Again it does not matter where, just as long as the SQL Agent can get to it.
Create that folder, and also create a folder named Results in that same area.
Now that the PS1 file is in its final home, we need to edit just a few lines in it. This file is just a text file, so open it in your favorite text editor.
Here is the block we are going to touch.
# Declare variables to define the central warehouse
# in which to write the output, store the policies
$CentralManagementServer = "WIN2008"
$HistoryDatabase = "MDW"
# Define the location to write the results of the
# policy evaluation. Delete any files in the directory.
$ResultDir = "e:\Results\"
$ResultDirDel = $ResultDir + "*.xml"
Remove-Item -Path $ResultDirDel
# End of variables
The only 3 lines we edit are...
$CentralManagementServer = "WIN2008"
$HistoryDatabase = "MDW"
$ResultDir = "e:\Results\"
Set those to the appropriate values and save the file.
STEP 3:
The next thing I like to do here is create a text file with the correct PowerShell call so I can keep that on the server also. So create a new text file. Below is a start, then you will need to modify for your information.
SL "<Path to PS1 File>"
.\EPM_EnterpriseEvaluation_3.0.0.ps1 -ConfigurationGroup "<Central Management Folder>" -PolicyCategoryFilter "Microsoft Best Practices: Maintenance" -EvalMode "Check"
I need to break on a bit of a tangent here. Above we are putting in "Microsoft Best Practices: Maintenance". Each Policy that we make has a Category. You can make your own category, and then change this name. If you imported the policies like I did in Part 1, then you can use this name. Over time you will tweak the policies to you specific requirements, and then I would assume you use your own categories.
Okay, now just a few more things to do. Now that we have the script in place, and the execution command laid out, we need to add the reporting piece. This example was written for SQL 2008, so you will get some upgrade warnings if you open it in 2008R2. This is fine, and it works just fine.
Open up BIDS and then open a project. Navigate to \2Reporting\PolicyReports and open the project.
Once the project is open in BIDS you will have just a few areas to make modifications.
Shared Data Sources is the first. Double click on PolicyDW.rds. Now modify the connection string to the server that you created the database on. It should be localhost, but if you made it a named instance make sure it's correct.
The next step is to tell BIDS where to deploy (Install) this report. In Solutions Explorer, Right Click on PolicyReports (All the way at the top), and select Properties.
You will only need to modify the URL row here.
Once that is done, we now need to deploy the project to the Reporting Server.
Click on Build -> Deploy PolicyReports
If all goes well you will have the project deployed to your report server. Since we did not run the scripts yet, there will be no data to report on though.
FINAL STEP:
The final few steps are going to be testing the script, and then installing that script as a SQL Agent Job to run every day, week, or month.
To test run this script you need to be in Power Shell. The easiest way to get there is to go into MSSMS and right click on the server (Central Management Server), and then click Start PowerShell. You will get a black dos looking prompt, but it will be PowerShell.
Past in the code from above;
SL "<Path to PS1 File>"
.\EPM_EnterpriseEvaluation_3.0.0.ps1 -ConfigurationGroup "<Central Management Folder>" -PolicyCategoryFilter "Microsoft Best Practices: Maintenance" -EvalMode "Check"
You should see a window like this appear, and the script will be running.
If you want to check and see if it worked, you can either open the Reports you deployed or just check the table.
SELECT * FROM Policy.PolicyHistoryDetail
Again I just want to point out that this is just one way to do this. The Polices and PowerShell script will do all of the "Magic", all you need to do is create the Policies and the Reporting mechanism you want.
Eric, @SQLPilot
Tweet