locked
How to move SCE 2010 databases to different remote SQL Server? RRS feed

  • Question

  • Is there a documented procedure for moving the SCE 2010 databases from one remote SQL Server to another?

     

    Tuesday, October 26, 2010 2:36 PM

Answers

  • Hi,

    According to the following article, you may need to reinstall SCE server after the database migration:

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

    Here are more information:

    http://social.technet.microsoft.com/Forums/en-US/systemcenter/thread/8e550727-d3e7-4568-a478-bf835d26679b

    Hope it helps.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Vivian Xing Friday, November 5, 2010 9:18 AM
    Wednesday, October 27, 2010 7:33 AM
  • once the databases have been moved, all you "should" have to do is in fact, go into the registry, go to hklm\software\microsoft\microsoft operations manager\3.0\setup and modify the database server name.  

    The last hurdle, I can't verify what the value needs to be.  But supposedly, you need to modify a value within a table for the SystemCenterEssentials database.  the table in question is dbo.MT_ManagementGroup.  The column is SQLServerName_<guid>. 

    I'm not encouraging you to mess with this in any way because you very well could end up reinstalling if this is modified incorrectly.  Let us know how it goes if you attempt to do this.


    Ok, so I changed my name...you can still call me Tom if you like. It's a...jump...to conclusions...mat.
    • Marked as answer by Vivian Xing Friday, November 5, 2010 9:18 AM
    Wednesday, October 27, 2010 4:51 PM

All replies

  • short of doing a backup and restore from one sql server to another, im not sure how else you'd do this.  It probably won't be pretty.


    Ok, so I changed my name...you can still call me Tom if you like. It's a...jump...to conclusions...mat.
    • Proposed as answer by sammcdowell Tuesday, April 26, 2011 1:12 PM
    Tuesday, October 26, 2010 3:28 PM
  • I can handle moving the actual databases.  I just want to know what I need to change in SCE so that it's looking at the new location.   Something tells me it's not just a simple setting in the GUI.

     

    Tuesday, October 26, 2010 8:01 PM
  • Hi,

    According to the following article, you may need to reinstall SCE server after the database migration:

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

    Here are more information:

    http://social.technet.microsoft.com/Forums/en-US/systemcenter/thread/8e550727-d3e7-4568-a478-bf835d26679b

    Hope it helps.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Vivian Xing Friday, November 5, 2010 9:18 AM
    Wednesday, October 27, 2010 7:33 AM
  • No offense, but I find it absolutely ridiculous that moving the data is unsupported.   And we're told to reinstall to a machine and sql instance of the same names.   That's not really moving the data then is it?

    Since it seems this is going to require reinstalls no matter what, I'm going to try my hand at shutting down the services, moving my data, hacking the registry entries that point to the database, and restarting the services.

    Does anyone know if there are configuration files other than the registry that I may have to update?

     

    Thanks,

    John

    Wednesday, October 27, 2010 4:11 PM
  • once the databases have been moved, all you "should" have to do is in fact, go into the registry, go to hklm\software\microsoft\microsoft operations manager\3.0\setup and modify the database server name.  

    The last hurdle, I can't verify what the value needs to be.  But supposedly, you need to modify a value within a table for the SystemCenterEssentials database.  the table in question is dbo.MT_ManagementGroup.  The column is SQLServerName_<guid>. 

    I'm not encouraging you to mess with this in any way because you very well could end up reinstalling if this is modified incorrectly.  Let us know how it goes if you attempt to do this.


    Ok, so I changed my name...you can still call me Tom if you like. It's a...jump...to conclusions...mat.
    • Marked as answer by Vivian Xing Friday, November 5, 2010 9:18 AM
    Wednesday, October 27, 2010 4:51 PM
  • Will do.   Thanks for the info.

     

    John

    Wednesday, October 27, 2010 4:59 PM
  • just confirm something i assumed, you obviously have to change that registry setting on your sce server. 

    The table row value needs to reflect the db server name, but i'm not really seeing a field that reflects this currently.  

    One more thing, another registry entry will need to be corrected.  hklm\software\microsoft\update services\server\setup   sqlservername

    If you get lost, the procedure for moving the scom2007 databases is similar, but i can't confirm they're identical.

    good luck!


    Ok, so I changed my name...you can still call me Tom if you like. It's a...jump...to conclusions...mat.
    Wednesday, October 27, 2010 5:10 PM
  • As of right now here's my plan:

    1. Shutdown SCE services
    2. Backup databases
    3. Detach databases
    4. Move database files to new SQL server
    5. Attach databases
    6. Update SCE configuration
    • Registry:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup\DatabaseServer
    • Registry:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup\SqlServerName
    • SQL table:  SystemCenterEssentials.dbo.MT_ManagementGroup.SQLServerName_GUID
    1. Restart SCE services
    2. Verify 

    Now I'm wondering though if there's a table in the SUSDB database that may need updating as well.

    Wednesday, October 27, 2010 9:22 PM
  • very well could be.  You'll also need to change within sce the link for your reports server.  Certainly a daunting task, but it's probably do-able, just not recommended.

    Ok, so I changed my name...you can still call me Tom if you like. It's a...jump...to conclusions...mat.
    Wednesday, October 27, 2010 9:27 PM
  • I'll update this thread as I go.

    Found two more SQL table columns to be updated:

    • SQL table:  SystemCenterEssentials.dbo.MT_Server_0.SqlServerInstance_GUID
    • SQL table:  SystemCenterEssentials.dbo.MT_Server_1.SQLServer_GUID


    Wednesday, October 27, 2010 10:26 PM
  • theoretically, the sqlserverinstance wouldn't need to be the same unless you change the name of the instance on the new server, otherwise, yeah, that'd probably need to change.  Nothing against your abilities or technical aptitude, but I give this about a 70% chance of working.  Again, nothing against you, in fact, if I were doing it I'd probably give it a 30% chance. =)  My only reason is that as you're digging further into this, you're finding more things that may or may not need to be changed.  The probability of finding all values in all tables to be changed is pretty slim, not to mention getting the new value correct.  But that DOES bring us back to your original point....it certainly would be nice to have a tool to migrate the databases from one server to another.  Keep us posted!
    Ok, so I changed my name...you can still call me Tom if you like. It's a...jump...to conclusions...mat.
    Thursday, October 28, 2010 1:09 PM
  • I would agree about the SqlServerInstance column except that the value in there now does include the server name as well.

    I know this is a long shot to work 100%, but I figure if all else fails I do the reinstall like I would've had to do anyway.   And if by chance it does work it may help out others looking to do the same thing.

    I'll keep ya posted.

     

    Thursday, October 28, 2010 2:04 PM
  • Hi,

    I am writing to check if there is any update.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, November 3, 2010 2:39 AM
  • No update quite yet.   I'm waiting on additional memory to arrive for the SQL Server I'm moving the dbs to.
    Monday, November 8, 2010 3:02 PM
  • Just throwing a comment out there. I was just in a position where I needed to move an SCE2010 database from one server to another and I can confirm that this procedure worked for me:

    1. Shutdown SCE services
    2. Backup databases
    3. Detach databases
    4. Move database files to new SQL server
    5. Attach databases 6.Update SCE configuration
    • Registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup\DatabaseServer
    • Registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup\SqlServerName
    • SQL table: SystemCenterEssentials.dbo.MT_ManagementGroup.SQLServerName_GUID
    • SQL: create user account/login for the SCE database on the new server.

    **
    Creating the user account/login was not done initially and we could not start the management console for SCE with the following errors:
    - Event ID 26322 in the Operations Manager event log, login failed
    - Event ID 26319 in the Operations Manager event log, Sdk Service has not yet initialized. Please retry
    **

    1. Restart SCE services
    2. Verify

    • Edited by anorath Monday, February 28, 2011 5:03 PM Formatting error
    • Proposed as answer by anorath Monday, February 28, 2011 5:03 PM
    Monday, February 28, 2011 5:01 PM