Something which has come up when upgrading Microsoft Operations Manager 2007 to 2012 is that there is an extra step which isn’t really documented in the Ops Manager upgrade guide. You see when upgrading from Ops Manager 2007 to 2012 you also need to upgrade the SQL Server to SQL Server 2008 R2 as that is required by Ops Manager 2012. As the install of Ops Manager 2007 to probably from 2007 or 2008 it’s probably running on SQL Server 2005 today so that requires that the database be upgraded before the Ops Manager software can be upgraded as one of the prerequisites for running Ops Manager 2012 is that you are running SQL Server 2008 R2.
The problem comes from the fact that when you upgrade SQL Server there is a setting called the compatibility mode which doesn’t get changed by default. The reason for this is that you can continue to use older T-SQL syntax while still upgrading the database engine to the newest version. When the compatibility mode is left at the older level (in this case SQL Server 2005 compatibility mode) newer T-SQL features aren’t available. In the case of Ops Manager going from SQL Server 2005 to SQL Server 2008 R2 the feature in question that is needed is the MERGE statement which wasn’t available in SQL Server 2005.
The annoying thing here is that Microsoft doesn’t test for the compatibility mode when going through the Ops Manager upgrade process so this doesn’t get flagged. This means that you’ll get through the service upgrade and when you get into the second migrating phase, doing the management group updates) the System Center Management Configuration Service will throw Error number 29112 and the entire Ops Manager system will stop working. Why it is throwing this error message is because the Management Configuration Service is attempting to create stored procedures which use the MERGE statement which the SQL Server 2005 compatibility mode doesn’t understand.
Thankfully fixing this is very easy. Log into the SQL Server database engine which you are using to host the Ops Manager databases. In the object explorer within SQL Server Management Studio right click on the OperationsManager and OperationsManagerDW databases and select properties (do one database at a time). On the options tab change the compatibility mode from SQL Server 2005 to SQL Server 2008. Then click OK as shown below (click to enlarge).
If you prefer this change can also be made with a couple of simple ALTER DATABASE statements as shown below.
[sql]ALTER DATABASE [OperationsManager] SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE [OperationsManagerDW] SET COMPATIBILITY_LEVEL = 100[/sql]
Either way once the change is made there is no restart of the database engine required. Just fire up the System Center Management Configuration Service and let it do it’s thing and it’ll complete that step of the upgrade process.
I hope this helps,
Denny
