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

  • 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.

  • 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

  • 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).

  • Have you tried specifying the port # explicitly in the connection string?

    "Server=xx.xx.xx.xx,port #"

  • 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 .

  • 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.

  • 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.

  • SIDs checked - they're the same.

  • 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?

    😎

  • 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.

  • This sound logically to me. Good hint. Thank you very much - I need to check this.

  • "When a SqlConnection is successfully opened, the failover partner name is returned by the server and supersedes any values supplied in the connection string” indicates the failover partner name is returned. This would be the same failover partner name that you use for the Primary/Mirror relationship.

    It sounds like in your case you would want to make sure that the application is able to contact the mirror based upon that failover partner name. If you run the query on the primary in the mirror:

    select * from sys.database_mirroring

    You can see the mirroring_partner_instance will be what is returned and what it is going to connect to. I hope this information helps.

  • I'm aware of this ... but the application can't connect to the mirror just by its name - when the port information is missing ... and this is in my opinion the root cause. When I failover the database to the secondary server, on the client computer I can see attempts for connections to the secondary server but ON THE DEFAULT SQL TCP PORT 1433 (via TCPView). The "only" question is why aliases and/or default SQL port don't work.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply