-
Bug
-
Resolution: Unresolved
-
Major
-
3.1.2.Final, 3.2.1.Final
-
None
-
False
-
-
False
-
Critical
In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.
Bug report
For bug reports, provide this information, please:
What Debezium connector do you use and what version?
SQL Server CDC Source connector 3.1.2
What is the connector configuration?
{"connector.class":"io.debezium.connector.sqlserver.SqlServerConnector","database.applicationIntent":"ReadOnly","database.encrypt":"false","database.hostname":"sql-listener","database.port":"1433","database.names":"testdb","database.password":"XXXXXXXX","database.user":"XXXXXXXX"}What is the captured database version and mode of deployment?
SQL Server 2022 Enterprise Edition using availability groups and enabled for read-only routing to direct ReadOnly intent connections to the secondary replica followed by the primary replica. The CDC database does does not allow snapshot isolation level (ALLOW_SNAPSHOT_ISOLATION = OFF)
The SQL Server and Connect instances are on-premises
What behavior do you expect?
When the connector's configuration specifies "database.applicationIntent":"ReadOnly", the connector should NOT attempt to explicitly set snapshot isolation level unless it's specified by snapshot.isolation.mode
What behavior do you see?
In the configuration described above, Debezium will explicitly set the transaction isolation level to snapshot if ReadOnly intent is specified.
If the SQL Server secondary replica fails, the connection will go to the primary read-write replica. If the database does not allow snapshot isolation, any connector query will fail with the message "Snapshot isolation transaction failed accessing database 'database' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation". This effectively negates availability groups high availability features for Debezium connectors with this SQL/Debezium configuration.
Debezium's unnecessary snapshot isolation behavior is documented here: https://debezium.io/documentation/reference/stable/connectors/sqlserver.html#sqlserver-always-on-replica
Aside from causing a failure in this scenario, explicitly setting the isolation level to snapshot should not be necessary. Per Microsoft: 1) Connections to secondary replicas always and automatically map to snapshot isolation. 2) Any attempt to set an isolation level against a read-only secondary is ignored and disregarded (without error) therefore it is safe to not override the driver's default or client's setting from snapshot.isolation.mode when using read-only intent. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver17#bkmk_Benefits
Do you see the same behaviour using the latest released Debezium version?
Yes, also reproduced on 3.2.1 Final
Do you have the connector logs, ideally from start till finish?
Yes, I can provide logs
How to reproduce the issue using our tutorial deployment?
Create a 2-node SQL Server availability group with read-only routing configured with routing order of secondary first, then primary
Create CDC enabled database, ensure that ALLOW_SNAPSHOT_ISOLATION is OFF for the database
Simulate an infrastructure failure by halting the secondary replica instance
Deploy a connector that specifies the availability group listener as the database.hostname and database.applicationIntent = ReadOnly
Debezium will log errors that it cannot query the database using snapshot isolation
Feature request or enhancement
For feature requests or enhancements, provide this information, please:
Which use case/requirement will be addressed by the proposed feature?
<Your answer>
Implementation ideas (optional)
Remove the bahavior where Debezium explicitly sets snapshot isolation when using read-only intent