Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

Moving the Operations Database–My Experience

Moving the Operations Database–My Experience

  • Comments 11
  • Likes

The time has come to move my Operations Database Server role to a new server in my lab.  Today – this is installed on a single server (named OMDB).  This server is running Windows Server 2008 SP2 x86, and SQL 2008 SP1 DB engine (32bit to match the OS).  This machine is OLD, and only has 2GB of memory, so it is time to move it to a 64bit capable machine with 4GB of RAM. 

The server it will be moving to is running Server 2008 R2 (64bit only) and SQL 2008 SP1 (x64).  Since Operations Manager 2007 R2 does not yet support SQL 2008R2 at the time of this writing – we will stick with the same SQL version.

 

We will be using the OpsMgr doco – from the Administrators Guide:

http://technet.microsoft.com/en-us/library/cc540384.aspx

 

So – I map out my plan, based on the guide from Technet:

  1. Back up the OperationsManager database.
  2. Uninstall the OperationsManager database.
  3. Delete the Operations Manager database.
  4. Restore the OperationsManager database.
  5. Update management servers with the new database server name.
  6. Update the Operations Manager database with the new database server name.
  7. Update the Operations Manager database logins on the new database server. Ensure that for the root management server, the SDK Account and the Action Account are included in the logins and that they have appropriate permissions. If reporting is installed, ensure that the Data Warehouse Action Account has appropriate permissions.
  8. Set ENABLE_BROKER if needed.
  9. Verify that the move is successful by ensuring that the console is displaying valid data.

 

Seems easy enough.  Let’s get started.

 

  • In step 1, I install and configure SQL on the new server.  I verify I have configured this SQL server with my corporate security standards, and I have SA rights on this server.
  • In step 2 – the guide states to back up the OpsDB.  I DISAGREE with this step.  The reason for this is that step 3 tells us to then stop the OpsMgr services after the backup in step 2, and I feel this is a bad idea.  The reason is simply because in between the time of the backup, and the time we stop all the services – the Management servers are STILL WRITING to both databases.  When we restore our backup to the new server, it will be out of synch with the Warehouse database from an alert and state change event perspective, and this can wreak some havoc on alert detail reports and availability reports.  What we SHOULD do is FIRST stop all the core OpsMgr services on the RMS and all MS, and THEN take the backup/restore of the OpsDB.  This ensures our two database stay in synch.  It looks like the guide didn’t take into consideration the existence of a warehouse DB.  Therefore – MY recommendation for step 2 will be to perform the steps in “Step 3” (stop all services), FIRST.
  • After I confirm all services are stopped – I take a full backup of the Ops DB.
  • In step 4, I uninstall the OpsDB component from the old server.  I get an error about failed to run a SQL script.  Ignored.  :-)
  • In step 5, I (gasp) delete the OperationsManager database.  (if you are concerned – you might consider restoring the backup to the new server first – to make sure the backup/restore works before taking this drastic step.
  • In step 6, I restore my backup of OperationsManager to the new server.
  • In step 7, I edit the registry of each RMS\MS server, with my new server\instance name.  (SQLDB1\I01).  I DO NOT do step 7f.  Step 7F would have use start up the RMS and MS services.  This SHOULD NOT be done – as there is further configuration that should be done first, editing the DB for the correct name, and establishing the correct account rights.  I recommend leaving these services stopped until this is completed.  The services will just error out until these later steps are performed.
  • In step 8 – I modify the database table per the guide.
  • In step 9 – I add my SDK account login to SQL and make sure the mappings are correct.
  • In step 10 – I add my Management Server Action Account login and set/verify permissions.
  • In step 11 – I add my Data Warehouse Action Account login and set/verify permissions.  (Hint – this is your Data Warehouse Write Account)  I wish we didn’t have so many different names for the same things.
  • Last in the guide – I set ENABLE BROKER per the instructions.

***  Note – there is a footnote added by a user in the guide to enable CLR.  This is REQUIRED… it is missing from the core guide.

In order to support regular expressions in Operations Manager SQL queries, the development team needed to create CLR functions that use .NET's RegEx library. Operations Manager Setup configures SQL to allow execution of the CLR code. When the customer in this case moved the database from one SQL server to another they lost this setting in the OperationsManager Database.


To resolve this issue run the following query on the OperationsManager database:
sp_configure @configname=clr_enabled, @configvalue=1
GO

Let that command execute successfully then run

RECONFIGURE
GO

This will correct the issue above.

 

  • NOW – we can start up our services on the RMS and MS, and check for error events and validate everything is working.

Mine actually failed.  I forgot to open the SQL program and ports in the Windows Firewall.  I created a rule for the SQLServer.exe program, and another for UDP 1434 (for SQL browser) and all was well.

I started my services and validated everything is working and no bad events showing up in the RMS/MS event logs.

 

*** Note – there is an issue caused by moving the database that needs to be corrected in SQL on the new SQL server.  See:  http://blogs.technet.com/b/kevinholman/archive/2010/10/26/after-moving-your-operationsmanager-database-you-might-find-event-18054-errors-in-the-sql-server-application-log.aspx

Comments
  • IS SQL-DMO required for OpsMGR DB/DW in case one is migrating to SQL Server 2008 SP1?

  • @Meer -

    SQL-DMO is not required as a prerequisite for OpsMgr regardless of SQL version.  It USED to be a requirement for the SQL MP to monitor SQL, but that requirement has been removed with the current SQL MP.

  • hi - instead of backup the three db´s (acs,, opsDB and OpsDBDW) why not detach them, attach then on the new server and rerun permission to the new sql server according to support.microsoft.com/.../918992

  • @Retep -

    That is possible - and would be fine.  It simply depends on what your SQL team prefers.  Some are less familiar with setting permissions like that, which the backup/restore doesn't require.

  • Thanks 4 clearing that off :) -

    Btw do you see any difference of moving DB from one server scenario (RMS+SQL+Reporting in one) and moving where the DB is on its own hw.

  • @Retep -

    No - not really any differences.  Moving the OpsDB steps dont really change whether moving off the RMS, or off a dedicated SQL server.

  • Maybe a strange question, but how can you move the RMS role to new hardware. Have you any experience with that?

  • @shadowman -

    Not strange at all.  Easy peasy - simply install the new hardware as a Management Server - then move the RMS role using the supplied tool.  This is covered in the Admin/Operations guide - under making changes.

  • Where in the database do you make changes when moving to new hardware(if it is even necessary) for SCOM 2012?

  • Kevin

    My opsmgr DB is also on a management server so i don't want to remove it from the scom 2007 infrastructure so why would it say remove System Center Operations Manager 2007 R2?

    4b In the Add or Remove Programs dialog box for Windows Server 2003 or Programs and Features dialog box for Windows Server 2008, select System Center Operations Manager 2007 R2, and then select Remove for Windows Server 2003 or select Uninstall for Windows Server 2008.

  • Hi Kevin,

    Can we move SCOM db hosted on sql 2003 to sql 2008 in SCOM 2007 ?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs