SQL Server 2008: Administration Simplified
Policy-based management improves database performance.
- By Peter Varhol
The four pillars of SQL Server 2008's enhancements are mission-critical platform,
dynamic development, beyond relational data and pervasive business insight.
The second and third of these pillars are particularly interesting for data
modeling and development. The first and fourth revolve around data maintenance
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
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 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
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
Peter Varhol is the executive editor,
reviews of Redmond magazine and has more than 20 years of experience as a software
developer, software product manager and technology writer. He has graduate degrees
in computer science and mathematics, and has taught both subjects at the university