It's important to remember that Operations Manager is also a trend-based tool.
It will, of course, tell you about an impending problem or whether a problem
has occurred. However, Operations Manager also tracks historical data, so that
you can see relative performance of your SQL Server environment over
a period of days, weeks, or months, depending on the frequency you've set for
capturing metrics and the amount of database space that you've allocated to
storing historical data.
Data Protection Manager 2007
The upcoming new version of DPM protects systems running Windows 2000 or later
and runs on any Windows Server 2003 or Windows Storage Server 2003 server. Like
its predecessor, DPM 2007 requires AD, SQL Server 2005, and SSRS. DPM 2007 is
targeted primarily at distributed environments. The product works with an agent
running on every server that DPM is protecting. The agent captures byte-level
changes in real time and also once an hour by default (you can change the default
to any value, using 15-minute increments). The agent then sends these byte-level
changes back to the central DPM server, which allows you to configure DPM to
take snapshot views of server data at various points in time (up to 512 shadow
copies in DPM 2007, compared with 63 shadow copies in the earlier version. A
typical setup is to have DPM create three snapshots a day, say at 9:00 a.m.,
noon, and 3:00 p.m.). An end user can even restore a database, for example,
via DPM without administrator intervention.
One of the most significant changes in the new version is DPM's integration
with tape backups. You can now back up initially to disk, then grandfather data
from the disk backup to tape as the data reaches a certain age. Another important
change, especially for SQL Server DBAs, is that DPM provides improved continuous
data protection (CDP) and backup for SQL Server (as well as for Microsoft Exchange
Server 2007 and Exchange 2003 and Windows SharePoint Services 3.0 and 2.0),
compared with the earlier DPM version.
DPM's SQL Server support relies on the SQL Server Volume Shadow Copy Service
(VSS) Writer to capture disk changes. After you install the DPM agent and reboot
SQL Server, you can use the DPM Administrator Console to create a new
protection group, which will display all the available members (i.e., servers)
that could be included in the group. The agents running on the servers pass
information to the DPM console, so that when you expand a server to view its
details, you'll see basic information, such as volumes and shares. There's also
a cool new feature that lets you select a share for snapshotting, for which
DPM will automatically locate the data and set any needed ACLs. The console
also displays application-specific information; for example, on a SQL Server
system, expanding the server one level displays the SQL Server instances running
on the server. Expanding each instance displays the various databases that are
hosted in the instance.
You can set the protection frequency for the selected databases—DPM
can create a snapshot as often as every 15 minutes (up to 512 snapshots total,
as mentioned earlier). Via the VSS Writer, DPM can send only the updated blocks
or fragments of the database to the central DPM server, a backup method that
minimizes overhead on the network and makes restorations faster.
To recover SQL Server data, you use the DPM Administrator Console's Recovery
tab to select either a point-in-time snapshot that's stored in the DPM
server or simply opt to restore the most recent ("latest") version. If you use
the best practice of keeping the database and transactions on separate disks
and you want to restore a SQL Server database after a corruption or loss, opting
to restore the most recent version restores the latest available database snapshot
to its original location in the database, then plays back any missing transactions.
Using this restore option should effectively restore the latest data on the
database with no loss and without involving the SQL Server DBA. Additionally,
as Figure 3 shows, DPM provides
options to recover the database into a new database; recover to actual database
files to a location on disk, which an experienced SQL Server DBA can then use
to perform a recovery; or "restore"—that is, copy a snapshot of a point
in time—to a tape.
Configuration Manager 2007
Configuration Manager is involved in keeping SQL Server systems up to date by
ensuring that approved OS updates and SQL Server patches are applied in a controllable,
reportable fashion. Configuration Manager provides a centralized method for
deploying updates and software, which helps ensure a consistent Windows environment
across the entire enterprise, both in terms of the SQL Server system and the
other services that SQL Server depends on, such as DCs, DNS servers, and application
servers that rely on SQL Server for their data storage.
Configuration Manager also pushes out software and configurations, such as
updated SQL Server clients and configuration, to computers in the enterprise.
Other Configuration Manager capabilities, such as its ability to inventory client
and server hardware and software, can help you determine actions that you might
need to take related to your SQL Server environment and could also help with
troubleshooting, letting you quickly see which aspects of a system's hardware
setup could be causing performance problems.
Configuration Manager lets you establish a "desired configuration"—that
is, a configuration baseline that describes how you want a box to look in terms
of software installed and other configuration parameters. When you use the desired
configuration feature along with Group Policy, Configuration Manager can help
ensure that SQL Server systems adhere to a preferred configuration (e.g., which
users have local access) and help keep the computers in the enterprise correctly
configured for communication with the SQL Server environment. Many production
problems are related to configuration errors, and the desired configuration
feature can avoid this problem.
Adding Value to SQL Server
System Center's value for a SQL Server environment will depend on the size of
your environment and current facilities. To get the most benefit, you need to
understand the products—especially Operations Manager 2007, which provides
a huge amount of information but without proper and educated tuning can quickly
bury a monitoring team in warnings and alerts. DPM 2007 provides a great backup
and protection solution that's tailored to how SQL Server actually works. And
Configuration Manager can help you ensure that your SQL Server systems are updated
consistently and conform to a desired configuration standard.