we have 3 servers, 1 one of them host a primary and a secondary aoag, 2 also hosts a primary ( secondary on node 1 ) and a secondary (primary on node 1). 3 is just DR. All of them have the same jobs.
I'm trying to understand how can we run jobs on certain databases and determine if this database is primary on this server or not. I would like to achieve this without hardcode anything.
I have these 2 queries but they only used to work when I have an entire server as primary or secondary:
if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = 'YourAvailabilityGroupName'
and ars.is_local = 1) = 'PRIMARY'
begin
-- this server is the primary replica, do something here
end
else
begin
-- this server is not the primary replica, (optional) do something here
end
--ou
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'PRIMARY'
BEGIN
PRINT 'OK. NEXT...'
END
else
RAISERROR ('ERROR. JOB IS NOT GOING TO RUN BECAUSE THIS IS NOT THE PRIMARY INSTANCE.', -- Message text.
16, -- Severity.
1 -- State.
);
In this case now, I have multiple databases being primary and secondary (not readable).
so when runnin a job in database X I would like to run a function for example to determine that this database is not primary on that server, so the job stops:
for example:
IF cool_function_primary_or_secondary() <> 'PRIMARY'
BEGIN
do my stuff
msdb..sp_stop_job @job_name=my job
END
this will not fail the job.
and, I would need to set the database to the database that will run the job to execute this right? also, if it's not accessible, is it going to fail ?
