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 a single SQL Server agent for monitoring using the Simple Mode, ideal in creating single agents at a time.
This video is applicable for versions 5.9.1 and higher versions of Foglight.
The following video details how to create a new SQL Server agent using the simple mode.
The setup has some minimum requirements for database and os connections. Review the prerequisites and requirements as a setup reference; also consult the Foglight documentation guides mentioned below
–Instance connections are made through JDBC and the following needs to be valid as they are used to established DB connectivity to the monitored SQL Server Instance host:
–Valid resolvable/pingable hostname
–Valid active connection port default 1433
–Valid SQL Instance name even if it’s a default instance
–OS connections types:
–WinRM or WMI connections are needed for Operating System metric retrieval.
Please review the Foglight for SQL Server Permissions Guide, Hardware sizing Guide and Users Guide for additional requirement.
1. To add Agents, log into the Foglight web console with your Foglight user. 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
3. Under SQL Server Connection, The Agent Manger Host field specifies the agent manager selected to use to discover the instance. The host shown in blue is usually defaulted based on the least amount of instances already deployed against. Also helps with load balancing and sizing that effects performance. Click the host to change Agent Manager Host if desired, another optioned screen shows the current agent managers and the number of agents deployed against it. You have a choice of selecting a new agent manager or continuing on with the default host. Remember the host should meet the system requirements and supported number of deployed agents.
4. The blue circled information Little i is helpful explaining the login configurations use of each section
5. 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
6. 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.
7. 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, click the blue circled little i. There is also an option to select stored credentials from a lockbox if it exists, ideal for domain users accessing.
8. 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
9. Choose the desired authentication
10. 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
11. 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 is shown under status and the alarm sensitivity level is indicated initialized successfully popup is indicated. There is a choice of adding another database in which will take you back to the setup screen or click finished.
The new agent is 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.
After a couple minutes of collection time the dashboard should become populated with data.