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

Automating Best Practice Checks with PBM
Microsoft and the SQL Server community have established many best practices concerning SQL
Server and database design, coni guration, and deployment. Typically, a best practice is a deviation
from a standard or default coni guration to improve some aspect of the solution — performance,
reliability, maintainability, and so on. Because SQL Server is an application platform and can be
deployed and used in many different ways, use of these best practices is often hotly debated. As
such, there are may be exceptions to these best practices, whereby a given recommendation may
apply only in some situations, or some additional qualii cations or conditions are required to
determine whether the best practice is applicable.
ORGANIZING POLICIES USING CATEGORIES
The PBM feature has an option to populate metadata for each policy. This includes
a category, a description, and a hyperlink to further information. One particularly
useful i eld is Category, which can be used to separate policies based on their pur-
pose, e.g., best practices, organization standards, or bad coni gurations. The catego-
ries can be used to sort policies during evaluation and improve manageability.
This section provides guidance on implementing policies to automate checks for best practices. It is
not intended to dei ne a best practice, or to provide a full set of health-check policies.
There are several benei ts to dei ning and automating health-check policies — in particular scalabil-
ity and reuse of the solution. You can execute the checks on a schedule, and any deviation or omis-
sion will be highlighted immediately. Additionally, these checks can be scaled across an entire estate
to which new servers or additional environments are added with relatively little effort.
Database — Check Last Backup
This check ensures that a successful full backup occurred in the past 24 hours. The approach used
in this condition could easily be adapted for use with transaction log backups.
First, create a new condition using the name Last Good Backup . In the Facet i eld, select Database
maintenance. Choose the i eld @LastBackupDate and use the operator >=. Next, in the Value i eld,
click the ellipses (. . .) to launch the Advanced Edit dialog. This dialog permits entry of a T-SQL
statement. Enter the following, click OK, and save the condition:
DateAdd('day', -1, GetDate())
Next, create a new policy named Database — Recent Backup , and select the check condition Last
Good Backup that you created earlier. Initially, set the Evaluation Mode to On demand. All other
settings should remain at their default. Click OK to complete creation of the policy.
Database — Data and Log File Auto-Grow Increments
This policy checks the auto-growth increments for data and log i les to ensure they are optimized
given the database size. This particular policy is a good example of how the optimal value for the