March 4, 2020 at 12:08 pm
I'm looking to build a "dynamic" job step that is the first step in every job (due to availability groups) that quits if it is not primary
I have most of the code, but the bit i'm missing is how to find the name of the job that is currently running
I have the basics (which is based on widely published code on the internet)
CREATE PROC isprimary as
DECLARE @role VARCHAR(50)
DECLARE @db sysname
SELECT @db=database_name FROM sysjobsteps
SELECT @role = [role_desc] FROM [sys].[dm_hadr_availability_replica_states] hars
INNER JOIN [sys].[availability_databases_cluster] adc
ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] = DB_NAME()
IF @role = 'PRIMARY'
BEGIN
PRINT 'Primary - Job can proceed'
END
ELSE
BEGIN
-- Deliberately cause a Failure
SELECT 1/0
END
so this will be step 1 in every job , but i need to check the database for the next step in the job (sysjobsteps etc) - but getting the proc to figure out which job you are running is bugging me
MVDBA
March 4, 2020 at 2:18 pm
Have you taken a look at Job tokens Mike?
https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-ver15
Also any reason your not using sys.fn_hadr_is_primary_replica() instead of the query to determine if your on the right replica
March 4, 2020 at 3:01 pm
tokens is an interesting idea
the idea behind the proc is that we are consolidating quite a few servers onto a multi node server which is quite experimental hardware (I think we are the first to get it from HP) with a Greenlake/Nimble SAN - this means I have more than 400 sql agent jobs to move -
needless to say I don't want to recode it all by hand and manually set that first step.. I can script up the add job step (I can't put it in the proc calls in the job step, I have to use a 1st step in the job approach)
hence i'm looking at the database for the next step... easy, but making it automatically identify which job is eluding me.
bear in mind i'm trying to make this process as easy as possible because we have non-dbas making jobs and i'm trying to build a system to close new jobs or modified jobs between the 2 synchronous nodes (I don't care about the async reporting node) and none of these guys know anything about AG let alone server hardware
MVDBA
March 4, 2020 at 3:25 pm
DECLARE @Primary BIT, @DB SYSNAME
SELECT @DB = database_name from msdb.dbo.sysjobsteps where job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) and step_id = 2
SELECT @Primary = sys.fn_hadr_is_primary_replica (@DB)
IF @Primary = 1
BEGIN
SELECT N'Primary Woohoo Continue'
END
ELSE
BEGIN
RAISERROR (50001,16,1,N'Not the Primary I must silently fail')
END
Something like the above Mike would do it.
I'm too not a fan of adding the function call in on each step, gets a bit clunky doing that and have always done it as Step1 to run or skip the job depending on the above.
March 4, 2020 at 3:30 pm
This is what I use in a lot of places for SQL jobs. Not elegant, but it works.
On my newest set of AG servers, when I create a job that is dependent upon the primary, I added the string "AG Dependent" in the description of each job. I set up an alert for Error 1480: AG - Role Change, and in the response I execute a job that test for primary or secondary, and disables or re enables the jobs where the description LIKE 'AG Dependent'. Again, not elegant, and it is dependent upon a human (me!) remembering to add the right thing to any job description.
/****** Object: UserDefinedFunction [dbo].[IsPrimaryInAG] Script Date: 3/4/2020 10:21:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IsPrimaryInAG]
(
@AG_Name sysname = NULL
)
RETURNS tinyint
AS
BEGIN
DECLARE @PrimaryReplica sysname;
If @AG_Name IS NULL Begin
SELECT
@AG_Name = AG.name
FROM sys.availability_groups AG
End
SELECT
@PrimaryReplica = HAGS.primary_replica
FROM sys.dm_hadr_availability_group_states HAGS
INNER JOIN sys.availability_groups AG ON HAGS.group_id = AG.group_id
WHERE AG.name = @AG_Name;
IF UPPER(@PrimaryReplica) = UPPER(@@Servername) OR @AG_Name IS NULL BEGIN
RETURN 1
END; -- primary
RETURN 0; -- not primary
END;
GO
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2020 at 4:00 pm
DECLARE @Primary BIT, @DB SYSNAME
SELECT @DB = database_name from msdb.dbo.sysjobsteps where job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) and step_id = 2
SELECT @Primary = sys.fn_hadr_is_primary_replica (@DB)
IF @Primary = 1
BEGIN
SELECT N'Primary Woohoo Continue'
END
ELSE
BEGIN
RAISERROR (50001,16,1,N'Not the Primary I must silently fail')
ENDSomething like the above Mike would do it.
I'm too not a fan of adding the function call in on each step, gets a bit clunky doing that and have always done it as Step1 to run or skip the job depending on the above.
ok - nice and simple but can you explain the $escape_none(jobid) bit... this sounds like it is the bit I am looking for - I've never used it. does it just return back the current job id... if so then im more than happy
MVDBA
March 5, 2020 at 1:56 am
ok - nice and simple but can you explain the $escape_none(jobid) bit... this sounds like it is the bit I am looking for - I've never used it. does it just return back the current job id... if so then im more than happy
The following link explains it... there's much more there, as well.
https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-ver15
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2020 at 8:39 am
thanks guys - I think this will work nicely for me - also something I should have been researching since 2016, just never had the need to
MVDBA
December 29, 2022 at 11:01 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply