1

I would like to know how to add a version number to a SQL Server Agent job without using the description field, and with it being an attribute of the job.

A similar question was asked at StackOverflow Sql Server Agent Job - Adding Version Number but that question did not include the criteria of not using the description field. (accepted answer implies the description field was viable solution)

I have a script to update jobs, and I want to capture version information without overwriting any existing descriptions and being able to search for current version on a single field (without combing existing/new comments with version info)

I can use sp_update_job to update most fields, but the only one not in use that will take strings is @category_name and it is limited to values in sp_help_category. (Edit following day >) It can be updated with sp_add_category but that presents the value in the GUI drop down as available for all jobs. Possible, but suboptimal.

I can use sp_update_job this would be suboptimal as steps are parts of jobs. I don't see any reasonable solutions in there. I did consider creating a step named "Version 1.0.0" or similar but that was wrong on many levels. EDIT After much research and testing it became clear that this was the optimal approach.

You can not use sp_addextendedproperty to hold a version number in a job. Doing so would require changing the value of 'level1_object_type' to 'JOB' which is not an option. Source

I can use a table to list modifications. But that would not be an attribute of the job, and is subject to human error insertion. Possibly I might use a table, where a hash of the command field (@command) and/or schedule is used as a unique identifier. This would/should be unique to job version, while not a direct attribute it would be a derived attribute.

Solution to apply to SQL Server 2008R2 and later by preference, SQL Server 2012 and later by requirement.

  • Are you always updating the job, or can you drop and re-create assuming the net result is the same? What does "being able to search for current version on a single field" mean - are you storing old versions as well or you just want to identify what version # the current job iteration is? – LowlyDBA Feb 5 '18 at 18:53
  • Always updating. I have a normal schedule. but it may be customized on some instance. So updating is the only option. – James Jenkins Feb 5 '18 at 18:55
  • 1
    "consider creating a step named "Version 1.0.0" or similar" - this may actually end up being the most workable solution. Categories can be added via sp_add_category, but I think using those would quickly become difficult to maintain. – Gareth Lyons Feb 5 '18 at 18:57
  • @LowlyDBA I started suggesting that myself too, but they're horrible to implement for SQL Agent, even moreso if you want to only deploy changes. Plus it doesn't really help you know what version is actually deployed to any given server. – Gareth Lyons Feb 5 '18 at 19:06
  • @JamesJenkins What command type are the jobs? If they're all T-SQL you could have it always call the same SP and use extended props to version the stored procedure(s) themselves. Could also work for external scripts (PS, batch) by storing the version in those files. @ Gareth True - it would require a thoroughly automated process to make sure they aligned consistently. – LowlyDBA Feb 5 '18 at 19:14
2

This is a very interesting question. I think I agree with @Gareth Lyons in some way. My suggestion is that you append your version information in the step name of the first job step (after all, all jobs have at least a job step to be meaningful)

So what you can do is like this: For the first time, say my job [test_job] has the following steps

Initial Job Steps

Now I can run the following code to append the version info to the 1st step name

-- initially appending version name to step 1 name 
declare @job_name sysname = 'test_job';
declare @ver varchar(128) = '<V 1.0.0>';

declare @step_1st_name sysname;
select @step_1st_name=step_name + ' - ' + @ver --adding version string to the step 1 name
from msdb.dbo.sysjobsteps s 
inner join msdb.dbo.sysjobs j 
on s.job_id = j.job_id 
and j.name=@job_name
and s.step_id = 1;


exec msdb.dbo.sp_update_jobstep @job_name=@job_name, @step_id=1, @step_name= @step_1st_name;
go

After running the script, you will see the job step name changed to the following

Initial appending version

Later, if we need to update the version, we can easily operate on the 1st step name as the following

-- updating version name later when it is necessary

declare @job_name sysname = 'test_job';
declare @ver varchar(128) = '<V 2.0.0>';

declare @step_1st_name sysname;
select @step_1st_name=step_name 
from msdb.dbo.sysjobsteps s 
inner join msdb.dbo.sysjobs j 
on s.job_id = j.job_id 
and j.name=@job_name
and s.step_id = 1;

-- replace old version with new version number
set @step_1st_name = substring(@step_1st_name, 1, charindex('<V', @step_1st_name)-1) + @ver;
exec msdb.dbo.sp_update_jobstep @job_name=@job_name, @step_id=1, @step_name= @step_1st_name;
go

