Introduction

In my previous article I’ve written about using SQL Server 2008 Policy -Based Management (PBM) and how to use it to monitor Free Space of data file(s) . When you evaluate the policy, it will evaluate each data file(s) on each database on the instance and the results can be viewed using the syspolicy_policy_execution_history and syspolicy_policy_execution_history_details system views in msdb. In this article I’m going to walk you through how to evaluate policies on multiple instances (including down-level instances SQL 2000 and 2005) using the EPM (Enterprise Policy Management Framework) through PowerShell.

What EPM exactly does?

The Enterprise Policy Management Framework automates and extends the SQL Server 2008 Policy-Based Management feature to down-level instances of SQL Server. Because down-level versions such as SQL Server 2000 and SQL Server 2005 do not have the built-in SQL Server 2008 policy engine, policies must be evaluated periodically or on demand using Windows PowerShell command-line scripts. These PowerShell scripts evaluate each instance of SQL Server 2000 or SQL Server 2005 against policies that are defined and stored on an instance of SQL Server 2008. The results of these evaluations are stored in a management data warehouse that resides on a SQL Server 2008 instance that is defined as a Central Management Server. Read more at http://msdn.microsoft.com/en-us/library/dd542632.aspx

To implement this solution at least you need one SQL Server 2008 instance with SP1 Cumulative Update 3. The SQL Server 2008 instance is required for the following purposes.

1. To create and manage policies
2. To act as a Central Management server
3. To execute the PowerShell script
4. To store all policy history
5. To render and deliver all policy history reports (I’ve not used in this article)

The Central Management Server plays two roles here 1 is to store and manage all logical servers and 2 is to store and evaluate policies through PowerShell script. In CMS you can create server groups to administer easily and evaluate policies manually against each server groups.

To Get the Solution

The condition on the policy is to evaluate all datafiles and if any data file(s) falls below 10% of free space. The policy will be evaluated on each database on all the instances registered in the CMS.

Set up CMS: On MS SQL 2008, create your server group under CMS and register the servers which needs to be evaluated your policies (see below picture). I’ve registered all the servers including down-level versions under Ops_Prod group on CMS.

Enterprise Policy Management Script

I’ve used the Microsoft Enterprise Policy Management Framework script to get this solution but not the entire script; I’ve used bits and pieces and tweaked them in some places.

Policy Evaluation Results Tables

To store policy evaluation results, you need the PolicyHistory and PolicyHistoryDetail tables and they can be created on any databases, I’ve used msdb database on the same SQL 2008 CMS instance. The script to create PolicyHistory table is below:

       CREATETABLE [dbo].[PolicyHistory](
             [PolicyHistoryID] [int] IDENTITY NOTNULL ,
             [EvaluatedServer] [nvarchar](50) NULL,
             [EvaluationDateTime] [datetime] NULL,
             [EvaluatedPolicy] [nvarchar](128) NULL,
             [EvaluationResults] [xml] NOT NULL,
             CONSTRAINT PK_PolicyHistory PRIMARY KEYCLUSTERED
            (PolicyHistoryID)
      ) ON [PRIMARY]

The script to create PolicyHistoryDetail table is here:

        CREATETABLE [dbo].[PolicyHistoryDetail](
             [PolicyHistoryDetailID] [int] IDENTITY NOT NULL,
             [PolicyHistoryID] [int] NULL,
             [EvaluatedServer] [nvarchar](128) NULL,
             [EvaluationDateTime] [datetime] NULL,
             [MonthYear] [nvarchar](14)NULL,
             [EvaluatedPolicy] [nvarchar](128) NULL,
             [policy_id] [int] NULL,
             [CategoryName] [nvarchar](128)NULL,
             [EvaluatedObject] [nvarchar](256) NULL,
             [PolicyResult] [nvarchar](5)NOT NULL,
             [ExceptionMessage] [nvarchar](max)NULL,
             [ResultDetail] [xml] NULL,
             [PolicyHistorySource] [nvarchar](50) NOTNULL,
             CONSTRAINT PK_PolicyHistoryDetail PRIMARY KEYCLUSTERED
                  ([PolicyHistoryDetailID])
      ) ON [PRIMARY]

The following Powershell script is to evaluate policies on multiple Instances (including SQL 2000 and 2005). This script can be configured in a SQL Agent job and schedule it as per your requirement or save it in a PowerShell file and run manually when required.[S1]

The script does the following 4 important tasks.

