SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Client side automatic reconnect fails after DB failover when SQL Server on non-default ports


Client side automatic reconnect fails after DB failover when SQL Server on non-default ports

Author
Message
radek.hruby
radek.hruby
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 209
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.
Neeraj Dwivedi
Neeraj Dwivedi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1927 Visits: 1341
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
radek.hruby
radek.hruby
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 209
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).
Neeraj Dwivedi
Neeraj Dwivedi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1927 Visits: 1341
Have you tried specifying the port # explicitly in the connection string?
"Server=xx.xx.xx.xx,port #"
radek.hruby
radek.hruby
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 209
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 .
Neeraj Dwivedi
Neeraj Dwivedi
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1927 Visits: 1341
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.
radek.hruby
radek.hruby
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 209
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.
radek.hruby
radek.hruby
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 209
SIDs checked - they're the same.
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38500 Visits: 19424
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?
Cool
radek.hruby
radek.hruby
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 209
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search