How to get alert when the SQL Server failover occurs ?

Step 1 : First create table with name "CLUSTERFAILOVERMONITOR" with one column "PreviousActivenode"

Step 2 : Insert the value of Current active node

Step 3 Create the procedure which check the value of current active node and value from the table if it found difference it will send the alert

Create proceudre Clusterfailovercheck 
as 
Declare @var1 varchar(30)
SELECT @var1= PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR

-- Creating the table which store the current active node value

CREATE TABLE PHYSICALHOSTNAME
(
VALUE VARCHAR(30),
CURRENT_ACTIVE_NODE VARCHAR(30)
)

INSERT INTO PHYSICALHOSTNAME
exec master..xp_regread 'HKEY_LOCAL_Machine',
'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\',
'ComputerName'

declare @var2 varchar(30)
SELECT @VAR2=CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME

if @VAR1<>@VAR2

Begin

EXEC msdb..sp_send_dbmail @profile_name='DBAMail',
@recipients='basit.khan@company.com',
@subject=' Failover occurrence notification - InstanceA,
@body='Cluster failover has occured for instance InstanceA. Below given are the previous and current active nodes.',
@QUERY='SET NOCOUNT ON;SELECT PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR;SELECT CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME;SET NOCOUNT oFF'

update CLUSTERFAILOVERMONITOR set PREVIOUS_ACTIVE_NODE=@VAR2

End

DROP TABLE PHYSICALHOSTNAME

Step 4 : Create a job which execute the procedure at any specific time what you need....

There are a couple of methods.

You could create a startup stored procedure that executes when SQL restarts which captures the restart and emails out to you, or create it as a SQL Agent job that runs on startup. IF you go with the proc ensure that you put in a wait for 10 seconds or you might try to send the email before the email engine has actually started.

Here's basic code to send the information on the restart and include the node that the instance is currently running on (you will need database mail already configured)

DECLARE @node VARCHAR(128) = CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))

DECLARE @header VARCHAR(128) =  'SQL Server '+ UPPER(@@servername) + ' restarted!'
DECLARE @Message VARCHAR(max) = 'SQL Server '+ UPPER(@@servername) + ' restarted.

Now running on node: ' + @node 


exec msdb.dbo.sp_send_dbmail @profile_name = null --uses the default profile
     ,  @recipients = 'youremail@yourcompany.com' 
     ,  @subject = @header
     ,  @body =  @message
     ,  @body_format = 'HTML' --default is TEXT