Moving the OM12 Operations Database: Don’t Forget The Master Database

In the days of SCOM (R2) – and when dinosaurs roamed the planet - there was one small caveat to reckon with when moving the OperationsManager database to another SQL Server: A certain query had to be run against the Master database of that new SQL Server or otherwise errors like these in the Application event log of the new SQL Server would pop up:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 18054
Description:
Error 7779800008, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

But I already blogged about this issue and also referred to the solution as well (running a query against the Master database of the new SQL Server hosting the Operations Manager database).

Goodbye dinosaurs, HELLO OM12! Time to meet an old friend?
Now in these modern days where we all run OM12, this issue is still happening. So when you move the OperationsManager database of your OM12 environment to a new SQL Server, the Master database needs some adjustments as well. Otherwise similar errors will be logged in the Application event log of that new SQL Server.

BUT… the SQL script as stated before won’t do. Just because the OperationsManager database of OM12 is a different kind of beast and therefore requiring a different SQL script to be run against the Master database.

On itself no big deal. But the current procedure for moving the OM12 OperationsManager database to another SQL Server doesn’t tell you anything about it. Ouch!

Time for another approach
Sometime ago I contacted the product team of OM12 and started a mail thread about it. Soon confirmation was given about requiring to run a SQL query against the Master database. And after some puzzling and searching, the correct piece of SQL script was found, contained in one of the many SQL scripts found on the installation media of OM12. When installing OM12 these scripts are run in order to build your new OM12 environment.

I asked Microsoft whether I was allowed to share this knowledge. On itself no problem, but the code contained on the installation media is Intellectual Property (IP) of Microsoft. And therefore, it can’t be copied and shared openly without getting the attention of some legal people. Same goes for pieces of that same code.

However, I still deem this very important information for any one moving the OperationsManager database of OM12. Therefore I have chosen another approach.

Instead of sharing the piece of code – which can bring me a lot of legal hassle – I’ll tell you where to find the SQL query containing the piece you’ll need. And yes, I’ll tell where that piece starts and ends. So all you have to do is to select it, copy it into a SQL query yourself and fire it against the Master database.

This way I’ll respect the IP of Microsoft (no legal hassle) and still help you all along .

Let’s get started. For best results use Notepad++.

1. Load the OM12 RTM installation media;
2. Browse to ~\SETUP\AMD64;
3. Open the file BUILD_MOM_DB_ADMIN.SQL;
4. The piece of SQL code you’ll need starts with:
   --    MOMv3 messages are 77798xxxx     --
5. In order to keep it nice and shiny, start you copy action the line above, containing
   -----------------------------------------
6. In Notepad++ this is line 6524;
   
7. The piece of SQL query you’ll need ends with this section:
   -- Deployment: 77798-0500 to 77798-0550 –
8. The end of this section needs to be copied as well. In Notepad++ it’s line 6808
   
9. Summarized:
   - Open the BUILD_MOM_DB_ADMIN.SQL in Notepad++;
   - Copy the section starting at line 6524 and ending at line 6808;
   - Paste this section in a new query (an empty Windows Notepad file will do as well);
10. Run this query against the Master database of the new SQL Server hosting the OM12 database and be happy.

And when you want to play it nice and neat give the SQL query a header like this one for instance:

This way the IP of Microsoft is respected, not violated and yet your SQL server won’t log those strange errors any more. A real win-win situation .