Chapter 8. Policy-based management

The major goal of this book is to list and describe best practices for the administration of SQL Server systems. Knowing best practices is one thing, but ensuring they're implemented, and remain implemented, is an entirely different matter.

Suppose you've accepted a position as the new DBA for a company with thousands of server instances spread across production, test, and development environments, each of which were installed and configured by various DBAs and developers with different preferences and knowledge of best practices. If you're asked to perform an audit of all servers for compliance with best practices, how will you go about it? If you're handy with scripting technologies such as SQL Server Management Objects (SMOs) and PowerShell, that will certainly help, but other than that, you're facing a significant and time-consuming challenge. When you finally complete the exercise, how can you be confident that none of the servers have changed configuration since you started inspecting them? Like painting a bridge, by the time you finished you'd be due to start all over again!

Recent versions of SQL Server ship with good out-of-the-box settings and self-managing features, and together with well-documented and well-managed policies, ensuring enterprise-wide consistency in SQL Server configuration is somewhat easier, but it's still a challenge.

SQL Server 2008 introduces a new feature called policy-based management, and it's arguably the single most significant new feature for the DBA. In this chapter, we'll discuss some of the challenges facing DBAs in enterprise environments and how policy-based management can be used in assisting with such challenges. We'll also take a look at combining the power of policy-based management with central management servers and PowerShell.