This Months TSQL Tuesday topic from John Sterrett is an interesting one because it’s something that has been occupying quite a bit of brainspace for me recently. John asks “What are you doing, or what can we do to encourage younger people to get involved in the SQL community while increasing the number of younger speakers?”. It’s also Wednesday here in NZ, and a week late – but the topic is important enough to discuss even if I’m late tot he party.

I have a big scary number birthday coming up in November, but I’m also aware of a transition in my work life from “Building a CV” to “Building a legacy”. Legacy seems like a strong word, but I am spending increasing ammount of time thinking about the guy who has to look after things after me. That is in my own job role, and in my client environments, and, as is important to this topic – within the SQL community.

I have now been speaking at SQL events for around 15 years. I am still staggered by the depth of knowledge of those around me who give their time and in most cases not insignificant expense to make themselves available for these (mostly) free training events. I am no less in awe of the amazing things some of these speakers are doing in the world today than I was 15 years ago. And I do wonder if that awe is maybe one of the things that holds some of our younger speakers back.

There is a preconception that to stand in front of a room of people and talk about a technical topic you need to be some sort of guru level savant. In the tech world we accumulate knowledge so it takes quite a serious amount of self confidence to make that first step and put yourself in front of a group of people, many of whom will be quite senior to you, and take that position of expertise.

But here’s my take – it’s actually easier to choose a topic to speak on now than it was 15 years ago. Why? Because technology is changing so fast that no-one has five years experience in most of the technologies we are using today. They only came out three years ago, or last year, or yesterday. Adding to that those very experienced guys in the room, whether presenting or listening – they have a valuable skillset which means they are very likely very busy. The ‘curious itch’ I had in my twenties and thirties remains, but there is just no way I can test and discovery all the things in all the cool technologies that are coming out. I **need** to hear stories of how people are using new technologies because the busier I get the less time I have to play with the new way of doing things. But for someone starting their career, who is more time-rich – you can tell me how you get one and what amazing results you got.

So what can we do to encourage more of this sharing? First of all I think we need to encourage the younger crowd to attend these events and importantly – stick around afterwards. There is so much that each group has to offer the other. But mostly you’ll discover that the biggest difference between you and the guys already on the speaking stage is that we are older. Usually IT draws a person with a brain that works a certain way. When I started going to SQL events I felt like I found my crowd. I could have a chat and I found that most everyone was on the same wavelength. So take advantage of the aftermatch of these events.

Secondly – I like the shorter quicker talks. It does take some time to put together a full session for a talk, so as a group maybe we can schedule short form content in with the longer form content. A 5-10 minute talk is much easier to get through than a full hour session. It’s a small window to show a single thing or feature off, rather than go through a product end to end.

And finally we need to remember the crowd. I’ve done very few talks at SQL Saturdays etc that are above 200 level. I worked out pretty early that the introductory stuff is the sweet spot. Yes there’s some experienced people in the audience, but there’s also a lot of people at the start of their story. I want those guys to be just as comfortable asking questions and making comments as the guys who have done this as long as me.

At my company, we’re lucky to have a custom tool that monitors EVERYTHING happening in our SQL Server environment. But every now and then, I like to explore what’s possible using only SQL Server’s built-in features-just in case you don’t have a comprehensive monitoring suite at your disposal.

Recently, someone made an off the cuff comment about using TSQL Tasks in Maintenance Plans to handle more complex logic that SQL Agent handled out of the box. I was briefly excited by the prospect of building improved logic flows directly into SQL Server Maintenance Plans. This months Andy Levy hosts TSQL Tuesday and asks us about how we handle SQL Agent Jobs and this seemed like a great opportunity for me to share a story about how I wasted an afternoon testing a few components I have a passing knowledge with, attempting to implement auto-healing and conditional logic flow, only to snap out of it a few hours later when I realized that I was trying to solve a problem that someone else cracked nearly 2 decades ago.

The visual workflow designer and conditional branching on success or failure in Maintenance Plans seemed like a great way to automate not just routine tasks, but also smart responses to issues as they arise. I love any opportunity to implement automatic healing into anything that is likely to alert me. Getting a message saying “A thing nearly broke last night – the problem is fixed” is so much better than getting a page to fix something that retrying or a known remediation action needs to be manually performed. So I looked into a little side by side comparison of SQL Agent vs Mainteance Plans to address a scenario where a DBA performs the same boring checks and actions when a backup fails, and how both technologies allowed for implementing retries and logic flow to handle this automatically rather than have a human intervene.

