The use of a SQL Server based (MSSQL) PI repository requires a cartridge at the 5.9.5.20 or higher release.
A SQL Server based (MSSQL) PI repository must be available for use prior to replacing the PI repository. Please consult KB article 4227004.
To add the PI extension to an Oracle database agent
- Login to the Foglight console
- Select one or more of the Oracle database agents
- From the databases dashboard (Global View) click the Settings and then Administration link
- In the Administration panel, select SQL Performance Investigator in the panel.
- Then choose one or more of the Foglight Agent Managers. Click the No SQL PI repository link
- Review the licensing information and then click the Accept button
- Select the Agent Manager where the SQL PI agents will be created the Apply button
- Fill in the SQL Server connection details including the Server name (and instance name separated by a backlash if appropriate), the port (this is optional unless the default port of 1433 is not used), the connection type (SQL Server authentication or AD credentials), and the username & password. Then click the Next button
- Choose the radio button to either create a new SQL PI repository or use an existing PI repository.
- Either use the default database name, primary file group, and transaction log location, or optionally enter a new value
- Add a file directory location on the SQL Server instance to store 1m data. This folder must already exist on the server.
- Click the Finish button
- Click OK to create the repository
- Select the checkbox for one or more of the Oracle agents to add the PI extension on, then click the Enable button
- After a few moments, the PI extension should be associated to the Oracle database agent
To use PI repository in AlwaysOn Availability Group (AG):
- Before adding a PI agent, create an Always On Availability Group (AG) and define a listener. This will require adding an existing database that is not the PI repository to the AG. Alternatively, you can choose an existing Availability Group.
- Monitor sql server/Oracle/ssas agent and enable PI. Use the listener address of the Availability group as a database server for the PI repository and accept the default values for the database files location.
- Once PI is enabled, run a full backup for the PI database created in step #2 and add it to the AlwaysOn Availability group created or chosen in step #1.
To add an existing PI repository database to an AlwaysOn Availability Group(AG):
- Disable PI in database agents.
- Remove PI agents.
- Add the PI database to an AlwaysOn Availability Group.
- Re-enable PI on the agents and use the existing database added to the availability group.
Notes:
- Choose “existing database” instead of “creating new”.
- Use the listener address as the database server.
Video:
Please refer to the following video for details on adding the PI extension to an Oracle agent to connect to a SQL Server PI repository.