The Foglight Cartridge for SQL Server offers two methods when creating agents for monitoring, Simple and Advance Mode. This video will cover how to create multiple SQL Server agent for monitoring using the Advance Mode, ideal when configuring monitoring for multiple instances at a given setting, appropriate for complex deployments or large distributed environments
Creating procedure was built against Cartridge for SQL Server version 5.7.1 and above however the logic is applicable to some older versions of Foglight Cartridge for SQL Server.
This video is applicable for version 5.6.11, 5.7.1, and 5.7.5 of Foglight.
The Use this option to discover multiple SQL Server instances also known as the advance mode is suitable for configuring monitoring for multiple instances at a given setting, appropriate for complex deployments or large distributed environments..
Alternative steps are located in the Resolution section.
The setup has some minimum requirements for database and os connections. Review the prerequisites and requirements as a setup reference; also consult the guides mentioned below
Please review the Foglight for SQL Server Permissions Guide and Users Guide for minimum user permissions.
1. To add multiple agent instances for monitoring in one setting, log into the Foglight web console with your Foglight user and click on Databases to access the databases dashboard.
2. Click on Monitor and select SQL Server, the Monitor SQL Server Instance setup GUI should display in a simple mode format requesting the needed setup configurations for creating a single instance. Under the Monitor SQL Server Instance heading exists a blue link entitled Use this option to discover multiple SQL-Server Instances. To create multiple agents in a setting, click on the link.
3. Before you proceed, click the blue circle little information I for further in details on this process
4. The Select the Monitoring Agent Manager screen allows you to choose the Foglight agent manager client to deploy the agents against. Shown are the current Foglight Agent managers reporting into the FMS and the number of agents deployed against. The radial usually defaults to the recommended Foglight agent manager that contains the smallest number of deployed agents. This auto function helps control with load balancing and sizing that effects performance on the Foglight Agent Manager. If the defaulted Foglight Agent Manager is not the desired choice, there is an option to select a different host.
5. Once the agent manager is chosen, click Validate to check the Agent Manger system resources. When the status has validated, proceed to the next step.
6. Click on Add instance and a menu pops up with configuration options. Each option includes a blue circled little I with instructions on configuring.
7. Add manually involves keying in the connection details, Discover by hosts and Discover by IP requires the SQL browser services running to auto discover the broadcasting instance name. Import from file. Setup instructions are in Knowledge article 102518, accessible from support.quest.com.
8. After the instances has been added for configuring, click Set credentials
9. Under SQL Server Connection, The circled blue information Little i is helpful explaining the login configurations use of each section
10. Next Section, The Connection Details is where to specify the name of the SQL Server Instance to monitor. To monitor a default instance, only input the host name in the server field. For named instance, use the host name\instance format. In a clustered environment, insert the virtual host name of the SQL Server host
11. SQL Server Default port is 1433. If no port is specified, the wizard will use the port provided by the SQL Browser service, in which case this service must be started.
12. Next there are two methods for login connections, one is to specify login credentials section by either configuring manually using windows or SQL Server authentication both available in the dropdown. The same connecting methods when accessing the SQL Server Management studio. So if the provided user can connect via the SQL Server Management Studio it should be able to establish login through this wizard.
If Windows Authentication is selected to login into the instance, in one configuration this authentication will allow to connect to the instance as well as login to the OS to gather OS metrics if enable. Enter the user name in the domain\username format level
If SQL Server authentication is selected configure database connectivity using a database user and password. The OS credentials for configuring OS metrics are suggested separately under the Monitoring Extension/Operating System section. Click on the red Missing Credentials and complete the OS login setup in accordance to the information provided, consult the little i. There is also an option to select stored credentials from a lockbox if it exists, ideal for domain users accessing.
13. The second one is Log in to SQL Server using Windows Authentication and the Windows account that is running Foglight (SYSTEM), means if a local Foglight agent manager installed on the SQL Server host, it will adopt those credentials
14. Choose the desired authentication
15. Note the current sensitivity level for this instance is usually Normal. This alarm level is adjustable by clicking on the level (Normal). There is a sensitivity control definition for each level of choice, Essential, Normal, Tuning, and Performance
16. After the Alarm Sensitivity level has been defined, click monitor.
Configuration starts and if a Grant privileges box pops up supply a sysadm user to execute grant scripts. This may occur when the login selection is SQL Server Authentication. The script is viewable and has the option to be copied out.
Once completed, the monitored status is indicated. If preferred the display name of the instance displaying in the database dashboard can be changed, click here. Repeat the setup process to discover other instances without leaving this screen.
The new agents are now created to monitor and viewable in the database dashboards. There is also an entry in Agent Status for the created SQL Server agent and its corresponding Infrastructure OS agent, if OS was selected during the creation stage.
Click finish to return to the databases dashboards. After a couple minutes of collection time the dashboard should become populated with data for the newly created agents.