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
- Log onto the SQL server hosting the existing Orchestrator Database
- Create a folder called Support on the C Drive
- From Command Prompt Run Sqlcmd -Q “BACKUP SERVICE MASTER KEY TO FILE =’C:BACKUPMASTER_KEY.BAK’ ENCRYPTION BY PASSWORD = ‘password'”
- Backup Orchestrator Database and copy to new SQL server and also copy the backup folder created earlier
- On the new SQL server run Sqlcmd -Q “RESTORE SERVICE MASTER KEY FROM FILE = ‘C:BACKUPMASTER_KEY.BAK’ DECRYPTION BY PASSWORD = ‘password'”
- Restore the Orchestrator database
- Now Log onto the Orchestrator server and open “Data Store Configuration”
- In the server box specify the name of your new SQL server
- Select Windows Auth
- Select Next
- In Data Store Pan select use an existing database
- Choose Orchestrator
- Click Finish
- 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”
- Open IIS Manager
- Expand Sites
- Expand Microsoft System Center 2012 Orchestrator Web Service
- Click on Orchestrator2012
- On Features view select connection string
- Double click OrchestratorContext
- 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
- Click ok
- 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”
- 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!