SQL Server for Operations Manager 2007

  • Article

Applies To: Operations Manager 2007 R2, Operations Manager 2007 SP1

This section provides information about Microsoft SQL Server as it is used to support Operations Manager 2007 R2. It is not intended to be a guide to planning, installing, configuring, operating, and tuning SQL Server; for that information, see the Microsoft SQL Server 2005 Online Books https://go.microsoft.com/fwlink/?LinkId=146981 or Microsoft SQL Server 2008 Online Books https://go.microsoft.com/fwlink/?LinkId=146980. For minimum hardware configuration, see Operations Manager 2007 Supported Configurations (https://go.microsoft.com/fwlink/?LinkId=86539).

Selecting a SQL Server Version

Microsoft SQL Server comes in five editions: Express, Workgroup, Standard, Enterprise, and Developer. Of these five, you should consider the Standard and Enterprise Editions to support Operations Manager 2007.

  • Express Edition: This edition has a maximum database size of 4 GB and is intended for desktop development with very small datasets. Express Edition makes a suitable replacement for Microsoft Access databases.

    Note

    Using a database for Operations Manager 2007 R2 that has been upgraded from SQL Server 2005 Express Edition is not supported and fails the Prerequisite Viewer checks.

  • Workgroup Edition: This edition is only available for 32-bit operating systems and supports only 3 GB of memory. It is intended for departmental and branch office applications.

  • Developer Edition: This edition is not licensed for production use.

  • Standard Edition: This edition supports 32-bit and 64-bit hardware, up to four multicore processors, memory limited by operating system, unlimited database size and support of failover clustering, log shipping, and database mirroring. It is intended for large datasets and production loads.

  • Enterprise Edition: This edition supports 32-bit and 64-bit hardware, unlimited number of multicore processors, memory limited by operating system, unlimited database size, failover clustering for two or more nodes, log shipping, and database mirroring. It is intended for the largest and most demanding online transaction processing (OLTP) environments, data analysis, and data warehousing systems. When planning for ACS, SQL Server Enterprise Edition is the preferred version.

SQL Server Disk Subsystem Configuration

The Operations Manager 2007 operational database, named OperationsManager by default, is the single chokepoint when it comes to performance. The faster the SQL Server disk subsystem can perform, the better Operations Manager performs. For production systems, consider RAID 0+1.

  • RAID 0: This disk configuration creates stripes across the disks that are dedicated to it, and it delivers high performance but no fault tolerance.

  • RAID 1: This disk configuration creates a redundant copy of the selected disk, also known as disk mirroring. It provides fault tolerance, but the write performance is degraded.

  • RAID 5: This disk configuration uses data striping, but it includes parity information for fault tolerance. Multiple heads provide better performance for read and write operations.

  • RAID 0+1: This disk configuration provides data striping without parity for performance and mirroring for fault tolerance. It is the most expensive of the common disk configurations. This configuration provides better write performance than RAID 5.

Best Practices

  • Do not place database data files in the same drive as the operating system.

  • Place transaction logs and database data files on separate drives.

  • Place the TempDB database on its own drive.

SQL Server Version and Service Packs

Ensure that the database server offering the SQL Database Services, Reporting Services, and Workstation components all have SQL Server 2005 SP1, SP2 or SP3 installed. If you are using SQL Server 2008 Reporting services, you must use a version no earlier than SQL Server 2008 Reporting services SP1.

If you are working with SQL Server 2005 SP1, you must also ensure that hotfixes are installed for each of the SQL Server components to be supported by the server. To install the hotfixes, see the Cumulative Hotfix Package (build 2153) for SQL Server 2005 (https://go.microsoft.com/fwlink/?LinkId=78950).

These hotfixes are already included in SQL Server 2005 SP2.

Note

If you are using SQL Server 2008 R2, see KB2425714.

SQL Server Configuration

The SQL Server instance that is used to support Operations Manager 2007 must use Windows Authentication mode and the SQL collation setting must be configured for SQL_Latin1_General_CP1_CI_AS. No other collation configuration is supported.

The databases that you will be most concerned with in Operations Manager 2007 are:

  • OperationsManager: This database holds the operational data and the configuration settings for its management group.

  • OperationsManagerAC: This database is created when the Audit Collection Service is installed. It is optimized for Windows Security Event Log events.

  • OperationsManagerDW: This database is created when Operations Manager 2007 Reporting service is installed and the Data Warehouse option is selected. It holds the same operational data as the OperationsManager database, but reformats it for long-term storage and optimizes it for report generation.

  • ReportServer: This database is installed when SQL Server Reporting Services is installed and is used to house report definitions.

  • ReportServerTempDB: This temporary database is used during report generation.

To provide the best recovery experience, configure the OperationsManager database to run in the Full Recovery model. You can set this in SQL Server Management Studio, in the properties of a database. Select the Options object on the Database Properties page, and on the right side, select Full in the Recovery model list. Note that by using the Full Recovery model, the OperationsManager database transaction logs will fill very quickly and can become very large. Usually, you will need to back them up several times a day to clear them and preserve free disk space. You have to determine your own frequency of backup based on the growth rate of the transaction log and the amount of free space on the transaction log disk. Full Recovery is required for log shipping and in the need to restore, depending on the type of failure. Full Recovery mode provides up-to-the-last-transaction log backup or moment-of-failure restore capabilities.

By default, the OperationsManager database is configured to use Simple Recovery mode. This results in smaller transaction logs and the preservation of free disk space. However, log shipping is not an option when the database is operating in this mode; in the event of failure, database recovery is limited to the time of the latest successful full backup, and all data logged between the times of the last full backup and the failure is lost.

Best Practices for SQL Server Configuration for OperationsManager Database

Because each environment is different, Microsoft recommends that you thoroughly evaluate all configuration settings as they relate to your environment.

  • Database Growth Configuration: The OperationsManager database is the single bottleneck for all operational data, and its performance has a dramatic effect on the performance of Operations Manager as a whole. Keeping this database relatively small ensures faster operations.

SQL Server Broker Service

Operations Manager Discovery, in addition to other workflows, is dependent on SQL Server 2005 and SQL Server 2008 Service Broker, which must remain enabled for Operations Manager 2007 to be fully functional.