Policy Based Management in SQL Server 2008 – I
Posted by decipherinfosys on November 4, 2008
Among other important features that we have been covering on our blog for the new release of SQL Server, one another good feature is Policy Based Management. This feature helps in setting up policies in order to ensure that standards are enforced across the board. In the past, there have been situations where in inconsistencies have been introduced when there is a team of DBAs managing a lot of instances across many different servers. I have seen inconsistent instance and database settings for the environments for the same application(s), different authentication modes set up, different naming standards etc. and enforcing the policies has been un-manageable in the past.
All this changes with Policy Based Management. The DBAs can now:
1) Define Standard Policies as well as enforce them. One can define the standards for how the instances, the databases and the objects within those databases will be configured. Multiple sets of policies can be created to achieve the goals.
2) One can selectively enforce the policies. The granularity is well defined and if we need to omit a particular object or instance or database from the policy based management, one can easily do so.
3) We can easily automate the checking of these policies and can also automate the enforcement of these policies. So, a DBA can automate enforcing of a policy across instances for say a particular parameter or if he/she does not want to automate it and only wants to be notified of out of compliance policies, they can do that as well.
4) If there are out of compliance policies, then from a single place one can fix all of those scenarios.
With the default installation of SQL Server 2008, some sample policies are also delivered as part of an XML file which you can find in this folder:
X:\Program Files\Microsoft SQL Server\100\Tools\Policies
Where X is the drive letter where you did the installation. Under the above folder, you will see three different folders – one each for Analysis Services, Database Engine and Reporting Services. If you go under Database Engine and then the folder 1033, you will see a lot of XML files which relate to the policies. In my environment, I had 48 such files – I believe that this number would change depending upon whether the new service pack (whenever it is released) will provide more such files.
But the point is that this is a great way not only to learn what Policy Based Management has to offer, it is also a great way to use these as templates and start creating policies in your own environment(s) as well. So, how can we start using them? Go to SSMS (SQL Server Management Studio) and from under Management, open up Policy Based Management, right click on the Policies and then select Import Policy. Below is a screenshot to show this:
One can also choose to categorize these different policies into different sections – there are surface area configuration policies, Windows Event Log policies, Permissions and instance & database settings policies etc. Next thing to do would be to learn the T-SQL code that was used to create these policies. If you are like me, I want to know what goes on behind the scenes and do not rely on the GUI that much. If you are like that, then the best way to learn about these policies is to script them out. Once you have imported them in, the next thing to do would be to right click on it and script it out as shown in the image below:
The script is pretty simple and uses a system stored procedure – you can read more on it in BOL (Books Online):
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N’Database Auto Shrink’, @condition_name=N’Auto Shrink Disabled’, @policy_category=N’Microsoft Best Practices: Performance’, @description=N’Checks that the AUTO_SHRINK option is off for user databases on SQL Server Standard and Enterprise Editions. Frequently shrinking and expanding a database can lead to poor performance because of physical fragmentation. Set the AUTO_SHRINK database option to OFF. If you know that the space that you are reclaiming will not be needed in the future, you can manually shrink the database.’, @help_text=N”, @help_link=N”, @schedule_uid=N’00000000-0000-0000-0000-000000000000′, @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N’Enterprise or Standard Edition’, @object_set=N’Database Auto Shrink_ObjectSet’
In this post, we covered the very basics of policies. Policy Based Management is much more than that and requires an understanding of not only policies but Facets and Conditions which also you can find under the same folder structure in SSMS. In one of our upcoming posts, we will start looking into those aspects as well.