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