Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-9369

SQL Server availability group high-availability is not supported with read-only intent due to explicit snapshot isolation level

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Major Major
    • 3.4.0.Alpha1
    • 3.1.2.Final, 3.2.1.Final
    • sqlserver-connector
    • None
    • False
    • Hide

      None

      Show
      None
    • 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

              Unassigned Unassigned
              jdtrei Jeremy Treinen (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated: