Zurück
-
Titel
How to Add the SQL PI Extension to a SQL Server Agent -
Beschreibung
How to add the SQL PI Extension to a SQL Server database agent
-
Lösung
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.
Notes:- Choose “existing database” instead of “creating new”.
- 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 using a SQL Server PI repository
