This feature request is for the Generic SQL Server/Azure SQL Managed Instance/RDS SQL Server connectors. (Not the HVA SQL Server Connectors).

These SQL Server connectors use Change Tracking or Change Data Capture to read rows from SQL Server. The connectors do not currently support using Snapshot Isolation in SQL Server to do reads.

This means that when the database using the Fivetran connector is operating under the default implementation of Read Committed, the Fivetran connector's reads will  use row, page, and even table level locks when it reads data. This creates frequent scenarios where the Fivetran connector will block writes made by user applications. This happens frequently in the following  scenarios:

1. Initial sync of data
2. Data cleanup scripts have run to archive or remove unneeded data
3. Data modification scripts stage / write new data to be used by new features
4. Data modifications scripts correct data (or bring it into a state consistent with current features)
5. Data is imported into a new environment (consolidations or customer project)
6. New columns are added to a table and a script needs to populate the data for every row

The Snapshot Isolation feature in SQL Server uses automatic versioning to allow applications like Fivetran to read data consistently without blocking modifications that are coming in. The fivetran connector could either:

• Check if Snapshot Isolation is allowed at the database level, and if so, automatically  use it. (Snapshot_isolation_state will be 1 in sys.databases if this is enabled). I can't think of a reason why anyone who has snapshot isolation enabled in a database would NOT want fivetran to use it when it's available, so this seems pretty safe to me.
• Add an option into the connector to allow a user to say to use Snapshot isolation if it is enabled in the database. (This doesn't seem necessary to me but it would make the feature more visible to users / raise awareness of it.)

Microsoft recommends using Snapshot to avoid problems encountered due to race conditions with Read Committed as a best practice, so enabling this would also bring the fivetran connector into best practices they recommend for SQL Server. (Docs page: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server?view=sql-server-ver16#obtain-consistent-and-correct-results)

Thanks!

Kendra