CodePlex.Com – Enterprise Policy Management Framework

In MS SQL Server 2008, Microsoft released the first iteration of its Policy Management frameworks.

Prior to MS SQL Server 2008, to evaluate the compliance of a MS SQL Instance one has to use Best Practices Analyzer Tool.

For Microsoft SQL Server 2000, one uses “Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0”.  The tool is available @
http://www.microsoft.com/downloads/details.aspx?FamilyID=b352eb1f-d3ca-44ee-893e-9e07339c1f22&displaylang=en

For Microsoft SQL Server 2005, one uses “SQL Server 2005 Best Practices Analyzer (August 2008)”.  The tool is available @ http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Though these tools are very good, there are some missing pieces.

These are some areas that have been incorporated into the Policy Management Tool:

  1. Results of running the Analyzer are incorporated into the database repository
  2. New Policies that suits the need of the company can be incorporated

With all the advances incorporated into MS SQL Server v 2008, there are still some missing pieces:

  1. It is not easily down-level compliant, that is it is not easy to target its  Engine against MS SQL Server 2000 / 2005
  2. It does not incorporate Reporting capabilities

These areas have been addressed by a free tool “The Enterprise Policy Management Framework (EPM)”.  This tool is available @ http://www.codeplex.com/EPMFramework.

 

Prerequisite

  1. MS SQL Server Instance
    • Policy Repository – Policies are stored on this instance
    • Central Management Server – Instances to be queried are registered on this server
    • Controller – Will execute the PowerShell script
    • Results (Data) Repository – Results are saved in this instance
  2. File Share
    1. XML File Extracts for each policy and server combination are cached here prior to them being loaded into the Policy Results Database Instance

Installation Steps

Download

Download “Enterprise Policy Management Framework” from the download section of http://epmframework.codeplex.com/

 

Extract

Extract the downloaded zip file

Once extracted you will end up with the directory structure pasted below:

directoryStructure

  1. The first directory is “0 Setup Scripts”.  This directory contains a lone file EPM_Create_Database_Objects.sql.  The file contains all that is required to build the database repository.  To do so:
    • Launch MS SQL Server Management Studio
    • Load EPM_Create_Database_Objects.sql
    • In the imported code, replace SETVAR ServerName “WIN2008” with a reference to you actual server name SETVAR ServerName “DBMONITOR” – In this case our server name is DBMONITOR.
    • In the imported code, replace : :SETVAR ManagementDatabase “MDW” with a reference to our database name name : SETVAR ManagementDatabase “ManagementDataWarehouse” – In this case our database name is ManagementDataWarehouse.
  2. Switch to the “SQLCMD” mode by clicking on the menu items
    • (Query / SQLCMD Mode)

The second directory is “1Down Version Evaluation PS”.  This directory contains a lone file EPM_EnterpriseEvaluation_3.0.0.ps1.  The file contains the PowerScript code that invokes that evaluates each Policy against the registered servers.

There are a few customizable areas:


#replace with the server name where your Central Management Server
#is configured
$CentralManagementServer = "WIN2008"
$CentralManagementServer = "DBMONITOR"

#replace with the database name with your DataWarehouse
$HistoryDatabase = "MDW"
$HistoryDatabase = "MSManagementDataWarehouse"

# Define the location to write the results of the
# policy evaluation.  Delete any files in the directory.
$ResultDir = "e:\Results\"
$ResultDir = "C:\EPMResults\"

As this is a PowerScript file there are a few securiy constraints:

  1. In some corporations, unsigned PowerScript files may be prohibited.  To determine your “effective” current security constraints:From a command shell, run this command:

powershell Get-ExecutionPolicy

You will get a response such as “RemoteSigned”.

If you are using PowerShell 2.0, you can get a list of all defined execution policies.  The command to do is:


powershell Get-ExecutionPolicy -list

getExecutionPolicy

From a command shell, run this command


powershell Set-ExecutionPolicy [RemoteSigned | Unrestricted]

But, if domain policies have been placed to restrict execution to RemoteSigned or AllSigned, you will have to sign the powershell script.

Detailed instructions on how to do so are beyond the scope of this write-up.  Nevertheless, there are plenty of resources on the web for doing so.

References

PowerShell

  1. Scott Hanselman’s ComputerZen.com – Signing PowerShell Scripts
    http://www.hanselman.com/blog/SigningPowerShellScripts.aspx
  2. about_Execution_Policies
    http://technet.microsoft.com/en-us/library/dd347641.aspx

Management Policy Framework

  1. David E. Myers’ Blog ( www.DavidEMyers.com ) – A Canadian SQL Server
    MVP and Microsoft Certified Trainer (MCT)
    SQL Server Policy Management Framework
    http://www.davidemyers.com/sql-server-policy-management-framework/

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s