In this article I will describe on high level what is SQL Server Policy Based Management that was introduced in SQL Server 2008 (I believe).
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples/links we used should work in SQL Server 2008 and R2
- SQL Server 2005
- This Policy Based Management feature is not available in SQL Server 2005
Who is it for?
- People who would like to learn what is policy based management used for
- Those who are preparing for Microsoft certifications 70-462 Administering SQL Server 2012
- Why do I need policy based management?
- How does policy based management work
- What are the elements of a policy
- Policy Example using Login Facet and Language Facet Property
Why do I need policy based management?
The common challenge in projects that use SQL Server are:
- How to ensure developers and DBAs follow company standards
- How to ensure a certain configuration is not used that may potentially cause problems
- How to reduce time spend on code review
- How to minimize risks
How does policy based management work
The solution to common challenges is to simply set some rules and automate the process. Easy to say and before SQL Server 2008 there wasn't an easy way to do it but now there is and it is called Policy Based Management.
As the name suggest you set up a policy (and usually quite a few of them) that will used at certain point.
Policies can be created but may not be active so you can just run them ad-hoc or schedule the 'health check'. Some policies can react to certain changes (not all of them) and usually they apply to database level objects. For instance you can run a policy when you create or modify a stored procedure and you have two option: allow to execute and just log policy non-compliance or prevent that will stop the code and give error message that the code does not comply with certain policy.
For more information visit http://technet.microsoft.com/en-us/library/bb510667.aspx
What are the elements of a policy
You need to use a facet which is kind of a collection of properties related to a certain object like login, stored procedure, table etc. An example of Facet is Login with a property like Language or Stored Procedure Facet with property Name.
You also have a condition that you define which is your "rule". Condition will contain a facet property and condition. For instance Login Facet, Language Property = 'English'.
Then you have a Policy which is a name and the condition.
To better understand it let's give you an example.
Policy Example using Login Facet and Language Facet Property
Recently I come across an issue with CAST with datetime and in order to prevent any issues I had to ensure that all SQL Server Login (Logins are on instance level) have default language set to English.
I set up Condition where Login Language (Facet property) = 'English'
Then I set up Policy and evaluate the policy and changed all logins to use 'English' (article coming soon).