SQL2005, SQL2008, SQL2012, SQL2014

Supported Versions SQL2005, SQL2008, SQL2012, SQL2014
Collection Methods JDBC Queries and WMI
Requirements
  • JDBC query permissions:
    • If the SQL Server supports Windows Authentication mode, queries will be run using the credentials of the Longitude Statistics Server account. This account must have administrator privileges within the SQL Application.
    • If the SQL Server supports mixed Windows and SQL Server Authentication, the default behavior is to use Windows Authentication.
    • A SQL account may be specified for JDBC collections during configuration. If a SQL account is used, the specified account must have permission to execute select queries against the sys.dm_exec_query_stats view.
  • For WMI collections, the Longitude Statistics Server account must have Local Administrator privileges on the SQL computer.
  • For WMI collections, the Windows Management Instrumentation (WMI) service must be running on the SQL computer.
  • The SQL application will monitor SQL specific services via WMI and create an event when one of these services is not running. If a listed service is not installed, it will not be monitored. Additional services may be monitored using Service Transactions. The monitored services are:
    Service Display Name Default Instance Service Name Named Instance Service Name
    SQL Server
    Analysis Services
    MSSQLSERVEROLAPSERVICE MSOLAP$<instance>
    SQL Server Full
    Text Search
    MSFTESQL MSFTESQL$<instance>
    SQL Server
    Reporting Services
    REPORTSERVER REPORTSERVER$<instance>
    SQL Server Integration Services MSDTSSERVER MSDTSSERVER
    SQL Server Integration Services 10.0 MSDTSSERVER100 MSDTSSERVER100
    SQL Server
    Browser
    SQLBROWSER SQLBROWSER
    SQL Server Active Directory Helper MSSQLSERVERADHELPER MSSQLSERVERADHELPER
    SQL Server VSS Writer SQLWRITER SQLWRITER

     

Configuration
  1. Use Monitor Devices
  2. If necessary, select a Longitude Remote Agent.
  3. Select one of SQL2005, SQL2008, SQL2012 or SQL2014.
  4. Enter the name of the SQL server to be monitored in the Computers field.
  5. The Instance value is used by Longitude to distinguish between different SQL Server instances being monitored on the same server.
    • It is recommended to set the Instance to be the same as the NamedInstance, but any unique string may be used.
    • Registering the same Instance twice on the same computer for the same application will overwrite the details of the first registration.
    • The Instance field will be updated to the value entered into the NamedInstance field when that field is tailored, but the Instance may be modified before the collection is registered.
    • If the NamedInstance field is empty, the value for Instance will be default.
  6. The NamedInstance is the name of the SQL Server instance to be monitored. If left blank, this will monitor the default SQL instance on the server.
  7. The default Database Driver is: net.sourceforge.jtds.jdbc.Driver. Change this value only if your SQL instance requires a custom driver.
  8. The default Database URL is: jdbc:jtds:sqlserver://~host~;integratedSecurity=true;
    • During registration, Longitude will substitute the name of the monitored server for ~host~. If you need to use a host name for the SQL instance that is different than the name of the computer, modify it in the URL before registration.
    • The integratedSecurity=true; portion of the URL is preset for Windows Integrated Authentication. Set this to integratedSecurity=false; if using a SQL account for JDBC queries.
    • The JDBC url will use port 1433. If SQL uses another port, add the port after the host name, e.g. if using port 1435: jdbc:jtds:sqlserver://~host~:1435;integratedSecurity=true;
  9. Enter a SQL account name into the User field if using a SQL account for JDBC. If using Windows authentication, this field may be left blank.
  10. Enter the password for the SQL user account in the Password field if using a SQL account for JDBC. If using Windows authentication, this field may be left blank.
  11. The TopNJobs value will limit the number of jobs returned by the FailedJobs and LongestJobs collectors. The default value is 10.
  12. The TopNQueries value will limit the number of query results returned by the FrequentQueries,
    LongestQueries and WorstQueries collectors. The default value is 50.
  13. Click Monitor
  14. Repeat the configuration steps with different Instance and NamedInstance values to monitor additional SQL Server instances on he same server.
Troubleshooting
  • SQL Performance Collections fail with an Invalid Class error:
    1. Check PerfMon on the SQL Server computer to verify that the SQL Performance Monitor collectors have been installed.
    2. If the PerfMon counters are not listed, they will need to be rebuilt. In some cases, the following steps will restore the counters:
      1. Log on to the SQL server
      2. Open a command prompt with elevated privileges
      3. For the default instance run the command:
        unlodctr.exe MSSQLServer
      4. For a named instance, run the command:
        unlodctr.exe MSSQL$Instance
        where Instance is a named SQL instance
      5. Navigate to the Binn directory under the installation directory for the SQL instance
      6. Find the name of the PerfMon .ini file – this will be in the format perf-Instancesqlctr.ini
      7. Run the command: lodctr.exe perf-Instancesqlctr.ini, using the name of the Performance counter .ini file in the Binn directory
      8. Resynchronize WMI with PerfMon by running: winmgmt /resnycperf
      9. Restart the Windows Management Instrumentation service on the computer
      10. If the SQL PerfMon counters are still not present, reboot the server

      Please note that, depending on why the counters are unavailable, this method may not work to restore SQL PerfMon counters. Consult Microsoft documentation for additional methods of restoring PerfMon counters.

    3. If the SQL PerfMon counters do exist, resynchronize them with WMI:
      1. Log on to the target computer with a local administrator account.
      2. Open a command prompt and run the command: winmgmt /resyncperf.
      3. Restart the Windows Management Instrumentation service on the target computer.
  • Index collection resource usage
    The SQL Index collection runs at 6AM each day, and can be resource intensive. If you notice performance issues from this collection, contact Longitude Support at support@heroix.com for information on disabling SQL Index collections.
  • Low free memory alerts on SQL servers
    SQL instances may be configured to use most of the memory on a server, so that the server functions correctly with 100% memory used. In this case, either disable the alert, or configure the threshold for the SQL >> Resources >> LowAvailableMemory alert to AvailableMemoryMin = 0.
  • TranasctionLogsFull alerts for logs that are set to Autogrow
    Configure the threshold for the SQL >> Databases >> TransactionLogsFull alert to LogFilesUsedPctMax = 100.