IsItSQL Quick Start

1. Launch the application

The simplest way is to double-click the isitsql.exe file in the directory. You can also open a console window and run the application. Any errors will be displayed in the console window. A simple Control-C (or Break) will exit the application.

After launching, navigate your browser to http://localhost:8143 to view the monitor. It polls each server once per minute in the background. Pages refrehes automatically every minute.

2. Adding a server to monitor

Navigate to the Add Server page via the Gears icon in the upper right corner. I suggest starting with a server you can connect to via a trusted connection. Please remember that this application is running as you. Enter the server name in the FQDN column and save it.

Your server should be polled and available when the page refreshes back to the list of servers to monitor.

Note: If you enter a server using either a SQL Server login or custom connection string and then change to run the application as a service you will need to re-enter this information because it will be encrypted.

Table of Contents

  1. What’s New
  2. Required Permissions
  3. Running as a Service
  4. Settings
  5. Features
  6. Other Notes
  7. Connection Strings
  8. Bulk Adding Servers
  9. Feedback and Known Issues
  10. Extended Events
  11. Previous Releases

What’s New

2.0.3 (20 January 2024)

1.7.14 (8 March 2023)

1.7.11 (18 August 2022)

1.7.9 (2 August 2022)

1.7.6 (28 July 2022)

1.7.2 (28 June 2022)

1.7 (8 May 2022)

1.6 (22 February 2022)

1.4 (7 September 2021)

1.3.9 (8 August 2021)

Required Permissions

The application requires the following permissions for the login that is running the service or executable:

Running as a Service

There isn’t an installer but it is very easy to configure this as a service. Ideally this service will run as a domain service account. Please stop the application before completing these steps.

  1. Identify the domain account
  2. Launch SECPOL.MSC, Navigate to Local Policies -> User Rights Assignment and add the account to the Log on as a Service policy
  3. Grant the service account MODIFY permission on this directory
  4. Open an Administrator console window in this directory and run isitsql.exe install. This installs the executable as a service.
  5. Open the Local Services control panel and (1) change the service to run as the service account and (2) start automatically.
  6. Start the service. It will create a log directory here for error logs.
  7. If the application will be accessed from another machine, verify the Windows Firewall allows inbound access on port 8143.

Note: The service account is used to encrypt key data in the JSON files in the config directory. If the service account is changed, this information will need to be reentered.

Upgrading is as simple as stopping the service, copying a new executable, and restarting the service.

Settings

The Settings Page has the following options.

Settings - Security

There are three options to control who can change settings in the application: * Save from any client * Only Save from localhost (must RDP to the server to edit settings) * Settings Page Admin Group. You must be a member of this group to save the settings page.

Domain group membership is experimental. The Domain Group must be entered here. You can log in at http://localhost:8143/login. The user must be entered in the form user@domain.com.

Features

Availability Group Alerts

IsItSQL provides simple monitoring for Availability Group latency. There are two settings in ./config/settings.json:

"ag_alert_mb": 10000
"ag_warn_mb": 1000

If an AG send or recive queue is over the warn threshold, the screen will show a warning. If they are above the alert threshold, it will show an alert.

AG warnings and alerts are available in JSON form at http://localhost:8143/ag/json. This will list any server whose status isn’t healthy or that has latency.

Availability Group Display Names

IsItSQL displays the Listeners on the Availability Group page. We can override this using the config/ag_names.csv file. This file is only read on startup. It looks like this:

# Domain, AG_Name, Friendly_Name
PROD, AG1, db-txn.static.loc

The second field can be an Availability Group name or a Listener name. This is used if you have static DNS entries that point to Availability Group Listeners. It will also use the Display Name in any alerts that are displayed.

Waits

Prior to 2.0, waits were captured every minute from sys.dm_os_wait_stats which means we only saw them when the wait ended. Starting in 2.0, waits are polled every second from running processes and updated on the page every minute.

This means we only see significant for user sessions and don’t include waits for background processes. This shows fewer waits but they are more timely and actionable.

There is a server wait page at /server/:server_key/w2 that compares the two waits.

Other Notes

  1. To remove the service, stop the service, and type isitsql.exe remove from an Administrator console.
  2. To update the service, stop the service, repace the executable and restart the service.
  3. You can see other command line options by typing isitsql.exe /?. There aren’t many.

  4. The dashboard displays the three servers tagged with “dashboard”. You can display other servers by hacking the URL. For example, http://localhost:8143/dashboard/{GUID #1}/{GUID #2}/{GUID #3} will display those three specific servers.

  5. Pressing F11 in the browser will remove all chrome and display a nice dashboard.

  6. The service writes JSON files with server details and metrics in the cache folder. These are used for history between restarts.

Connection Strings

You shouldn’t need to enter connection strings very often. This is here in case you do.

The application will suggest a driver on startup. You can find the drivers installed by opening the ODBC data sources and looking at the Drivers tab. If you need to choose a driver I’d suggest the following priority:

  1. {SQL Server Native Client 11.0} Click on install instructions and look for Microsoft “SQL Server 2012 Native Client”. This is also the version that ships with SQL Server 2016.
  2. {ODBC Driver 11 for SQL Server}
  3. {SQL Server}. Most servers have the generic SQL Server ODBC driver installed. It works but won’t support some of the more advanced connection string settings.

Sample Connection Strings

Bulk Adding servers

The application tries to import any servers found in servers.txt at start up. We can take advantage of this to bulk import servers.

This is a CSV file with up to three values: server, “display name”, and tags. The server can be a name (NetBIOS, FQDN, IP, etc.) or a connection string. If the server name specifies a port, (ex. MyServer,1433) it should be enclosed in quotes. If you use just a name it will use a trusted connection. Connection strings should always be in quotes.

If you don’t include a display name it will use the server name.

Tags is a comma separated list of tags. Obviously it needs to be enclosed in quotes.

Extended Event Session

Each server page looks for an Extended Event session named “ErrorSession”. It expects a session holding SQL Server errors in a ring buffer. That session should be defined like this:

IF EXISTS (select * from sys.server_event_sessions where [name] = 'ErrorSession')
  ALTER EVENT SESSION ErrorSession ON SERVER STATE = STOP

IF EXISTS (select * from sys.server_event_sessions where [name] = 'ErrorSession')
  DROP EVENT SESSION ErrorSession ON SERVER 

CREATE EVENT SESSION ErrorSession ON SERVER 
  ADD EVENT sqlserver.error_reported            
  -- collect failed SQL statement, the SQL stack that led to the error, 
  -- the database id in which the error happened and the username that ran the statement 

  (
      ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, 
    sqlserver.username, sqlserver.client_app_name, sqlserver.client_hostname, package0.collect_system_time)
      WHERE severity >= 14 and error_number <> 2557 and error_number <> 17830
  )  
  ADD TARGET package0.ring_buffer    
      (SET max_memory = 1024)
WITH (max_dispatch_latency = 1 seconds, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, STARTUP_STATE = ON)

IF EXISTS (select * from sys.server_event_sessions where [name] = 'ErrorSession')
  ALTER EVENT SESSION ErrorSession ON SERVER STATE = START

Feedback and Known Issues

Please email Bill Graziano with any issues.

  1. If your server doesn’t have internet access, the graphing libraries won’t download to the browser. Run the browser from a machine that does have internet access and open http://yourserver:8143. It needs access to code.highcharts.com to download the graphing libraries.
  2. If you find the waits aren’t mapping to wait groups properly, please email me.

Previous Releases

1.2 (April 2021)

1.0.37 (July 2020)

1.0.36 (October 2019)

Further, all Enterprise features are now enabled. This includes:

What’s New - 1.0.29 (18 April 2018)

What’s New - 1.0.28 (30 August 2017)

What’s New - 1.0.27 (10 July 2017)

1.0.25 (3 May 2017)

1.0.24 (April 6, 2017)

1.0.23 (December 13, 2016)

1.0.22 (November 10, 2016)

1.0.20 (September 19, 2016)

August 24, 2016 (1.0.19)

August 4, 2016 (1.0.18)

August 1, 2016 (1.0.17)

July 28, 2016 (1.0.16)

July 19, 2016 (1.0.15)

July 14, 2016

July 13, 2016