How to Add the SQL PI Extension to a SQL Server Agent (4226824)

How to add the SQL PI Extension to a SQL Server database agent at the 5.9.5.20 or higher version.

The use of a SQL Server based (MSSQL) PI repository requires a cartridge at the 5.9.5.20 or higher release.

To add the PI extension to a SQL Server database agent

1. Login to the Foglight console
2. Select one or more of the SQL Server database agents
3. From the databases dashboard (Global View) click the Settings and then Administration link
   
4. In the Administration panel, select SQL Performance Investigator in the panel.
5. Then choose one or more of the Foglight Agent Managers. Click the No SQL PI repository link
   
6. Review the licensing information and then click the Accept button
   
7. Select the Agent Manager where the SQL PI agents will be created the Apply button
   
8. 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
   
9. 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.
10. Click the Finish button
    
11. Click OK to create the repository
    
12. Select the checkbox for one or more of the SQL Server agents to add the PI extension on, then click the Enable button
    
13. After a few moments, the PI extension should be associated to the SQL Server database agent.
    

1. 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.
2. 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.
3. 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.

1. Disable PI in database agents.
2. Remove PI agents.
3. Add the PI database to an AlwaysOn Availability Group.
4. Re-enable PI on the agents and use the existing database added to the availability group.

1. Choose “existing database” instead of “creating new”.
2. Use the listener address as the database server.

Video review:

The following video details how to add the SQL PI extension for SQL Server agents at the 5.9.5.20 or higher version level using a SQL Server PI repository