Availability Group failover and server trigger

Question

Availability Group failover and server trigger

Meunier, Alex 101

Anyone ran with this issue?

When failing over I get a timeout occurred during the failover process and it then returns to the initial server.
I have a "login server trigger" which does only save the current session login to a table. If I disable it it failover without issues. There is no rollback are anything in the trigger. I don't see any errors in the logs that might help to investigate the issue.

SQL Server 2019

Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator

2023-01-03T22:18:24.08+00:00

Keep in mind that the logon trigger is in the master database which is not part of the AG. That is, you may have different logon triggers on the two nodes in the AG.

You say that the login triggers saves to a table. Is that table in a database which is part of the AG?
David Hlaváček 1 Reputation point

2023-01-10T21:58:54.85+00:00

As Erland said, probably there is problem in trigger code. More info would be helpful... Do you have same trigger on both replicas? Where did you disable trigger to fix problem? On Primary replica or Secondary replica you are failing to? Is database where is trigger inserting data part of Availability Group? Can you connect to secondary replica where logon trigger is enabled and working (without any exeption for your login)? There can be unhandled error in trigger body. Maybe you can some exeption to trigger body like IF SUSER_SNAME()=<> DO NOTHING for service and cluster account. Failover Cluster is trying to do healtcheck and connection can be refused due trigger. Or you can share trigger body...