How to migrate SQL Server and Oracle database agents from Infobright to a SQL Server based PI repository (4230783)

Beginning with the Foglight for Databases cartridges version 5.9.5.20, the back-end Performance Investigator (PI) repository, Infobright, will no longer be supported and SQL Server should be used as a back-end PI repository. This article contains general guidelines to help you to migrate SQL Server and Oracle database agents to SQL Server-based PI repository from Infobright PI repository.

Minimum Requirements

• Foglight for SQL Server or Foglight for Oracle cartridge version 5.9.5.20 or above
• A SQL Server 2016 (or higher) instance as described in KB 4227004

PLEASE NOTE: Do not upgrade the cartridge to 5.9.5.20 or higher if the environment used for the SQL Server PI repository is not available. The cartridge cannot be rolled back to an earlier release.

Migrating a SQL Server Agent or Oracle Agent

Upgrade the Oracle and SQL Server cartridges to 5.9.5.20 or higher

The upgrade wizard will automatically disable Infobright PI for existing SQL Server and Oracle database agents. 

• Disable the Infobright PI extension for SQL Server agents
• Disable the Infobright PI extension for Oracle agents
• Pause the PI Engine for SSAS agents
• Disable Infobright PI for SQL PI agents

Delete SQL PI Agents

Delete the PI Repository Manager Agent (SPIRepository-xxxxx_xxxxx) and PI Domain Manager Agents (DBSS-SPIRepository-xxxx-xxx, DBO-SPIRepository-xxxx-xxx) that are connecting to the Infobright PI repository through Agent Status dashboard.

Migrate PI data (optional)

1. Initialize the SQL PI schema in the SQL Server-based PI Repository
   1. Select one of the existing agents that was connected to Infobright PI repository, and enable the PI extension.
      This step will create the new repository database in the SQL Server Instance that was already created.
   2. After the SQL PI extension has been enabled successfully on the agent from Step A, disable the PI extension immediately
   3. Deactivate the SQL PI agents (SPIRepository-xxxxx_xxxxx, DBSS-SPIRepository-xxxx-xxx, DBO-SPIRepository-xxxx-xxx) that are connecting to the SQL Server-based PI repository
2. Follow the SQL Server-based PI Repository Migration Tool User Guide  to complete the SQL PI data migration
3. Activate SQL PI agents (SPIRepository-xxxxx_xxxxx, DBSS-SPIRepository-xxxx-xxx, DBO-SPIRepository-xxxx-xxx) that are connecting to the SQL Server-based PI repository

Note: this step can be skipped if you don't want to keep the historical data in the Infobright PI repository.

Re-enable PI with SQL Server-based PI Repository

1. Go Databases dashboard and Select one of the existing agents that was connected to Infobright PI repository
2. Go to Databases | Administration | SQL Performance Investigation
3. Select agents and click the "Enable" button
   • The SQL Server-based PI repository needs to be created and attached first if the DB Agent Manager has not yet been attached to the PI repository. A series of popups will guide the user to create the new repository database in the SQL Server instance if it has not already been created. 
   • If PI data has been migrated, please use the pulldown option to choose the SQL Server-based PI repository created in step "Migrate PI data"
4. Navigate to Administration | Tooling | Script Console, Click the Scripts tab in the bottom panel, Click the Add button, clear the Script Text panel contents and paste in the contents from unpause_sqlpi.groovy and then click the Run button
5. Then remove the contents from the field and replace the Script Text contents with the code from check_for_any_paused_SQLPI.groovy. There should be no database agents listed in the output. If any agents are still listed as paused, please rerun steps #4 and #5. 

The following video details the process to Delete the SPI agents and Re-enabled PI with SQL Server-based PI Repository

Please note: Once a database cartridge is upgraded it cannot be rolled back to a previous version.