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.

What is this DCEXEC.EXE running on my server?

If you have been playing around with SQL Server 2008, chances are that you may have already seen this in your Task Manager's running processes. DCEXEC.EXE is the data collector run-time component which manages data collection based on the definitions provided in a collection set and can accept any collection set as input. What is it for? The concept of performance data warehouse is not something new. Almost every system administrators out there would probably have a methodology to capture the performance data of their systems for analysis, forecasting and capacity planning. DBAs are not excluded. But the effort and time to create a solution that does these tasks of collecting data and storing them in a data warehouse, coupled with creating a reporting and analysis solution, is not a joke. Good thing Microsoft included this out-of-the-box feature in SQL Server 2008 which allows DBAs create a performance data warehouse in as few clicks on the wizard as possible. It creates a new database named MDW and the corresponding SQL Server Agent jobs, SSIS packages and SSRS reports which makes monitoring your SQL Server 2008 (I still have to find out whether i can monitor SQL Server 2005 systems as well since this feature is only available in SQL Server 2008) database systems a bit easier. You can use this to have a loko at historical data of your system performance and pin point what might have caused an issue. Let's say end users are complaining that their application is slow, you can identify which queries were running on that specific time frame, and even extract their query execution plans. You have CPU, memory, Disk I/O, network usage and a whole bunch of stuff related to server, query statistics and disk usage. To know more about the new performance monitoring and troubleshooting feature using SQL Server 2008 Management Studio, check out this TechNet webcast