Policy-Based Management (PBM) was first introduced in SQL Server 2008, to help DBA’s implement and enforce best practices or company policies around your SQL Server environment similar to the way group policy does for Windows system settings.
More detail on these PBM concepts is available at Policy-Based Management Overview.
What can I use PBM against?
SQL Server ships with some best practice policies that you are able to import to be used to evaluate against your SQL Server servers. These policies are grouped as follows:
-
Audit
-
Configuration
-
Maintenance
-
Performance
-
Security
-
Windows Log File
-
Surface Area Configuration
Policy-Based Management is supported to run against SQL Server 2000 through to SQL Server 2012. Not all policies are applicable against all versions of SQL Server. To see which policies are restricted to which versions of SQL Server:
select sp.policy_id,
sp.Name,
spc.name As ‘Server Restriction’,
spp.name As ‘Policy Category’
from [dbo].[syspolicy_policies] sp
Inner Join [dbo].[syspolicy_conditions] spc
on sp.root_condition_id = spc.condition_id
Inner Join [dbo].[syspolicy_policy_categories] spp
on sp.policy_category_id = spp.policy_category_id
where spc.Condition_id in (17,20,52,54,63,65)
order by 4,3
Apart from the best practice policies that we have imported you can create your own policies to meet some company policies like the naming convention of your objects for example. To create your own policy Right Click Policy | New Policy. These can be quite simple or quite complex depending on your requirements.
How to Manage PBM
The policies that you import or create reside in the instance of SQL Server you are connected to. More than likely your environment will have multiple instances of SQL Server and these will more than likely be of various versions. To make the management of your policies across all your environment easier you can use Central Management Servers.
There are 4 evaluation modes that you can set for your policies. These determine how and when your policies are evaluated.
-
-
On Demand
-
On Change : prevent (Automated)
-
On Change : log only (Automated)
-
On schedule (Automated)
-
To achieve the automated policy valuations then you need to have the relevant policies on the specific serverinstance that you are wishing to evaluate. The use of Central Management Servers makes this easier for you to ensure they are all the same. You will need to export the appropriate policies out to xml and then import them into the appropriate serverinstances.
To undertake evaluating policies manually the use of Central Management Servers allows you to expand and appropriate group of servers, Right Click | Evaluate Policies. You will need to choose where the policies are coming from. As you have imported the best practice policies into your CMS you can select this ServerInstance and then select appropriate policies to be evaluated. When you hit the Evaluate button this will sequentially run through the list of servers in your registered CMS group. Be mindful this may take quite some time.
How to report on PBM
As each policy that is being evaluated is located on each specific serverinstance there is no central collection and reporting built into SSMS to provide this capability. There is a free download from codeplex – Enterprise Policy Management Framework which was produced for SQL Server 2008 that utilises powershell scripts to collect the data and produce some management reports on the implemented policies in your SQL Server environment.
Stay tuned for a more in depth look into these in the near future.
Enjoy using PBM in your environment.
Leave a Reply