To create a new SQL Server database agent with SQL Performance Investigator monitoring
- Login to the Foglight console
From the databases dashboard (Global View) click the Monitor button, then SQL Server
Fill in the Connection Details with the Server name and instance name (separated by a backslash) or just the server name if the Default SQL Server instance is used. Also add the port (if it is not the default port of 1433) and the Username and Password, as well as if SSL is used. The username and password can either be an Active Directory (AD) account such as DOMAIN\USERNAME or a SQL Server account. Alternatively, a radio button is available to use the AD account used to run the Foglight Agent Manager.
- Configure the Sensitivity Level and agent name if desired.
- Then click the Operating System entry
- Add the OS connection information with the connection type, userid, and password details, a stored login can be used if an agent used the same credentials was previously configured and added to the lockbox. Then click the OK button
- Update the VMWare connection information if desired
- Click the SQL PI checkbox or entry
- Accept the licensing agreement
- Select the Foglight Agent Manager (FglAM) where the SQL PI agents will be created
- Fill in the SQL Server connection details where the SQL Server instance used as the SQL PI repository will be located, and 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
- Click Monitor to begin creating the new agent
- Click the button to either Add Another Database or Close the dialog when the agent creation process completes.
- The new agent should now be created and after a few minutes data should be visible in the databases dashboard.
The following video details how to add a SQL Server agent with PI monitoring to a SQL Server PI repository.