Within a large environment where Ivanti Workspace Control is used in combination with a Microsoft SQL Database, the SQL Server has to handle numerous connections initiated by all the Ivanti Workspace Control Agents.To minimize the load and connections on the SQL Server, Ivanti introduced the Ivanti Relay Servers, which act as proxies for all agent connections to the SQL Server, thereby reducing the total number of SQL connections and the overall load.
While monitoring our Ivanti Relay Server, we noticed that a couple of times a day, the Ivanti Relay Servers started queuing all messages. The transactions folder filled up with transactions that weren’t forwarded to the SQL Server. The Ivanti Relay Servers have a built-in failsafe option, where they stop accepting new transactions when the queue exceeds 50,000 transactions. So, in a short period of time, all Ivanti Relay Servers went offline because they hit the 50,000 transaction threshold.
Out of the blue, all transactions in the queues were processed to the SQL Server, and the Ivanti Relay Servers came online as if nothing had ever happened. So, why bother investigating this when the problem seems to self-heal? Well, several things start failing when transactions are not processed by the SQL Server immediately. Active sessions and user logs are not visible within the IWC Console, causing all kinds of issues.
Since we suspected that the SQL Server was overloaded and causing these issues, we got our DBA involved. Quickly, our DBA noticed the issue was caused by a SQL query, which created a lock on the SQL Database. As a result, the other Relay Servers weren’t able to write information to the database until the lock was removed by the specific Ivanti Relay Server.
In our case, this query was causing the SQL Database lock:
@RowGUID uniqueidentifier,@ClassID int,@DateTimeUTC varchar(14),@ComputerName varchar(26),@ComputerNameLC varchar(26),@ComputerIP varchar(13),@SessionID int,@ClientName varchar(12),@ClientIP varchar(13),@User varchar(18),@UserLC varchar(18),@Process varchar(8000),@AppGUID uniqueidentifier,@Error bit,@Info varbinary(612),@Replicaguid uniqueidentifier,@ExternalIdentity varchar(8000))INSERT INTO tblLogs
(RowGUID, lngClassID, strDateTimeUTC, strComputerName, strComputerNameLC, strComputerIP, lngSessionID,
strClientName, strClientIP, strUser, strUserLC, strProcess, AppGUID, ysnError, imgInfo, replicaguid,
strExternalIdentity)
VALUES
(@RowGUID, @ClassID, @DateTimeUTC, @ComputerName, @ComputerNameLC, @ComputerIP, @SessionID,
@ClientName, @ClientIP, @User, @UserLC, @Process, @AppGUID, @Error, @Info, @Replicaguid,
@ExternalIdentityAccording to our DBA, this article Ivanti wrote for Ivanti Neurons was exactly what was happening to our Ivanti Workspace Control database. In cases of “a high number of transactions,” “a high number of locks in your database,” and “multiple back-end servers,” it’s recommended that you use RCSI (Read Committed Snapshot Isolation).
Using Read Committed Snapshot Isolation (RCSI)
For the Ivanti Service Manager database, ensure that the following two parameters are set to ON:
- Allow Snapshot Isolation
- Is Read Committed Snapshot On
We discussed this specific Ivanti Neurons article with the Ivanti Workspace Control support engineers, but they were unsure if this would also improve the Workspace Control database.
Our DBA was quite sure that the negative side effects of using Read Committed Snapshot Isolation (RCSI) were far less significant than the database locks we were currently experiencing. We decided to change our Ivanti Workspace Control to use RCSI and monitor the environment to ensure no unwanted side effects arose.
And indeed, since the SQL Database settings were changed, we haven’t noticed any Ivanti Relay Server queues filling up anymore.