Share via

Issue with Inserting to Table on replicated server

Michael Baker 1 Reputation point
2022-10-19T20:12:23.573+00:00

I have an Always On Availability Cluster, inserting on one instance works fine but when the Availability Group fails over inserts fail into the table on the new primary instance. Failing back to the old primary then works fine again.

The tables are identical tablediff returns no differences, anyone have any thoughts on what might cause such behavior? The error being reported at the client is
Error On OK. Error Retrieving Data.

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The version is 11.0.7469.6

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bjoern Peters 8,921 Reputation points
    2022-10-19T21:44:59.357+00:00

    Hi @Michael Baker

    My first thought was … what about the users and their permissions?

    Do you have a job in place that synchronize all users and their permissions between those instances? Or how do you make sure both instances have the same rights?

    Often customers forget that they have to establish a process for this!

    Make sure that writing user has the same roles/permissions on all instances!

    Was this answer helpful?

    1 person found this answer helpful.
  2. Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator
    2022-10-21T21:02:59.373+00:00

    Version 11.0.7469.6

    So if by chance, there was another thread with the same problem a few days back: https://learn.microsoft.com/en-us/answers/questions/1054689/high-availability-insert-fails-on-primary-replica.html. And even more by chance, the SQL version is exactly the same. I suspect the other poster is your colleague. :-)

    When we run it on the current active node runs in 11 ms on node with issue times out in ssms after 30 secs and times out in application after the timeout set in the connection string

    You cannot set the query timeout in the connection string, only the connection timeout which is something else.

    By default, SSMS does not use a timeout in a query window. But you can set one if you wish.

    I know the exact statement that causes the issue. It is an insert into a table with a text field, where the text field being inserted is ~ 2.8 MB

    But is essentially insert into TableA (DataField) values ('a lot of text in xml format')

    This is useful information. Before I dive into speculations of what might be going on, I like to clear a few things out:

    1. So that INSERT is just an INSERT of a constant value, and there is no subqurey with SELECT?
    2. Are there any triggers on the table?
    3. What does DBCC TRACESTATUS report on both nodes?

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.