8.6. Best practice considerations: policy-based management

As an enterprise DBA who struggles with best practice implementation on a daily basis, I'm excited about the potential of policy-based management:

• Policies (and their evaluation history) are stored in the MSDB database. You should back up this database on a regular basis, or at least each time a policy definition changes.

• Implement proactive DBA checks as automated policies where possible. As well as saving time that can be spent on other tasks such as performance baselining, creating policies enables you to configure new servers faster and more reliably than with manual methods.

• Create central management servers to maximize the power of policy-based management. Where classes of servers exist, such as production, test, and development, use individual central management server groups to check groups of servers in a single action.

• If using the On Change – Prevent mode, ensure the policies are tested in a load-testing environment for their potential performance impact. If a measurable impact is detected, implement policies as On Schedule.

• Consider creating alerts for policy violations. All policy failures are logged to the SQL Server error log with an error code of 34050 through 34053.

• If you use the ExecuteSQL() function to create custom policy conditions, ensure the permissions of the ##MS_PolicyTsqlExecutionLogin## account are set to the minimum required, particularly if you're using the MSDB PolicyAdministratorRole.

Additional information on the best practices covered in this chapter can be found online at http://www.sqlCrunch.com/policy.

Throughout this chapter, we've touched on a number of database properties such as AutoClose and AutoShrink. In the next chapter, we'll expand on these properties further when we look at the topic of data management.