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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Solution provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.