Upgrade to SCOM 2012 failed – SQL MPs involved

Hey,

I recently tried to upgrade a customer pre-production environment from SCOM 2007 R2 to SCOM 2012. As usual, I followed my upgrade guide: http://scug.be/christopher/2012/10/23/upgrade-guide-from-scom-2007-to-scom-2012/

But this time, when I started the RMS upgrade part, it failed with the following errors:

The errors

Threw Exception.Type: System.Data.SqlClient.SqlException, Exception Error Code: 0x80131904, Exception.Message: Column names in each table must be unique. Column name ‘Post_InstallPath_1ACC50C3_CE60_075D_18CF_BC3CAC665124’ in table ‘MT_AnalysisServices_Log’ is specified more than once.

Cannot find the object “dbo.MT_AnalysisServices_Log” because it does not exist or you do not have permissions.

Column names in each table must be unique. Column name ‘Post_InstallPath_1ACC50C3_CE60_075D_18CF_BC3CAC665124’ in table ‘MT_AnalysisServices_0_Log’ is specified more than once.

Cannot find the object “dbo.MT_AnalysisServices_0_Log” because it does not exist or you do not have permissions.

Column names in each table must be unique. Column name ‘Post_ServiceName_3F85503C_3D5E_19D8_0466_95AA9BB8AC05’ in table ‘MT_ReportingServices_0_Log’ is specified more than once.

Cannot find the object “dbo.MT_ReportingServices_0_Log” because it does not exist or you do not have permissions.

Column names in each table must be unique. Column name ‘Post_ServiceName_3F85503C_3D5E_19D8_0466_95AA9BB8AC05’ in table ‘MT_ReportingServices_Log’ is specified more than once.

Cannot find the object “dbo.MT_ReportingServices_Log” because it does not exist or you do not have permissions.

This upgrade failure completely crashed the environment as it let the SCOM2007 environment without any RMS. /! There is no automatic rollback process in case of RMS upgrade failure. /! The only choice that I had was to restore the complete environment from the snapshot that I took just before the upgrade proces.

It’s really mandatory for fixing this issue to have a SCOM environment up and running. When done, let’s try to understand what’s happened and find a way to fix it.

The debug

As we could see in the error logs above, there is a problem with the following 2 tables:

  • dbo.MT_AnalysisServices_log
  • dbo.MT_ReportingServices_log

These two tables has been created in the OperationsManager DB by the import of the SQL Management packs. I will not focus on the _log tables, but directly on the “main tables”, dbo.MT_AnalysisServices and dbo.MT_ReportingServices.

dbo.MT_AnalysisServices

Connect to the SQL server and run the following query:

select * FROM dbo.ManagedTypeProperty where managedtypeid = ‘1ACC50C3-CE60-075D-18CF-BC3CAC665124’

The query above will retrieve the properties available for the type AnalysisServices from the ManagedTypeProperty table. Below, the output of the column “ColumnName”:

Edition_1ACC50C3_CE60_075D_18CF_BC3CAC665124

InstallPath_1ACC50C3_CE60_075D_18CF_BC3CAC665124

PerformanceCounterObject_1ACC50C3_CE60_075D_18CF_BC3CAC665124

ServiceName_1ACC50C3_CE60_075D_18CF_BC3CAC665124

ServicePackVersion_1ACC50C3_CE60_075D_18CF_BC3CAC665124

Version_1ACC50C3_CE60_075D_18CF_BC3CAC665124

 

The second SQL query will retrieve the properties (Column name) that are available in the table MT_AnalysisServices:

SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘MT_AnalysisServices’ AND ORDINAL_POSITION > 1 ORDER BY ORDINAL_POSITION ASC

Query result:

Edition_1ACC50C3_CE60_075D_18CF_BC3CAC665124

ServiceName_1ACC50C3_CE60_075D_18CF_BC3CAC665124

ServicePackVersion_1ACC50C3_CE60_075D_18CF_BC3CAC665124

InstanceName_97408C11_D1C8_5BCE_EF78_74F0473964F3

Version_1ACC50C3_CE60_075D_18CF_BC3CAC665124

DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C

InstallPath_1ACC50C3_CE60_075D_18CF_BC3CAC665124

PerformanceCounterObject_1ACC50C3_CE60_075D_18CF_BC3CAC665124

ServiceClusterName_1ACC50C3_CE60_075D_18CF_BC3CAC665124

As you could see, there is some inconsistences between the properties available in the two tables. The properties DisplayName, InstanceName and ServiceClusterName are not available in the ManagedTypeProperty table for the type AnalysisServices.

For the two firsts values, DisplayName and InstanceName, the Microsoft Premier Support told me that was normal. I double checked in another environment and that seems to be true.

Now for the ServiceClusterName properties, there is a real problem, this properties must be available in both tables, so we already find a first problem here.

dbo.MT_ReportingServices

As, we already have one properties missing for the type AnalysisServices, we will now check if we have the same problem for the type ReportingServices.

Connect to the OperationsManager DB and run the following query:

select * FROM dbo.ManagedTypeProperty where managedtypeid = ‘3F85503C-3D5E-19D8-0466-95AA9BB8AC05’

The result of the query:

Edition_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

InstallPath_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

ServiceName_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

ServicePackVersion_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

Version_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

 

The second SQL query will retrieve the properties (Column name) that are available in the table MT_ReportingServices:

SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘MT_ReportingServices’ AND ORDINAL_POSITION > 1 ORDER BY ORDINAL_POSITION ASC

The result:

Edition_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

ServicePackVersion_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

InstanceName_97408C11_D1C8_5BCE_EF78_74F0473964F3

DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C

InstallPath_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

Version_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

ServiceName_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

ServiceClusterName_3F85503C_3D5E_19D8_0466_95AA9BB8AC05

 

We have exactly the same problem here, the property ServiceClusterName is also missing from the table ManagedTypeProperty for the type ReportingServices.

The fix

The ServiceClusterName property is missing from the ManagedTypeProperty for the types ReportingServices and AnalysisServices. We also know that these properties are link to the SQL Management Packs.

I had several calls with the Microsoft Premier Support and finally, they proposed me to Remove and Re-add the SQL management packs by following the steps below:

  1. Ensure to have all the binaries for the SQL MPs that you are currently running.
  2. Export your custom management packs where you stored SQL overrides
  3. Delete the all the SQL management packs
  4. Wait 30 minutes
  5. Import all the SQL MPs
  6. Import the custom SQL MPs

Now, we have to check if this export/re-import process of the SQL MPs fixed the problem or not. For that we have to run the following two queries:

select * FROM dbo.ManagedTypeProperty where managedtypeid = ‘1ACC50C3-CE60-075D-18CF-BC3CAC665124’
select * FROM dbo.ManagedTypeProperty where managedtypeid = ‘3F85503C-3D5E-19D8-0466-95AA9BB8AC05’

The both queries are now returning the ServiceClusterName_3F85503C_3D5E_19D8_0466_95AA9BB8AC05 properties for the type AnalysisServices and ReportingServices. Which is what we’re expecting J

As the export/re-import of the SQL Management packs seems to fix the problem, I started the RMS upgrade to SCOM2012. Hopefully for me, this time, it ran successfully without any problem. So yes, the first time, the SQL Management Packs crashed my upgrade process. FYI, the environment was running the following versions of the SQL MPs:

The conclusion part

I must admit that I still don’t know why the ServiceClusterName_3F85503C_3D5E_19D8_0466_ properties was missing from the ManagedTypeProperty table for the MT_Analysis and MT_ReportingServices tables.

I don’t believe to a DB corruption, as I noticed the same problem, in 2 different environments which were running exactly the same MPs.

Now, what is sure, the export/re-import fixed the problem and allow me to upgrade the environment to SCOM 2012 without any problem. My advice is now to run the following 2 queries:

select * FROM dbo.ManagedTypeProperty where managedtypeid = ‘1ACC50C3-CE60-075D-18CF-BC3CAC665124’
select * FROM dbo.ManagedTypeProperty where managedtypeid = ‘3F85503C-3D5E-19D8-0466-95AA9BB8AC05’

To ensure that the ServiceClusterName property is available for AnalysisServices and ReportingServices before starting an upgrade from SCOM2007R2 to SCOM2012.

Finally, I would like to thanks the Microsoft Premier Support.

Christopher

 

Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Email this to someoneShare on TumblrPin on PinterestDigg thisShare on RedditFlattr the authorBuffer this pageShare on StumbleUpon

About Christopher Keyaert

Christopher Keyaert is a Consultant, focused on helping partners to leverage the System Center and Microsoft Azure cloud platform. He is also a Microsoft Most Valuable Professional (MVP) for Cloud and Data Center Management and a Microsoft Certified Trainer (MCT).
This entry was posted in Operations Manager. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *