Introduction

Policy Based Management (PBM) can be used to inform DBAs when an incorrect setting is being used on a SQL instance and in some cases, can restrict changes being made to the SQL instance. Microsoft explain it as follows...

“Policy-Based Management is a policy based system for managing one or more instances of SQL Server. Use is to create conditions that contain condition expressions. Then, create policies that apply the conditions to database target objects. For example, as the database administrator, you may want to ensure that certain servers do not have Database Mail enabled, so you create a condition and a policy that sets that server option.”

Most settings within the database\instance environment can be monitored with PBM. The feature also includes the ability to use TSQL queries with the ExecuteSQL function. For processes outside of the SQL instance, the ExecuteWQL function can be used. Unfortunately, this is where I have run into problems in the past which has made me explore using Powershell as an alternative.

My PBM checks run daily and any failures populate the following tables

• syspolicy_policy_execution_history

• syspolicy_policy_execution_history_details

A stored procedure then queries these tables and sends an email to the DBA team showing which policies failed on each instance.

For this example, I will show how to use Powershell to check the status of SQL Browser and how to report if it fails the policy. The check will be triggered by a SQL Agent job.

Condition and Policy 

The following condition and policy are created.

The condition is deliberately set to check that 1= 0 which will obviously fail!

Powershell 

Two Powershell scripts are created. The first script is used to make the check. It takes in 3 parameters. The Hostname\Instance name the check is being executed on, the Policy name of the check and a hashtable containing any variables needed in the script.

There's a reference to the second Powershell script and the ability to write out the parameter values using the verbose switch. The second Powershell script needs to be in the location referenced. Although I've used C:\temp, this can be a network share.

The script then has the logic to test whether the check passes or fails. In this case, the required status and actual status of the service are retrieved. The required status is a value passed into the script from SQL Agent (which will be shown further down) and the actual status is found using the get-service cmdlet.

The values are compared and either the script finishes if the comparison is equal or the second Powershell script is executed if theres a difference.

The second Powershell script, or Execute-PBM as its called, is executed when the required status is different to the actual status. The script takes the Hostname\Instance name and Policy name as parameters. These values are used with the SQL provider for Powershell to expose and set the location to the PBM folder of the SQLServer drive. The Policy is then invoked.

This will then execute the Policy which is set to fail and populate the relevant DMVs.

SQL Agent Job

The process can be scheduled using SQL Agent.

A job or jobstep can be created to run the script. The jobstep should be of type Operating system (CmdExec).

And the command needs to call the first powershell script, saved as a ps1 script on the file system, with the 3 parameters from the script specified.

The HostnameInstanceName parameter uses a SQL Agent token, which passes in the @@ServerName value. The advantage of using the SQL Agent token is that each job\jobstep doesn't need to be manually updated for each instance. The PolicyName needs to be the same as the policy set up in SSMS. The variables parameter is set up as a hashtable and can accept multiple values as a key\value pair. In this example, the SQLBrowserRequiredStatus is passed into the script with the value of Running. If the script requires further variables then they can be added to the Variables parameter using key = value. Separate each key\value pair with a semi-colon.

Conclusion

Powershell can be used in conjunction with Policy Based Management to extend the scope of checks that can be performed.