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.
Added an Application menu with links to Monitored Servers, Settings, Credentials, the Application Log, and a few other pages
Added an About page that links to lots of internal pages
Fixed a bug where ignored backups with trailing spaces wouldn’t ignore
Added a page to list all database snapshots and their age
1.3.9 (8 August 2021)
Supports domain login to edit settings
Most server displays now use the max memory if it is configured
A “Versions” page lists all the versions and editions at http://localhost:8143/versions. You can also download this as CSV. The CSV includes tags, cores, edition, and memory so you can do rudimentary licensing validation.
The Log Events now includes a filter. You can reach this by clicking on the “Refreshed” label on the upper right.
If servers have the proper extended event session, those events can be filtered. If you haven’t created the Extended Event session, the code to do so is listed on the page. Please see the Extended Events section in this document.
The Availability Group page now has JSON output at http://localhost:8143/ag/json. This should be suitable for generating alerts.
Required Permissions
The application requires the following permissions for the login that is running the service or executable:
VIEW SERVER STATE - View basic server information such as CPU, disk I/O, waits, and version.
VIEW ANY DEFINITION - View database details such as name, status, and size.
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.
Identify the domain account
Launch SECPOL.MSC, Navigate to Local Policies -> User Rights Assignment and add the account to the Log on as a Service policy
Grant the service account MODIFY permission on this directory
Open an Administrator console window in this directory and run isitsql.exe install. This installs the executable as a service.
Open the Local Services control panel and (1) change the service to run as the service account and (2) start automatically.
Start the service. It will create a log directory here for error logs.
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.
It listens on port 8143 by default. Changing this requires a restart.
The logo on the upper-left is a link to the “Home Page” field. This defaults to /. I typically override it to a tag of my choosing: /tag/prod. You can always get back to all the server by choosing Tags -> All Servers from the menu.
You can configure the threshold for backup alerts. They default to 36 hours for full and 90 minutes for logs.
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.
Other Notes
To remove the service, stop the service, and type isitsql.exe remove from an Administrator console.
To update the service, stop the service, repace the executable and restart the service.
You can see other command line options by typing isitsql.exe /?. There aren’t many.
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.
Pressing F11 in the browser will remove all chrome and display a nice dashboard.
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:
{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
Driver={SQL Server Native Client 11.0};Server=127.0.0.1;Database=tempdb;Trusted_Connection=Yes;App=IsItSql;
Driver={SQL Server Native Client 11.0};Server=MyServer;Database=tempdb;Trusted_Connection=Yes;App=IsItSql;
Driver={SQL Server Native Client 11.0};Server=127.0.0.1,1433;Database=tempdb;Trusted_Connection=Yes;App=IsItSql;
Driver={ODBC Driver 11 for SQL Server};Server=127.0.0.1;Database=tempdb;uid=test;pwd=test;App=IsItSql;
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
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.
If you find the waits aren’t mapping to wait groups properly, please email me.
Previous Releases
1.2 (April 2021)
Added filters to the application error log and the extended events page.
On server lists, display memory percentage as the percentage of the memory cap for the instance. Hovering over the memory field shows the cap the physical memory on the box.
The Extended Events page refreshes every 60 seconds and displays in absolute time instead of relative times. See Extended Events for more details.
The settings page allows you to set a URL for the “home page”. This is the link the “Is It SQL” text in the upper left links to. I’ve set this to a tag for key servers such as “/tag/your-tag”. The Tags menu has a link for “All Servers”.
1.0.37 (July 2020)
Shared Credentials can be defined. These are SQL Server logins and passwords. These can then be assigned to servers. This is useful when a common SQL Server login for monitoring is used. These are stored encrypted in connections.json.
Polling now checks if a server is up and does an AG health check every 10 seconds. A full poll is done every minute.
All usage and error reporting has been removed.
All functionality that was in BETA has been enabled.
Further, all Enterprise features are now enabled. This includes:
IsItSQL will assign tags for domain, edition, and version so you can easily group servers together.
Capture database mirroring status
Monitor availability group health and backups
Single page showing all instance missing backups
What’s New - 1.0.29 (18 April 2018)
The app better handles “names” that repoint to new instances. For example, an AG listener or static DNS entry that switches to a new instance doesn’t create odd spikes in disk I/O or waits. It also better handles reseting metrics on server restarts.
You can choose which servers appear in the dashboard by assigning them a “dashboard” tag. It will show the friendly name you’ve entered and sort by that name.
Backup reporting now reports an AG backup from any node. If you are looking at the database page for a node in an AG, it will show that a backup was completed for that database even if it was done on another node. You can hover over the backup and it will show which node completed the backp, when it was done, and what file it sent the backup to.
What’s New - 1.0.28 (30 August 2017)
The Dashboard is now populated by the first three servers assigned the “dashboard” tag.
We better handle servers that are the primary for multiple Availability Groups.
Fixed a bug with the save settings on localhost
What’s New - 1.0.27 (10 July 2017)
The tags have been broken out into user tags and auto generated tags. Full tagging functionality is available when you sign up for the newsletter. I’ve found this makes it easier to work with the tags I assign but still lets me get to the auto generated tags.
The Memory column of the server list now shows the memory that SQL Server is using, the total memory on the server, and the percentage of those two numbers. It highlights the total memory on the server. I found that’s the number I’m looking for most often.
If you hover over the Cores column, you’ll see something like this: SQL Cores Used: 1.29; Other Cores Used: 0.45. This is just multiplying the CPU percentage by the number of cores. That math is also performed on the total line. I have tags for my VM hosts and that lets me see the CPU usage looks like in “effetive cores” for each host.
Many of the wait types around SQL Server 2016 Availability Groups and Query Store have been cleaned up. You should see more interesting waits now.
The list page doesn’t show the SQL Server version any more. You can see that on the server detail page. There are auto generated tags for the version so it’s still easy to find all your old 2005 servers. Sign up for the newsletter to enable tagging.
1.0.25 (3 May 2017)
The README above has been completely rewritten for this release. Please read it!
Polling now times out after one minute.
A number of settings are now configurable via the GUI. These are the number of concurrent pollers, the port to host the web server, how often to expect a backup, and a security setting for changing these settings.
Servers are now entered using the GUI.
The menu has been simplified to include pages that summarize data across servers under a “Global” menu item
Backups in different timezones are now handled appropriately.
Servers identified by a GUID instead of a S# notation in the URLs.
1.0.24 (April 6, 2017)
Add a page to show database servers that don’t have appropriate backups.
Increased the number of servers that can be polled concurrently
Cleaned up various wait group names
Added a page to show a summary of all your servers
The active sessions will show a percent complete if one is available. Hover over the duration to see it.
1.0.23 (December 13, 2016)
Include support for availability groups. Is it SQL displays the health of any availability groups it finds. Note: This is an Enteprise feature. Sign up for the mailing list above and I’ll send details on enabling this feature.
Disk performance now breaks out the MB/sec, IOPS, averge IO size, and average duration over the one minute monitoring period. It does this for reads and writes. This column is also sortable based on the IOPS. This gives an easy way to see which servers are generating the most disk I/O.
The pages that show a list of servers now include a total line. It totals the disk I/O, SQL batches per second, SQL Server memory, and the size of data and log files. This lets you see the total load you’re placing on your infrastructure across your all servers.
1.0.22 (November 10, 2016)
Database Mirroring is show in two places:
It appears on the server page will show any databases that are mirrored.
Second, there’s a global database mirroring page that will show each mirrored database across all servers. It will show the status, partner, and send and receive queue sizes. It also includes a “priority column”. This gives an easy way to prioritize databases that aren’t online and synchronzied or have a send or receive queue.
The log size of database is split out into its own column which makes it sortable.
Information that is polled in real-time is identified with a cool lightning bolt. Every page refresh will update this data.
1.0.20 (September 19, 2016)
BETA: User-defined tags can be assigned to each server. Please sign up for the newsletter to enable this feature.
Assorted small bug fixes
August 24, 2016 (1.0.19)
You can now view basic information about the databases on a server
We now prefer the ODBC 13 driver to the ODBC 11 driver.
If you run two instances or launch the EXE while the service is running we provide a better error
The menu bar now stays on top while scrolling down.
Processes waiting on BROKER_RECEIVE_WAITFOR are now excluded from the list of active processes
August 4, 2016 (1.0.18)
Unreachable servers are displayed at the top of every page. Previously some pages may have displayed them twice or not at all
Sessions with a wait type of WAITFOR no longer show up as Active Sessions when looking at a server
Previously the database size included snapshots. Snapshots are no longer included in when computing the data size. The next update should add them back in but only include the actual data in the snapshot.
The default sorting for some columns has been changed to show the higher values first. For example, CPU percentage and database size.
August 1, 2016 (1.0.17)
The table sort now retains the sort between page refreshes.
Any unreachable server now shows at the top of each page since a page may be sorted in a way that wouldn’t show it.
If a server is unreachable, it will only log once. It will then log when it becomes reachable.
Polling should be faster due to more concurrent threads.
All JavaScript, CSS, HTML, fonts, etc. have been moved inside the executable.
SQL Server 2005 support is included. Barely.
July 28, 2016 (1.0.16)
Includes support for limited tags
Added a simple gradient for CPU
Changed the disk I/O chart to show writes as a line to fix the random gaps
July 19, 2016 (1.0.15)
The homepage now displays the number of databases and their total size
Added support for SQL Server Native Client 10.0
Improved graphing of CPU for servers in different time zones
Improve reporting of active sessions for SPIDs in unusual states
July 14, 2016
The disk graph is improved. There’s still an issue in some browsers with gaps however.
July 13, 2016
The app captures the domain name and displays it when you hover over a server name.