Configuration and Discovery

Download this manual as a PDF file

The following sections describe how to configure and discover Microsoft SQL Servers for monitoring in SL1 using the Microsoft: SQL Server Enhanced PowerPack:

If you already have Windows Server discovered, you might not need to create a new SQL Server credential or run a separate discovery session for SQL Servers if the PowerShell credential information is the same as that used for the Windows Server credential. In this scenario, you need only to install the Microsoft: SQL Server Enhanced PowerPack and ensure that the Windows user account used in the credential has the appropriate permissions, as outlined in the Prerequisites section.

Prerequisites for Monitoring SQL Servers

To configure the SL1 system to monitor SQL servers using the Microsoft: SQL Server Enhanced PowerPack, you must first have the following information about the SQL Servers that you want to monitor:  

  • IP addresses and ports for the SQL Servers
  • Username and password for a Windows user account with access to the SQL Servers

The SQL Servers that you monitor must be running PowerShell version 3.0 or later and need to have the SQL Server PowerShell (SQLPS) module installed. This SQLPS module is installed by SQL Server Management Studio. You can also install the SqlServer PowerShell module found here: https://www.powershellgallery.com/packages/Sqlserver/21.1.18218

The InvokeSqlCmd cmdlet must be present on the server and is available in the SQLPS and SqlServer PowerShell modules mentioned above. To determine if the proper cmdlets are available for this PowerPack to collect, run Get-Command Invoke-SqlCmd to see if the Invoke-SqlCmd cmdlet is installed.

In addition, the Microsoft: SQL Server Enhanced PowerPack requires the following permissions for the user account used for monitoring:

  • SQL 2014 and newer versions require one of the following configurations:
  • The user account has an enabled login on every instance and database to be monitored, with CONNECT SQL, VIEW SERVER STATE, and CONNECT ANY DATABASE permission granted to the login on each instance. The login should have VIEW DATABASE STATE permission and DB_DATAREADER role granted on the 'master' database, and the DB_DATAREADER role granted on the 'msdb' database. The user account will also need VIEW ANY DEFINITION permissions which can be granted using the following SQL statement:
  • GRANT VIEW ANY DEFINITION TO [DOMAIN\USER]
  • The user account has the replmonitor role assigned for the distribution database to monitor Replication Agent. To assign, perform the following:
    1. Open the SQL Server Management Studio and connect to the SQL Server with replication.
    2. Go to the Security Properties for the monitoring user.
    3. Select User Mapping from the Select a page list.
    4. Select the checkbox to choose distribution in the Database column. Then select the checkbox to choose the replmonitor role for the user from the Database role membership panel.
    5. Click OK to apply the changes.

  • The user account has an enabled login on every instance and has the SYSADMIN role.

  • SQL 2012 requires one of the following configurations:
  • The user account has an enabled login on every instance and database to be monitored, with CONNECT SQL and VIEW SERVER STATE granted to the login on each instance. The login should also have VIEW DATABASE STATE permission and the DB_DATAREADER role granted on the 'master' database, and the DB_DATAREADER role granted on the 'msdb' database. In addition, every database in the instance should have CONNECT access granted to the login. The user account will also need VIEW ANY DEFINITION permissions which can be granted using the following SQL statement:
  • GRANT VIEW ANY DEFINITION TO [DOMAIN\USER]
  • The user account has the replmonitor role assigned for the distribution database to monitor Replication Agent. To assign, perform the following:
    1. Open the SQL Server Management Studio and connect to the SQL Server with replication.
    2. Go to the Security Properties for the monitoring user.
    3. Select User Mapping from the Select a page list.
    4. Select the checkbox to choose distribution in the Database column. Then select the checkbox to choose the replmonitor role for the user from the Database role membership panel.
    5. Click OK to apply the changes.
  • The user account has an enabled login on every instance and has the SYSADMIN role.

ScienceLogic provides a PowerShell script on that automates the permissions-granting that is required as stated above. The script is included with the Microsoft: Windows Server PowerPack.

To use the PowerShell script, perform the following steps:

  1. When you download the Microsoft: Windows Server PowerPack from the ScienceLogic Support site, a .zip file for the WinRM Configuration Wizard Script(winrm_configuration_wizard._v3.4.ps1) will be in the folder with the PowerPack's EM7PP file.
  2. Copy the WinRM Configuration Wizard Script .zip file to the Windows server where Microsoft SQL Server is installed and from which you will be collecting data. Unzip the file
  3. Using the credentials for an account that is a member of the Administrator's group, log in to the Windows server you want to monitor. You can log in directly or use Remote Desktop to log in.
  4. Copy the PowerShell script named winrm_configuration_wizard_v3.4 to the Windows server that you want to monitor with SL1.
  5. Right-click on the PowerShell icon and select Run As Administrator.
  6. At the PowerShell prompt, navigate to the directory where you copied the PowerShell script named winrm_configuration_wizard_v3.4.
  7. At the PowerShell prompt, enter the following to enable execution of the script:

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process -Force

NOTE: The execution policy setting persists only during the current PowerShell session.

  1. After the warning text, select Y.

NOTE: If your Windows configuration requires further steps to allow execution of the script, PowerShell will display prompts. Follow the prompts.

  1. To set the required, least-privileged permissions for the user account SL1 will use to monitor all SQL Server instances and databases on the server, run the following script:

.\winrm_configuration_wizard_v3.4.ps1 -user <domain>\<username> -sql_only

Creating a PowerShell SQL Server Credential

To configure SL1 to monitor SQL Servers, you must first create a PowerShell credential. This credential allows the Dynamic Applications in the Microsoft: SQL Server Enhanced PowerPack to connect with an SQL Server. An example PowerShell credential that you can edit for your own use is included in the PowerPack.

NOTE: If you are using an SL1 system prior to version 11.1.0, the new user interface does not include the Duplicate option for sample credential(s). ScienceLogic recommends that you use the classic user interface and the Save As button to create new credentials from sample credentials. This will prevent you from overwriting the sample credential(s).

To create a PowerShell credential for an SQL Server:

  1. Go to the Credentials page (Manage > Credentials).
  2. Locate the SQL PowerShell - Example credential, click its Actions icon () and select Duplicate. A copy of the credential, called SQL PowerShell - Example copy appears.
  3. Click the Actions icon () for the SQL PowerShell - Example copy credential and select Edit. The Edit Credential modal page appears:

  1. Supply values in the following fields:
  • Name. Type a new name for your SQL Server credential.

  • All Organizations. Toggle on (blue) to align the credential to all organizations, or toggle off (gray) and then select one or more specific organizations from the What organization manages this service? drop-down field to align the credential with those specific organizations.
  • Timeout (ms). Type "18000".
  • Account Type. Select Active Directory from the dropdown.
  • Encrypted. Toggle this setting off.
  • Username. Type the username for a Windows user with access to the SQL Server.
  • Password. Type the password for the Windows account username.

The user account whose username and password are provided in the credential must have certain permissions in all SQL Server instances that SL1 will monitor. For a list of these permissions, see the Prerequisites section.

  • Hostname/IP. Type "%D".
  • Port. Type "5985".
  • PowerShell Proxy Hostname/IP.If you use a proxy server in front of the Windows devices you want to communicate with, enter the IP address of the proxy server in this field. Otherwise, leave this field blank.
  • Active Directory Host/IP. Specify the hostname or IP address of the Active Directory server that will authenticate the credential.
  • Active Directory Domain. Specify the domain where the monitored SQL Server resides.
  1. Click Save & Close.

The PowerShell credential test is not supported by the Microsoft: SQL Server Enhanced PowerPack.

Creating a PowerShell SQL Server Credential in the SL1 Classic User Interface

To configure SL1 to monitor SQL Servers, you must first create a PowerShell credential. This credential allows the Dynamic Applications in the Microsoft: SQL Server Enhanced PowerPack to connect with an SQL Server. An example PowerShell credential that you can edit for your own use is included in the PowerPack.

To create a PowerShell credential for an SQL Server:

  1. Go to the Credential Management page (System > Manage > Credentials).

  1. Locate the SQL PowerShell - Example credential, and then click its wrench icon (). The Edit PowerShell Credential modal page appears.

  1. Complete the following fields.
  • Profile Name. Type a new name for your SQL Server credential.

  • Account Type. Select Active Directory.
  • Hostname/IP. Type "%D".
  • Timeout. Type "18000".
  • Username. Type the username for a Windows user with access to the SQL Server.
  • Password. Type the password for the Windows account username.

