Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: Support ApplicationIntent=ReadOnly for SQL Server Connectors

Answered
Anthony Moquin User

SQL Server supports scaling out via read-only replicas to reduce load on a primary read/write server.  To take advantage of this feature, the connection string needs to include "ApplicationIntent=ReadOnly".

So, this is a feature request to enable Fivetran Connectors for SQL Server and Azure SQL Database to be configured so that the connection string will include this "ApplicationIntent=ReadOnly" parameter.

Thanks!

Please sign in to leave a comment.

Comments

3 comments

Date Votes
  • Official comment
    Kevin Kim Fivetranner

    Hi Anth,

    Thanks for your feedback! One of the key processes of our SQL Server replication is reading from either Change Tables or Change Data Capture.

    For Azure SQL specifically, we need to connect to the primary as AGs are not supported.

    One possibility is to read from a secondary for the import phase using the ApplicationIntent parameter, and then leverage the primary only for the change logs. This is certainly something we can discuss internally.

    We are always looking to improve performance for our connectors, and SQL Server is a priority for us. This feedback helps us make decisions for the product and is much appreciated!

    We don't have this on our roadmap at the moment, but I will bring it to the team for discussion.

  • Gil Milbauer User

    This should be part of better support for Change Data Capture from ReadOnly replicas. 

    Right now, we have to have a special feature flag when our connector is pointing to a secondary so that it will not fail trying to use Change Tracking on the read-only replica.  That shouldn't be necessary, and we should also be able to point to the Listener and Fivetran should know to use ApplicationIntent = ReadOnly (or we should be able to request it on the connector) so we won't need to change the connector after failovers.  I'm not sure why you would need to use the primary for change logs, either.  Everything is on the secondary, and you're not writing to the tables as far as I know.

  • Ben Sawyer User

    Hi team, can this please be revisited?

    I am syncing using "Teleport" sync method on Azure SQL using a mirrored copy of the db, which is read only access, and this is working fine (proving it doesn't need write access) except the additional cost required for this approach.

    Having ApplicationIntent=ReadOnly, I would be able to use one of the free read scale out servers that Azure offers in the premium compute tiers, significantly reducing my overall cost.