2

We have our clusters set to High Availability and manual failover. I have an alert to email DBAs whenever a failover occurs. What we would like to also see in the email is WHY and/or WHO. We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong. Right now the email is "The availability group database "TESTIN2" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required." Thanks

  • 1
    Instead of having your Alert fire off an email directly, you could instead set it up to run a job that would search through the error log looking for the relevant information and email that out via sp_send_dbmail – John Eisbrener Jun 13 '17 at 17:56
  • I did something similar to test. One of the alerts I created to test has a job it triggers that I added this step to DECLARE @sub nvarchar(100) SET @sub = @@SERVERNAME + ' AG Changed' Declare @Email_body nvarchar(100) Set @Email_body = 'Availability Group has been changed by ' + SYSTEM_USER BEGIN EXECUTE msdb.dbo.sp_send_dbmail @recipients= '', @body= @Email_body, @subject = @sub, @profile_name ='DBA Mail'; END however not sending me the person who initiated it but the job owner. I checked audit and error logs, how do I add the user who did it in the email sent – CrashWave Jun 13 '17 at 18:39
3

What we would like to also see in the email is WHY and/or WHO.

Then you have quite a good bit of development work ahead of you. Seriously.

Everyone asks this same question, but it is not an easy one to answer with any more resolution than "It failed over at this time." Things such as virtualization make this even harder as the tools and the hypervisor itself may do things that are outside the purview of your sandbox.

However, to get you started, places to scrub for data:

  1. SQL Server Errorlog
  2. Application Event Log
  3. System Event Log
  4. Cluster Log/Clustering Event Log
  5. Hypervisor Logs/Database
  6. Internal Lists of Downtimes/Upgrades/Patches/Etc.

Best of luck.

We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong.

That's an organizational information sharing/Administration issue and not one you're going to have much help in dealing with through a simple SQL Server Alert. In fact, to do this properly, you'll want to take SQL Server out of the picture!

Most likely you're going to need to write a service that constantly consumes event logs, traces, error logs, etc., and then takes action when issues occur.

2

From my experience, when a user initiates a failover, it is not logged. You could spend hours searching the logs, but it won't tell you who did it. If you only want specific people access to the AlwaysOn dashboard, you will want to configure your security settings within the instance. If someone rebooted the server housing the primary replica, you can see when the failover happened and then look in the System Logs for a USER32 event which will tell you who rebooted the server. Other than that, you could use login auditing to see who was connected to the instance but it could be a guessing game.

The lack of error logs lets you know it is a user (either manual failover, or reboot). On the other hand, if it was caused by an error you will definitely see explicit errors in places that Sean mentioned.

2

I would look at setting up an extended event to track where the sql_text for the batch_starting, sql_statement_starting, alwayson_ddl_executed, or availability_replica_state_change events. The top image is a view of the live data for the XE, and the image below is what was logged. I blurred out my info, but the user names are present to show who issued the failover command.

Live Data in SSMS

sql_batch_starting event details

Code to create the XE that I used (probably overkill on the events I included, but it worked for me):

    CREATE EVENT SESSION [Audit_Availability_Group_Failovers] ON SERVER 
ADD EVENT sqlserver.alwayson_ddl_executed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.availability_group_lease_expired(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.availability_replica_automatic_failover_validation(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.availability_replica_manager_state_change(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.availability_replica_state(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.availability_replica_state_change(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.hadr_undo_of_redo_log_scan(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%'))),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER AVAILABILITY GROUP%FAILOVER%')))
ADD TARGET package0.event_file(SET filename=N'D:\Audit_Availability_Group_Failovers.xel'),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
0

At the same time you need to check Windows Application Event log (ID-19406). It will show the same log with additional info like User who initiated the failover.

Log Name:      Application
Source:        MSSQLSERVER
Date:          4/7/2018 8:59:15 AM
Event ID:      19406
Task Category: Server
Level:         Information
Keywords:      Classic
User:          ABC\TestUser
Computer:      abc.local.com
Description:   The state of the local availability replica in availability group
'XXXXX' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'. The
replica state changed because of either a startup, a failover, a communication issue,
or a cluster error. For more information,  see the availability group dashboard,
SQL Server error log, Windows Server Failover Cluster management console or Windows
Server Failover Cluster log. 
-1

If there is a manual failover then below log gets captured in SQL error logs

Message The state of the local availability replica in availability group 'XXXXXXXXXXX' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'. The state changed because of a user initiated failover. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

But if you further look into Event viewer and AlwaysOn health events, besides below nothing gets captured and that sucks because if you are looking to find the person who initiated the fail over there is just no other way

AlwaysOn Health Events

statement ALTER AVAILABILITY GROUP [XXXXXXXXXXX] FAILOVER;

Event Viewer The state of the local availability replica in availability group 'XXXXXXXXXXX' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'. The state changed because of a user initiated failover. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

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.