How to Migrate an Orchestrator Database

Some time ago Anders wrote a great blog post (http://contoso.se/blog/?p=2423) on how to move your Orchestrator database. However, with the release of System Center 2012 Orchestrator, some of this information has changed. This post is an updated version for the Orchestrator 2012 installations.

  1. Stop all running runbooks. You can find a quick list of running runbooks by executing the query below against your Orchestrator database.

    select P.Name

    from POLICYINSTANCES i

           inner join POLICIES p on i.PolicyID = p.UniqueID

    where status is null

  2. Stop the following services on your management server:
    1. Orchestrator Management Service (omanagement)
    2. Orchestrator Remoting Service (oremoting)
    3. Orchestrator Runbook Server Monitor (omonitor)
  3. Stop the following service on your runbook servers:
    1. Orchestrator Runbook Service (orunbook)
    2. Orchestrator Remoting Service (oremoting)
  4. Since the Orchestrator database uses a Master key, we must move the database along with the key. On your current SQL Server, open SQL Server Management Studio and open a new query window. Execute the two scripts below to create a migration password, and to drop the master key. Be sure to update the script to match your database’s name, and a better password.

    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

  5. Now you can migrate your SQL database to the new SQL server using either the Backup and Restore, or detach and attach method.
  6. Once the database is restored on the new SQL server, you will need to restore the master key, and drop the migration password. Open a new query window in SQL Server Management Studio, and execute the queries below.

    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. On the new SQL server, in SQL Server Management Studio, expand the Orchestrator database then Security > Users. Right click on your management service account and select Script User as > DROP To > New Query Editor Windows. Execute the query that is generated. Do this for the runbook service account also.  
    image_thumb[2]
  8. Navigate to Security, and right click on Logins and choose New Login.
  9. In the Login – New window General page, add the login name for your management service account, and set the Default Database to your Orchestrator database.
    image_thumb[1]
  10. On the User Mapping page, select the check box next to your Orchestrator database, and add the roles listed below
    1. Microsoft.SystemCenter.Orchestrator.Admins
    2. Microsoft.SystemCenter.Orchestrator.Operators
    3. Microsoft.SystemCenter.Orchestrator.Runtime
    4. public
    5. db_owner (only required for the account running the IIS application pool)
  11. Click OK
  12. Repeat steps 7-10 for you Runbook service account
  13. On your Management server, open the Orchestrator Data Store Configuration
  14. Enter your new SQL server and Authentication method and click Next
    image_thumb[3]
  15. Select Use an existing data store, select your Orchestrator database and click Finish. If you receive a warning about the data store already exists click Yes.
    image_thumb[4]
    image_thumb[5]
  16. Repeat steps 12 through 14 on each of your runbook servers.
  17. Restart all services on your management and runbook servers.
  18. Open the Runbook Designer. If everything looks good, continue on to update the Web Service connection.
  19. On the server running your Orchestrator Web Service open an administrative command prompt.
  20. Execute the command below based on your version to decrypt the Web.Config file:

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

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

  21. Open IIS and navigate to the Microsoft System Center 2012 Orchestrator Web Service site
  22. Expand the site and click on Orchestrator2012, then Double click on the Connection Strings feature
    image_thumb[7]
  23. Double click on OrchestratorContext
  24. In the custom box, change the Data Source to the new SQL Server, and click OK.
    image_thumb[8]
  25. Close IIS
  26. Execute the command below, based on your version, to encrypt the Web.Config file:

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

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

  27. Connect to your Orchestrator web console to ensure that it is working.

Note that this post is provided “AS-IS” with no warranties at all. As always I recommend making full backups of your environment prior to making any major changes.

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.