How To Manually Create / Migrate Insights DB For Orchestrator PaaS ?

How to manually create / migrate Insights DB for Orchestrator PaaS?

Background:

The Insights DB is tightly coupled to the Orchestrator DB schema. Additionally, Orchestrator publishes data to the Insights DB. As such, it is Orchestrator that does migrations and creates the DB, rather than the Insights component. This ensures the Insights DB is always compatible with the Orchestrator version.

DB Migration / Creation Steps:

  1. If this is a new installation and the Insights DB was completed without first following these steps, the DB should be dropped and recreated as an empty DB. Dropping all the tables should be sufficient as well.
    1. For new installations, the migrator expects an empty DB
    2. If the DB was not empty, that means Insights created its users tables. This is OK, drop them. However, before doing this, make sure to stop the IIS service on the Insights server
    3. Post the creation of the Insights DB, restart the IIS service. Then try logging in again. The users will be lost but can be recreated. Insights will recreate the user tables as needed.
  2. Download the PaaS Orchestrator installation media
  3. Unzip the file
  4. In the folder, unzip the UiPath.Orchestrator.Cli.zip
  5. Go into the folder UiPath.Orchestrator.Cli
  6. In the directory, create a file called UiPath.Orchestrator.dll.config
  7. Add the following contents to the file. The Default connection string needs to be updated with the connection string to the Orchestrator DB.
    • <?xml version="1.0" encoding="utf-8"?>
      <configuration>
      <connectionStrings>
      <add name="Default" providerName="Microsoft.Data.SqlClient" connectionString="<Connection string for Orchestrator DB" />
      </connectionStrings>
      </configuration>
    • i.e.
      1. <?xml version="1.0" encoding="utf-8"?>
        <configuration>
        <connectionStrings>
        <add name="Default" providerName="Microsoft.Data.SqlClient" connectionString="Data Source=sql.uipath.devtest;Initial Catalog=UiPath; UserId=myUsername; Password=myPassword;" />
        </connectionStrings>
        </configuration>
  8. Open a powershell prompt and go to the UiPath.Orchestrator Cli directory.
  9. Run the following command and make sure to specify the Insights connection string
    1. ./UiPath.Orchestrator.Cli.exe database upgrade-database -d Insights -c ".\UiPath.Orchestrator.dll.config" --connection-string "<connection string for Insights DB>"
    2. i.e. ./UiPath.Orchestrator.Cli.exe database upgrade-database -d Insights -c ".\UiPath.Orchestrator.dll.config" --connection-string "Data Source=sql.uipath.devtest;Initial Catalog=UiPath Insights; UserId=myUsername; Password=myPassword;"
  10. Post this step, make sure to update the Orchestrator App Service connection string with the Insights connection string. The connection string name should be "Insights"
  11. Additionally, to enable the Insights module on Orchestrator, the follow key needs to be added in the app service settings:
    • Insights.ModuleEnabled = True