Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

High Availability - insert fails on primary replica but runs on secondary after failover.

Status
Not open for further replies.

TheBugSlayer

Programmer
Sep 22, 2002
887
0
0
US
We have an HAG configured with 3 servers running Microsoft SQL Server 2012 (SP4-OD) (KB4091266) - 11.0.7469.6 (X64) Feb 28 2018 17:47:20 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor).

S1 normally functions as the primary replica and S2 as the secondary, with the roles switching on failover. S3 is always secondary and is used mostly as a report server. Applications connect to the databases through the listener.

All 3 servers are configured almost exactly the same way with S1 having a bit more RAM. They are part of the same virtual environment, with consecutive IP addresses.
However, users of a particular application have trouble inserting/updating some tables when S1 is the primary replica, to the point where the transaction simply times out and fails consistently. The same transaction consistently works when S2 is the primary replica and S1 becomes secondary as a result of a failover, which is what we’ve resorted to doing as a solution but we would prefer S1 be the primary or at least be able to failover in whatever direction. The main offending table only has 1100 rows with a couple of TEXT fields.

We’ve tested every theory that we could come up with to no avail. SQL 2012 EOL therefore not supported by Microsoft.

The image below depicts the current AG configuration.
WP_HAG_nog1ev.png



I need help understanding what could possibly be causing the behavior described above and how it could be fixed. I am willing to provide more details if necessary.
Thanks much!




MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top