Policy Based Management Templates – TSQL2sDay #70

T-SQL Tuesday (aka #TSQL2sday) is a monthly SQL Server blogger event started back in late 2009 by Adam Machanic (blog | twitter). The first topic was about Date/Time Tricks. Read the original TSQL2sday call-out. Each month a different SQL Server blogger is the host who announces the topics and performs the round-up. A big thanks goes out to Jen McCown (b | t), one of the renowned Midnight DBAs, for hosting T-SQL Tuesday #70, strategies for managing an enterprise.

TSQL2sDay150x150

As one of my strategies for managing an enterprise I’d like to talk about Policy Based Management. Policy based management (PBM) enables you to verify best practices and corporate policies across a large enterprise. You execute policy checks from a central management server and retrieve results for those that violate the policies. PBM is a great way of finding loose threads and keeping your enterprise tight.

For someone just starting out with policy based management, it can be a challenge to brainstorm every best practice or policy that you would like to enforce. That is the reason for this blog post. Microsoft has provided us almost six dozen policies with the installation of SQL Server which can be imported as a great jump-off point with PBM. The unfortunate part is that you would not be able to find these best practice policies by just stumbling around in SQL Server Management Studio.

Importing policy based management templates

MSDN informs of us the location of the PBM templates here. The exact file path will vary for each version.

C:\Program Files\Microsoft SQL Server\130\Tools\Policies\DatabaseEngine\1033

Begin by opening SQL Server Management Studio (SSMS) and navigating to Management > Policy Management.

PBM-workthrough-1

Right-click Policies and select Import Policy…

PBM-workthrough-2

Browse to the path of the policy files, select all of the files, and click open.

PBM-workthrough-3

On the next dialog box, decide whether you want these policies to be imported in the disabled or enabled state and select OK.

PBM-workthrough-4

That’s it! Your policies have been imported. 

We now have 66 imported policies and 89 new conditions.

SELECT COUNT(*) [PolicyCount] FROM msdb.dbo.syspolicy_policies
SELECT COUNT(*) [ConditionCount] FROM msdb.dbo.syspolicy_conditions

PBM-template-counts-2014

These are simply a starting point. I am not recommending that you start modifying your servers to meet these conditions. What I am recommending is that you evaluate each policy and decide whether or not it fits your enterprise. Drill into the Policies folder in SSMS’s object explorer and view the policy properties for a description. Note that there is an address block where you can reference more information on MSDN. You can also look at the full list of policies and their details here.

PBM-desc

When you decide to implement one of the policies, edit it and add a schedule to catch drift from your policies.

PBM-schedules
From here you should be well on your way towards establishing your enterprise’s policies and have an efficient method of enforcing them.

Leave a Reply

%d bloggers like this: