With the ever-increasing complexity and volume of applications in a typical corporate environment, it is critical that administrators have a way to manage and monitor their business-critical applications in a consistent and standard way. Microsoft System Center Operations Manager is part of Microsoft System Center, which is a suite of products that allows you to capture and aggregate knowledge and information about your IT environment. System Center consists of the following core products:

SCOM 2007 addresses the end-to-end monitoring challenge by providing a comprehensive organizational view of the health of the enterprise IT environment. SCOM is the best manager of Windows-based systems available, and each of the Microsoft Server products, such as SQL Server 2008, provides a plug-in known as a management pack that allows SCOM to provide management and monitoring centrally.

Figure 16-1 shows the System Center Operations Manager Operations Console, which is used to monitor and manage your environment. Notice that the Overview section provides you with tasks, information, and a high-level state of the systems under management.

Figure 16.1. System Center Operations Manager—Monitoring node

The SQL Server management pack for SCOM provides end-to-end management for the SQL Server platform, including servers back to SQL 2000. One of the great features of the SCOM is that it allows you to monitor and manage your entire database environment, including the OS, from one console. The intent of this environment is not meant to replace the features of SQL Server Management Studio; however, as mentioned earlier, it does provide you with a mechanism to collect SQL server events and alerts and then perform management activities on those events and alerts.

The SQL Server SCOM management pack is a culmination of the best practices and design principles from the SQL Server product team. The service pack contains a predefined set of events, alerts, best practices, knowledge, and actions to be performed within SCOM to provide for true enterprise-caliber SQL Server management.

Installing the SQL Server management pack is largely straightforward. However, you should be aware of one kink. The management pack uses a SQL Server feature called SQL Data Management Objects (SQL-DMO) that is deprecated in SQL Server 2008. To use SCOM to manage a SQL Server 2008 database, you must install SQL-DMO into each SQL Server 2008 database you want to manage from the Microsoft SQL Server 2005 backward-compatibility components available within the SQL Server 2008 Feature Pack. At the time of this writing, the most recent SQL Server 2008 feature pack is dated October 2008. The URL for this feature pack is at http://www.microsoft.com/downloads/details.aspx?familyid=228DE03F-3B5A-428A-923F-58A033D316E1. This limitation requiring SQL-DMO should be addressed in the next major release of Systems Center Operations Manager.

After installing SQL-DMO into any SQL Server 2008 database that you plan to manage, you can proceed to install the management pack as follows:

Figure 16.3. Installing management packs in SCOM

Administrators and DBAs use SCOM to see their entire application environment, from the front-end clients to the middleware and back-end SQL databases including the server hardware. This allows them to monitor not only the databases but also all the components of the line-of-business applications that SQL Server supports.

Table 16-2 lists the views that are grouped under the Microsoft SQL Server node in the Monitoring pane of the Operations Console, as shown in Figure 16-4. If you need additional capabilities, you can add your own events, alerts, and rules and perform SQL DB engine tasks from the console.

Figure 16.4. Monitoring database free space with SCOM

So, how do you use the Operations Console to find and resolve an issue? In the following paragraphs, we will show how to do just that. In Figure 16-5, you can see that there is a critical agent alert from SQL Server under Health Monitoring. The alert indicates that the SQL Server Agent has stopped on the server SQL08DEMO.

Figure 16.5. SCOM Agent health and SQL agent state

If you click the alert in the Agent Alerts window, you will see the alert details (located in the bottom-center pane in Figure 16-6). Notice that the details give you a lot of valuable information. You get detailed information about the alert (the source, path, monitor that detected the issue, and when it was created), as well as additional knowledge such as causes for the alert and, most important, a resolution. In this case, the resolution is to attempt to start the SQL Agent service on the SQL08DEMO server.

Figure 16.6. Agent health—the alert details

If you click the "Start SQL Agent service" link as indicated in the resolution, SCOM will open a task window that will allow you to execute the resolution recommendation (in this case, starting SQLSERVERAGENT), as shown in Figure 16-7.

Figure 16.7. Starting SQL Server Agent from SCOM

You can modify any parameters here and then click Run. You will see the result in the Task Status window, as shown in Figure 16-8. In this case, you can see the successful completion and detailed output from the execution of the task.

Figure 16.8. Run Task execution status

We've covered only a small portion of the features available in SCOM with the SQL Server management pack. Refer to Books Online for more detailed information. You'll find a wealth of it—enough to fill a small book.

We have talked a lot about SCOM in this chapter since it plays a key role in providing a centralized administration and management environment. There is a parallel utility that is part of the core products in the System Center suite called System Center Data Protection Manager (DPM). DPM extends the basic data protection capabilities in SQL Server by guarding databases with more granular control over recovery time and recovery points (the last time you took a backup).

The recovery time is a function of the time it takes to recover the transactions that have been lost since the last recovery point. Under normal circumstances, the frequencies of standard backups you perform with SQL Server 2008 are limited by the speed of the backup system and the volume of data being backed up. This prevents you from creating frequent recovery points and thus minimizing your recovery time. DPM provides much more granular protection by combining SQL Server's transaction log architecture with DPM's block-level synchronization.

After the initial setup, DPM can provide synchronization as often as every 15 minutes. Online snapshots represent differential backups that leverage transaction log replication and block-level synchronization in conjunction with the SQL Server VSS Writer as part of the DPM backup technology. DPM can maintain up to 512 snapshots or differential backups. See Figure 16-9 for the architecture of a DPM solution.

Figure 16.9. Data Protection Manager architecture

Another way that DPM extends the protection solution for SQL Server is by allowing you to mix disk and tape as recovery media. Recovery with DPM is provided at the database level, and an entire protected server can be restored with DPM if you capture system state. The main features of DPM for SQL Server are as follows:

To illustrate the benefit of DPM, we will show how to set up a protection group, run a status report on the group, and finally recover a database.

Setting Up a Protection Group

To use DPM, you begin by organizing one or more databases into a protection group. You then define goals on a group-by-group basis. This is much more efficient than managing each database as a separate entity.

For example, the following steps show how to create a protection group:

1. Open the DPM Administrator Console (Start

   

2. The Create Protection Group window will open. Click Next to continue beyond the welcome page. Select the group members from the "Available members" list. In this case, we selected the Engineering database, as shown in Figure 16-10. Click Next.

   

   Figure 16.10. Selecting members for a protection group

3. Next, give your protection group a name, and select a protection method. In this case, we selected Disk, as shown in Figure 16-11. Click Next.

   

   Figure 16.11. Selecting the data protection method

4. Select your short-term protection goals. In this case, we selected a retention range of 30 days and a synchronization frequency of every 15 minutes. The express full backup will be performed at 8 p.m. every day, as shown in Figure 16-12. Click Next.

   

   Figure 16.12. Selecting short-term goals

5. In the next step, DPM will show you the recommended disk allocation. You can change the disk space allocated in DPM if you want by clicking the Modify button. In this example, just click Next.

6. To start the protection process, an initial replica of the data must be copied to the DPM server. In this step, you select the method you want to use. In this example, select Automatically and Now. Then click Next.

7. At this point, you need to review your selections prior to executing the creation of the protection group. If they meet your expectations, click Create Group.

8. This is the final step in the Create Group process, and it might take a little while for the replica to be copied to the DPM server. Once the group has been created successfully, you will see a result, as shown in Figure 16-13.

Figure 16.13. Results of creating a Data Protection Manager group

Executing DPM Reports

Data Protection Manager comes with a series of reports that will help you understand various aspects of your protection environment. See Figure 16-14 for the categories of reports.

Figure 16.14. Data Protection Manager reports

If you execute the Status report, you will see the result in Figure 16-15, which shows you the status of all your recovery points. In this example, we have not been running very long and have only two recovery points.

Figure 16.15. Data Protection Manager Status report

Recovering a Databases

Recovering a database in DPM is a straightforward process. The Recovery tab in the Administrator Console lists the protected data. In the case of restoring a SQL Server database, navigate down the tree structure to locate the database that you want to restore. Figure 16-16 shows an example of a recovery selection.

Figure 16.16. Reviewing recovery selection for a database restore

The process of recovering protected SQL Server data with DPM provides several choices:

• Recover a database to its original location: Recovers the SQL data directly where it was originally hosted by allowing DPM to restore the data directly.

• Recover a database to an alternate location on the same server: Provides a database administrator with the ability to recover a database to a different location. This is particularly useful for ad hoc recoveries of data without affecting the production environment.

• Recover database files to an alternate server: Delivers the database files to a different server running the DPM agent.

• Recover database files to tape: Packages the database files to offline tape media.

DPM allows you to extend the backup and recovery capabilities of SQL Server 2008 by leveraging a combination of transaction log replication and block-level synchronization via SQL Server Volume Shadow Service (VSS) Writer to guarantee your databases are backed up with integrity. DPM provides continuous data protection, which allows you to easily recover SQL Server 2008 databases to their original or any other location quickly and efficiently.

In this chapter, we introduced some very important topics related to using SQL Server 2008 in the enterprise. We covered how each of those topics relate to SQL Server 2008 and gave you some practical examples and recommendations as to which tools and techniques you can use to successfully deploy and manage SQL Server 2008 in your enterprise.

Our coverage in this chapter has been brief and shallow by necessity. Systems Center, auditing, and compliance are subjects on which entire books have been written.

We hope we've give you a good taste of what is available to help you when faced with larger numbers of databases to manage, but you should read more before charging forward too far with any of the tools we've discussed. Table 16-3 lists a number of additional resources where you can find more detail on the topics introduced in this chapter.