How Policy Based Management can help you manage SQL Server

Policy Based Management is over a decade-old mechanism, introduced in SQL Server 2008 to help companies enforce their configuration and best practice. It can help ensure the correct configuration is applied and that it never deviates from the baseline.

A single set of policies can be applied across the entire estate. For this example, let’s implement a trivial policy to make the Database option AUTO SHRINK disabled. Create a new policy in Management -> Policy Management -> Policies:

Policy Based Management SQL Server

Please give it a name and create a condition where @AutoShrink = False

Policy Based Management SQL Server
Policy Based Management SQL Server

There are four ways in which the policy can be evaluated and applied, which we explain below.

On Demand

This option will allow you to evaluate policies manually when required. The drawback is that the desired configuration will not be applied if you forget to run it. The benefit, however, is that you will have more visibility and control over what is being applied, especially when you just implemented policy management on an older server that may not be in good shape. Another benefit is that you can easily apply the configuration in the GUI.

Right-click the policy and click Evaluate:

Policy Based Management SQL Server

The policy will now process all databases and highlight those that have AUTO SHRINK enabled:

Policy Based Management SQL Server

We can quickly correct this by clicking “Apply”. SQL Server will automatically disable AUTO SHRINK on the non-compliant databases:

Policy Based Management SQL Server

And we are compliant again without having to write a single line of code or check every database manually.

Policy Based Management SQL Server

On Schedule

As the name suggests, the On Schedule evaluation will automatically do all of the above on a pre-defined schedule. This option is recommended once your environment is in shape to ensure it does not deviate again.

To see which facets support the “On Schedule” evaluation, you can use the following query:

select * from msdb.dbo.syspolicy_management_facets
where execution_mode & 4 = 4 --schedule

On Change: Prevent

The On Change Prevent evaluation will work as a guard and will prevent specific changes from happening in the first place. For example, we can prevent new server roles from being created by creating the following condition:

And set the evaluation to “On change: prevent.”

If we now try to create a new server role, the command will fail:

This is a great way to prevent vendors, third-party support, and applications from creating unauthorised objects.

To see all facets that support the On Change: Prevent evaluation, you can use the below query:

select * from msdb.dbo.syspolicy_management_facets
where execution_mode & 1 = 1 --on change prevent

On Change: Log

The “On Change” evaluation is similar to the “On Change: Prevent”, with the difference being that it will log non-complaint operations rather than prevent them.

To see all facets that support On Change: Log evaluation, you can use the below query:

select * from msdb.dbo.syspolicy_management_facets
where execution_mode & 2 = 2 --on change log

It gets better with PowerShell

SQL Server Policy Based Management is a fantastic feature that allows companies and DBAs to stay on of SQL Server. And because it is a part of the SQL Server engine, auditors also trust it. However, as always, things aren’t perfect, and there are a few that we don’t like, such as policy deployment and centralised reporting. For that reason, we developed a simple PowerShell script to help you run policies against many servers with easy reporting.

You can get a copy from our GitHub repository, which also includes several handy policies (on top of the default policies that SQL Servers come with)

The script uses the Microsoft-provided cmdlet Invoke-PolicyEvaluation to evaluate policies and writes output as an HTML report:

If you have any questions, please get in touch!

Conclusion

SQL Server Policy Based Management is a powerful feature that can make your life easier and make your auditors happy.

Help us grow, please share this content!

Author

Posted by
Marcin Gminski
September 22, 2022

Let us help you. Get in touch.

Help us grow, please share this content!