Quick Start
This has changed starting in 1.0.25. Please read this section.
If you're upgrading from a previous vesion, please see that section below.
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.
Upgrading from a previous version
If you are upgrading from a version prior to 1.0.25
Previously servers were stored in servers.txt in plain text. Now they are stored in .\config\connections.json and password and connection strings
are encrypted. When the application starts it will import the contents of servers.txt and then rename the file to servers_imported_YYYYMMDD_HHMMSS.
This will encrypt the passwords and any connection strings. It will attempt to extract trusted connections and SQL Server logins and passwords where possible.
If that fails it will just store the entire connection string encrypted.
The passwords and connection string are encrypted using DPAPI by the account that is running the service. Please dont't run the application manually
and then run it as a service. It won't be able to read the encryped data.
And please delete old servers text file after you're comfortable that it imported successfully.
Table of Contents
- What's New
- Required Permissions
- Running as a Service
- Enterprise Edition
- Other Notes
- Connection Strings
- Bulk Adding Servers
- Error and Usage Reporting
- Feedback and Known Issues
- Previous Releases
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. If you have enabled the Enterprise features, you can see all this on the central backup alerts page.
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.
- The application has always logged errors back to a hosted error reporting service. I also added daily usage reporting. I'm interested in memory usage vs. server count and the health of the service. And I'm really curious how many people are using this :). The only identifying information is a GUID that I used to track unique users. You can find a copy of what is reported at http://localhost:8143/appusage. If you really want to turn this off you can find instructions at the bottom of the README. It doesn't report until it's been running for an hour so you have plenty of time to look at the data and turn it off if you'd like.
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 change the service to run as the service account and 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.
Enterprise Edition
Just downloading the application enables basic functionality. Sign up for the newsletter to enable additional features.
- The base software allows user-assigned tags. In the Enteprise Edition, 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 instances missing backups
Other Notes
- If you have outbound Internet access the application will upload any internal errors. This won't include any
information about your SQL Servers. You can disable this in the settings.
- 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.
- Displaying Dashboards
- 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 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.
- {ODBC Driver 11 for SQL Server}
- {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;
- Driver={SQL Server};Server=myserver.mydomain.fqdn;Database=tempdb;uid=test;pwd=test;App=IsItSql;
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.
Error and Usage Reporting
The following settings are buried in the JSON settings file (./config/settings.json). If they don't already exist they will be added
at startup.
- Error Reporting. By default the application will upload panics and unrecoverable errors. If the server itself
doesn't have Internet access this will fail. In either case, they should be logged in the ./log directory.
This can be disabled by editing the settings file and changing
errorReporting
to "false". This only takes effect at startup.
I encourage you to leave this enabled. This is best way I have to track down bugs.
-
Usage Reporting. By default the system will upload usage statistics one hour after startup and then again every 24 hours.
If the server doesn't have Internet access this will fail silently. You can see what data is reported
at http://localhost:8143/appusage once the application is running. This includes
the number of servers, memory usage, run time, version, pollers, etc. It does include a GUID for each site in the settings.json file. This tells me how
many unique users are using the application. I encourage you to leave this enabled. This is how I know that people really are using this.
This can be disabled by editing the settings file and changing
usageReporting
to "false". This only takes effect at startup.
If you have questions or concerns about this I encourage you to email me at the address below.
Feedback and Known Issues
Please email Bill Graziano with any issues.
- 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. If that doesn't work email me
and I'll try to help you.
- If you find the waits aren't mapping to wait groups properly, please email me.
Previous Releases
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.
- This is a beta feature. Sign up for the newsletter and we'll send instructions on enabling it.
- 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.