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.
First, a simple scenario that the connector handles properly:
- Start a pipeline from the tutorial.
- Execute the following in the database:
In this case, the connector will encounter the following error and handle it as retriable:
By the time when the task has restarted, the database will be already available, and the task will successfully resume.
Now, a more complex scenario when the transition takes longer than the task restart.
- Start the same pipeline.
- Start a transaction in testDB in one client session:
- In another session, switch the database into read-only mode. This command will block since there is a transaction in the previous session:
- Restart the connector task:
The task will fail with:
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:
A real production scenario may look like the following: a database administrator will execute something like:
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.