This was a new feature added to SQL Server 2008 and it’s just superb. I honestly don’t think it gets the appreciation that it deserves as it seems to be rarely seen or used despite the control it can give you over your instances.
Now I’m not going to claim it’s perfect as there are a few things I would like it to do better, but even so I use it whole heartedly in all SQL Servers I work with as it’s ideal to keep an eye on some key aspects of SQL Server and also to verify settings across multiple instances.
So, what is Policy Management? Well, simply put, you apply a policy to SQL Server and it either enforces it on the spot (live tracking), or reports breaches to you based on a schedule you provide.
What can these Policies cover? Almost every setting inside SQL Server. Really. I could list them, but it would take days and truthfully you’ll find them all yourself without much effort as you play with the Policy Management system.
So let’s dive right in… you can find Policy Management in the Management section of SSMS:
Expanding this will reveal 3 subfolders (which I’ll cover in reverse order):
These are the areas of SQL Server upon which you can create a policy. There is a huge number of items in this list and you’ll be hard pushed to find something that isn’t covered in this list.
Double click your chosen Facet and you will be presented with another window showing the list and description of every individual setting within the Facet that you can monitor.
The following shows just a few that you can monitor inside “Database”:
Here we create conditions around the previously mentioned Facets. So, for example, if you wanted to check that every database (Facet) is in the FULL recovery model (Facet item) except system databases, here is where you would define that.
The Policy will tie everything together. You create a Policy based on a Condition and use it to tell SQL Server how you would like it enforced.
There are 4 options for the enforcing of a Policy (although not all of these will appear against every Condition as will be explained later).
The options are:
This is as it implies. You can create the policy but it will not be enforced or even checked against your Server until you manually request it to run.
This is also obvious as it implies you simply schedule a job and have the Policy evaluated at that time. This is quite good for a setting that may not be critical but you wish to keep an eye on. Or something that would be heavy to run in On Change mode as opposed to once overnight.
On Change: Log Only
This is the option I find most useful in certain cases. If SQL Server detects a breach in the Policy then it will instantly place an Event in the Event Log. This means that you can use this in conjunction with SQL Server Agent, for example, to give you a nice email reporting system surrounding any changes made.
On Change: Prevent
This is the strongest of the 4 options as this will actively block any breach of Policy. For example, if you have specified that a table must be in the ‘dbo’ schema and someone fails to abide by this, SQL Server will present an error message (which you can customise yourself) and rollback the transaction. An example of this is as follows:
Note that there is ZERO performance overhead in having Policy Management on UNLESS you are using the On Change options as these use triggers inside SQL Server in order to monitor activity and therefore if you are doing anything that requires the policy to be checked then a trigger will fire and you will see overhead. Useful to consider if you are enforcing an object naming convention on a SQL Server that has hundreds of objects created and destroyed every second, for example, as the overhead would be noticeable.
This brings me back to an earlier point... you don't always get all 4 enforcement policies available, and that is because not everything can be monitored by a trigger and not everything can be automatically rolled back. For example, changing the recovery model of a database can be tracked with a trigger, but it cannot be rolled back. Therefore you will have On Change: Log Only available to you, but you will not see On Change: Prevent in the options list.
Also note that once these policies are in place on your server, you can also use Registered Servers to evaluate a policy from one server over numerous other servers at the same time. This means that you don’t necessarily have to have all policies on all servers, and that you can use the results as a comparison between servers that should maybe be configured in the same manner.
Likewise this can be useful in order to set up a new server or after a rebuild. If you have a standard company policy of basic sp_configure settings and you have these in a policy then simply use Registered Servers to run the policy against the new server and request that it applies the fixes to bring the server inline. Simple.
Hopefully this will all become clear with an example… I have a few of these in my Policy Management section here