In Always on AG How can we setup jobs with a failover
In SQL Server 2019, We have primary and secondary replicas. if primary goes down then secondary will become the primary server, In such case how to handle the SQL jobs.
Please help me to get it resolved
Here's a blog post I like that shows you how to add a first step to every job to check if you're on the primary node. If you are, the rest of the job runs. If you're not, it doesn't.
I don't like the new function, sys.fn_hadr_is_primary_replica, and prefer the older method of checking for primary because fn_hadr_is_primary_replica forces you to embed a database name that's in the AG. If that changes, your step behavior changes.
The query for the older method looks like this: SELECT ars.role_desc FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_groups ag ON ars.group_id = ag.group_id AND ars.is_local = 1
Be sure to set your backup preference appropriately for your environment and then just let your backups run, no need to check for primary on those.
Make sure that you push all new jobs and job changes to all servers. You can either automate that or do it manually, but make sure it's done.