Sometimes we, as data professionals, have to think outside the box. I know, crazy idea right? Each shop and situation are different; there will always be several different ways in most cases that you can arrive at a solid solution.
This post has a few intentions behind it:
- It is not a “take this solution; it’s the only way”.
- Generate some thought and additional methods to reach a goal.
- This is not intended for a production environment.
Good, now that we have those few things out-of-the-way let’s get to the meat of the topic. A situation arises where you want to give a bit more control to teams to execute jobs without giving full access to the SQL agent. In that case a good utility database may come in handy.
Example of an issue: A QA team is in need of kicking jobs off to test in a specific environment. Keeping in mind that each shop can be different this also means that security levels at varying shops will be different. There are a few choices that may come to mind with this issue:
- Fire off an email to the DBA team and wait for them to kick job off.
- Fire off an email to someone with access and wait for them to kick the job off.
- Wait for the predefined schedule on the job agent and let it kick the job off.
Another method would be to utilize a utility database. You can give it whatever name meets your criteria in this case we will just call it TestingJobs. Let’s look at the overall picture below and how this all fits together:
Things you’ll need
- UtilityDatabase
- Two Stored Procedures
- Table
- Agent Job
Step1: Create the TestingJobs database (I won’t go into specifics here on proper set up; assume this is already created).
Step2: Create a table called ControlJobs inside the TestingJobs database
USE [TestingJobs]
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[ControlJobs](
[JobControlID] [INT] IDENTITY(1,1) NOT NULL,
[JobName] [VARCHAR](500) NOT NULL,
[RunStatus] [BIT] NOT NULL DEFAULT ((0)),
[LastRanBy] [VARCHAR](50) NOT NULL,
[LastRanByApp] [VARCHAR](150) NULL,
[Date_Modified] [DATETIME] NOT NULL DEFAULT (GETDATE()),
[Active] [BIT] NOT NULL DEFAULT ((1))
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
GO
Step3: Store procedure creation for table insertion (note the parameter @JobName)
USE [TestingJobs]
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[test_StartJobs] ( @JobName VARCHAR(100) )
AS
BEGIN
/************************************************************************
This script will insert the record needed to kick off agent jobs.************************************************************************/
INSERT INTO [TestingJobs].[dbo].[ControlJobs]
( [JobName] ,
[RunStatus] ,
[LastRanBy] ,
[LastRanByApp] ,
[Date_Modified] ,
[Active]
)
VALUES ( @JobName ,
1 ,
” ,
” ,
GETDATE() ,
1
);END;
Step4: Set up stored procedure that will run the pending jobs.
USE [TestingJobs]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RunPendingJobs]
AS
SET NOCOUNT ON;DECLARE @JobName VARCHAR(500) ,
@JobStatus INT ,
@RC INT;DECLARE cur_RunJobs CURSOR
FOR
SELECT JobName
FROM Ddbo.ControlJobs
WHERE RunStatus = 1
ORDER BY JobName;OPEN cur_RunJobs;
FETCH NEXT FROM cur_RunJobs
INTO @JobName;WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Checking to see if job is currently running. ‘;EXEC @RC = dbo.GetCurrentRunStatus @job_name = @JobName;
IF @RC = 0
EXEC msdb.dbo.sp_start_job @JobName;
ELSE
PRINT @JobName + ‘ is currently running.’;UPDATE ControlJobs
SET RunStatus = 0 ,
Date_Modified = GETDATE()
WHERE JobName = @JobName;FETCH NEXT FROM cur_RunJobs INTO @JobName;
END;
CLOSE cur_RunJobs;
DEALLOCATE cur_RunJobs;
Step5: Set up stored procedure to check if job is already running
USE [TestingJobs];
GOSET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[GetCurrentRunStatus] ( @job_name sysname )
AS
SET NOCOUNT ON;
/* Is the execution status for the jobs.
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions */DECLARE @job_id UNIQUEIDENTIFIER ,
@is_sysadmin INT ,
@job_owner sysname ,
@Status INT;SELECT @job_id = job_id
FROM msdb..sysjobs_view
WHERE [name] = @job_name;
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N’sysadmin’), 0);
SELECT @job_owner = SUSER_SNAME();CREATE TABLE #xp_results
(
job_id UNIQUEIDENTIFIER NOT NULL ,
last_run_date INT NOT NULL ,
last_run_time INT NOT NULL ,
next_run_date INT NOT NULL ,
next_run_time INT NOT NULL ,
next_run_schedule_id INT NOT NULL ,
requested_to_run INT NOT NULL , — BOOL
request_source INT NOT NULL ,
request_source_id sysname COLLATE DATABASE_DEFAULT
NULL ,
running INT NOT NULL , — BOOL
current_step INT NOT NULL ,
current_retry_attempt INT NOT NULL ,
job_state INT NOT NULL
);
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner,
@job_id;
SELECT @Status = running
FROM #xp_results;
RETURN @Status;DROP TABLE #xp_results;
SET NOCOUNT OFF;
Step6: Job Creation
Create a SQL agent job that will call the RunPendingJobs in the database. You can set this schedule to three minutes for this test.
The Benefit
Now think of a QA team member sitting at their desk running multiple tasks. This does take some coordinated effort in getting the job names but now that the basics are set up the team member could run the execute command for the test_StartJobs which will place the necessary information into the control jobs table. Of course the proper security would need to be set up in order for the user to be added (think AD groups). By utilizing the above method the team can suffice on it’s own in a non prod environment streamlining some of the inefficiencies that have plagued the groups in the past.
Summary
A few things to note here:
- Don’t ever take code off the internet without testing it. This is just a thought-provoking post and there are some things in this post that are dependent upon one to set up and test.
- I realize there are multiple ways to accomplish this. This is just an avenue to explore and test with some thinking outside the box.
- Don’t limit yourself to “I can’t” or “This will not fly at my shop”; challenge yourself to become innovative and think of ways to tackle problems.
Being a data professional you assume a certain amount of responsibility. It often requires having the right attitude and an action plan in place for finding the solutions to our problems at hand. Too many times we attack the symptoms causing the issue, but overlook the root cause. The quick Band-Aid fixes are found many times over, whereas our jobs should be identifying the real issues that lie beneath the symptoms. Now, don’t get me wrong – I understand at times you have to stop the bleeding. In the end though one should uncover the root cause and make the permanent fix.
Often times we as leaders within our respective shops tend to waste our time focusing on the wrong things. Think about that for a moment and think about the team that you are on or that you are leading. I’ve seen it happen among some very talented teams where we (yes I include myself in this) are not tapping into the expertise of our teams bringing for the most potential out of each team member.
If you’ve been involved in technology for any length of time you are aware of outside threats to your network or databases. You read about some of these threats in the news such as hacking, breaches, etc.