The flow for this process was good at first, but as I dug deeper, I realized that while Maintenance Plans can be powerful, you can achieve the same (and often better) results with a little creative thinking in SQL Server Agent Jobs. In fact, Agent Jobs offer even more flexibility, allowing you to mix and match different step types-T-SQL, PowerShell, SSIS packages, and more-within a single job. Let me walk you through what I learned, with some real-world examples.

Maintenance Plans: Visual Logic, Limited Flexibility

Let’s start with the Maintenance Plan approach. Here’s a screenshot of a workflow I put together to handle database backups with built-in remediation and notifications but the same ideas can apply to ETL or other processes.

• The plan tries to back up all user databases.
• If the backup succeeds, it updates a central monitoring table.
• If the backup fails, it runs some auto-remediation steps (like clearing disk space or restarting a service), then tries the backup again.
• If the second attempt succeeds it updates the central repository, and if it fails, it notifies the on-call operator.

This is a solid approach, and the visual editor makes it easy to follow the logic. But there are some limitations:

• You’re mostly limited to backup, maintenance, and T-SQL tasks.
• Complex branching or looping is awkward.
• Integrating with external scripts or tools isn’t straightforward.

Agent Jobs: The Real Powerhouse

What’s more, my inital enthusiasm around the workflows was misplaced. SQL Agent actually has success and failure branching built in also at a per step level, it’s just in most cases the failure action is to say the job failed. But it doesn’t need to be.

With Agent Jobs, you can:

• Chain as many steps as you want, each with its own success/failure logic.
• Use different step types (T-SQL, PowerShell, CmdExec, SSIS, etc.).
• Easily implement loops, retries, and advanced remediation.
• Integrate with external systems, scripts, or APIs.

Here’s a screenshot of a way of achieving a better outcome using a non-standard workflow with SQL Agent. The job again handles backups with layered remediation and targeted notifications:

1. Reset Autoheal Remediation Counter – Prepares the environment for a new backup attempt.
2. Check Retry Loop – Determines if a retry is needed based on previous attempts. If the number of retries is exceeded the job generates a failure which redirects to the final step(Which is configured to alert the oncall operator)
3. Backup User Databases – The main backup step.
4. Update Central Reporting Repository – On successful backup the job proceeds to this step which logs the result and ends the job.
5. Initiate Backup Drive Maintenance – If the backup fails, attempts to resolve likely issues.
6. Perform Additional AutoResolution Tasks – Further remediation if needed. Following the completion of this step the job is redirected back to step two which increments the loop counter and retries the backup.
7. Job Failed Notification – If all else fails, notifies the operator and ends the job.

Each step specifies exactly what should happen on success or failure, making the workflow both robust and transparent.

Why Agent Jobs Win

After experimenting with both approaches, I decided I hadn’t infact finally found a use for Maintenance Plans. Really the only thing they have going for them in this scenario is a nice graphic interface to visualise what is going on. That’s not to undersell the importance of a clearly identifiable workflow. The job step approach is very hard to follow if your workflows become more complicated. But here’s why I think Agent Jobs are the smarter choice for most advanced workflows:

1. Multiple Step Types – You’re not limited to T-SQL or maintenance tasks. Want to run a PowerShell script, call an external API, or execute a batch file as part of your remediation? No problem.
2. Granular Control – You can direct the job flow on a step-by-step basis, allowing for complex logic, conditional retries, and targeted notifications.
3. Easy Integration – Agent Jobs make it simple to integrate with external monitoring systems, send custom alerts, or update centralized logging repositories.
4. Maintainability – It’s easier to document, update, and troubleshoot jobs when each step is clearly defined and can be of any type you need.

Conclusion – So which should you use?

So obviously I’m leaving out some additioanl options in my side by side test. SQL Agent Jobs have auto retry built in at the per step level if you want, and the loop doesn’t have to be at the task or job step level – you can build that right into the TSQL if you like. I played around with both of these approaches for a while, trying to see if I could refine them to resolve the issues that I came up with. I really wanted that graphical format, but I also wanted the flexibility of the SQL Agent jobs. Which should I use?

When you delve into these things it’s really easy to get stuck with your blinkers on. The answer here was pretty clear. Microsoft actually built a whole tool for us to handle SQL data movement with complicated workflows and event handlers that has a flexible graphical presentation layer – and that tool is called Integration Services. Yes SSIS has some of it’s own issues but flexibility of logic flows is not one of them.

Another afternoon frittered away investigating a problem that was solved years ago….