March 9, 2016 at 6:09 am
Morning All,
So, the saga continues! Eventually found out from our outsourced developers the Service that connect to our SQL Databases are actually written in Java and use JDBC/JTDS - Despite when being asked they said SqlClient for .NET.
Has anyone any experience of JTDS on SQL 2014 AO AG's as it stands the connection will ONLY connect to the Listener when the listener is pointing to a very specific Instance. It refuses to connect on the other two nodes.
No one as yet can tell me what version of JDBC or JTDS is being run but the connection string is as follows:
<connection-url>jdbc:jtds:sqlserver://LN-PROD-001-IWFR:1433;databaseName=frog1;loginTimeout=0;multiSubnetFailover=true;prepareSQL=2;</connection-url>
And only works when pointing at a very specific node - BUT - I have proved the listener works to all nodes via Management Studio and my own Powershell Script.
They all listening on 1433.
Any thoughts anyone, or can I provide any more information?
Does anyone know a way of determining what version of JDBC and JTDS is being run, so far Google has not yielded anything for Windows Hosts.
Cheers
Alex
March 9, 2016 at 4:44 pm
March 10, 2016 at 1:23 am
Cheers! But that requires havng some Java skills in house, which we do not.
This is a third party application.
I don't even know if we have a Java compiler (and subsequently how to use it)
Cheers
Aex
March 10, 2016 at 4:54 pm
AFAIK JTDS doesn't support AlwaysOn/HADR in the sense that it won't handle failover as one would expect (it can still connect just fine). However, Microsoft's jdbc driver does and that is what we use to connect to any database in an availability group.
March 11, 2016 at 11:04 am
Jim_K (3/10/2016)
AFAIK JTDS doesn't support AlwaysOn/HADR in the sense that it won't handle failover as one would expect (it can still connect just fine). However, Microsoft's jdbc driver does and that is what we use to connect to any database in an availability group.
Good Catch Jim!
Back when mirroring was a thing :hehe: I had good link for the list of drivers that supported mirrroing.
If anyone reading this post has a similar link for the supported Availablity Group drivers, please post so I can add to my bookmarks.
March 15, 2016 at 4:55 am
Hi All,
I also read that JTDS doesnt support the automatic failover, but, I was hoping that, at best, the listener would always point to the primary replica and that simply restarting the client application (that uses java/jtds) would then connect to the listener again, whcih is now pointing at the new primary replica.
Automatic Failover/Transaction Retrying at this stage I am not too bothered about.
Just being able to point the app at the right replica without too much human involvement.
Cheers
Alex
September 22, 2016 at 7:15 pm
We're using jTDS java version 1.2.5. It doesn't support multisubnet failover but it doesn't need to. Multisubnet failover can be handled on the SQL side (we're using SQL 2014), as long as the SQL server login you're using has it's SID number matched (and you're using the Listener name).
How to match the SID's
From the Primary (where the username should already exist under Security | Logins and should already be mapped correctly to user database(s):
select SUSER_SID('the_username');
From the Secondary (where the username DOES NOT exist under Security | Logins but DOES exist under user databases due to synchronization):
Take the Hex ID from the first command and use it below:
USE master
GO
CREATE LOGIN [the_username] WITH PASSWORD = 'whatever', SID = yourHexNumberNoQuotes, DEFAULT_DATABASE = [sameDefaultDB_as_PrimaryLogin], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Go into Properties of the SQL Server username (Security | Logins) on BOTH servers and reset the passwords to match.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply