Category Archives: #SQL

SCOM: Unable to view overrides in Authoring pane when no scope is selected.

An odd issue I encountered today at one of my customers that has recently completed a migration to SCOM 2012 R2, when trying to open the overrides node with no scope selected I got the following error “An object of class ManagementPackClass with ID <GUID Removed> was not found” and then the view shows 0 overrides until the console is reopened with a /clearcache.

Turns out this error is caused by having an override that references a class that no longer exists, in order to track it down you first have to access the overrides which you can no longer see unless you do the following:

1. Export your overrides to text file using poweshell by running get-scomoverride | out-file c:\SCOMOverrides.txt
2. Search the output text file for the GUID which was in the original error “An object of class ManagementPackClass with ID <GUID Removed> was not found
3. The GUID will appear on a line labeled Context, below that one look for a line labeled Identifier, In my case it looked as follows “Identifier: 1|Windows.Operating.System.Custom.Monitors”
4. Generally this is enough information to identify which management pack needs to be deleted if it is not then continue to step 5
5. In SQL run the following query, remebering to edit it for your Identifier, against your operationsmanager database select * from ManagementPack where ManagementPackSystemName like ‘Windows.Operating.System.Custom.Monitors’
6. Once you have identified which management pack is causing your issue it needs to be deleted, once the new configuration is processed you will be able to see your overrides again.

Solution provided by Mcirosoft on Technet forum

Loading

SCOM: Database Status Report version 3

Last month I blogged about a SCOM Database Status Report that I was working on in order to get a single page overview of the SQL environment for a SCOM implementation, as a starting point to a complete SCOM heath report.

Below are samples of version 3 of the report which now includes additional sections for the OperationsManager database as well as the Data Warehouse. I have also amended some of the colouring to make important aspects easier to identify.

db1 db2 db3

 

I would be interested to hear from anyone who has any comments or suggestions on this report or ideas for future ones.

Loading

SCOM 2012 R2: Now supports SQL Server 2014

This article was posted on the Operations Manager Engineering blog this week detailing the newly included support for SCOM 2012 R2 having SQL Server 2014 Enterprise, Standard (64-bit) as its database.

They note the following issues and workarounds to be aware of:

Issue: Installation to a SQL 2014 cluster using Cluster Shared Volumes (CSV) is blocked by SCOM 2012 setup.
Workaround: Deploy to a physical server and then use the documentation here to move the database to the CSV.

Issue: Upgrade of SQL SRS is blocked due to SCOM 2012 use of Custom Security Extensions.
Workaround: Uninstall SCOM Reporting and run ResetSRS.exe to clear the custom security extensions. Once that is complete, upgrade SQL and install SCOM 2012 reporting again. Can find similar steps in the documentation here.

Loading

SCOM: Database Status Report

I’ve been tinkering with trying to get a single health view for one of my clients SCOM environments.

I started with the SQL instance as this can be intensive to manually check every component.

Here is a sample of what it looks like so far my next step is to add free space information:

DB Status Report

 

Thanks to Dave Pinal for his post giving me a starting point.

Please leave a comment if you have any suggestions as to what else can be added or if you have something similar.

Loading

SCOM: Updated SQL MP version 6.5.4.0

An updated version of the SQL Server Management Pack was released this week, the new version is 6.5.4.0. This updated focuses on minor improvements and fixes.

They are available for download at the following links:

SQL Server 2005, 2008, 2008 R2 – here

Changes in this version:

  • Added Mirroring monitoring scenarios for SQL Server 2012 product
  • SPN monitor now has overridable ‘search scope’ which allows the end user to choose between LDAP and Global Catalog
  • Fixed the error with blocked discovering DBs on Windows 2003
  • Fixed Timeout error in CPU utilization monitoring scenario
  • Monitoring SQL Server Instances on the same server with their own network interfaces and default port is now available
  • SQL Server instances with underscores and other allowed special symbols in names can be monitored
  • Minor fixes.

SQL Server 2014 – here

Changes in this version:

  • Added Mirroring monitoring scenarios for SQL Server 2014 product
  • SPN monitor now has ‘search scope’ parameter which allows the end user to choose between LDAP and Global Catalog
  • Fixed Timeout error in CPU utilization monitoring scenario
  • Monitoring SQL Server Instances on the same server with their own network interfaces and default port is now available
  • SQL Server instances with underscores and other allowed special symbols in names can be monitored
  • Minor fixes.

Loading

SCOM: Updated SQL Managment Packs SSRS and SSAS 6.5.2.0

Microsoft has released a new version of several SQL Management Packs, they are now on version 6.5.2.0 and are available for download at the links below:

