October 10, 2018 at 4:32 am
We are using SQL Server mirroring since more than 10 years on different OS and versions of SQL Server starting from SQL Server 2005 to SQL Server 2012 Enterprise Editions with similar pair of Primary and secondary servers with no issue. Recently we had an opportunity to get SQL Server 2016 setup for an existing application which was running on .net version with SQL Server 2008 as database version.
We configured two same vm machines Primary (192.168.100.10) and Secondary (192.168.100.11) on Windows Server 2016 with SQL Server 2016 and the web application on windows server 2003 on IIS6 (10.200.12.18) and SQL Server 2008 Enterprise Edition with SP2 installed as a previous installation. Actually we wanted to separate DB Server from web application so we setup 2016 version to run with this web application.
We configured the connection string, the port/telnet tested from Application server (10.200.12.18) to SQL Server 2016 (Primary Server on 192.168.100.10) with no issue and web application was running since last 4 months. We had configured the mirroring with another SQL Server (192.168.100.11) - All was perfect with no issue.
Due to policy we had to run manual Fail-over switching so we did manual fail-over last week and then replaced the IPs of both server (192.168.100.10 -> 192.168.100.11) and (192.168.100.11 -> 192.168.100.10) so that the application web.config file does not need any change, we replaced DNS names on AD domain as we do before on others before.
This is a normal practice and we had done this several times before we face connectivity issue on fail-over server. There was no issue with telnet on port 1433, we use default port and configuration was same as on 10 & 11 servers, we can connect database servers from 10 to 11 server and from 11 to 10 with no issue and use Management Studio without any issue. But when we connect web application, it started giving following error:
"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)"
Even though, if I enter wrong userid or password, it still gives me same error. There is no any error appearing in SQL Server and event log of database. I can till connect the previous machine which was before fail-over from web application but as the database is now set to recovery mode due to mirroring secondary server, we can't run application, we are stuck and after revert, the application started to working as normal. See attached files for error details. Remember that both machines are same (.10 and .11), one is connecting from web application and other is no connecting.
Can anyone help me on this issue?
October 11, 2018 at 10:29 pm
The fact its set in recovery mode sounds like a comms issue - the two servers I think cant see each other properly. AGs will do the same thing when one AG node drops out.
Have you checked the windows firewall on each server?
Also check whether DNS has old name/IP values that might need to be removed manually as it may be confused due to the IP change.
If it still doesnt work, you may have to drop & set up the mirroring again to confirm it will talk properly between the machines with new IP addresse and confirm each machine can find the other.
October 11, 2018 at 11:26 pm
Two servers (windows server 2016/SQL Server 2016) can see each others, mirroring was working too. Both machines can access each other using management studio. However the old machine having the web application running under IIS6 (windows server 2003) can't access the newly fail-over server, as it was tried using management studio of SQL Server 2008. The web application is using IP which is supposed to be not changed as we switched the IPs after db fail-over activity, This is normal practice and we had done the same thousands of times before with other versions of SQL Server with different environment, no issue like this one we experienced first time. After searching, nothing fixed the issue, due to live issue we reverted back the mirroring, web application started working as usual.
We do replaced the IP/DNS names on AD. as its normal. I still see its network issue but I can't proof yet.
October 11, 2018 at 11:36 pm
Sounds like the web server still has only the IP for the SQL Server it can currently see, either that or your DNS is faulty.
If the web server only works with one SQL box, it has to be network / config related.
Can the web server ping both SQL boxes? Has the web server had its route table chnaged?
October 14, 2018 at 10:50 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply