In this post, I will demonstrate the steps for moving the remote Orchestrator database to the local management server. Since SQL 2016 is out there, we can also use as our new SQL server. My Orchestrator version is 1807 and SQL is on remote server with version 2012 SP1.

It is recommended to upgrade the Orchestrator server OS to the latest before your upgrade the SQL.

1. Install SQL Server 2016 on the Orchestrator Management Server with Database Engine Services enabled. Use the same collation as ‘SQL_Latin1_General_CP1_CI_AS’ and add the Orchestrator service account as SQL administrator.
2. Restart the server.
3. Stop all running Monitor Runbooks on the Orchestrator management server.
4. Stop the following services on the management server:
   • Orchestrator Management Service
   • Orchestrator Remoting Service
   • Orchestrator Runbook Server Monitor
   • Orchestrator Runbook Service
5. Back up and restore the Orchestrator database. In order to do that we need to move the Master key along with the database. First run the below queries against your Orchestrator database. Replace he database name and password with yours.

   Use Orchestrator
   go
   alter master key add encryption by password = 'password'
   go

   use Orchestrator
   go
   alter master key drop encryption by service master key
   go

   After running the above queries, back up the Orchestrator database from the  old server and restore it to the new server.

   

6. After restoring the database, we need to restore the Master Key as well. Use the below queries for that. Use the same password that was used in previous step.

   use Orchestrator
   go
   open master key decryption by password = 'password'
   alter master key add encryption by service master key
   go

   use Orchestrator
   go
   alter master key
   drop encryption by password = 'password'
   go

7. Since the Orchestrator database is now restored to the  new server, we need to tell the Orchestrator to use this new database instead of the old one. To do that, open the Data Store Configuration on the Orchestrator management server and point to the new SQL server with the instance name. Use Windows credentials.
8. Stop the old Orchestrator SQL service.
9. Open the Runbook Designer. It should be up and running except the Web Service. Orchestrator Web Service is still pointing to the old database and the database configuration is encrypted in the Web.Config file.
10. In order to update the Web Service, first we need  to decrypt the Web.Config file using the below code running CMD as Admin:

    C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -pdf "connectionStrings" "C:\Program Files (x86)\Microsoft System Center\Orchestrator\Web Service\Orchestrator2012"
    

    After that, open the Connection String and update the Database server name.

    

    

    Encrypt the Web.Config file with the below code:

    C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -pef "connectionStrings" "C:\Program Files (x86)\Microsoft System Center\Orchestrator\Web Service\Orchestrator2012"
    

11. Restart the Orchestrator server.
12. Start all Monitor Runbooks again.