how to do this script?

I have a always-on databases, I want to have a script to do

When the always-on availability  group failover is successful from primary to secondary, run this stored procedure. If not successful, do nothing. how to do that? I want to do that in an agent job or something, when the failover happened, automatically kick off this job. My always SQL is 2016 enterprise
wasabi3689Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't know if you developed the solution that I gave you but in case you didn't, I will give a push.
Start by creating the table where you'll store the last status:
CREATE TABLE AG_Status (
    LastStatusDateTime DATETIME,
    PrimaryReplica VARCHAR(200)
)

Open in new window


Then create a SQL Agent job that will run the following script every minute:
DECLARE @LastPrimaryReplica VARCHAR(200)

IF sys.fn_hadr_is_primary_replica ( 'db_name' ) = 1   -- Check if current instance is the Primary Replica for the database
BEGIN
    -- Get the last known Primary Replica server name
    SELECT @LastPrimaryReplica = PrimaryReplica
    FROM AG_Status

   -- Compare with the actual Primary Replica server name
   IF @LastPrimaryReplica <> @@SERVERNAME
   BEGIN
        -- if they are different means it changed so run the SP
        EXEC sp_storedproc_name 
    
        TRUNCATE TABLE AG_Status  -- delete all rows from status table
        -- and insert the new Primary Replica status
         INSERT INTO AG_Status (LastStatusDateTime, PrimaryReplica)
         SELECT GETDATE(), @@SERVERNAME
   END

END

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Problem is that SQL Agent jobs aren't triggered by actions so you can't really have this kind of solution.
You may have a SQL Agent job that runs every minute to check if the Primary Replica changed and then execute something when this validation is true.
0
 
wasabi3689Author Commented:
how to do that? how to check every minute in scripting?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, you'll need to store last status in a table and then compare it with the actual status. When differs then perform what you want to perform.
0
 
wasabi3689Author Commented:
need more detail coding you have before
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No code. I'm giving you an high-level solution.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Solution provided.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.