What is Invantive UniversalSQL Server
The Invantive UniversalSQL Server provides real-time access to all over 80 (cloud)platforms using the familiar TDS protocol of Microsoft SQL Server (see Invantive UniversalSQL Server sneak preview). These platforms include the popular platforms Exact Online, Twinfield, Odoo, AFAS Profit and ActiveCampaign.
When deployed on-premises, this allows all corporate users to access the databases from tools such as Microsoft Access, SQL Server Management Studio, Crystal Reports and others.
Log on to Monitor
For administrative purposes, a character-based monitor is available to get an overview of current activity. The monitor is available by default on port 5933 when not otherwise configured in appsettings. It is recommended for security reasons to configure the monitor to only listen on the localhost IP-address 127.0.0.1.
To access the monitor any raw telnet connection can be used within a secured network, such as using PuTTY with a raw connection:
The monitor is, similar to Invantive UniversalSQL Server, available in 16 languages.
The first step is to provide credentials for one of the administrative users configured.
Prepare Test Case
Before using the monitor, make sure you have opened connections to one or multiple databases on your Invantive UniversalSQL Server instance or on one or more of the cloud databases located at database.invantive.com.
For the topic, we will use an environment with five opened databases in SQL Server Management Studio:
Use of the Monitor
Databases
Now a number of connections have been opened, let us query first for a list of the databases available using:
show databases
with result:
Details of an individual database can be retrieved using show database details while specifying a part of the ID from the list above:
show database details id '31fd'
with results:
The most relevant fields are:
- ID: a unique ID of the database.
- Name: the name of the database, which must equal the server name specified on logon from a SQL Server client program.
- Data Container Count: the number of data containers in the database.
- Active Connections: the then current number of simulatenous connections in the pool to this database.
- Active Users: the then current number of distinct users having the database open.
- Is Licensed: whether the database use is licensed. Always
True. - Is Valid: whether the database has a valid structure. Always
True. - Source File Name Path: location of the settings file with the definition (if any).
- Short Description: as registered in settings.
- Description: as registered in settings.
Users
In a similar way, a list of users can be retrieved using show users and/or show user details id '...':
show users
for instance displays:
Connections
Similar, the list of open and idle connections in the pool can be displayed using:
show connections
with results such as:
The details can be retrieved using show connection details id '...' with results such as:
Errors are also displayed in case the connection registered any errors.
The most relevant fields are:
- Active: whether active.
Truewhen a client is connected. - User Name: connected user.
- User Validated: whether the user’s credentials matched the requirements.
- TLS Configured: whether encryption is enabled.
- Database Server Name: name of the database, identical to the server name in the SQL Server client product.
- Database: name of the database (null when default).
- Server Hostname: server running the instance.
- Request ID: unique ID of the connection.
- Pool Identity ID: equals the pool identity ID when running in combination with Invantive Cloud.
- Language: non-default user interface language configured.
- Application Name: name of the SQL Server client application connected.
- Client Hostname: hostname of the client as specified using the TDS protocol.
- TDS Version: version of the TDS-specifications used. Typically always 7.4 or 8.0.
- Message Number: incrementing counter on number of messages exchanged.
- Mode: whether the connection is sending, processing or receiving/awaiting data.
- Phase: the phase in the data exchange process. Always
S9:MSGafter the connection has been established. - Library Name: library as indicated by the SQL Server client.
- Packet Size: size of an individual transport packet in bytes.
- Program Name: name of the Invantive UnivesalSQL Server.
- Program Version: version of the Invantive UniversalSQL Server.
- Local IP Address and Port: port on which Invantive UniversalSQL Server listens.
- Remote IP Address and Port: port from which the SQL Server client connects.
- Success:
Truewhen the connection has been returned to the pool after the user disconnected. - Start (UTC): date/time in UTC when the connection was established.
- End (UTC): date/time in UTC when the connection was last closed.
- Age (sec): age of the connection in seconds.
- Current Operation: type of request last executed on behalf of the connected user.
SQL Batch,RPC Request,Bulk LoadandTabular Resultare the most common operations. - Last SQL Statement: last executed Invantive UniversalSQL statement.
- Last SQL Statement Row Count: number of rows returned on last statement.
- Last Error Severity: severity of last error.
- Last Error Number: number of last error.
- Last Error Message: error message text including code.
- Last Error Line Number: line number of line that triggered last error.
- Last Error SQL Statement: SQL statement that triggered the last error (JSON format).
- Unhandled Error Code: code of any unhandled error.
- Unhandled Error Message: message text of any unhandled error.
- Total Error Count: number of errors registered on this connection.
- Total Row Count: total number of rows returned by this connection.
- Total Rececived (bytes): number of bytes received from the client.
- Total Sent (bytes): number of bytes sent to the client.
Server
The server can also be interrogated for it’s status using show server statistics with result such as:
Other
Further, the monitor has a number of other statements:
help: show an overview of possible statements.exit: leave the monitor.