1. Connect to the CMS and fetch the policy details
2. Connect to the CMS and fetch the registered servers from the msdb.dbo.sysmanagement_shared_registered_servers the system views.
3. Evaluate the policy on the target servers one by one using while loop.
4. Write the results on the PBMOut.xml file first and then insert them in the msdb.dbo.policyhistory table. The reason for using xml file is the down level sql versions doesn’t have system tables to hold the result sets, so you’ve to use xml file to write all the results then move them into SQL using sqlcmd.

The code is shown here:

# Connect the CMS and fetch the policy details

$ManagementServer="SQLDBOps"

# Connection to the policy store

$conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$ManagementServer;Trusted_Connection=true");

$PolicyStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);

#Connect the CMS and fetch the registered target servers from the #msdb.dbo.sysmanagement_shared_registered_servers the system views.

# Create a recordset to fetch the target servers to evaluate

$sconn = new-object System.Data.SqlClient.SqlConnection("server=$ManagementServer;Trusted_Connection=true");

#Filtered with a specific group of servers - Here server_group_id=6 filters only Ops_Prod group

$q = "SELECT server_name FROM msdb.dbo.sysmanagement_shared_registered_servers rs

INNER JOIN msdb.dbo.sysmanagement_shared_server_groups sg ON rs.server_group_id=sg.server_group_id

WHERE sg.server_group_id=6;"

$sconn.Open()

$cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn);

$cmd.CommandTimeout = 0;

$dr = $cmd.ExecuteReader();

#Evaluate the policy on the target servers one by one using while loop

while ($dr.Read()) {

$ServerName = $dr.GetValue(0);

# This forloop helps to evaluate all available policies on the target servers

foreach ($Policy in $PolicyStore.Policies)

{

$File = $Policy

#Using the below if clause to execute only “DataFile_FreeSpace_Check” policy

# if you want to evaluate all the policies then comment the below if clause

if ($File -match "DataFile_FreeSpace_Check")

{

#Evaluating policies on the target server and writing the output on the xml file

Invoke-PolicyEvaluation -Policy $Policy -TargetServerName $ServerName -OutputXML > C:\Krishjay\Personal\SQL\Tips\PBM_Job\PBMOut.xml;

#The below cmdlet will tell you current policy evaluating server and policy name

Out-Default -inputObject $ServerName

Out-Default -inputObject $Policy.Name

#Write the results on the PBMOut.xml file first and then insert them in the #msdb.dbo.policyhistory table. The reason for using xml file is the down level sql #versions doesn’t have system tables to hold the result sets, so you’ve to use #xml file to write all the results then move them into SQL using sqlcmd.

#getting policy result content from xml file

$PolicyResult = Get-Content C:\Krishjay\Personal\SQL\Tips\PBM_Job\PBMOut.xml -encoding UTF8;

$PolicyResult = $PolicyResult -replace "'", ""

$PolicyName=$Policy.Name

#Inserting the evaluated results to msdb.dbo.policyhistory table

$q1="INSERT INTO msdb.dbo.PolicyHistory (EvaluatedServer,EvaluationDateTime,EvaluatedPolicy,EvaluationResults) VALUES (N'$ServerName', Getdate(),N'$PolicyName',N'$PolicyResult')"

Invoke-sqlcmd -ServerInstance $ManagementServer -Database msdb -Query $q1

}

}

}

$dr.Close()

$sconn.Close()

Execute the script on PowerShell command window:

To execute the script from a command line, open the SQL Server PowerShell command window and navigate to the folder where you keep the powershell script file (.ps1) and execute it as the below example.

PS C:\> . \Krishjay\Personal\SQL\Tips\PBM_Job\PBM_Upd.ps1

Execute the script on SQL Server Agent Job:

While setting up the job step, make sure the type is PowerShell and then copy/paste the script on the command window and save the job as shown in the image below.

Parsing the xml policy results

The above script will store the evaluated policy results in PolicyHistory table in XML format, and you have to parse them out to get a readable format. The T-SQL script below is used to parse the EvaluationResults xml column in the PolicyHistory table and insert them into PolicyHistoryDetail table. In this script I’ve commented out the syspolicy_policy_categories view because I’ve not used policy category details anywhere in this article.

From the below script we can get almost all the details expect few important data which we all looking for, that is exists in ResultDetail column.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08'AS DMF)
 INSERT INTO dbo.PolicyHistoryDetail (          
             PolicyHistoryID
             , EvaluatedServer
             , EvaluationDateTime
             , EvaluatedPolicy
             , EvaluatedObject
             , PolicyResult
             , ExceptionMessage
             , ResultDetail
             , policy_id
             , CategoryName
             , MonthYear
             , PolicyHistorySource
       )
        SELECT
             PH.PolicyHistoryID
             , PH.EvaluatedServer
             , PH.EvaluationDateTime
             , PH.EvaluatedPolicy
             , Res.Expr.value('(../DMF:TargetQueryExpression)[1]', 'nvarchar(150)')AS EvaluatedObject
             ,(CASE WHEN Res.Expr.value('(../DMF:Result)[1]', 'nvarchar(150)')= 'FALSE'AND Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)')= ''
                      THEN'FAIL'
                      WHEN Res.Expr.value('(../DMF:Result)[1]', 'nvarchar(150)')= 'FALSE'AND Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)')<> ''
                      THEN'ERROR'
                      ELSE'PASS'
                   END)AS PolicyResult
             , Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)')AS ExceptionMessage
            , CAST(Expr.value('(../DMF:ResultDetail)[1]', 'nvarchar(max)')ASXML) AS ResultDetail
             , p.policy_id
             ,'None' AS CategoryName --c.name
             ,datename(month, EvaluationDateTime) + ' '+ datename(year, EvaluationDateTime)  AS MonthYear
             ,'PowerShell EPM Framework'As FrameWork
       FROM dbo.PolicyHistory AS PH
       INNER JOIN msdb.dbo.syspolicy_policies AS p
             ON p.name = PH.EvaluatedPolicy
       --INNER JOIN msdb.dbo.syspolicy_policy_categories AS c
       --    ON p.policy_category_id = c.policy_category_id
       CROSS APPLY EvaluationResults.nodes('
      declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
      //TargetQueryExpression'
       ) AS Res(Expr)
       WHERE NOT EXISTS (SELECT*
             FROM dbo.PolicyHistoryDetail PHD
             WHERE PHD.PolicyHistoryID = PH.PolicyHistoryID);

I’m cracking further down the ResultDetail xml column in the PolicyHistoryDetail table to get the DatabaseName, LogicalFileName, ActualFreeSpace and ExpectedFreeSpace values. This is really required to fullfill the purpose of this whole article.

I’ve filtered the column PolicyResult='FAIL', this is give you only the failed results.

SELECT
       EvaluatedServer,EvaluationDateTime ,EvaluatedPolicy, PolicyResult
       ,Res.Expr.value('Attribute[1]/Name[1]', 'nvarchar(15)') CheckParam
       ,SUBSTRING(EvaluatedObject,convert(int,PATINDEX('%Databases%',EvaluatedObject))+10,((convert(int,PATINDEX('%FileGroups%',EvaluatedObject))-1)-(convert(int,PATINDEX('%Databases%',EvaluatedObject))+10)))AS DatabaseName
       ,SUBSTRING(EvaluatedObject,convert(int,PATINDEX('%Files%',EvaluatedObject))+6,(LEN(EvaluatedObject)-(convert(int,PATINDEX('%Files%',EvaluatedObject))+5)))AS LogicalFilename
       ,Res.Expr.value('Attribute[1]/ResultValue[1]', 'nvarchar(15)')AS ActualFreeSpace
       ,Res.Expr.value('Function[1]/ResultValue[1]', 'nvarchar(15)')AS ExpectedFreeSpace
       FROM PolicyHistoryDetail AS PO
       CROSSAPPLY ResultDetail.nodes('Operator')AS Res(Expr)
       WHERE PolicyResult='FAIL'
      ORDER BY Evaluationdatetime desc

The above query will give you the below result set. From the ResultDetail column in the PolicyHistoryDetail table you can get the Database CheckParam, DBName, LogicalFileName, ActualFreeSpace and ExpectedFreeSpace column values.

From the above script can be used for other policy results as well if you tweak little based on the xml nodes. That can be done easily if you understand the xml values. From this script you can send alert/notification to the DBA team, if any data/log file falls under the specified threshold.

Conclusion

I hope this script will help DBA’s to monitor their instances thru SQL 2008 PBM with PowerShell from a single instance. Also if he applies this logic to all possible monitoring stuffs that can reduce his work to avoid implementing the default monitoring scripts on each instance.

Reference

http://msdn.microsoft.com/en-us/library/dd542632.aspx

http://www.codeplex.com/EPMFramework.

http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/

http://www.powershellpro.com/powershell-tutorial-introduction/