The use of a SQL Server based (MSSQL) PI repository requires a cartridge at the 22.214.171.124 or higher release.
Please refer to KB 261361 for details on upgrading a SQL Server cartridge.
To add the PI extension to a SQL Server database agent
- Login to the Foglight console
- Select one or more of the SQL Server 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),
- port (this is optional unless the default port of 1433 is not used),
- connection type (SQL Server authentication or AD credentials),
- username & password.
Then click the Next button
Choose the radio button to set the SQL PI Schema:
- Choose Create a New PI Repository Database for SQL Server.
- Use the default value for Database Name, Primary File Group Location, and Transaction Log, or optionally enter a new value.
- Add a file directory location on the SQL Server instance to store 1m data.
Note: This folder must already exist on the server.
- Choose Use an Existing PI repository Database for SQL Server. Choose the Database Name from the drop-down list.
- Click the Finish button
- Click OK to create the repository
- Select the checkbox for one or more of the SQL Server agents to add the PI extension on, then click the Enable button
- After a few moments, the PI extension should be associated to the SQL Server 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.
- Choose “existing database” instead of “creating new”.
- Use the listener address as the database server.
The following video details how to add the SQL PI extension for SQL Server agents at the 126.96.36.199 or higher version level using a SQL Server PI repository