The issue was a result of changes done in #3374 - basically Stored Procedures aren't executed in transactions which may results in exceptions (especially in cluster mode). Similar thing was applicable to MySQL (v. #3175).

It turned out that MS SQL driver has problems with handling enforced transactions in stored procedures which results in SQLServerException: The server failed to resume the transaction. Desc:5a00000001. exception. After trying different approaches to tackle the problem (named transactions, explicit rollback, enforcing transaction handling with snapshot isolation options) I realized that the solution works under open source jTDS driver and the latest version (1.3.1) doesn't cause problems like the ones we had initially while implementing MS SQL Server support in Tigase.

With jTDS driver I've run a couple of local tests, I've re-run both test scenarios that utilize pubsub and they pass without any issues/exceptions:

• TTS MS SQL Server

• NextPlane tests

In the end:

• I've updated version of jTDS driver that we ship with tigase

• Edited documentation and changed recommendation to jTDS.

Changed ticket to private. Who is/can be affected by the issue and who can benefit from the solution?

Artur Hefczyc wrote:

Changed ticket to private.

My bad - I thought I created the issue in Private project.

Who is/can be affected by the issue and who can benefit from the solution?

Currently only NextPlane - the fix was a result of their ticket. As far as I remember only CAR uses MS SQL Server with Tigase, however they were using different strategy which shouldn't result in same Exceptions that NP had. They switched to ClusteredNodeStrategy (pubsub) not long ago but we haven't had any report from them regarding possible Exceptions.

Before making any recommendations I would like to have this configuration run against our load-cluster (e.g. http://graph.cluster-c.xmpp-test.net/2015-08-20/) against MS SQL Server with jTDS driver.

Who is/can be affected by the issue and who can benefit from the solution?

Currently only NextPlane - the fix was a result of their ticket. As far as I remember only CAR uses MS SQL Server with Tigase, however they were using different strategy which shouldn't result in same Exceptions that NP had. They switched to ClusteredNodeStrategy (pubsub) not long ago but we haven't had any report from them regarding possible Exceptions.

What about CarNow? I think Zing Media can be affected as well.

Do they use Sql Server as well? They reported some problems too as far as I remember.

Before making any recommendations I would like to have this configuration run against our load-cluster (e.g. http://graph.cluster-c.xmpp-test.net/2015-08-20/) against MS SQL Server with jTDS driver.

Of course, we need to test it thoroughly.

Artur Hefczyc wrote:

Who is/can be affected by the issue and who can benefit from the solution?

Currently only NextPlane - the fix was a result of their ticket. As far as I remember only CAR uses MS SQL Server with Tigase, however they were using different strategy which shouldn't result in same Exceptions that NP had. They switched to ClusteredNodeStrategy (pubsub) not long ago but we haven't had any report from them regarding possible Exceptions.

What about CarNow?

They use MySQL.

I think Zing Media can be affected as well.

Do they use Sql Server as well? They reported some problems too as far as I remember.

They opted to use jTDS for MS SQL.

%kobit

On that note and talking a bit about communication - we may want to collect basic installation details - version, database, driver in order to provide updates to the customers.

Before making any recommendations I would like to have this configuration run against our load-cluster (e.g. http://graph.cluster-c.xmpp-test.net/2015-08-20/) against MS SQL Server with jTDS driver.

Of course, we need to test it thoroughly.

Could you change the load-cluster configuration accordingly (i.e. run it against MS SQL server with jTDS driver) and verify that it's working correctly?

Load tests will be run using jtds driver beginning tonight. However, rosters won't be complete until tomorrow's tests. I'm running a vim macro to convert rosters written by DBGenerator.groovy into something acceptable by MSSQL. Won't be finished in time for tonight's test.

can we modify groovy script to produce output compatible with MS SQL? I guess it doesn't like parenthesis in stored procedures invocations?

Yes we can modify the groovy script. Also I was using isql client and it wants each query on a single line.