Beta Man

SQL Server 2008: Administration Simplified

Policy-based management improves database performance.

• By Peter Varhol
• 12/01/2007

The mission-critical and business insight enhancements to SQL Server 2008 will have a huge impact on the ease of administration. SQL Server's administration tools and functionality appear to have made some significant strides in this release.

It's All About the DMF
SQL Server 2008 introduces the Declarative Management Framework (DMF), which is a new policy-based management framework for the SQL Server Database Engine. DMF is a policy-based system for managing one or more instances of SQL Server on a network. It lets you enforce policies for system configuration, employing the SQL Server Management Studio to create policies that manage entities on the server.

The Framework consists of three components: policy management, policy creation and explicit administration. Policy management and creation let you define and apply policies across SQL server instances, making management easier and less error-prone. Explicit administration lets you define a one-time administrative action and apply it across servers, regardless of defined policies.

Perhaps the most interesting thing about the DMF, though, is that it lets you administer and enforce policy of SQL Server installations across one or multiple servers. You can apply one action to multiple SQL Servers simultaneously, rather than having to apply it to individual servers one at a time.

Using the DMF to apply policies is easy. You simply select one or more of the managed target databases and either explicitly check that the targets comply with that policy, or explicitly force the targets to comply with the policy. You can do all of this within the GUI console, and it typically takes a few minutes to define and execute.

Managing System Performance
SQL Server 2008 has a raft of new tools for improving and managing database performance. You have a greater array of metrics and options for collecting performance data, as well as a new centralized repository for storing and accessing that data. Measuring, assessing and planning performance standards will be incrementally easier than in the past, especially when dealing with multiple instances of the database.

SQL Server 2008 also offers improved data compression. I didn't compare the data compression performance to that of SQL Server 2005, so I can't directly say whether or not it was faster or produced smaller compressed files. In general, though, data compression enables data in a database to be stored more effectively and reduces the storage requirements for your data. Data compression also provides significant performance improvements for large I/O-bound workloads, getting data to and from the database more quickly.

Mind the Governor
Lastly, SQL Server lets you meter and control utilization of its resources through a new tool called the Resource Governor. Resource Governor lets you define resource limits and priorities for different workloads. In other words, you determine how much of SQL's resources each job can use. That job will then be limited by your preset resource availability. This lets you execute multiple jobs without having any one job end up starved for resources.

SQL Server 2008 also incorporates a feature called Hot Add CPU. This lets you add CPUs on supported hardware platforms without requiring application downtime -- though, to be honest, I couldn't tell you what systems you'd want you to add a CPU to without powering down. Still, if you can't bring down a database, or don't want to wait until the middle of the night to do so, Hot Add CPU lets you upgrade the database server with almost no effort. If the alternative is expanding and rebuilding the database on a faster system, Hot Add CPU is a welcome time-saver for anyone responsible for managing the database.

As a database administrator, you'll definitely enjoy many of these features, especially those that make your day-to-day life a little easier. Things like Resource Governor and new performance metrics will make squeezing more work out of SQL Server 2008 much easier and less time-consuming than with earlier versions.