This topic describes how to automatically move Orchestrator database in SQL always configuration. This could be useful when you want to just move to a new SQL Server always configuration.
Before we will start, we need to make sure we should follow the below sequence as it is super critical.
The following steps are required to enable an automatic migration of Orchestrator to a new SQL always on environment:
1. Take a full backup from primary replica from where Orchestrator is working fine.
2. Remove the Orchestrator database from AG.
3. Delete the Orchestrator database from all secondary replicas.
4. Back up SQL Server service master key from Primary replicas. Please change the File path and password in the below query.
BACKUP SERVICE MASTER KEY TO FILE = '\\WIN2K19HOST1\Software\service_master_key_INS2.BAK' ENCRYPTION BY PASSWORD = 'P@ssw0rd';
5. Back up the Orchestrator database in Primary replicas by running below query.
Sqlcmd -Q "BACKUP DATABASE Orchestrator TO DISK=N'C:\BACKUP\OrchestratorDB.bak'"
6. Deploy SQL Server in Secondary replicas.
7. Restore the SQL Server service master key in Secondary replicas. Please change the File path and password in the following SQL query.
RESTORE SERVICE MASTER KEY FROM FILE = '\\WIN2K19HOST1\Software\service_master_key_INS2.BAK' DECRYPTION BY PASSWORD = 'P@ssw0rd' GO
8. Now run the following query on Primary replicas against Orchestrator database.
Use Orchestrator ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'P@ssw0rd'; GO
9. Now create the AG and during this process, it will prompt for a password, you need to use the password from STEP 8 and add all the secondary replica, create the listener.
10. Once done, please run data store configuration on all the Orchestrator server by providing the listener’s name.
Migrate Orchestrator between environments | Microsoft Docs
Modify the Orchestrator database | Microsoft Docs