Delivering Manageability and Performance - SQL Server 2012 Internals and Troubleshooting - page 447

POLICY-BASED MANAGEMENT

The Policy-Based Management (PBM) feature, introduced in SQL Server 2008, enables DBAs to
enforce standards and automate health-check-type activities across an entire SQL Server environ-
ment. The PBM feature provides a framework for DBAs to enforce organizational standards for
naming conventions, security, and coni guration settings, and to provide regular reports and alerts
on these conditions.

The PBM feature requires an initial investment in terms of understanding the mechanics and imple-
mentation, but the benei ts of the solution can be quickly realized through rapid deployment and
automation across an entire organization. Therefore, the return on investment (ROI) of the initial
investment required to coni gure and implement the platform can be rapid. In addition, many DBAs
carry out morning checks, and automating a lightweight 15-minute morning check could save more
than 65 hours per year! Clearly, the benei ts of automation — including scalability and consistency —
present a strong business case for investing effort in a solution such as PBM.

Overview
Policy-Based Management provides a mechanism for DBAs to manage coni guration and deploy-
ment standards and compliance within the SQL Server environment. Managing compliance reduces
variation within an organization, which in turn reduces the complexity and effort required to sup-
port and maintain the provisioning of benei ts, such as reduced resolution time for issues and efi -
ciencies in terms of the effort expended for such issues.

The types of policy that can be implemented by PBM include database-levels checks, such as ensur-
ing that Auto Close and Auto Shrink are disabled, enforcing object-naming conventions, and ensur-
ing that instance-level coni guration options, such as Max Degree of Parallelism and Max Server
Memory, are correctly coni gured.

Three key aspects of PBM are required to get started:

➤

Facet — Object properties for checks (e.g., database, login, or server). Facets are i xed and
cannot be added or changed.

➤

Condition — Evaluates to true or false , and contains logic to validate a setting or option;
e.g., to coni rm AutoClose is false

➤

Policy — Applies a condition on a target, determines policy mode, such as evaluate or
prevent

In addition, using conditions can be a powerful way to rei ne the targets for policies. This can be
useful in situations where different policies or best practices apply to different servers within an
environment. A good example is the database data and log i le autogrow settings. It's a common
best practice to specify the growth increment based on a i xed size, rather than a percentage, to
avoid disk fragmentation and minimize the synchronous i le grow operation. However, it can be dif-
i cult to build a one-size-i ts-all policy for the optimal i le growth increment, as many organizations
host databases with i les ranging between a couple of megabytes to several terabytes.

To account for these variations, you can use conditions to create policies that ensure best practice
compliance for data and log i le growth, as shown in Table 16-1.