ApplicationIntent:Readonly in tiny_tds?

[SebastianGarin]

Is this possible? I've tried several ways of adding this string to the connection and it isn't documented.
I'm trying to connect to MSSQL high availability group.
Any clue/idea around this will be greatly appreciated.
Thank you

[metaskills]

You will have to provide more information. Do you have any links on what you are talking about?

[SebastianGarin]

Hi, I want to use the connection string that is required in order to connect to a high availability group.
This string makes the following: "Connections with the "ReadOnly" property set in the connection string will be redirected to the secondary replica"

The "ApplicationIntent=ReadOnly" is added to the connection in this tab:

http://blogs.msdn.com/cfs-filesystemfile.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-84-42-metablogapi/7610.clip_5F00_image007_5F00_thumb_5F00_3589CC7F.png

Without this I cannot connect to the MSSQL DB.

When trying to connect, the error given by tiny_tds is:

/var/lib/gems/1.9.1/gems/tiny_tds-0.6.2/lib/tiny_tds/client.rb:77:in connect': The target database ('database') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online. (TinyTds::Error) from /var/lib/gems/1.9.1/gems/tiny_tds-0.6.2/lib/tiny_tds/client.rb:77:ininitialize'
from MSSQL.rb2:5:in new' from MSSQL.rb2:5:in

'

Again, I couldn't find any documentation regarding this.

Thanks,

[metaskills]

Very interesting. Thanks for the details. I did some Googling on "freetds application intent read only" and found some mentions that you have to use a user that only has readonly access. Is this the case for you? Also, I just found this too.

https://gitorious.org/freetds/readintent-freetds/source/10a7f55ed3ac2b095910226f40768ef4a91e8e83:

If this is true, I will have to go digging in the FreeTDS code and make some changes. This will take me a few weeks since I am working on the latest 4.2 adapter now.

[SebastianGarin]

Yes, I'm a read only user on that db. Should there be a way of configuring freetds and use it as a "DSN" via tinytds?
Thank you for your feedback Ken

[metaskills]

configuring freetds and use it as a "DSN"

I did see some things mentioned but they only be used when FreeTDS is compiled with ODBC and hence set in a ODBC specific configuration. This project built on top of FreeTDS but is all about avoiding RubyODBC entirely and the two are not the same past sharing FreeTDS. Hope that makes sense.

Either way, I do think that commit above shows that we can indeed set a simple login bit based on a new config option. The work is very likely simple, I just need to finish the adapter work first.

[SebastianGarin]

Excellent, thank you for looking into this.

[SebastianGarin]

Hi Ken, any news on this? I know you are alone working on this, just curious.
Thank you

[metaskills]

None yet, was working on SQL Azure stuff over the past few days. Will get to this soon.

[metaskills]

DIFF https://gitorious.org/freetds/readintent-freetds/commit/10a7f55ed3ac2b095910226f40768ef4a91e8e83

[SebastianGarin]

I have searched for hours, and never found that! If I change the highlighted code it should work with application intent?
Thank you so much

[metaskills]

No, the idea is that we have to enhance the TinyTDS connect implementation to accept a new read_only config option. Much like we did for azure. That said, we have to find out if there is a DBLIB interface for even doing this. I will have to read over the FreeTDS source and that DIFF to find out if that is the case.

Also, the commit message does indicate that this may not even work? The words SHOULD ignore kind of hit me front and center.

[SebastianGarin]

I've sent an email to Tapas, will let you know what does he replies.

[SebastianGarin]

He told me that he used it on freetds, not tiny_tds - he sent me some C code but Its for freetds, not tiny.

[SebastianGarin]

Hi Ken, sorry for bringing this up again, any news?

[SebastianGarin]

Hi Ken, any news?