Jon Gurgul

Jon Gurgul with SQL Serverand occasionally something else. 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 Manag... ement Studio (SSMS) and navigate to the Management/Policy Management node you can see that there are three sub-folders which are as follows. 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. 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, ...Read More