If we run the script, we can see the job 1st step name is changed to the following

update the version

It is also very easy for you to extract the version number from any job's first step name.

I hope this may help you.

  • Thank you, this is the general scenario I am currently pursuing. I am working through the life cycle of this approach, and solving issues that could impact our our standards. – James Jenkins Feb 7 '18 at 10:46
  • 2
    @JamesJenkins, I'd also remind you that SQL Server SMO job class has a property called VersionNumber msdn.microsoft.com/en-us/library/…, this number changes each time, the job is updated (automatically), and you can check to see whether you can take advantage of this info in your design.. – jyao Feb 7 '18 at 17:18
1

Very useful question; and then you put on constraints such that it becomes quite difficult to meet everything you want.

This answer approaches it from the standpoint of answering two questions with very high reliability:

  • Which version of my approved deployment script based jobs is on each instance I check?
  • Did someone modify an existing job (without going through the correct versioning process)?

I'll address SQL 2016 first, and then 2014 and below second.

For SQL 2016, HASHBYTES can handle VARCHAR(MAX) correctly, so my suggestion is to craft a SQL that:

  • Script out each versioned agent job's steps and whatever settings you care about that will be identical between servers
    • Or that you can MAKE identical; for instance if your log path is \server\share\SQLname$instancename\Joblog1.txt, you can use REPLACE(path,REPLACE(@@SERVERNAME,'\','$'),'CorrectServerName') and then if the path is right for that instance, you get the same result string
  • use FOR XML PATH in a CROSS APPLY on the above to condense all the rows of data per job into a single string
  • use HASHBYTES on that string, ideally with SHA2_512
  • Store a lookup table of the job name, the version, and the result hash
  • On any given instance, you can run the same SQL you ran on your sample deployment, and LEFT JOIN to your lookup table on the hash to see what version's out there, as well as which jobs don't have their hash in there, and are thus compromised/impure/modified/suspect/rage-inducing.

SQL 2014 and earlier, however, limit input to 8000 bytes. Thus, in this case, you have a nasty twofold step in order to account for >8000 bytes of data (i.e. big SQL got into a job, whether by you or some other way - say, vendor software).

  • Hash each 8000 bytes or less of the above steps + settings after the FOR XML PATH
  • concatenate all those hashes
  • Check the total length of those hashes; if THAT is over 8000 bytes, there are 125 BINARY(64) SHA2_512 allowed within your maximum VARBINARY(8000)
    • if you have over 125*8000 bytes of job steps plus settings, either panic or add another loop here.
  • Hash the result of the above.
  • Continue as above.
1

While I still think the below is good advice, I did happen upon the following today:

SELECT name, version_number 
FROM msdb..sysjobs

Apparently sysjobs tracks this for you already. I assume you can figure out a way to use this to correlate each version to your scripts.


Based on the various constraints and unique factors, I'm going to walk back and just say version control it with Git or whatever you prefer.

If you use PowerShell + Git + Flyway + < a build tool > you can automate the release of each job version, have a table that tracks the current version, and easily compare/restore/etc. all versions within the source control tool. Lock down control access and make this the only way to modify the jobs.

This approach is ideal since it can be applied to the entire database, as compared to a complicated and obscure method to track agent jobs that may not have applicability to other server-level objects for re-use.

Spend the time to make a generic, over-arching process and you can version control any or every aspect of your database with ease.

  • Thank you; we are using github, due to customizations of parameters it is not possible to automate updates to several hundred servers. We can use the extended properties of the sproc to validate it has been updated. But how do we find the jobs that have not been updated to the correct version? Some (maybe 100) will be customized, and some will be non-customized. – James Jenkins Feb 6 '18 at 14:49
  • Not sure what is impossible about it. Save the change scripts to github, on merge run Flyway to release to the destination server. Then use a central management server to query the change log table Flyway matains to verify that script xxxx has been applied to the server. You'll have to work out the details of your process yourself, but read up on Flyway. – LowlyDBA Feb 6 '18 at 15:01
  • @JamesJenkins Check out my update – LowlyDBA Feb 9 '18 at 18:49
  • Yes, jyao mentioned that in a comment under their answer. The field will hold an integer value. it is automatically updated. It can not be updated with sp_update_job, but even if you wrote code to change it, and someone made minor modification to the job that did not modify the version parameters it would appear to have been updated to the next version, via automatic updates. . – James Jenkins Feb 9 '18 at 19:06
  • @JamesJenkins ah drat didn't see he had that already. I guess if you're making job changes but not incrementing the version every time, that further narrows your requirements. I wouldn't try to manage it manually, but with tight change controls there isn't any reason why it shouldn't work. You can always map the integer to a more robust versioning system, but honestly its just an agent job, so I'd take what you can get. – LowlyDBA Feb 9 '18 at 19:21
0

The following answer has been tested in SQL 2008R2 to 2016 (Enterprise Edition, but I don't think that matters)

Extended events are not available for jobs. This solution provides specific version information that is available for searching across multiple servers in the form of a Job step name (sysjobsteps.step_name). It also provides space to track complete versioning history. It can be used with or without a versioning tools like github.

It assumes all the jobs you are interested in, are owned by the SA account and the job names begins with 'Custom'. These assumptions can be modified as needed. The approach below is 3 fold, Steps 1 and 2 should be run against "Central Management Servers" or 'Local Server Groups"

  1. Identify servers with "Custom%' jobs that have not had any version tracking appied
  2. Identify Servers that do not have the current version applied
  3. Where no version tracking is present, update existing step one to the correct version, and add version tracking as a new step one. A slightly different approach would be required for versions updates after v1.0.0.

Notice that new version tracking step is added after the exiting step has been modified to give durability. If there are multiple steps, they are all migrated one step by the insertion of the new step one. There are 3 levels of redundancy in the area (SQL Command) used to track version history;

  • On both success and failure of step 1, the action is go to next step
  • Block comments (Slash star) are used to isolate the entire body of version history
  • Line Comments are used on each line.

Identify servers with "Custom%' jobs that have not had any version tracking appied

-- Identify servers that have not had all versions of our custom jobs applied
-- If the counts are not equal they are missing a version identifier. 
-- Count Custom and count Correct version
-- Excludes servers that don't have any jobs beginning Custom


USE [msdb]
GO
SELECT (
    SELECT COUNT ( DISTINCT sysjobs.name)
    FROM dbo.sysjobs
    WHERE owner_sid = 0x01 -- SA owns the Job
    AND enabled = 1 -- It is enabled
    AND name like 'Custom%' -- name starts with custom
    ) AS CountCustomJobs
    , (
    SELECT  COUNT ( DISTINCT sysjobsteps.step_id)
    FROM dbo.sysjobsteps
    WHERE  step_id = 1 --Limit to step 1
    AND  sysjobsteps.step_name like 'v1%' -- Name starts with v1
    ) AS CountVersionSteps

Identify Servers that do not have the current version applied

--Run against CMS to find jobs that have not been updated to current version

USE [msdb]
GO
SELECT sysjobsteps.step_id
, sysjobsteps.step_name
, sysjobsteps.command
--, sysjobsteps.job_id
, JN.Job_Name
FROM dbo.sysjobsteps
LEFT JOIN (
SELECT sysjobs.name as 'Job_Name'
, sysjobs.job_id
FROM dbo.sysjobs
) AS JN
ON sysjobsteps.job_id = JN.job_id 

WHERE  step_id = 1
AND sysjobsteps.step_name = 'Old Wrong Name'
OR (sysjobsteps.step_name like 'v1%'
    AND sysjobsteps.step_name <> 'v1.0.0 - Custom - <jobname>'
    )

Where no version tracking is present, update existing step one to the correct version, and add version tracking as a new step one

--Update Custom - <jobname> to v1.0.0 
--Author: James Jenkins Feb 6, 2018
--Puts the version number in the title of step 1, includes details in the command as comments, 
--Updates step 1 and moves orginal step 1 to step 2 with updates
--It does not modify schedules or existing descriptions on the job

--Update existing step 1 to current standard parameters 
USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_name=N'Custom - <jobname>', @step_id=1 , 
        @command=N'EXEC Admin.dbo.<StoredProcedure>
@V1 = N''<value>''
'
GO

--Create new step 1 (creates as step 1 and SQL pushes old step one to step 2)
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Custom - <jobname>', @step_name=N'v1.0.0 - Custom - <jobname>', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_fail_action=3, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'/*
--Step exists to track verision history
--v1.0.0 - Change made documented here
--
*/', 
        @database_name=N'master', 
        @flags=0
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.