SQL Server connector doesn't handle retriable errors during database state transitions

An SQL Server database may be switched to the read-only mode which on its own doesn't make it unusable for the connector but the process of transition does.

Success scenario

First, a simple scenario that the connector handles properly:

1. Start a pipeline from the tutorial.
2. Execute the following in the database:

   ALTER TABLE testDB SET READ_ONLY WITH ROLLBACK IMMEDIATE
   

In this case, the connector will encounter the following error and handle it as retriable:

com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed.

By the time when the task has restarted, the database will be already available, and the task will successfully resume.

Failure scenario #1

Now, a more complex scenario when the transition takes longer than the task restart.

1. Start the same pipeline.
2. Start a transaction in testDB in one client session:

   USE testDB;
   BEGIN TRAN;
   

1. In another session, switch the database into read-only mode. This command will block since there is a transaction in the previous session:

   ALTER DATABASE testDB SET READ_ONLY;
   

1. Restart the connector task:

   $ curl -i -XPOST localhost:8083/connectors/inventory-connector/tasks/0/restart
   

The task will fail with:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "testDB" requested by the login. The login failed.

Failure scenario #2

This is similar to the previous one but with a slightly different error message.
Replace database.dbname: testDB with database.names: testDB in the connector configuration. This will turn the connector into multi-partition mode. Now, in the above scenario, the error will be:

com.microsoft.sqlserver.jdbc.SQLServerException: Database 'testDB' is in transition. Try the statement later.

Additional information

A real production scenario may look like the following: a database administrator will execute something like:

ALTER TABLE testDB SET READ_ONLY WITH ROLLBACK AFTER 30 SECONDS 

During these 30 seconds, the database will wait for all transactions to complete but won't allow any new transactions. This is when one of the above errors may occur.