Archive | July 2013

SQL Policy Management – Compatibility Level

After implementing Policy Management company-wide I decided to expand the built in set of policies that comes from the Microsoft offering.

Implementing a check against compatibility level differences is a little beyond the normal scripts but still relatively easy once you figure out some of the minor issues.

First:

Create a new condition.

You want to use the facet type of “Database”.

In field you will want to paste:

ExecuteSql(‘Numeric’, ‘
SELECT compatibility_level
FROM sys.databases WHERE [name] = ”model”’)  since model is where the server compatibility level should be configured.

Operator will be “=” and the value will be @CompatibilityLevel.

Name your condition and save.

Next up — create your Policy.

Name it whatever you want and then use your newly created check condition.

Check against targets ‘online user databases’ and evaluate on demand.  Save it and then evaluate against some test servers!

Comments?  Questions?  Drop a message!