2

When new DB is added to SQL Server's AG setup, a notification email like below is sent to us.

The availability group database "XXXX" is changing roles from "SECONDARY" to "SECONDARY" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only.

What is confusing is the phrase

"changing roles from "SECONDARY" to "SECONDARY" ..."

  1. Can someone please decode the meaning of it?
  2. Is it normal that a notification of such is sent when new DBs are added or am I doing it wrong or any other issues ?

--In 'thoughts'...

  • 1
    notification email like below is sent to us can you please add more details. Where this notification is coming from? Is it in a script? In GUI I do not see an option of getting email notification. – SqlWorldWide Sep 17 at 19:10
  • 2
    @SqlWorldWide It looks like the message from having SQL Server Agent alerts set up for error number 1480 (AG role change). It's pretty common on AG systems. – Josh Darnell Sep 17 at 20:24
  • Yes, SQL Server Agent > Alerts are configured for AG related notifications. – LonelyRogue Sep 26 at 12:34
7

...a notification email like below is sent to us

Sounds like you have alerts set up for error number 1480. This is a good thing to have on systems that use Availability Groups.

Can someone please decode the meaning of it?

When the message says "SECONDARY" to "SECONDARY" like that, it normally means there was just a brief disconnect between the PRIMARY and the SECONDARY, and the connection was re-established just before the error was raised. It's essentially bad timing.

Is it normal that a notification of such is sent when new DBs are added or am I doing it wrong or any other issues ?

I wouldn't call it "normal" but I don't think it's a cause for alarm, or something that you're explicitly causing. I have heard that this can happen from time to time, especially on VMs with low core counts (1-3 cores).

I've personally experienced this more when adding a database to an AG with automatic seeding turned on. I haven't seen any problems arise related to this no-op "failover" message.

  • Thanks @Josh Darnell . So, these alerts aren't sent because of addition of new DB to AG but because of network glitch which was a coincidence. Will see it happens again when I add another DB and report the findings. – LonelyRogue Sep 26 at 12:36
  • 1
    @LonelyRogue Right, they are not because of adding the new DB. It's not necessarily a network glitch, it could be anything that delays the connection acknowledgement (including the network, or high CPU usage, or oversubscribed VM, etc). – Josh Darnell Sep 26 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.