SQL Server Reporting Services

SQL Server 2008 Reporting Services – Available here
SQL Server 2012 Reporting Services  – Available here
SQL Server 2014 Reporting Services  – Available here

SQL Server Analysis Services

SQL Server 2012 Analysis Services – Available here
SQL Server 2014 Analysis Services – Available here

This version of the SQL Server Analysis Services management packs contain several new features:

New features

  • New version of Generic Presentation Management Pack has been introduced
  • Summary dashboard for SQL Server Analysis Services 2012 Instances with drill-down to SQL Services Analysis Services 2012 Database dashboard
  • Summary dashboard for SQL Server Analysis Services 2012 Databases
  • Cross-version views
  • CPU utilization monitoring scenario on SSAS Instance level
  • New performance collection rules are introduced:
    • SSAS 2012: Database Disk Free Space (GB)
    • SSAS 2012: Database Drive Space Used By Others (GB)
    • SSAS 2012: Cache Evictions/sec
    • SSAS 2012: Cache Inserts/sec
    • SSAS 2012: Cache KB added/sec
    • SSAS 2012: CPU utilization (%)
    • SSAS 2012: Processing Rows read/sec
    • SSAS 2012: Storage Engine Query Rows sent/sec

Improvements:

  • Icons for various classes are updated
  • Performance counters object names are updated

Fixes:

  • An issue related to the discovery of a non-cluster instance on a cluster virtual node is fixed
  • Some other minor fixes

Loading

SCOM: Performance views still show counters when performance collection rules are disabled

Microsoft recently released KB3002249 which details an issue whereby when performance collection rules are disabled in SCOM, performance views still show counters even after all the data is groomed out.

This effects all versions of SCOM and can make the PerformanceDataAllView difficult to read due to clutter.

“This issue is by design. The Operations Manager grooming processes does not groom the PerformanceSource table.”

The below query can be used to see which performance counters will be deleted for what objects before you run the delete script:

Use OperationsManager select PS.PerformanceSourceInternalId, BME.BaseManagedEntityId, BME.DisplayName, PC.CounterName, PC.ObjectName, PS.TimeAdded, PS.LastModified, PDA.PerformanceSourceInternalId from PerformanceSource PS left join PerformanceDataAllView PDA on PDA.PerformanceSourceInternalID = PS.PerformanceSourceInternalId join PerformanceCounter PC on PC.PerformanceCounterId = PS.PerformanceCounterId join BaseManagedEntity BME on BME.BaseManagedEntityId = PS.BaseManagedEntityId where PDA.PerformanceSourceInternalId IS NULL

 

The following is a small SQL script that will remove the entries from PerformanceDataAllView for which no data is recorded.

Note Stop all the Operations Manager services on all Management Servers before you run the script. Always back up your OperationsManager Database before you run this script.

Use OperationsManager
delete from PerformanceSource where PerformanceSourceInternalId in 
(
select PS.PerformanceSourceInternalId from PerformanceSource PS
left join PerformanceDataAllView PDA on PDA.PerformanceSourceInternalID = PS.PerformanceSourceInternalId
where PDA.PerformanceSourceInternalId IS NULL
)

Loading

SCOM 2012 R2: Different coallation settings now supported.

With the release of SCOM 2012 R2 Microsoft has official amended the supported SQL collation settings. In the past you had to use SQL_Latin1_General_CP1_CI_AS, this was due to the data warehouse being installed with this setting by default regardless of the SQL server collation setting. This was a nice addition which has been due for a long time, having collation mismatches between your OpsDB and DW can cause a whole host of issues which are well documented.

Of course the new settings only apply to fresh installations, when upgrading the previous collation settings will still be used.

Click here for the complete supported configuration.

Here is the full list of supported collations:

SQL_Latin1_General_CP1_CI_AS
Latin1_General_100_CI_AS, // EN, IT, DE, PT-BR, NE, PT-PT
French_CI_AS
French_100_CI_AS
Cyrillic_General_CI_AS
Chinese_PRC_CI_AS
Chinese_Simplified_Pinyin_100_CI_AS, // CN simplified
Chinese_Traditional_Stroke_Count_100_CI_AS, // CN traditional, CN-HK
Japanese_CI_AS; Japanese_XJIS_100_CI_AS
Traditional_Spanish_CI_AS
Modern_Spanish_100_CI_AS
Latin1_General_CI_AS
Cyrillic_General_100_CI_AS, // RU
Korean_100_CI_AS
Czech_100_CI_AS
Hungarian_100_CI_AS
Polish_100_CI_AS
Finnish_Swedish_100_CI_AS.

Loading