Policy-Based Administration in SQL Server 2008 (formerly known as Declarative Management Framework)

This was a long awaited blog post dating back last November 2007 when I did a session at the Singapore SQL Server User Group specifically for this topic. One of the most difficult task that DBAs are faced with in managing complex enterprise database environments is ensuring that all elements of the system conform to their company's established security and usage policy. Administrators need to constantly monitor security surface area of the entire database server along with permissions and settings for every database and server objects. DMF (as what is was formerly called in the previous CTPs) is a policy-based system for managing one or more instances of SQL Server 2008. To use the DMF, SQL Server policy administrators use SQL Server Management Studio to create policies that manage entities on the server such as the instance of SQL Server, databases, and other SQL Server objects. DBAs select one or more managed targets and specifically check that targets comply with the policies. Or they explicitly force the targets to comply with the policies. Let's take a look at a typical scenario. In the past, you might have used the SQL Server Best Practice Analyzer (there is also a version for SQL Server 2005) to check whether or not your SQL Server instances comply with Microsoft's best practices. The problem with this is that you need to manually run the tool which generates the report telling you which settings are running at best practice or not. Plus, you need to manually configure those settings not running at best practice. Imagine having to do this over and over depending on the number of SQL Server instances you manage. This is a very laborious task for DBAs working for an enterprise hosting services company like me. With DMF, you can create policies, similar to how you create policies in Active Directory, that are based on company's established security and usage policy, use these policies to either inspect a server and even change a group of server instances all at the same time with just a click of a button. Amazing how a DBA can do these maintenance tasks with ease using SQL Server 2008.

For more information on SQL Server 2008 Policy Based Management, you can check out a virtual hands-on lab at the TechNet Virtual Lab site so you can play around with how it works. The SQL Server Manageability Team also came up with a Level 300 webcast sometime February 2008 which digs deeper on this new feature in SQL Server 2008. Plus, I've written an article for MSSQLTips.com and created a video at BlogCastRepository.com on this as well - more on evaluating policies on multiple instances at the same time.