Database Clustering, SQL Server Editions, and Operations Manager 2007

We’ve been asked in the newsgroups:
How does database clustering require enterprise edition? I still don’t understand … You can cluster with standard, if so that’s clustering, correct? Other than having more nodes than two, are there any benefits, as they relate to the OpsMgr database itself that you gain by going to Enterprise Edition over Standard? Does it do re-indexing and stuff that may take it offline while jobs run, etc that warrant paying more for Enterprise?

Okay, here’s how it works:

  • SQL Server Standard Edition supports up to two clustered nodes – and for the OpsMgr databases, those can be configured as active/passive
  • SQL Server Enterprise Edition supports up to 8 clustered nodes

The operating system in either case MUST be Windows Server Enterprise (or Datacenter) Edition.

One advantage of clustering – other than the obvious one for high ability when one node of the cluster goes down, is that it can be used to maintain the functionality of the database in situations such as patch management (this is in addition to maintaining availability when there are temporary outages).

Let’s look at a creative way where you can use more than two clustered nodes. This scenario involves a 4-way Active/Active/Active/Passive configuration using SQL Server Enterprise Edition, where:

  • The first node would host the Operations database in the default cluster instance
  • The second node would host the Data Warehouse database in a second instance
  • The third node would host the ACS database in a third instance
  • The fourth node would be available to provide high availability in case of the failure of a node in the cluster

Assuming the boxes have a lot of memory, this enables you – using a single clustered implementation of SQL Server Enterprise Edition – to host all the databases used by OpsMgr (and ACS).

Now let’s look at some specific advantages of using SQL Enterprise Edition in an ACS installation.

ACS supports the use of SQL Server 2005 Standard Edition and SQL Server 2005 Enterprise Edition. The version used impacts how the system behaves during the daily 2:00 AM database maintenance window while the ACS database is reindexed. During the maintenance window, any database partitions with timestamps outside the data-retention schedule (14 days in the default configuration) are dropped from the database. Keep the following points in mind:

  • If SQL Server 2005 Standard Edition is used, security event insertion halts and events queue up on the collector until maintenance is completed.
    This is because SQL 2005 Standard Edition cannot perform online index operations, whereas the Enterprise Edition can.
  • If SQL Server 2005 Enterprise Edition is used, insertion of processed security events continues during the daily database maintenance, but at only 30%-40% of the regular rate.

SQL 2005 Enterprise Edition is probably mandatory in high-volume ACS environments because it reduces the chance of lost security events from filling the collector queue during the maintenance window.

The Operations database also has various maintenance tasks. This includes Discovery Data Grooming (2 AM), Partitioning and Grooming (12 midnight), Detecting and Fixing object space (every 30 minutes), and Auto Resolving Alerts (4 AM). These are in addition to a daily backup job you should schedule for all databases used by Operations Manager as well as significant system databases such as master and msdb (for more information on backups, see Chapter 12 of System Center Operations Manager 2007 Unleashed). 

The Data Warehouse database also does optimizations, reindexing, and grooming.

SQL Server Enterprise Edition does not appear to be recommended for the Operations and Data Warehouse databases as strongly as Microsoft recommends it for ACS, due to the databases being maintained differently. However, based on your particular environment, you may discover benefits to using Enterprise Edition. Enterprise Edition is better able to handle querying large amounts of data (> 500 GB), which can make it valuable for the data warehouse component. Another example is that while SQL Standard can run on a maximum of 4 CPUs, Enterprise can handle 64 (based on the version of the OS that is running). Enterprise Edition also supports parallel index operations, parallel DBCC operations, table and index partitioning should you want to implement that, online index operations, and online page and file restores.

Microsoft has a scalability and performance comparison between the two versions available at http://www.microsoft.com/sql/editions/enterprise/comparison.mspx.

Advertisements
This entry was posted in Operations Manager 2007. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s