Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Client side automatic reconnect fails after DB failover when SQL Server on non-default ports Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2014 3:19 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 1, 2014 3:50 AM
Points: 777, Visits: 131
Hi experts, I have the following issue:
Three SQL Server DEFAULT instances with a database DBX (on two of them - Primary, Secondary) configured for high safety mirroring with automatic failover where all three listens on port TCP:2444 instead of the default TCP:1433 (company security policy).

Primary server (should be Principal for 99.999% of the time): SQL1.company.com,2444
Secondary server (should be usually Mirror): SQL2.company.com,2444
Witness: SQL3.company.com,2444

There is an application AppX that uses the database DBX using an ODBC driver where both connection strings configured properly i.e. SERVER=SQL1.company.com,2444 and FAILOVER_PARTNER=SQL2.company.com,2444. In case of failover this application doesn't reconnect automatically to the Secondary server (acting as Principal that time). However if I restart the application manually (either only the application or the whole server with the application installed) it connects successfully to the Secondary server. In case of failback (i.e. primary server becomes Principal again) everything works OK and the application reconnect to the Primary server automatically. I'm aware that in case of successful connection to the Primary (when the application starts and Primary is Principal), the instance sends the address of the secondary server which is then cached at the client side and supersedes the information specified in the ODBC file. The problem is that this address sent seems to not contain the port information. As the result in case of a failover the client is attempting to connect to the proper address i.e. SQL2.company.com but on the default port 1433 NOT 2444 which causes the issue.

1) I tried to change the default SQL Server port on the client to 2444 - didn't help (and I'm pretty certain this should work)
2) I tried to create two aliases on the client ("SQL2" -> "SQL2.company.com,2444" and "SQL2.company.com" -> "SQL2.company.com,2444") - didn't help (again - it should)
3) I changed the port on the secondary server to the default 1433 - this works as expected but not acceptable by the company security policies
The client still attempts to connect to the secondary server on the default port when failover occurs. I'm out of ideas.
Post #1594976
Posted Tuesday, July 22, 2014 5:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:47 AM
Points: 839, Visits: 1,198
Are the SID'd for the application user on primary & mirror server are same and also default DB for that user is set?

I would suggest to test this via failing over to mirror and try to connect via application user credentials?

Please see the below thread:-

http://www.sqlservercentral.com/Forums/Topic1451617-1549-2.aspx
Post #1595016
Posted Tuesday, July 22, 2014 6:22 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 1, 2014 3:50 AM
Points: 777, Visits: 131
Hi Neeraj, unless I oversight something important this is not a credentials related issue. If I restart the application when the Secondary server is the Principal - it connects with no problem. I was running TCPView on the client machine and saw that the application attempted to connect to SQL2.domain.com,1433 where the server listens on 2444. The root cause seems to be that initially the Principal returned "SQL2.domain.com " to the client rather than "SQL2.domain.com,2444" and this returned value got cached and used then.

Also I can't see any login errors on the Secondary server (neither in SQL Error log nor in Security log).
Post #1595052
Posted Tuesday, July 22, 2014 6:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:47 AM
Points: 839, Visits: 1,198
Have you tried specifying the port # explicitly in the connection string?
"Server=xx.xx.xx.xx,port #"
Post #1595063
Posted Tuesday, July 22, 2014 7:00 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 1, 2014 3:50 AM
Points: 777, Visits: 131
As mentioned in my initial post:

>>... ODBC driver where both connection strings configured properly i.e. SERVER=SQL1.company.com,2444 and FAILOVER_PARTNER=SQL2.company.com,2444<<

Again - both connections work when the application starts/restarts. Only the automatic failover from Primary to Secondary when the application is running doesn't .
Post #1595065
Posted Tuesday, July 22, 2014 7:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:47 AM
Points: 839, Visits: 1,198
If you are able to connect after restart, then mismatching SID's could be only thing which I can think of. We had same issue in our environment and after SID's were mapped and the application user's default DB was set, it got resolved.

If that is already set, I couldn't think of anything else but I will try this in my testing env when I get some time and will let you know. Sorry I couldn't help.
Post #1595068
Posted Tuesday, July 22, 2014 7:28 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 1, 2014 3:50 AM
Points: 777, Visits: 131
I can't check this right now but I'll will ... there is a question then ... assuming you're right - why during failback (Primary becomes again Principal and secondary Mirror) everything works without restarting the application? Also why this works when the secondary reconfigured to listen on default port 1433?

It really seems that the main issue here is the non-default port rather than anything else.
Post #1595077
Posted Monday, August 25, 2014 6:27 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 1, 2014 3:50 AM
Points: 777, Visits: 131
SIDs checked - they're the same.
Post #1607032
Posted Monday, August 25, 2014 6:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:20 AM
Points: 2,533, Visits: 7,115
radek.hruby (7/22/2014)

2) I tried to create two aliases on the client ("SQL2" -> "SQL2.company.com,2444" and "SQL2.company.com" -> "SQL2.company.com,2444") - didn't help (again - it should)


Quick questions, did you create these aliases as the application user? Did you try to connect as the application user to these aliases?
Post #1607034
Posted Monday, August 25, 2014 6:37 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 1, 2014 3:50 AM
Points: 777, Visits: 131
Hi, I have the following answer:

When connecting to a mirrored database SQL client always attempts to connect to the primary connection specified in its configuration file first (Server=xxxx). This is why the failback works. If it can't connect to this server/database it will go for FAILOVER_PARTNER but this value has been overridden by the address sent by the PRIMARY server.
Post #1607036
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse