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. 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';
2. Back up the Orchestrator database in Primary replicas by running below query.
Sqlcmd -Q "BACKUP DATABASE Orchestrator TO DISK=N'C:\BACKUP\OrchestratorDB.bak'"
3. Deploy SQL Server in Secondary replicas.
4. 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
5. Restore Orchestrator database in Secondary replicas by running the following SQL query.
Sqlcmd -Q "RESTORE DATABASE [Orchestrator] FROM DISK = N'C:\BACKUP\OrchestratorDB.bak'WITH FILE = 1, NOUNLOAD, STATS = 10"
6. Now run the following query on Primary replicas against Orchestrator database.
Use Orchestrator ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'P@ssw0rd'; GO
- Add the secondary replica in Always on availability group.