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 @
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:
- Results of running the Analyzer are incorporated into the database repository
- 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:
- It is not easily down-level compliant, that is it is not easy to target its Engine against MS SQL Server 2000 / 2005
- 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.
- 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
- File Share
- XML File Extracts for each policy and server combination are cached here prior to them being loaded into the Policy Results Database Instance
Download “Enterprise Policy Management Framework” from the download section of http://epmframework.codeplex.com/
Extract the downloaded zip file
Once extracted you will end up with the directory structure pasted below:
- 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.
- 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:
- In some corporations, unsigned PowerScript files may be prohibited. To determine your “effective” current security constraints:From a command shell, run this command:
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
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.
- Scott Hanselman’s ComputerZen.com – Signing PowerShell Scripts
Management Policy Framework
- David E. Myers’ Blog ( www.DavidEMyers.com ) – A Canadian SQL Server
MVP and Microsoft Certified Trainer (MCT)
SQL Server Policy Management Framework