Policy-Based Management

Policy-Based Management is a policy based system for managing one or more instances of SQL Server. This has been around in SQL Server since 2008 but is often overlooked for both its primary function in administration as well as its ability to store baselines.

If you open up SQL Server Management Studio (SSMS) and navigate to the Management/Policy Management node you can see that there are three sub-folders which are as follows.

• Policies
• Facets
• Conditions

A facet is a logical grouping of properties. A condition is a question or check that we apply to our facet. Finally a policy contains a group of facets that have been selected to run checks against.

An example implementation could check that the Server Facet property backupdirectory is equal to ‘c:\backups’. This would then confirm that the default location for backups is the same all instances.

This policy is stored on the instance that we created on, however we could also export this to a file and run in straight from the file-system.

Now the important point to gain from running through the above is that Facets have access to configuration data and it is this that can be used to get a configuration baseline.

Baseline

Right click on the root node of the server, click facets. We can see that there are multiple facets and the values contained represent the current state. These can be exported as policies stored on an instance or as xml files providing a point in time snapshot baseline of the configuration. Also be aware that facets also exist at the database node level.

Automating that export process with PowerShell.

Import-Module –Name SqlServer;

<#
Get-Facets
#>
function Get-Facets {
[CmdletBinding()]
Param(
[Parameter(Position=0,
Mandatory=$true,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[System.Data.SqlClient.SqlConnection]$SqlConnection,

[Parameter(Position=1,
Mandatory=$true,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[string]$Directory,

[Parameter(Position=2,
Mandatory=$false,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[string[]]$DatabaseList
)
begin {

}
process {
$SqlStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SqlConnection);
$PolicyStore = New-Object Microsoft.SQLServer.Management.DMF.PolicyStore($SqlStoreConnection);

$XmlWriterSettings = New-Object System.Xml.XmlWriterSettings;
$XmlWriterSettings.Indent = [System.Xml.Formatting]::Indented;
$XmlWriterSettings.OmitXmlDeclaration = $true; #strip of declaration

#Server Facet
$ServerFacets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets |?{$_.TargetTypes -contains [Microsoft.SqlServer.Management.Smo.Server]};
$DataSource = $SqlConnection.DataSource;
$SfcQueryExpression = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SfcQueryExpression("Server");

If(($ServerFacets.Count -gt 0) -and ($SfcQueryExpression)){
$ServerFacets|%{
$Guid = [Guid]::NewGuid();
$Policy = $_.Name;
$Name = "$($SqlConnection.DataSource.Replace('\','$'))_$($Policy)_$($Guid).xml"
$FullName = $(Join-Path -Path $Directory -ChildPath $Name);
$XmlWriter = [System.Xml.XmlWriter]::Create($FullName,$XmlWriterSettings);

$PolicyStore.CreatePolicyFromFacet($SfcQueryExpression,$($_.Name),$($_.Name),$($_.Name),$XmlWriter);

$XmlWriter.Flush();
$XmlWriter.Close();
$XmlWriter.Dispose();
[Void]$SqlStoreConnection.Disconnect();
$SqlConnection.Close();
}
}

#Database Facet
if($DatabaseList.Count -ge 1){
$DatabaseFacets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets|?{$_.TargetTypes -contains [Microsoft.SqlServer.Management.Smo.Database]};
Foreach($Database in ($DatabaseList | select -uniq))
{
If($Database.Length -gt 0)
{
$SfcQueryExpression = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SfcQueryExpression("Server/Database[@Name='$Database']");
If(($DatabaseFacets.Count -gt 0) -and ($SfcQueryExpression)){
$DatabaseFacets|%{
$Guid = [Guid]::NewGuid();
$Policy = $_.Name;
$Name = "$($SqlConnection.DataSource.Replace('\','$'))_$($Policy)_$($Database)_$($Guid).xml"
$FullName = $(Join-Path -Path $Directory -ChildPath $Name);
$XmlWriter = [System.Xml.XmlWriter]::Create($FullName,$XmlWriterSettings);

$PolicyStore.CreatePolicyFromFacet($SfcQueryExpression,$($_.Name),$($_.Name),$($_.Name),$XmlWriter);

$XmlWriter.Flush();
$XmlWriter.Close();
$XmlWriter.Dispose();
}
}
}
}
}

}
}

This code then be run via a command such as the following. I have chosen to export master database facets as well as the server level facets in the example.

$Server = "$env:COMPUTERNAME";
[System.Data.SqlClient.SqlConnection]$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=$Server;Integrated Security=SSPI;Application Name=Discovery");

$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlConnection;
$DatabaseList = $SmoServer.Databases.Name;
#$DatabaseList = @("master","model","tempdb");
$DatabaseList = @("master");
$Directory = 'C:\temp'

Get-Facets $SqlConnection $Directory $DatabaseList;