question

adkalavadia avatar image
adkalavadia asked

Random Issue with SQL Server 2012 Availability Groups Listener

Dear Friends, We are facing issue with SQL Server 2012 Availability Groups Listener. Please find below details. Two server with Windows Sever 2008 R2 Enterprise - Clustering Services
1) Server A (Physical Server) --> SQL Server 2012 2) Server B (VM Server from VMWare) --> SQL Server 2012 We have two groups AG Groups.
1) AGMain (LISNERMain, 192.168.111.XXX,1433) --> Primary on A, Secondary on B 2) AGReports (LISNERReport, 192.168.111.XXX,1433) --> Primary on B, Secondary on A When we use Listener in connection string in our application, In some case of DataReader connection will close & throw below error. **A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)** If we use server IP with same scenario, It works..when we use Listener..randomly it throw above error. Connection string : "user id=sa; password=pwd; Initial Catalog=DBNAME;Data Source=LISNERMain,1433;enlist=false;MultiSubnetFailover=True;MultipleActiveResultSets=True;Connect Timeout=15;Max Pool Size=200;"
sql-server-2012availability-groupswindows-server-2008-r2
7 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
Are you able to ping the listener? What version of .net is installed on the the application servers (do you have the latest updates)? Are you able to connect via SSMS?
0 Likes 0 ·
adkalavadia avatar image adkalavadia commented ·
Microsoft.Net 4.0. No issue in Ping breaking. Also we can connect through SSMS. The only issue we are getting while using DataReader. for latest updates, i need to check after 15-Mar-2013.
0 Likes 0 ·
sp_lock avatar image sp_lock commented ·
Have you followed the steps in the article below? http://support.microsoft.com/kb/2792139 If you haven't already, you may need to update 4.0.3 for .net 4.
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
Do the servers have multiple NICs and are the binding orders the same on both?
0 Likes 0 ·
adkalavadia avatar image adkalavadia commented ·
@sp_lock : i go through below link. http://support.microsoft.com/kb/2792139 Also we will update windows Server with latest updates.
0 Likes 0 ·
Show more comments

1 Answer

·
adkalavadia avatar image
adkalavadia answered
Dear Friends, Problem is identified as network issue. Two server with Windows Sever 2008 R2 Enterprise - Clustering Services 1) Server A (Physical Server) --> SQL Server 2012 NIC 1 --> LIVE IP = XXX.15.169.XXX NIC 2 --> Local IP = 192.168.15.111 2) Server B (VM Server from VMWare) --> SQL Server 2012 NIC 1 --> LIVE IP = XXX.15.169.YYY NIC 2 --> Local IP = 192.168.15.222 Listner IP is local IP 1) AGMain (LISNERMain, 192.168.111.21,1433) --> Primary on A, Secondary on B 2) AGReports (LISNERReport, 192.168.111.22,1433) --> Primary on B, Secondary on A We have disable both LIVE IP....It's working. Main issue was that, when we send query to LISTNER, It will resolve IP & forward query to SQL SERVER. So In our case, both Subnet are different..so sometimes it can't resolve Listner & query was failing. Special Thanks to @Blackhawk-17. Regards, Ankur
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.