The user account whose username and password are provided in the credential must have certain permissions in all SQL Server instances that SL1 will monitor. For a list of these permissions, see the Prerequisites section.

  • Encrypted. Select no.
  • Port. Type "5985".
  • PowerShell Proxy Hostname/IP. If you use a proxy server in front of the Windows devices you want to communicate with, enter the IP address of the proxy server in this field. Otherwise, leave this field blank.
  • Active Directory Hostname/IP. Specify the hostname or IP address of the Active Directory server that will authenticate the credential.
  • Domain. Specify the domain where the monitored SQL Server resides.
  1. Click the Save As button.
  2. When the confirmation message appears, click OK.

SQL Cluster Monitoring

For SQL Clusters that only include SQL Instances in an Active/Active configuration, follow the steps in the Discovering SQL Servers section.

For SQL Clusters that include an SQL Instance in an Active/Passive configuration, additional discovery steps are required and listed below.

NOTE: SL1's Active/Passive SQL Instance monitoring leverages the SL1 GUID Component Identifier to allow the SQL Instance component and its child database components to move between SQL Servers during a failover. Adding this GUID Component Identifier on SL1 versions prior to 8.12.1 will create a duplicate SQL Instance component on any already discovered SQL Servers. To prevent this, the GUID Component Identifier is not used by default. The "Enable Active Passive Cluster Failover" threshold in the "Microsoft: SQL Server Cache and Discovery" Dynamic Application provides the option to use the GUID Component Identifier when enabled. A value of "0" in the Threshold Value disables Active/Passive cluster failover; a value of "1" enables it.

Monitoring SQL Clusters on SL1 12.1.0 or greater.

  • Go to the Dynamic Applications Manager page (System > Manage > Applications).
  • Click the wrench icon () for the "Microsoft: SQL Server Cache and Discovery" Dynamic Application to open the Dynamic Applications Properties Editor page.
  • In the Thresholds tab, click the wrench icon () for the "Enable Active Passive Cluster Failover" threshold and change the Threshold Value to 1.
  • Click Save.
  • Follow the steps in the Discovering SQL Servers section on each Windows Server in the cluster.

Discovering SQL Servers

When you discover SQL Servers in SL1, SL1 auto-aligns a series of Dynamic Applications to discover, configure, and monitor the following SQL Server component devices:

  • SQL Servers
  • SQL Server instances
  • SQL Server databases

To discover SQL Servers and their component devices, perform the following steps:

  1. On the Devices page () or the Discovery Sessions page (Devices > Discovery Sessions), click the Add Devices button. The Select page appears:

  1. Click the Unguided Network Discovery Workflow button. Additional information about the requirements for discovery appears in the General Information pane to the right.

  1. Click Select. The Add Devices page appears.
  2. Complete the following fields:
  • Discovery Session Name. Type a unique name for this discovery session. This name is displayed in the list of discovery sessions on the Discovery Sessions tab.
  • Description. Optional. Type a short description of the discovery session. You can use the text in this description to search for the discovery session on the Discovery Sessions tab.
  • Select the organization to add discovered devices to. Select the name of the organization to which you want to add the discovered devices

  1. Click Next. The Credentials page of the Add Devices wizard appears:

  1. On the Credentials page, locate and select the PowerShell credential you created.
  1. Click Next. The Discovery Session Details page of the Add Devices wizard appears:

  1. Complete the following fields:
  • List of IPs/Hostnames. Type the IP addresses or the range of IP addresses for the SQL Servers you want to discover.
  • Which collector will monitor these devices?. Required. Select an existing collector to monitor the discovered devices.
  • Run after save. Select this option to run this discovery session as soon as you save the session.

In the Advanced options section, click the down arrow icon () to complete the following fields:

  • Discover Non-SNMP. Enable this setting.

  1. Click Save and Run if you enabled the Run after save setting, or Save and Close to save the discovery session. The Discovery Sessions page (Devices > Discovery Sessions) displays the new discovery session.
  2. If you selected the Run after save option on this page, the discovery session runs, and the Discovery Logs page displays any relevant log messages. If the discovery session locates and adds any devices, the Discovery Logs page includes a link to the Device Investigator page for the discovered device.

Discovering SQL Servers in the SL1 Classic User Interface

When you discover SQL Servers in SL1, SL1 auto-aligns a series of Dynamic Applications to discover, configure, and monitor the following SQL Server component devices:

  • SQL Servers
  • SQL Server instances
  • SQL Server databases

To discover SQL Servers and their component devices, perform the following steps:

  1. Go to the Discovery Control Panel page (System > Manage > Classic Discovery).

  1. Click the Create button. The Discovery Session Editor page appears.

  1. Supply values in the following fields:
  • IP Address/Hostname Discovery List. Type the IP addresses or the range of IP addresses for the SQL Servers you want to discover.
  • Other Credentials. Select the PowerShell credential you created.
  • Discover Non-SNMP. Because the discovery session is not using an SNMP credential, select this checkbox.

  1. Optionally, supply values in the other fields in this page. For a description of the fields in this page, see the Discovery and Credentials section.
  2. Click the Save button.
  3. The Discovery Control Panel page will refresh. Click the lightning bolt icon () for the discovery session you created.
  4. In the pop-up window that appears, click the OK button. The Discovery Session page displays the progress of the discovery session.

Relationships Between Component Devices

SL1 can automatically build relationships between SQL servers and other associated devices:

  • If you discover Windows server clusters using the Dynamic Applications in the Microsoft: Windows Server Cluster PowerPack version 100 or later, SL1 will automatically create relationships between SQL servers and Windows server clusters.

SQL Cluster Node and SQL Cluster Instance Relationships

When discovering clustered SQL Server instances you will see multiple component device trees that represent the clustered SQL server. In these component device trees, the SQL servers are described as Nodes and the instances described as Roles.

NOTE: Discovering clustered SQL instances is the same process as discovering standalone SQL servers.

The following SQL cluster configurations are supported by the PowerPack. The relationship between the SQL cluster nodes and instances are described for each: 

  • Active/Active. The default configuration. After discovery, the roles (instances) will be modeled in both nodes (servers). To determine which instances belong to a specific node, go to the Devices page and click on the node device. Click the Configs tab from the Device Investigator and click "Microsoft: SQL Server Instance Discovery" in the pane on the left. There you will see which roles (instances) belong to the node.
  • Active/Passive. To enable this configuration, you must find the "Microsoft: SQL Server Cache and Discovery" Dynamic Application in the Dynamic Applications Manager page (System > Manage > Dynamic Applications). Click its wrench icon () and then click the Thresholds tab. Click the wrench icon () for the Enable Active Passive Cluster Failover threshold object. In the Override Threshold Value dropdown, select Enabled and then click Save. This configuration is more intuitive, as the roles (instances) will be modeled only under the node (server) that they belong to, and in the event of a failover the instances will move between nodes.

NOTE: If a node has been stopped it won't be discovered.

Viewing SQL Server Component Devices

In addition to the Devices page, you can view the SQL Server and all associated component devices in the following places in the user interface:

  • The Device Investigator Map page (click Map in the Device Investigator page) displays a map of a particular device and all of the devices with which it has parent-child relationships. Double-clicking any of the listed devices reloads the page to make the selected device the primary device.

  • The Device Components page (Devices > Device Components) displays a list of all root devices and component devices discovered by SL1 in an indented view, so you can easily view the hierarchy and relationships between child devices, parent devices, and root devices. To view the component devices associated with a SQL Server, find the server and click its plus icon (+):

  • The Component Map page (Classic Maps > Device Maps > Components) allows you to view devices by root node and view the relationships between root nodes, parent components, and child components in a map. This page makes it easy to visualize and manage root nodes and their components. SL1 automatically updates the Component Map as new component devices are discovered. The platform also updates each map with the latest status and event information. To view the map for a SQL Server, go to the Component Map page and select the map from the list in the left NavBar. To learn more about the Component Map page, see the section on Maps.