Skip to main content
To homepage To homepage

Is it possible to configure the default SQL connector Database Type "SQL Data Source" to use the Connection String property "ApplicationIntent=ReadOnly".   It does not appear to honor this setting as Timextender connects to the primary, not the read only replica.   We are confident in our Server side configuration as timextender is the only app we are having an issue with where an app does not connect to the read only replica when the connection property exists.  Is there another method for us to do this?


Thanks,


Luke

Have you tried this?

 

Hi Joseph,

Much apologies for the delayed reply.  First time using the forum and I thought I'd receive a forum update via email, should have checked earlier.  Thanks for the good suggestion but I have tried this already only since it is a global database I tried to configure it here:  

Thanks,

Luke

I got it working but I did find some unexpected behavior in Timextender.  This Microsoft article tipped me off to a connection string difference for OLE DB: 

https://techcommunity.microsoft.com/t5/sql-server-support/connect-to-sql-server-using-application-intent-read-only/ba-p/317758

It mentions:

1) When using SQL Native Access Client SQL OLE DB, the application intent connection string should be specified with a space : 'Application Intent'

2) When connecting using SQLClient or ODBC specify 'ApplicationIntent' with no space

As TX is using OLE DB I figured a "space" is what I needed to use.

Here is the strange part. To test this I changed my SQL connector from global to local and configured it using "Connections String Properties"

1) I included a space and I got this error:

2) and than as Joseph suggested above, using a local configuration but no space. Result is no error in TX GUI but SQL profiler did not show the read only replica being used.

So I tried

1) Global (no space), no error, "execute table" connected to primary db and not the read only replica

2) Local (no space), "execute table" connected to primary db and not the read only replica

3) Local (with space), GUI error, did not "execute table" as "test connection" failed

So the only option I did not try was "global (with space)" and figuring it would also not work but being curious to try, I went back to a global database configuration and added a space there

and surprisingly, on executing a redeployed table using that newly defined connection, TX now reads from the read only replica, as is confirmed by a SQL Profile.

Hope this helps anyone else that may run into this.

Thanks,

Luke

Interesting... Well glad you got it working and thanks so much for providing the useful info to others!

Reply


Terms and ConditionsCookie settings