Migrating System Center Orchestrator 2012 Database to a Different SQL Server

Daniel Davies | 27th March 2014 | SQL

Recently we were asked by a customer to switch the SQL server that Orchestrator 2012 uses. So we tried to follow the TechNet article

There is a MS TechNet article on the process to do this http://technet.microsoft.com/en-us/library/hh473578.aspx , we followed this article to the letter and unfortunately ended up with a broken Orchestrator Server which was giving the error “The Runbook Server is suspended because the product license expired.” and various other SQL related errors.

So I thought I’d share how we managed to migrate the Database onto a different SQL Server successfully with no issue J

Migration Steps

  1. Log onto the SQL server hosting the existing Orchestrator Database
  2. Create a folder called Support on the C Drive
  3. From Command Prompt Run Sqlcmd -Q “BACKUP SERVICE MASTER KEY TO FILE =’C:BACKUPMASTER_KEY.BAK’ ENCRYPTION BY PASSWORD = ‘password'”
  4. Backup Orchestrator Database and copy to new SQL server and also copy the backup folder created earlier
  5. On the new SQL server run Sqlcmd -Q “RESTORE SERVICE MASTER KEY FROM FILE = ‘C:BACKUPMASTER_KEY.BAK’ DECRYPTION BY PASSWORD = ‘password'”
  6. Restore the Orchestrator database
  7. Now Log onto the Orchestrator server and open “Data Store Configuration”
  8. In the server box specify the name of your new SQL server
  9. Select Windows Auth
  10. Select Next
  11. In Data Store Pan select use an existing database
  12. Choose Orchestrator
  13. Click Finish
  14. Open Command prompt on Orchestrator server and run C:WindowsMicrosoft.NETFrameworkv4.0.30319aspnet_regiis.exe -pdf “connectionStrings” “C:Program Files (x86)Microsoft System Center 2012OrchestratorWeb ServiceOrchestrator2012”
  15. Open IIS Manager
  16. Expand Sites
  17. Expand Microsoft System Center 2012 Orchestrator Web Service
  18. Click on Orchestrator2012
  19. On Features view select connection string
  20. Double click OrchestratorContext
  21. In the custom box find the string “connection string=”Data Source=NEWSQLSERVER;Initial Catalog=Orchestrator;Integrated Security=True;” and edit the Data Source string to point to your new SQL server , the initial catalog should already be Orchestrator           
  22. Click ok
  23. Go back to command prompt and run C:WindowsMicrosoft.NETFrameworkv4.0.30319aspnet_regiis.exe -pef “connectionStrings” “C:Program Files (x86)Microsoft System Center 2012OrchestratorWeb ServiceOrchestrator2012”
  24. Restart Orchestrator Server

 

After the above steps your database should be migrated and there should be no errors J

 

The main step that MS forgot to add to their migration guide was to backup/restore the service master key as this is used to encrypt/decrypt the DB!

About the author

Daniel Davies