Is there any way to get the alert without using any third party tool whenever the SQL Server Failover occurs from one node to other...
asked Aug 22, 2012 at 01:57 PM in Default
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
Step 4 : Create a job which execute the procedure at any specific time what you need....
answered Aug 24, 2012 at 12:15 PM
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)
answered Aug 22, 2012 at 02:44 PM