This download details a DS shortcut accelerator with T-SQL improvements over the previously published v1.0. This version narrows down in SQL more definitively our job shortcut definition and improves on the previous documentation.
1. Introduction
Many of us DS 6.x administrators manage the distribution of our Deployment Server 6.x jobs using shortcuts. These are brilliant constructs - like file system shortcuts, no matter now many shortcuts are distributed across your Deployment Server, the integrity of the target job is preserved. If you've ever suffered from job 'version hell' on your server, you'll quickly understand why these shortcuts are so valuable.
The implementation of shortcut objects however comes at a price -each shortcut's execution introduces (by design) a scheduling delay in the execution of any jobs that follow them in the job chain. You see, when a shortcut's target is injected into a chain of scheduled jobs, all the jobs that follow it are rescheduled for the next minute. To illustrate, below I show the install 'clocks' for 4 browser plugins. The first clock shows the plugins being installed using native DS jobs, and the second illustrates the effect of the re-scheduling when using shortcuts.
The important take-away from the above figure is that sequences of shortcuts will negatively impact your job deployment times. The penalty will be most severe for fast acting jobs like plug-in installs (as illustrated above) and configuration scripts. As you can imagine, this poses a significant problem in scenarios where you execute a large number of fast acting jobs -the delays that accumulate from each shortcut's execution can be crippling.
We've been aware of this delay for some years, but have never taken the time to assess the impact. So, after taking some stats from our Deployment Server, we found that we were well sited within the fast-running jobs scenario. Below is a histogram of DS Job execution times from our server.
As you can see, half of our jobs complete within a minute which means that once chained as shortcuts the scheduling penalties will begin to accumulate. It's not all bad news though. For those who are looking to improve the shortcut execution times, today I'll demonstrate a simple script which will allow you to completely bypass the native DS scheduler delays when executing such shortcuts. It's simple and nifty. If you are a shortcut admirer this could be the answer you've never known you needed, but have always been looking for.... ;-)
2. Background
Deployment Server shortcuts are superb little inventions by Altiris administrators. Shortcuts are jobs which consist of a single dummy task which executes another task on completion. At first glance they can seem a little pointless[1] as they do not after all enhance the deployment of the job on the target machine. Their power however is not seen in the deployment of your jobs, but in the administration of them. Job Shortcuts help preserve job integrity and reduce job sprawl on your deployment servers.
At first glance the overhead of these shortcuts is minimal. When executing the dummy task (which is often just a single line REM statement) the total elapsed time is rarely more than a couple of seconds. However when scheduling large sequences of jobs using shortcuts a problem emerges; as each shortcuts executes, the engine injects the target job into the scheduled job chain and reschedules the subsequent jobs for the next minute. When scheduling folders of many shortcuts onto your client computers, you'll appreciate that this constant re-timing of your job schedules can accumulate delays which needlessly gnaw away at your deployment statistics. The administration gains however from using shortcuts generally outweigh this scheduling annoyance, so most admins just accept this hit as inevitable and move on.
Personally, I love shortcuts and we use them extensively here;
- Image Deployments
Every image we deploy has a number of post-deployment jobs attached. These jobs typically deploy latest browser plugins and software which can't reside in the image (for technical or legal reasons). With perhaps 10 shortcuts used in each deployment, that's a potential extra 5 minutes wasted in a typical deployment.
- Image Creation
Our image creation process is entirely automated. Depending on the department, the number of shortcuts used to create the images varies from 20 to as much as 100. Many of these target jobs take just a few seconds to execute which means when creating our images the entire process can be slowed down for as much as 40 minutes whilst the client awaits the engine's minute hand to tick over.
When I say we use shortcuts extensively, I really mean it. Our master deployment server has over 4000 shortcuts in use today.
3. Our Definition Of A Job Shortcut
The solution I detail now programmatically replaces all the scheduled job shortcuts with their targets at execution time. The result is that the shortcuts themselves are never actually be executed by the engine -the first job in the scheduler chain removes the shortcuts by resolving them in situ to their targets.
This approach bypasses the scheduler delay (as the re-scheduling sequence is no longer required) whilst allowing us to continue to reap the administrative boons of using shortcuts. Simple and neat. And like Darren says, he doesn't know why I didn't think of it earlier....
Messing around with scheduled jobs at the database level is obviously dangerous. In order to proceed safely we need to carefully define what a shortcut is so we can translate this definition into SQL.
Below is an example of a job folder on our deployment server which contains nothing but shortcuts,
I know they are all shortcuts as they include the arrow string, '->', in their name (which is our standard). If I were to drill into the Adobe Flash Player shortcut, this is what we find;
So, the job contains a single run script. Let's now drill down further into this script,
As we can see this is just a trivial batch script which executes in Windows. It's job is simply to exit with a return code of 0. If we click 'Next' here, we'd see the 'Script Information' screen which would reveal that,
- The 'Run Script' task was scheduled to within the production environment
- The script executes in the system context
The only requirement is that it's a task which executes and returns quickly with success, and these default settings certainly achieve that. What is important is the next screen where we define the return code behaviour,
And this is the heart of the shortcut magic. Once the dummy 'Run Script' task executes, the job "[I] Adobe Flash Player Install (Latest Packaged)" is called. This is a real job, which is indicated to us by the absence of the shortcut string '->' in the job name.
This quick tour of our shortcuts allows us to summarise a shortcut as follows. It is a job which,
- Contains the substring '->'
- Contains a single 'run script' task
- On successful execution of the single script task, another job is subsequently called.
4. The Shortcut Scheduling Problem
Before we proceed further, I think it best to illustrate clearly the scheduling delays which are incurred when using shortcuts. For this, I'm going to create a job which executes a server-side 10 second ping (ping -n 10 127.0.0.1) and a shortcut pointing to this job;
The idea here is to create a job which executes fairly quickly, thus simulating the delay of a configuration script or perhaps the installation of a small software package.
Now, let's create two folders. Let one contain 10 copies of the shortcut, and let the other contain 10 copies of the original job;
Let's first schedule the 'Folder of Jobs' folder on a computer and see what happens. Noting that I scheduled the folder at 10:45am, below is the results screen I saw about a minute and a half later,
This is an example of a normal job schedule result. You can see that all the jobs were scheduled at 10:45am, and that each took between 10-11 seconds to execute. All the jobs completed at 10:46.
Let's now see what happens when we schedule the folder of shortcuts. This time, I scheduled the folder at 11:10 am.
There you can see that although I scheduled the folder of shortcuts at 11:10am, the scheduling times have all been driven forward incrementally by a minute. So, our chain of jobs which intrinsically should have a deployment time of 1m40s in reality took 10 minutes. That's 6 times longer!
Although this seems to be an extreme case, it's amazing how many software installs and configuration tasks we have which take just a few seconds to run. Yes we have big office apps and .NET packages, but these are (statistically speaking) not that common in our day-to-day deployments.
So, about half of all our jobs execution times take less than a minute to run, and the skew is certainly towards the faster executing tasks even within that minute range. So, our imagined extreme case isn't so extreme after all. It's quite likely in fact. Below is a histogram of job execution times which shows quite clearly the impact of chaining together shortcuts in our deployments. Whereas before we were most likely to have a job execute within 10 seconds, once shortcutted this job execution time effectively becomes one minute.
Having assured ourselves that we are indeed tackling a real problem, let's see what we plasters we can code to make it all better....
5. Casting the Shortcut Definition into T-SQL
We now need to convert our job definition of a shortcut into something more definitive and T-SQL like. The tables which are going to be of interest to us for this exercise are,
- dbo.event
This table stores the job details like it's id (the event_id), it's name and the folder it's stored in
- dbo.event_schedule
This table keeps track of scheduled jobs. It stores the schedule_id, event_id (the job that's been scheduled), the computer it's been scheduled on, the scheduling time, its current status and so on.
- dbo.task
This table stores the breakdown of the tasks within each event. Loosely speaking, tasks are categorised by the condition index (cond_seq), the task index within each condition and the task type. A good example for how tasks are broken down in events is to look at the DAgent Upgrade task which comes in the 'Samples' folder. Below I show the task list for each condition,
From the tasks table point of view, this job then has 12 task entries. The condition mappings are: 0 = IsMachineX86, 1 = isMachineX64, 2 = IsMachineIA64, 3 = Default. Under each condition, we have 4 tasks which are indexed by the task_sequence number (which must be unique for each condition) and task type. A task type of 12 is for "Copy File" tasks, and a task type of 10 is for "Run Script".
- dbo.task_return_handlers
Each task (as uniquely defined by the combination of event_id, cond_seq and task_seq) can perform different actions depending on its return code. The important field in this table for us is the handler which determines what should be done for the return codes specified,
handler=0 means the job stops
handler=1 means the job continues
handler=xxxxxxx means that another job should be executed
From the above we can form a more database-like definition for a job shortcut,
- Event.name like '%->%'
It must look like a shortcut
AND
- COUNT(hander) across all tasks in event=1
There can only be one handler attached to the job
AND
- SUM(task_type) across all tasks in event = 10
The minimum value of task_type is 10 and this refers to a 'Run Script' Task. So if we sum all the task_type values in any job and the sum equals 10, then this means there can only be one task, and further it must be a 'run script' task
AND
- SUM(handler) across all tasks dbo.task_return_handlers for event > 1
The single run script task must on completion call another job. This must be the case if the sum of handlers (of which there will be exactly one if above criteria are met) is greater than one.
Which is more nicely written as,
SELECT *
FROM event
WHERE name LIKE '%->%'
AND (SELECT COUNT(handler)
FROM task_return_handlers
WHERE event_id = event.event_id) = 1
AND (SELECT SUM(handler)
FROM task_return_handlers
WHERE event_id = event.event_id) > 1
AND (SELECT SUM(task_type)
FROM task
WHERE event_id = event.event_id) = 10
I don't know about you, but this struck me as being quite elegant.
5.1 The T-SQL for the DS Shortcut Accelerator
Now we can identify a shortcut accurately, we can work on some SQL code to replace any scheduled shortcut with the job it resolves to (as provided by the handler field). This needs to work on a per-computer basis, so we'll need to be filtering the event_schedule table by the computer_id of the target computer.
A useful piece of information now is that any scheduled job awaiting execution will be listed in the event_schedule table with a status_code of NULL. So we now want to UPDATE any event_id that corresponds to a currently scheduled shortcut with the single handler value listed for that job. This is neatly presented as,
UPDATE event_schedule
SET event_schedule.event_id = (SELECT TOP 1 handler
FROM task_return_handlers
WHERE event_id = event_schedule.event_id),
next_task_seq = 0,
cond_seq = 0
WHERE (SELECT name
FROM event
WHERE event_id = event_schedule.event_id
AND event_schedule.status_code IS NULL) LIKE '%->%'
AND (SELECT SUM(handler)
FROM task_return_handlers
WHERE event_id = event_schedule.event_id) > 1
AND (SELECT COUNT(handler)
FROM task_return_handlers
WHERE event_id = event_schedule.event_id) = 1
AND (SELECT SUM(task_type)
FROM task
WHERE event_id = event_schedule.event_id) = 10
AND event_schedule.computer_id = %ID%
Where I've already taken the liberty of inserting the token %ID% which is the DS Token for the computer_id. I've also set next_task_seq and cond_seq to 0 to make the final result of this UPDATE to the event_schedule table to look like we'd dragged and dropped the jobs manually.
6. Creating and Using the DS Shortcut Accelerator
Now let's create a job which resolves shortcuts and see how it can be used.
- Create a new job called "0 - DS Shortcut Accelerator"
- Add a 'Run Script' task and in the script window paste the following code,
REM Resolve Shortcuts using SQLCMD.EXE (v1.2.1)
SET SQLCMD="%PROGRAMFILES%\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE"
IF EXIST %SQLCMD% GOTO :RUNCMD
SET SQLCMD="%PROGRAMFILES%\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
IF EXIST %SQLCMD% GOTO :RUNCMD
EXIT 100
:RUNCMD
%SQLCMD% -d "eXpress" -Q "update event_schedule set event_schedule.event_id = (select top 1 handler from task_return_handlers where event_id=event_schedule.event_id), next_task_seq=0,cond_seq=0 where (select name from event where event_id=event_schedule.event_id and event_schedule.status_code is NULL) like '%%->%%' and (select sum(handler) from task_return_handlers where event_id=event_schedule.event_id) > 1 AND (SELECT COUNT(handler) FROM task_return_handlers WHERE event_id = event_schedule.event_id) = 1 and (select sum(task_type) from task where event_id=event_schedule.event_id) =10 and event_schedule.computer_id=%ID%"
This code runs SQLCMD.EXE from the SQL tools folder, so you'll need to make sure you have this installed and set the path above appropriately.
- Configure the script to run on the server, and allow it to execute when the agent is disconnected.
Now, copy this job into any deployment folders you have which heavily rely on shortcuts. Below I've shown a job folder for plug-ins where I've added the accelerator as the first job,
Now, just drop this onto your target computer and see what happens...
So Presto! You should see that all the shortcuts have been resolved and will now execute without delay. Now, although in the example above I've illustrated this for resolving shortcuts within a folder, remember it works by resolving any scheduled shortcuts. So, if you schedule a nested folder of jobs, all will be resolved as long as the accelerator job is prior to the shortcuts.
Dead easy really.
7. Summary
Shortcuts are great, but the delays they induce can be frustrating when you are trying to optimise your deployment times. The shortcut accelerator works by resolving job shortcuts at scheduling time to their respective targets. As a result, the engine never actually executes a job shortcut, thus circumventing the penalties incurred at the scheduler when resolving job chains. You can now have all the administrative advantages gained by leveraging shortcuts, without incurring the time penalties they incur at execution time.
In the majority of our deployments we expect this to shave between 5-10 minutes off our total delivery times. For some of our job sets (which build our production images from bare-metal) we expect fairly phenomenal savings -upto 40 minutes! Pretty impressive I think you'll admit for a tiny piece of T-SQL.
For those that want some homework, try throw the T-SQL UPDATE statement into a stored procedure and then using DS custom tokens to trigger it. This would be a tidier solution than running SQLCMD.
Happy New Year!
Ian./