A DBA’s-Eye View of System Center 2007

SQL Server 2005 needs the support of a number of Windows services to function reliably. Microsoft is updating and rebranding its systems management products, Microsoft Systems Management Server (SMS) and Microsoft Operations Manager (MOM), in its System Center product family. The enhanced capabilities in the new System Center products, particularly Microsoft System Center Operations Manager 2007, which is available now; Microsoft System Center Data Protection Manager (DPM) 2007, which was in beta at publication time; and Microsoft System Center Configuration Manager 2007, also in beta at press time, will support SQL Server 2005 in a number of ways. (Note that Operations Manager 2007 can also monitor SQL Server 2000.) Let's take a tour of these products and see what they offer to help SQL Server DBAs keep a closer watch on their systems. (For a quick look at all the components that comprise the new System Center, see "What's in Microsoft System Center 2007?".)

Operations Manager 2007

Operations Manager 2007 is based around IT service models. For the first time, it's possible to manage an IT service from an end user's perspective (i.e., as a single service) instead of as the separate components (e.g., server, application, disk space, Active Directory—AD) that the service uses. Operations Manager still monitors these components individually but groups all required components for a particular service together, so that, for example, if AD were unavailable, any service that relied on AD would be shown as having a problem.

In most environments, Operations Manager runs an agent on each monitored server or workstation (an agentless operation mode is also available). The agent basically only stores and relays information back to the Operations Manager server. The true power of Operations Manager lies in its management packs, which are product- or feature-specific packs of knowledge that are installed on servers that Operations Manager monitors. The management packs installed on a server depend on the software or features it's running; for example, on a domain controller (DC) running DNS, you'd install the base OS, AD, and DNS management packs. Management packs are available for many Microsoft products; you can find a complete list of Operations Manager management packs here. (You can download the Microsoft SQL Server Management Pack for MOM 2005 here.)

The management pack contains large amounts of information about the product (basically everything about the product that's in the Microsoft Knowledge Base) and its use of the environment and tells the agent performance counters to watch for, registry values (which are important in configuration monitoring), and any other factors that could cause the agent to preemptively alert IT and take steps to avoid potential problems detected by the management pack.

Although Operations Manager provides the standard "x is down, go fix it" reporting, to which it generally will add information about how to fix the problem, the product's ability to warn preemptively is its most useful feature. You can configure a granular level of alerting. For example, you could configure Operations Manager to send alerts related to your SQL Server service to the DBA team and IT administrators. Or, you could set up Operations Manager to initially alert the Help desk, then, if the problem hasn't been resolved after a certain time period, escalate the alerts by paging another group, and so on until the problem is fixed.

The SQL Server Management Pack

The SQL Server Management Pack provides a discovery component that lets Operations Manager examine instances, databases, file groups, files, agent jobs, and SQL Server roles in a SQL Server environment. You can fully customize the aspects you want to monitor and actions to perform. The management pack's event-analysis function monitors all the key aspects of the SQL Server environment, such as clustering, log shipping, backup, SQL Server Agent, and, as mentioned, SQL Server roles (e.g., replication). In addition to providing overall views of the SQL Server system's health, the management pack also provides in-depth views of databases, the database engine, SQL Server Reporting Services (SSRS), SQL Server 2005 Integration Services (SSIS), and other SQL Server components. The main monitoring screen in Figure 1 provides a high-level status view of the computers that Operations Manager is monitoring. In the Computers section, you can add different columns to the components being monitored, such as database functionality. (In this example, I've added the column SQL DB Engine.) You select the columns to add to the main monitoring screen by right-clicking the column heading.

After the SQL Server Management Pack has identified all the attributes and components that need to be watched, the management pack with Operations Manager can start monitoring the environment. The management pack provides three core types of monitoring:

Another feature that will appeal to both SQL Server novice users and experienced DBAs are the new ways to access functions that can aid in running SQL Server. As Figure 2 shows, if, for example, in the Microsoft Management Console (MMC) System Center Operations Manager 2007 snap-in you select SQL DB Engine Tasks in the Actions pane, you'll see actions related to the database—for example, access to SQL Server tools, configuration options, and control of SQL Server services. Not only does Operations Manager let you see what's happening, it gives actions for the relevant component. This applies to any item you select; for example, if you select a computer and not a component, you'll see options to list active sessions, processes, and other information relevant to the computer. As you can see, Operations Manager is very much a management solution and not only a monitoring tool.

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.