May 22, 2019 at 4:08 pm
We have bunch of SQL agent jobs and I have implemented a logic to it where it checks for the server status and if the server is primary, move to step 2, if not then exit gracefully. However, recently we have implemented a different logic. Each server we have is acting as a primary as well as secondary and I am pretty sure my logic is not for this design. So I am looking to get some help.
This worked before where 4 AGs on the server were working as primary or secondary
DECLARE @ServerState TINYINT;
SELECT @ServerState = role from sys.dm_hadr_availability_replica_states;
IF @ServerState = 1
BEGIN
RAISERROR('Server is Secondary', 11, 1);
END
Now this is what I have:
SQL01 server
AppdevAG-Primary
ComsAG-Secondary
BIAG - Primary
SQL02 server
AppDevAG - Secondary
ComsAG - Primary
BIAG secondary
SQL03 Server
AppDevAG - Secondary
ComsAG - Secondary
BIAG secondary
The logic I had only is only working on SQL03 but not on SQL01 and SQL02.
Question: How do I get past this issue? I can't find the best solution. Solution I have which checks for DB status (read/write or readonly), add that step before each job and based on that condition, run the 2nd piece of code. Any help?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 22, 2019 at 4:54 pm
You would need to change your logic to check at the database level using sys.dm_hadr_database_replica_states to determine primaries and secondaries.
Sue
May 22, 2019 at 6:00 pm
Not trying to be lazy but one of the server has 128 jobs and some of the jobs have multiple steps, some have SSIS packages. Am I to go through each job, look at the DB in a job step properties and then add this piece of code?
SELECT
d.name
FROM sys.dm_hadr_database_replica_states r
INNER JOIN sys.databases d
ON r.database_id = d.database_id
WHERE r.is_local = 1
and d.name = 'My DB 1'
If Primary, execute, else exit gracefully
SELECT
d.name
FROM sys.dm_hadr_database_replica_states r
INNER JOIN sys.databases d
ON r.database_id = d.database_id
WHERE r.is_local = 1
and d.name = 'My DB 2'
If Primary, execute, else exit gracefully
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 22, 2019 at 9:44 pm
Then do it based upon the availability group itself. You'd still need to modify the query. You didn't notice any issues when you tested the change of the setup to have the different availability groups replicas and primaries on the same instance?
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply