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

TheBugSlayer 1 Reputation point

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.

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,584 questions
{count} votes