SQL Server Policy-Based Management (PBM) is a powerful feature that allows database administrators to define and enforce policies for their SQL Server instances. In a previous article, we discussed how to use PBM to monitor free space in data files. In this article, we will explore how to evaluate policies on multiple instances, including down-level versions of SQL Server, using the Enterprise Policy Management Framework (EPM) through PowerShell.

The EPM extends the PBM feature to down-level instances of SQL Server, such as SQL Server 2000 and SQL Server 2005, which do not have the built-in SQL Server 2008 policy engine. With EPM, policies can be evaluated periodically or on demand using Windows PowerShell command-line scripts. The results of these evaluations are stored in a management data warehouse on a SQL Server 2008 instance defined as a Central Management Server.

To implement this solution, you will need at least one SQL Server 2008 instance with SP1 Cumulative Update 3. This instance will serve as the Central Management Server and will be responsible for creating and managing policies, executing PowerShell scripts, and storing policy history.

The Central Management Server plays two roles: storing and managing all logical servers, and storing and evaluating policies through PowerShell scripts. In the CMS, you can create server groups to easily administer and evaluate policies manually against each server group.

To get started, you need to set up the CMS by creating a server group and registering the servers that need to be evaluated against your policies. Once the CMS is set up, you can use the following PowerShell script to evaluate policies on multiple instances:

# Connect to the CMS and fetch the policy details
$ManagementServer = "SQLDBOps"
$conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$ManagementServer;Trusted_Connection=true")
$PolicyStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($conn)

# Connect to the CMS and fetch the registered target servers
$sconn = new-object System.Data.SqlClient.SqlConnection("server=$ManagementServer;Trusted_Connection=true")
$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
while ($dr.Read()) {
    $ServerName = $dr.GetValue(0)
    
    # Evaluate all available policies on the target servers
    foreach ($Policy in $PolicyStore.Policies) {
        $File = $Policy
        
        # Evaluate only "DataFile_FreeSpace_Check" policy
        # Comment the below if clause to evaluate all policies
        if ($File -match "DataFile_FreeSpace_Check") {
            # Evaluate policies on the target server and write the output to an XML file
            Invoke-PolicyEvaluation -Policy $Policy -TargetServerName $ServerName -OutputXML > C:\Krishjay\Personal\SQL\Tips\PBM_Job\PBMOut.xml
            
            # Insert the evaluated results into the msdb.dbo.policyhistory table
            $PolicyResult = Get-Content C:\Krishjay\Personal\SQL\Tips\PBM_Job\PBMOut.xml -encoding UTF8
            $PolicyResult = $PolicyResult -replace "'", ""
            $PolicyName = $Policy.Name
            $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()

This script connects to the CMS, fetches the policy details, and retrieves the registered target servers. It then evaluates the policies on each target server and writes the results to an XML file. Finally, it inserts the evaluated results into the msdb.dbo.policyhistory table.

Once the policies have been evaluated and the results are stored in the PolicyHistory table, you can parse the EvaluationResults XML column to get a readable format. The following T-SQL script can be used to parse the EvaluationResults column and insert the parsed results into the PolicyHistoryDetail table:

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)') AS XML) AS ResultDetail,
    p.policy_id,
    'None' AS CategoryName,
    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
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
);

This script parses the EvaluationResults XML column in the PolicyHistory table and inserts the parsed results into the PolicyHistoryDetail table. From the ResultDetail column in the PolicyHistoryDetail table, you can extract important data such as the database name, logical file name, actual free space, and expected free space.

By using these scripts, you can monitor your SQL Server instances through SQL 2008 PBM with PowerShell from a single instance. This approach can help reduce the effort required to implement monitoring scripts on each individual instance.

For more information on SQL Server Policy-Based Management and PowerShell, you can refer to the following resources:

• SQL Server Policy-Based Management
• Enterprise Policy Management Framework
• Reading XML Files using T-SQL
• PowerShell Tutorial

With SQL Server Policy-Based Management and PowerShell, you can easily define and enforce policies across multiple instances, ensuring consistency and compliance in your SQL Server environment.