There are a few important things to check for SQL Server Always On Availability Group.
Did the failover occur on any AG?
Does the transfer to the Secondary Database not continue?
You can define an alert for the actions I have specified above, and you will be able to send an e-mail when it happens.
Before this article, I would recommend you to read the article titled “SQL Server Best Practices and Configurations that need to be made After Installation“.
This article will guide you through information about Alert and Database Mail.
First, we will create an alert, it will be triggered when the availability group failed over and send us to mail.
Right click on the Alert tab under SQL Server Agent and click on new alert as below.
As you can see in the screen below, we write “Always ON Monitoring Role Change“ in Name. We write 1480 by selecting Error Number from the “Alerts will be raised based on” section.
Then, in order to send us an e-mail, we go to the Response tab which appears on the left. As shown in the following screen display, we select the Operator we have defined previously by selecting Notify operators and we mark the Email section.
I would recommend reading the “How To Configure Database Mail On SQL Server” and “How To Create An Operator On SQL Server and What is It” named articles to ensure that these operations work correctly.
And after these operations, we are doing the same things about the question “Does the transfer to the Secondary Server not continue?”. We only write 35264 in the error number part.
|
1 2 3 |
select * from sys.messages where --(language_id=xx or language_id=yy) AND text like '%availability%' |
First, we will create an alert, and then it will be triggered when the availability group failed over and send us to mail.
