SQL Server Agent Job - Adding Version Number

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

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

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

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.

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.

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.