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