Policy-Based Management is indeed one of the greatest administration features in SQL Server 2008. With this feature you can easily manage one or more instances of SQL Server!
The main concept in Policy-Based Management, is that Policy Administrators can set up policies that are evaluated upon certain events within the scope of the monitored SQL Server instance(s). After setting up the policies, based on certain scenarios, the policies are evaluated and specific actions are automatically performed as set up in the active Policies.
To this end, there are four evaluation modes:
- On demand - This mode evaluates the policies when explicitly requested by the user.
- On change: prevent - This mode uses DDL triggers in order to prevent Policy violations.
- On change: log only - This mode allows Policy violations but it logs each violation using event notification. It logs the violations to the SQL Server logs and Windows Application logs.
- On schedule - This mode allows the user to schedule (using a SQL Agent job) when the policies will be evaluated.
Let’s get a first look and feel at the Policy-Based Management set up dialogs in SSMS. In the following screenshot you can see the Policy Management module in the Object Explorer in SSMS:
- When right-clicking on Policies you can create a new Policy.
- When right-clicking on Conditions you can create a new condition and then assign it to an existing or a new Policy.
- The Facets is a collection of SQL Server objects that allow you to set up conditions and policies against their properties. By right-clicking on a Facet you can create a condition or a Policy.
OK! Now let's proceed with an example, showing how we can set up policies based on the abovementioned evaluation modes. For this post, I will be using the "AdventureWorks2008" sample database.
We right-click on Policies and we select New Policy. We are presented with the following dialog where we provide a name for the Policy and then we either select the check condition or we create a new one. In this case we will be creating a new condition:
We click on the OK button and we are returned to the Policy:
Now, let’s set the Policy to only evaluate the “AdventureWorks2008” database by clicking on the “In Every - Database” list item. Here’s the new condition for restricting the Policy’s evaluation on the specific database:
That’s it! We click on OK and the Policy is ready! As this Policy’s Evaluation Mode has been set to “On demand” we cannot enable it. It wouldn’t make any sense after all. We can only use it by right-clicking on it and selecting “Evaluate”. This is the purpose of the On Demand evaluation mode after all! :)
Now, what about adding a new procedure on the “AdventureWorks2008” database having the name “sp_SampleSP”? Let’s do so, re-run the Policy and check the evaluation results!
Here are the results:
Let’s delete now the sample stored procedure we earlier created. We need to comply after all! :)
On change: prevent
If we want to change the Evaluation Mode of the above Policy to “On change: prevent”, we can double-click on the Policy, change it and then enable the Policy:
Similarly, we can change the Evaluation Mode of the Policy to “On change: log only”. This will allow the stored procedure to be created but it will also add a log entry in SQL Server Logs and Windows Application Log reporting the violation of the Policy:
The Policy-Based Management feature in SQL Server 2008 is extremely powerful and allows setting up strict Compliance scenarios ensuring the health and desired structure of the monitored SQL Server instances and the databases that exist within them.
The best way to learn more about Policy-Based Management in SQL Server is to get experimented with it in order to fully realize its potentials.
You can also check out this link for more information on Policy-Based Management in SQL Server.
Until next time!