IntegrationsAll integrations

Menu

Menu

Missed the live session? Watch the full recording now.

Why You Shouldnโ€™t Use SQL Agent for Batch Processes

Brent Ozar

SQL Server Performance Expert ยท Founder, Brent Ozar Unlimitedยฎ

Read more

Key moments

1:48 Where SQL Server Agent works well

6:57 Failovers and which replica runs the job

12:21 Backup jobs that fail without anyone noticing

42:06 Demo: one view across servers in JAMS Scheduler

1:01:00 Converting existing SQL Agent jobs

SQL Server Agent handles database-local tasks like backups, corruption checks, and statistics maintenance well. But as batch processes grow more complex, they need things Agent wasnโ€™t built for: cross-system orchestration, real observability, HA-aware recovery, source-controlled deployments, replayability, security boundaries, and cloud portability. Teams end up patching the gaps with scripts and workarounds instead.

In this session, Brent Ozar traces where that ceiling shows up and what signs to watch for as your own batch processes approach it.

Agenda includes:

  • Where Agent fits well: backups, corruption checks, statistics maintenance
  • Why Agentโ€™s scope stops short of general batch processing
  • Capabilities complex batch processes need that Agent lacks
  • Common workarounds, and why they add fragility
  • Signs your batch processes have outgrown Agent
  • What to look for in the next layer of the stack
  • Live demo: JAMS Scheduler addressing that ceiling
  • Live Q&A

Watch the full session

Sign up once and every full recording in the video hub opens for you.

Read the full transcript

โ–พ

This transcript was generated from the recording and lightly edited for readability.

0:00 Why this session exists

For batch. Thereโ€™s the recording. So, this todayโ€™s session is going to be about why you shouldnโ€™t use SQL Agent for batch processes. I am very opinionated about these kinds of things. Iโ€™m Brent Ozar. I do a lot of work with Microsoft SQL Server. Iโ€™ve got over 25 years of experience banging on it. And Iโ€™ve seen all kinds of oddball situations with SQL Server, both in terms of performance and health and reliability. When I talk to you, usually when you come to see my sessions, youโ€™re coming for answers. And I try to make them as easy as possible. My ideal session is where I hand you a script and I say, โ€œGo back to the office, run this script in your office, and itโ€™ll magically solve problems. Things like the first responder kit, sp_BlitzFirst, sp_Blitz, things like that.โ€ Ideally, inside the span of an hour, I can give you all the answers that you need. This is not one of those sessions. This is a session where I am more giving you the questions. Iโ€™m giving you the problems that I have seen over the years with SQL Server Agent that have come back to bite me in the rear end. Itโ€™s that I want you to be aware of what those problems are so that as youโ€™re using SQL Agent or as youโ€™re looking at alternatives because over the course of the years the older that you get the more you start to want to roll your own stuff or if you work in teams with Linux administrators theyโ€™re like why donโ€™t we just use cron you know things like that I adore SQL Agent I recommend it by default in many circumstances itโ€™s Great for single SQL Server environments.

1:48 Where SQL Server Agent works well

When Iโ€™m working with a shop that only has one SQL Server, all their data is in that database and they have no other platforms or database systems. SQL Server Agent will keep things as simple as possible for you to do as many things as possible. It gets to the point where people even think of it as an application server because theyโ€™ll set up reports on it. Theyโ€™ll set up housekeeping jobs. Theyโ€™ll set up alerting things, stuff like that. I love it for backups. I love it for check DB and checking for corruption because itโ€™s something thatโ€™s built into the engine. And it doesnโ€™t have dependencies on anything else. I want to make sure that my backups get done. I want to make sure that my database is free of corruption. I want to make sure that my indexes are maintained, that my stats are maintained. And one of my favorite uses for it is startup alerts. Whenever the SQL Server Agent starts, I want to get an email because if I walk in first thing in the morning and I see an email from SQL Agent that our production server restarted last night or at least someone restarted SQL Server Agent, then I know that something went wrong. Even if there was a failover where things took care of themselves, I want to at least have an email saying here the SQL Server Agent restarted.

Here are things we noticed at startup. You can do things like have it output SP blitz to a spreadsheet if you want or to a markdown file so that you can see is there something unusual that happened recently. You can have it read the error log like the last 100 messages from the previous SQL Server error log andor the previous SQL Server Agent error log so that you can see well it probably restarted because it was a command by Alex over in the BI team or it restarted because it hit a non-yielding scheduler. I can get an answer right there inside that email. Agent is great at these kinds of things, but as your shop starts to become larger, as you start to have more than one SQL Server, as you start to have complex high availability and disaster recovery setups, you start to see more stuff. I have been working with SQL Server for over a couple of decades now. And some of you out there in the audience have been working with SQL Server for more than a couple of decades as well. The value of this session is just in the terrible things that I have seen over the years as I tried to make my agent jobs more complex.

Itโ€™s also funny to me that whenever I put up a picture of myself, especially when Iโ€™m focusing on my hair, I canโ€™t help but notice my forehead. When I was very young, I was in my teens, I was self-conscious about how big my forehead was. Not because I was worried about it as like a physical problem. You would think that I would be more self-conscious about my nose. Select that thatโ€™s the biggest problem on my face. But I was actually more self-conscious about my my hairline cuz I was like, am I going to notice if it recedes? Am I going to notice if my hairline goes back? And so when I was a teenager, I measured my hairline. I put my hand over my head like this. And I was like, โ€œOkay, Iโ€™m going to remember that the bottom of my hand touches my eyebrows and the top of my hand touches my hairline.โ€ So, if thatโ€™s ever not the case, then Iโ€™m going to shave my head. Like, I have no vanity about my hair. Iโ€™m just like, Iโ€™m just going to shave it. But it actually has always been this large. It was like this when I was a teen. Now, over the last couple decades, Iโ€™ve done all kinds of things for money. I am gladly whenever the business says, we want a solution that does X, I will build X. I may advise them against it. I may say itโ€™s a bad idea. But generally I want to accomplish whatever problem the or whatever resolution the business is having.

5:42 What a growing shop needs from a scheduler

If they want to move data in between Microsoft Access and somebodyโ€™s Palm Pilot, by God, Iโ€™ll build a job in order to do that if it makes my manager happy. But the older that I get, the more I find myself wanting to be able to go on vacation and not be on call, to be able to sleep through the night. And in order to get that, some of the things that I want are there on the slide. And weโ€™re going to dip through each of those and talk in more detail about SQL Server Agent and how it combines or how it causes me problems. So start with reliable failovers. Letโ€™s take a relatively simple architecture. Iโ€™m going to have get out my magic pointer here. Iโ€™m going to have one SQL Server instance thatโ€™s a failover clustered instance. It can fail over back and forth between prod one and prod two. Itโ€™s got its fast shared storage sitting right next to it. This is what your grandpa used to call an active passive failover cluster, but these days we call it a failover clustered instance. Itโ€™s just one instance of SQL Server.

6:57 Failovers and which replica runs the job

Itโ€™s got the databases on it. And this way if one of these instances goes bump in the night, we get an automatic failover within 60 seconds with zero data loss. Failover clustering is reliable. Bolts work the same way for about 20 years now. Itโ€™s pretty straightforward. Itโ€™s great for high availability. Then we have another SQL Server sitting next to it. This just got local SSDs in it and itโ€™s cheap. And we do this because we want to be able to let people query it for reports. We want people write the nastiest reports and like PowerBI or Tableau or whatever. They write really crappy queries and I want them to be able to hammer this box without causing me problems. Then Iโ€™ve got another box sitting next to it in say Boston in another data center and I use this for my disaster recovery. The databases are synced over here. All of this stuff is in one availability group. So I can fail over relatively easily to my DR data center if I need to. The way that I set up backups, and most of us do in most cases, is we schedule them either with maintenance plans or with Ola Hollingrenโ€™s maintenance scripts, which I love. The way that weโ€™ve decided to set up backups is we do full backups every night at 11 p.m. Then I do log backups every 15 minutes.

And because Iโ€™m trying to load my data warehouse overnight at the same time, because I have a lot of maintenance jobs that are going on, Iโ€™ve decided to offload my backups to a readable secondary, like the fast report box sitting next to it because nobodyโ€™s supposed to be querying that in the middle of the night. Well, letโ€™s say on Saturday morning, the full backups kick off and then the server thatโ€™s doing backups locks up. And I want to say lock up rocks up here because sometimes you know we get the blue screen of death. Sometimes SQL Server stops processing due to like a non-yielding scheduler type thing. The bottom line is SQL Server stops working. Whatever SQL Server was doing, it stops. Okay, which SQL Server stopped? Was it the primary? Was it the replica that was taking the backups? Am I going to deal with the failover thatโ€™s happening while the backups are running? Does the replica that failed come back online? So say for example that weโ€™re offloaded our backups to a readable secondary. That thing crashes and doesnโ€™t come back up offline. What happens? There is no system that automatically says, โ€œOh, that replica over there, he was in the middle of taking a backup.

I he hasnโ€™t had any activity for like the last 10 minutes. Iโ€™m going to go start over and take a full backup from here on out. Okay, look, right about now, some of you are like already brainstorming on the ways that youโ€™re going to fix this problem. Hold that thought cuz Iโ€™mma hammer you with problems like youโ€™re going to be standing underneath a waterfall of not water. You know, itโ€™s going to be gross. Weโ€™re going to discuss a lot of the problems. To stay current, go hit at the bottom of the slide. It says brentosar.com/go/batch batchjob. Iโ€™m like slurring because I have to use a throat loss when I talk like this because I burn my voice out fast. So this whole PDF is available there. So before you start writing the solutions, hold on. Letโ€™s just talk through the questions. So 10 minutes has gone by. Is a secondary going to pick up that the backup job failed and started? Even if I have my backup jobs running every minute, even if I set up a full backup job that says on every replica, has there been a successful backup in the last x hours? If not, go run one. Iโ€™m going to run into a race condition between my multiple replicas. Iโ€™m going to have either multiple replicas are all going to start kicking off taking backups or theyโ€™re going to try to run linked server queries to each other trying to understand whoโ€™s supposed to be taking the backup.

11:03 Long-running jobs across replicas

Do my other replicas even know that thereโ€™s a problem? If thereโ€™s a longunning query, a lot of us donโ€™t do anything about that. And we certainly donโ€™t automate doing anything about that because weโ€™re used to backups taking forever. Weโ€™re used to check DB taking forever. Weโ€™re used to our index rebuilds taking forever. And just because a job failed or it ran for a short period of time doesnโ€™t mean that weโ€™re going to leap into action. More questions on here. Do we have any monitoring systems for failed jobs? And right about now, a lot of you are going, โ€œOf course, I use a commercial thirdparty monitoring app that emails me every time I have a failed job.โ€ Yeah. And letโ€™s go open up your Outlook and letโ€™s go look at the folder that says alert emails. The one thatโ€™s got 6,000 unread email messages in it that youโ€™re not actually doing anything about. Oh, I go in once a week whether I need to or not. I look at it and I go, โ€œOh, yeah, thatโ€™s a lot of messages.โ€ And then I go back to surfing Reddit. Letโ€™s be honest, our servers have too many jobs on them and they fail all the time for all kinds of reasons. We never really go in and take a look at that. I know I have so many stories about problems like that. Do we use any third-party backup tools like Convault?

12:21 Backup jobs that fail without anyone noticing

So far, youโ€™ve been assuming that we use native ma native maintenance plans or Ola Hollingrenโ€™s backups. What happens if the same scenario takes place and itโ€™s like a convol backup? Convolult starts the backup connected to a readable secondary. That readable secondary crashes or the primary the primary fails over from one place to another. What do you have convoled to do? Is it configured to automatically retry your backups? Is it configured to wait after a certain period of time and then try them? Or do you just figure youโ€™re going to pick them up tomorrow and see what happens? Do we have any subsequent jobs that depend on it? Like refreshing our dev or QA servers? At some shops, every night or once a week, we pull the most recent restores and we go apply them to another server. And are we automatically deleting backup files older than a certain number of hours or days? Let me tell you a story. So, this is 2004, Brent. This is a picture of me from over 20 years ago when I was managing the databases at a company where we had only a couple of SQL servers and I was also managing developers and writing code myself. And back then we had a problem where we only had so much money and we could only keep so many days of full backups.

And we had a long holiday weekend where I checked out on Friday, went off and go do my thing. The backups failed Friday night. They failed Saturday night. They failed Sunday night. We only had so much space. So Iโ€™d only configured SQL Server and the network share to only store the most recent 3 days worth of backups. And thatโ€™s it. And of course, Sunday night, the backups fail and the jobs are already sweeping out the old expired deleted files and I didnโ€™t have anything in terms of being able to restore my databases. I was lucky when I came in on Tuesday where I had actually caught the failures and said, โ€œAll right, let me go correct that.โ€ 2004 didnโ€™t bring didnโ€™t include any of this. 2004 Brent didnโ€™t think about any of this stuff ahead of time. In 2004, Brent only figured out these problems after 2004 Brent got burned. Like I had to be burned repeatedly in order to solve before I started fixing some of these problems. Donโ€™t feel bad if everything that I just talked through already exposed holes inside your own backups scheduling and this is just your backups. This isnโ€™t even anything like corruption checks or index maintenance or statistics maintenance or heavenly batch jobs things that are even more complicated.

I am always stunned that in the year 2026 when I install SQL Server, the distance between installing SQL Server and having protected databases is so far. Thereโ€™s no guey that says how many days of backups would you like to keep? How much data are you willing to lose? Whenโ€™s the best time for maintenance? And it doesnโ€™t automatically set anything up. The cloud databases all do this. Azure SQL DB does it. Hypers scale does it. Managed instances does it. Amazon RDS does it. Google Cloud SQL for SQL Server does it. Every cloud provider has figured out how to do this. But the including the ones that cost like a dollar an hour, the Microsoft SQL Server, $2,000 a CPU core doesnโ€™t have that capability. Thatโ€™s kind of bananas to me. But I understand that Microsoftโ€™s been a little busy lately putting Copilot in Notepad and then taking it back out. Okay. The next thing that causes problems with me with SQL Server Agent, how many times have I seen a SQL Agent job fail and when I go to investigate into it, the data thatโ€™s inside there is so close to useless that I end up laughing at all I get is a little red X showing me that the job failed.

15:20 No built-in defaults or policies

16:38 Job history and logging limits

And often itโ€™s a big long line of red Xโ€™s and then all I get when I look into the logs is the job failed. There was too much text inside of there. Huh. Thanks. Thatโ€™s why I went into this screen. I didnโ€™t need you to tell me that the job failed. This is really frustr Microsoft automated backup configuration us DBAs wouldnโ€™t be making the big money. Donโ€™t even get me started. You donโ€™t make the big money. Youโ€™re not wearing a Balenciaga shirt. So, how do we fix that? So how do we conf how do we get so that we can get some observability inside of SQL Server Agent? Well, you can Aaron Bertrand has a phenomenal tutorial post on it that talks you through how you configure SQL Agent to either log all the manute details to a text file or log them into a database table. But we have to do this for every single step of every job. We have to do it for every agent job step that we have.

Thereโ€™s no inheritance. Itโ€™s not like I can say at the SQL Server Agent level, hereโ€™s the default of how I want all my logging to done to be done. And for Godโ€™s sakes, if thereโ€™s going to be default logging done, I want it done by default out of the box. SQL Server should jo should log I get so pissed off about this should log every step the input and output of that not as an exception that should be the default and then you should have to opt out of that and even when you get it thatโ€™s only standalone logs these are text files that live in a folder somewhere and thereโ€™s no high availability or disaster recovery for it by default they just go into a local folder which is stupid when Iโ€™m dealing with a cluster or an availability group. I want a shared central place for logging where I can look at everything involving that cluster to understand what was the first failure that happened, what were subsequent failures that were caused by that and then what teams are impacted by it or what databases are impacted by it because sometimes for example when we have restore failures like if Iโ€™m refreshing dev QA and that fails I want to be able to give visibility into the teams that are affected by it. Observability means a lot of different things, but generally speaking, it means making it easier to troubleshoot, to have someone be able to have a a single pane of glass where they can just look and see very quickly at a high level everything thatโ€™s happened inside the code and the infrastructure that it relied on.

19:31 Time to diagnose a failure

I want to like start a timer and say whatโ€™s the fastest that I can get from knowing that there was a job failure to knowing [clears throat] what caused it to knowing what systems it took down to knowing when Iโ€™m going to be done recovering from it and how quickly can I get this email out and have this email be accurate. In a perfect world, I donโ€™t even want to write the email. And I donโ€™t mean that it doesnโ€™t go down. I expect systems to go down or in tech but in a perfect world I want a dashboard that my end users can look at so that they can see whatโ€™s the status across all the jobs in the enterprise and going working from the other way around hereโ€™s the system that I have what jobs have failed that affect my system both Azure and AWS do a really good job of this and their status pages the their status pages are now to the point where if you log in and look at your status page, it only shows you the services that you use and the regions or data centers that you use them in. Then whatโ€™s impacting those so that you donโ€™t see about a data center outage in Iran caused by the war going on over there if you donโ€™t have any assets inside that data center.

20:53 Disaster recovery across regions

If you havenโ€™t been staying current on the news, AWS lost a data center and over in the Middle East and doesnโ€™t think that theyโ€™re going to be able to get it back. They didnโ€™t replicate the data offsite. So, if that was your strategy, your single point of failure, youโ€™re in for a big surprise. Next up, the next problem that I have with SQL Server Agent is dependencies. Itโ€™s very often to have or itโ€™s very common to have multiple jobs in SQL Agent that impact each other. And itโ€™s hard as a as an architect or as a database administrator to go, how do I want to make these things trigger each other? And if this one fails, should this one still happen? How long should I wait in between these two? Do I put them inside the same job or do I have one job kick off another job? And to explain it, Iโ€™m going to just stick purely with DBA jobs. Just the simple stuff that you and I should be able to do without thinking about it. Backups, corruption checking, index rebuild, and maybe refreshing production or refreshing Devon QA refreshing production would be pretty funny. So, letโ€™s say, but I want every morning at 1:00 a.m. I want my full backups to start. Then whenever they finish within 1 minute, I want my dev and QA refresh restores to start because Iโ€™ve got some jobs that run over on the dev and QA servers.

22:15 Dependencies across servers

I want them to start stripping out the customer personally identifiable data that nobodyโ€™s supposed to see. We do all that churning over in dev QA, letโ€™s say. And because that takes a bit of time, I want to kick that off the moment the full backups finish so that the dev box is ready for my developers whenever they come in. Then at that same time, at the same time the dev and QA refreshes are starting to churn, then I also want my prod index rebuilds to start too. As soon as my full backup is done, I immediately want to kick off my index rebuilds. And both of these systems are churning in parallel at the same time. But my backups get out of the way first. I want that fast and quick so that my dev servers can go do their thing. Thatโ€™s one thing I might want. But I might want something else. I might want after my full backups finish, I want my dev and QA refresh restores to start because I have to sanitize their data. Then my sanitize jobs start and finish. Then after that, then I want my prod index rebuilds to start because I only have one set of VM hosts and theyโ€™re not all that powerful.

23:33 Everything at midnight

Itโ€™s really funny. Iโ€™ve had a couple of clients where they trigger everything to happen in the entire VM farm at midnight and so the servers are bored, bored, bored, 10 p.m. 11 p.m. Whatever. Folks are putting in like alternate tools over in the webinar chat. Hey, do me a favor. Remember how I said that you probably needed to pay attention and listen to the problems that weโ€™re talking about. I meant that. Focus on this and letโ€™s talk about the problems because youโ€™re missing the problems by going on and talking about solutions that donโ€™t even cover everything. Your attention is not expensive. You donโ€™t have to get Iโ€™m not charging you for this. This is free. F re you can pay attention. I know youโ€™re broke. I know you donโ€™t make that much money, but you can pay attention during this class. The more you know. So, Iโ€™ve had environments where the everythingโ€™s piled on to the same VM host and things are nice and quiet and bored leading up to midnight and then wam at midnight everybody has all their scheduled jobs kick off and you can see the lights in the data center start to grow dim because everybodyโ€™s using every bit every bit of power and then they canโ€™t understand why the thing is so dogone slow at midnight and why their jobs take forever to run.

Whereas, if they just staggered these out and was aware of what was happening at different servers at different times, all of the jobs would run faster because they could leverage every CPU cycle that was available across all of the hosts. Or maybe maybe I want my index rebuilds to start first and as soon as those are finished, then I want to run a transaction log backup. Hey, this is the part where you should really pay attention because this is actually pretty awesome. I want my index rebuilds to start and then finish first because when it comes time to do a restore, letโ€™s say at 10:00 a.m. I get that phone call. Weโ€™ve all had that phone call. Yes, this is Brent that Oh, Iโ€™m talking into the wrong side of the phone. Brent, yes, this is Brent, the database administrator. You just dropped a table in production. Well, first off, let me go disable your login. But how fast can I get that table restored? Well, [sighs] Iโ€™m going to have to restore last nightโ€™s full backup onto another database because I donโ€™t want to overwrite the production database. Iโ€™m going to restore last nightโ€™s full backup. Then I have to restore all of the transaction logs that happened since then up until the moment that you dropped the table.

26:29 Lining up maintenance, backups, and restore points

In that situation, the more activity that you had after the full backup, the longer this restore will take. So, in a perfect world, if Iโ€™m going to do a bunch of maintenance stuff overnight, I want to do all the maintenance stuff first, get all that scrabbling to disk out of the way because I donโ€™t want to repeat it. I donโ€™t want to repeat that whenever itโ€™s time to do the restore. If you do the restore and it has to do a full backup, then backups that include index rebuilds, your restore is going to be slow as hell because youโ€™re essentially doing those index rebuilds again. SQL Serverโ€™s reading through all that transaction log stuff and applying all of that to the data files. Itโ€™s going to be slow as all get out. If you want to perform performance tune your restores to make them happier, make them finish faster, then the thing that you want to do is do your maintenance jobs before that and then kick off your full backups. But thatโ€™s also one of the hardest things to do as database administrators because I donโ€™t know how long my index rebuilds are going to take. Itโ€™s different every night, right? Depending on what your fragmentation percentages were or how many objects need to be rebuilt or reorganized. And I usually if youโ€™ve been through my other classes, you hear how Iโ€™m normally a much bigger fan of rebuilds and reorgs are kind of a waste of time.

These rebuilds can be all over the place in terms of their time and in terms of how much activity the thing had. And I donโ€™t want to have like a two-hour gap of loneliness where the server is not doing anything. The instant that those index rebuilds start, I immediately or finish, I immediately want to run a transaction log backup to clear out that log file, then go start the production full backup so that Iโ€™ll be able to run my restores faster. Thatโ€™s only backups and index rebuilds and a possible dev QA environment. Jobs get so complex so fast, especially when we start to talk about things where we layer on complexity. Do I have to do things over in a dev or QA environment? Do I have reporting servers that I need to go refresh and repopulate? Do I have source servers where I need to pull data from? Do I have any kind of third-party app thatโ€™s going to go through and change the database like remix, excuse me, remix personally identifiable data in order to get it to be presentable to the devs or to outside third parties. And then heaven forbid youโ€™ve got anything like other database platforms, MySQL, Postgress, Oracle, whatever, and you want to have all of their restores available at around the same point in time so that in the event that you do a disaster recovery failover, you restore from one data center to another.

You sure donโ€™t want to have your SQL Server like backups available around 9:00 a.m. Or your restore point at 9:00 a.m. And Oracle like midnight. You want those two as close as possible to make it easier for your teams like data warehouse teams to be able to coordinate that data. And across all of those jobs, everybody wants to read their own logs. The data warehouse team wants to be able to see what was happening in their jobs. The DBA team wants to see whatโ€™s happening in their jobs. The development teams that set up all these agent jobs that are basically app servers. Go repopulate this table. Go trigger the order shipped thing. They want to be able to see all of their agent jobs. They want to be able to check the status. They want to see which ones are running long. They want to see how long theyโ€™re running. They want to be able to disable the jobs if theyโ€™re going to go make changes to them or if theyโ€™re going to rebuild some of the source data. They want to be able to postpone the schedules or run them again in the event that they had a problem with them last night. They want to know if their job failed because someone elseโ€™s job failed. And SQL Agent security blows. Microsoft has made some improvements to it over the last couple of ver versions, but itโ€™s still terrible. I still canโ€™t easily say these agent jobs are the dev teams.

30:22 Job ownership and security

These are managed by the Windows AD group that is the dev team. These are the DBA teams jobs. If anybody touches them, put their fingers into a lawnmower. These are the reporting teams and so forth. Itโ€™s not easy to break that out and associate it with Windows security to the point where when someone creates a job, it automatically goes into the right group and inherits the right permissions. And if I mess it up, letโ€™s face it, this is not the most glamorous part of the database administration work. If I mess this up, Iโ€™m the one who gets in trouble for not having it correct. Thatโ€™s why I said at the start, and some of you with eagle eyes might have caught this and said, Brent, why would you want someone else to control the security? Thereโ€™s no glamour in that. Thereโ€™s no fun in that. Thereโ€™s only blame. And itโ€™s rare for me to not want to control something. Iโ€™m a control freak. I donโ€™t want to control everything, anything that goes around with a SQL Server. I want to control the cloud. I want to control the storage. I want to control all of the permissions. But SQL Server Agent just doesnโ€™t have the tools built in to enable me to do that. So I would have to roll my own. Iโ€™d have to roll my own dashboard, my own configuration tools, whatever.

31:39 Keeping job code portable and consistent

And if I did that, I wouldnโ€™t be able to take it from one company to the next. My employer would say, โ€œSorry, you anything that you wrote while you worked here is ours. Weโ€™re going to keep this. Itโ€™s proprietary. Itโ€™s ours.โ€ And nobodyโ€™s open sourcing anything like that to make it easy either. Oh, there are more. Letโ€™s come back to our environment. I got my three instance environment thatโ€™s spread across a couple of data centers. I got my two in Seattle here. Iโ€™ve got my one over in Boston. When Iโ€™m managing agent jobs, each one of these has its own SQL Server Agent. And as a database administrator, Iโ€™ve got some things that can only be done on the primary index maintenance and stats maintenance. Of course, you can only do index maintenance on the primary. And let me go back one. Itโ€™s not necessarily that thereโ€™s a primary server. I could have three different availability groups. I could have one availability group for staging, one availability group for the data warehouse, one ag for the archives like you are forced to in standard edition, but sometimes you choose it even on enterprise due to the complexity of stuff. I may need to have the staging and data warehouse be the primary over here and then have the archives primary somewhere else.

33:03 Running each task on the right replica

So itโ€™s up to me as a database administrator to make sure that the primary database tasks run on the primary and some of them I can offload to a secondary. Some of them I have to offload multiple secondaries. Corruption checking. Letโ€™s go back slide again. So, letโ€™s say that you decide to offload corruption checking and you do it on the reports box in Seattle. Okay, cool. But what happens when you fail over to DR? If you fail over to DR, this box could already have corrupt storage and you wonโ€™t know. And because you failed over, automatic page repair isnโ€™t going to work if the primary boxes arenโ€™t available. If Seattle went offline and we failed over to Boston and we find corruption there, itโ€™s too late. So really, when I say that you want to do corruption checking, you want to do corruption checking in two places. Everywhere that youโ€™re backing up from and everywhere you want to fail over to. And those are separate. You want to do corruption checking anywhere youโ€™re backing up.

Cuz letโ€™s say that weโ€™re doing our backups over here on this Seattle box and weโ€™re running corruption checking over in Boston. Well, Iโ€™m corruption checking something I ainโ€™t backing up. So, I could be gladly backing up corrupt data in Seattle and the only time that Iโ€™ll find out is when I go to restore it. When someone says, โ€œHey, we need that sales table from last year. Go restore it.โ€ And I go restore it and itโ€™s corrupt. Sure, I got a clean copy of right now in Boston, but thatโ€™s not what they asked for. They asked for a table as of last year. So, I have to run corruption checking anywhere that Iโ€™m backing up. And I also have to do it anywhere I want to fail over to. So itโ€™s not enough if Iโ€™m doing offloaded backups over here into Seattle and then I want to fail over to Boston. I have to do check DB on both of those and thatโ€™s up to me as a database administrator to schedule. I have to know where these things need to run. Some of those tasks rely on something else happening elsewhere first. Like maybe I want to have a my production restores finished. Then I can kick off jobs to go restore on a secondary. And user tasks are all over the place.

Sometimes those run on a primary, sometimes they run on a secondary because they feed data that needs to happen. So be sent out to somewhere else. What I need is I need the agent job to figure it out. I need the agent job right at the very beginning to have a step that says, am I running on the right replica that needs to do this work? If so, go do the work. But thatโ€™s not enough. You also have to say, am I the right secondary to be doing this work? And if there was another secondary that was supposed to do the work, but thereโ€™s something wrong with him. Heโ€™s frozen. He has non-yielding schedulers. He canโ€™t access that database. SQL Server until 2025 wouldnโ€™t even fail over if there were problems with the user database and be like, โ€œYeah, weโ€™re having problems with that database.โ€ And still not fail over. I need all of that logic to be in every agent job that I have. And I canโ€™t trust the people who create agent jobs to do this and know this. I canโ€™t trust database administrators. I canโ€™t trust myself to do that reliably. Itโ€™s so much work.

So I need consistent code thatโ€™s copy pasted across all of my jobs. And when I find a bug in it, I need that to replicate out across all of my jobs. And I need consistency across servers. I can add replicas, drop replicas. I can have problems with replicas. People can upsize, build a brand new SQL Server. I all these issues. And it only gets worse when we start to go up into the cloud because our cloud administrators like to say things like treat servers like cattle, not like pets. Meaning they want to be able to just create and destroy replicas without h with doing as little human intervention as possible. We as DBAs, weโ€™re used to thinking, well, Iโ€™m just going to handedit this agent job over here because this is the primary. Iโ€™m going to make sure that it runs. Then Iโ€™m going to hand edit this one over here on the replica to make sure it only runs whenever we do the failover. And then all of that get stuff gets lost whenever people build brand new cloud VMs or want to do something like managed instance link. So what I want in a perfect world is I want one source of truth that has a tracked history for all of my jobs. I want the ability to change a job in that central place and have it replicate out to all of my relative re relevant SQL servers.

And then whenever I spin out a new SQL Server, I want it to automatically inherit the jobs that are relevant for the environment that itโ€™s in. You can do this with agent. This is why I busted some of yโ€™allโ€™s chops when you were starting in. I could do an MSX or TSX server. Keep watching. It only gets harder. Next, I want restartable item potent processes. I love the word item potent. Itโ€™s fantastic. What it means is that if I have a job that does A, B, C, and D, and it gets through A, B, C fails, and then D runs successfully, I want to be able to rerun that same agent job and have it only do C. I donโ€™t want it to have reinvent the wheel for A and B. I only want it to do the parts that failed. And right about now, somebodyโ€™s like, โ€œWell, I could code that in TSQL.โ€ Yeah, you can. And itโ€™s up to you. You have to be the one to actually do that because you canโ€™t even just say rerun this agent job and only pick up the steps that failed. If you could, that doesnโ€™t work because sometimes things in A and B need to be regenerated in order to produce C accurately.

39:28 Where this leaves SQL Agent

Like theyโ€™re reliant on the current date or in the last 24 hours. I love SQL Agent. Itโ€™s fantastic. Itโ€™s pretty reliable. I canโ€™t tell you the last time that I had a bug in SQL Agent and a job didnโ€™t fire because of it. There was some kind of deal with time zones and leap years like a few years ago, but beyond that, I mean, like was something involving if a job failed and was supposed to retry within a minute and it was the exact moment of leap year didnโ€™t work. Something along those lines, but who does that anyway? The older that I get, the more that I want this kind of reliable, predictable, scalable, repeatable type infrastructure. And SQL Server Agent just doesnโ€™t have that. These are the kinds of things that I desperately need for batch jobs. I donโ€™t need them for some jobs. I donโ€™t need them for corre for corruption checking. I donโ€™t need them for index or stats maintenance. I can use tools like Ola Hollingren scripts and just rip through whatever database is writable right now. Go do the index maintenance on that job. If it fails halfway through and the server crashes, I donโ€™t really care because it can always just start again the next night. Itโ€™s not going to kill me. But the instant that batch jobs are involved, that is when youโ€™re really going to want this stuff.

And that is where agent makes it really hard to do these things. I wish I had a little script or an answer or a checklist that I could give you and hand off and say, โ€œDo this with agent.โ€ But unfortunately, it just doesnโ€™t work that way. So, with that in mind, Iโ€™m going to hand it over to Whs Daryl. Iโ€™m gonna hand it over to Darl. I will stop sharing and he will take over. >> Yep. Thanks, Brent. Go ahead and share my screen. All right. Hopefully you guys can see that. All right. >> Yeah, there it goes. Yep. Perfect. Perfect. >> All right. Yeah. So, Brentโ€™s been gracious enough to to share some of his his time and platform with us today. So, for those you donโ€™t know, Iโ€™m Daryl Walker. Iโ€™m a solution engineer at Jam Software. We are a workload automation orchestration solution. Really helping businesses with the exact types of problems that Brennan was just talking about, right? All those issues that he sees with, with SQL Server agents and how they they run jobs, observability, you know, restartable process, all that type of stuff. So I just take a few minutes to kind of walk through our solution which we call jams and how we can really help you kind of tackle some of those issu issues.

42:06 Demo: one view across servers

So the first piece of the puzzle here is you know the observability side right being able to see all the things that are actually happening across the entire organization. SQL agents might be good at seeing whatโ€™s going on on its own server but you canโ€™t really see whatโ€™s happening outside of those those processes. That could be other SQL instances. That could be other servers or platforms in the business that could be running other types of processes. One of the the main features of JAMS is that we can see those things, right? We can see jobs that running on any SQL instance that we have jobs running on. We can see jobs running on any other platform that JAMS can build jobs around and we can see that in one place. So you donโ€™t have to log into multiple instance of SQL and to RDB hop RDP hop around to different servers, right? Everything is here in one view. And thatโ€™s done here on the JAMS monitor view. So this is going to give us kind of that, you know, single pane of glass type view where we can see jobs that are running right now in the business. So we see jobs that have recently ran and their status. I can easily see all my successes, all of my failures, anything that may be waiting on some sort of dependency or verification check, right? Jobs that are currently executing, what their status is, how long theyโ€™ve been running, how long we think until that jobโ€™s going to be complete based off its historical run averages, right? So, so kind of everything here in one place and we can take action on them if we need to, right?

Restart processes, stop them, release things maybe on hold or waiting on dependencies. So, really giving you, the observability side of things that SQL agents, you know, really does lack. This can be filtered as well. So, maybe Iโ€™m only interested in certain jobs. I only want to see jobs on my server. Maybe jobs of a certain type. Jobs running in a certain credential. Fully filterable, fully customizable interface here to be able to filter out and view just those types of jobs. [clears throat] So, one piece, excuse me, one piece of the puzzle there, right? Asability side of things thatโ€™s great. But one of the the really big factors and I think one of the key pieces of of where JAMS can help is with the the task relationships, right? So being able to have consistency across our tasks be able to have all sorts of jobs running on various platforms and be able to see their inter relationships you know whatโ€™s the upstream dependencies whatโ€™s the downstream dependencies and and how do those all interrelate and interact right so SQL Agent can obviously run you know your SIS packages store procedures maybe some PowerShell scripting if you have to but what happens when those jobs then affect something further downstream we need to run a file transfer we need to refresh a BI report somewhere, right?

We canโ€™t see outside of that initial silo of of whatโ€™s in that agent job itself. So, with JAMS, we can do that. Iโ€™ve got a couple sample jobs here. Weโ€™re just going to kind of talk through real quick about how, you know, we can build things in jams and how we can actually see the relations between them and how we can easily chain jobs together. So, u Iโ€™ve got a few examples here. Here Iโ€™ve got a store procedure job, an SSIS job, got file transfer, a PowerShell scripts, Iโ€™ve got an Azure data factory pipeline run and we can easily set these jobs up. So a store procedure very simple we can pre-build connections to our SQL boxes and thatโ€™s done here in our connection store, right? We can build out those connections are stored securely in the JAMS database and now I can simply choose which which SQL box I want to to use for this particular job. From there I can pull a list of databases that are available on that machine and from there what procedures are available within that database. So makes the building process very simple. I can easily say hey in this step of the process I need to run this stored proc and I can quickly build out the job to do just that. Same thing for SSIS, right? File transfer is going to look very similar where we have the same type of setup at the top of choosing our connection type whether thatโ€™s SFTP, FTPS, file transfer between local local you know local paths setting or getting files and then what is the information for the different locations and files that we need to move.

46:05 Building jobs, folders, and Active Directory security

So very straightforward setup. We can also consistently build things. So, you know, we build our jobs here within this definition section. And we have a folder tree as well. And this is used to help not only just organize the jobs themselves, but also set up some of that consistency of how we want these things to be built. So, JAMS follows a kind of traditional inheritance model. So, everything that we set on a folder gets automatically inherited to any child folder and jobs within there. So I can set basic information here about the job. Whether thatโ€™s scheduling information which could include things like basic alerts here, triggers that we need for the jobs to run, dependencies that have to be verified, statuses that are going to tell us and alert us about how a job is actually running, whether itโ€™s too long, too short, stalled, waiting on some sort of dependency, and we can have all of the jobs automatically inherit all of this information by default. Same thing with our properties. What agent or server is this job going to run on? What are the credentials used to launch this job? Do we need to build in automatic retries, right? Scheduling priorities to to make our more pressing jobs run before some of our other lesser pressing jobs. We can do the same with our security, right? I think Brent, one of the things he mentions a key piece is having someone else manage the security of the jobs and that is done very easily in jams.

We use your existing active directory users and groups. Ties right into AD. I can build the users and groups in here and say who has access to not only the folders but even all the way down to the individual job levels, individual credentials, individual agents can all be controlled of who has access to them and what they can do with those different objects. So you see here Iโ€™ve got on this root folder Iโ€™ve got my admin group here that has full control obviously, but I have a lesser group here Iโ€™m calling jam submitters. And what theyโ€™re allowed to do is essentially do stuff on that monitor view. So they can see the jobs there. They can manage those jobs might mean stopping them, restarting them, resubmitting a new instance of it, right? U releasing it from a hold that may be on and maybe even submitting, you know, completely new instances of of that job. So, a lesser role there. They canโ€™t go in and do any sort of destructive action, add or remove or delete any sort of job from my instance, but do have the ability to go in check on their jobs and do potential actions on them. So, again, fully customizable here within the JAMS interface at the job level. Additionally, right, we have some basic jobs, but one of the things that SQL Asia does is be able to kind of, you know, link stuffs together. And we can do the same thing in jams, but Iโ€™m going to argue that itโ€™s itโ€™s a little bit easier. We have a specific job type that we call a sequence.

48:44 Sequences instead of hand-built dependencies

And it looks like this. So, a little bit different than some of the other jobs that we just saw, but basically it allows us to take any of the jobs weโ€™ve already built and easily chain them together. So being able to easily see all of these upstream downstream dependent jobs and how they may interrelate regardless of what platform or what SQL Server theyโ€™re actually going to live on. So weโ€™ve got a couple items here to control the flow of the jobs. Weโ€™ve got sequential and parallel blocks. So sequence here being this larger outer block where jobs run top down and then a parallel block here in the center. We have a couple jobs that going to run at the same time. Building is very simple. We take this submit job. We drag it in to wherever we want to put it. We click this red box and we just pick what job weโ€™ve already built that we want to actually run. So maybe I have an EC2 job Iโ€™m going to throw in here somewhere. Right now Iโ€™ve easily added this job into the chain and itโ€™s going to run this process on this server with these credentials in this slot of of this overall overall process or overall workflow. So taking those you know disparit processes that may have been running outside in their own silos bringing them together we can easily see we can easily observe all of these different pieces and thatโ€™s kind of the the real power of jams is that we can see the entire picture right we can see all of the dependent actions weโ€™re not going to run a job in this sequence until the previous one completes successfully by default if a job in this sequence fails itโ€™s going to halt the sequence so weโ€™re not going to have to worry about a downstream process executing before it should.

We donโ€™t have to worry about building in job dependencies, right, to make sure the previous one actually completed. Itโ€™s all handled by this sequence itself. I think one of the issues that that Brent mentioned in one of his slides was we have to make sure that X process is done. I think it was I donโ€™t index rebuild or something and before we kick off some backup process, right? The the sequence handles that right we need that second job to run immediately after the first one and thatโ€™s exactly what this is designed to do. But we donโ€™t have to build in those pillows scheduled between if we have a variable running process. The sequence itself knows that hey this store procedure is done kick off the next job in the chain here. So again not having to log into multiple platforms to see all of this information. Itโ€™s really here at our fingertips. Second piece of this right notifications and alertability. So [clears throat] by default all jobs in JS come with some basic notifications. If the job fails, weโ€™re going to get the email alert. And then we have a catchall email thatโ€™s designed to grab anything else that might happen. Again, send an alert and send us some basic information about the job. So we can customize this out if we want to send to whatever mail server we want to whatever destinations customize the subject information whatโ€™s in the body of the message and we have all sorts of jams system variables we can use to grab data about the job itself and be able to pass that into the in this instance email alert but we can also do other types of alerting mechanism if you have additional needs there.

51:46 Alerting on exit codes and conditions

So you know SQL Agent can probably do some decent alerting on success failure right but if we have variable alerting types or or things other things we might need to alert on that gets a little bit trickier to actually build in but in jams itโ€™s a little bit simpler of a process because one we can monitor different things about how the job ran so not just static hey the job succeeded the job failed but obviously we have statuses on again how the job is currently running is it running too long? Is it running too short? Is it waiting on something that weโ€™re thatโ€™s dependent on that we havenโ€™t found yet or we havenโ€™t verified. We can also do the same with other types of actions that may make the job exit in a different way. So for instance, this job here is a PowerShell script, but Iโ€™ve coded in some additional exit codes that mean certain things to me. So, weโ€™re doing some data validation on a file that gets spit out from one of those other processes. And we need to make sure that thereโ€™s something in the file and it has the data that weโ€™re expecting, right? We donโ€™t want to send this file to our trading partner potentially if the file is empty or it has doesnโ€™t have the right column headers or missing data. So, we can spit out different exit codes based off of that. And I can set various properties on the job.

So, Iโ€™m using this one called specific warning. Itโ€™s going to take those 10 11 12 13 exit code numbers and spit out a warning alert here. So thatโ€™s tied to this email and we can see Iโ€™ve checked my entry warning. So when that job runs and fails with a warning, itโ€™s going to run this email alert here telling me I have a bad source file and again give me some custom information about the job itself that ran. So I can see all the information. I know exactly why the job failed and I can go in and address that particular cause. So, weโ€™ve gone from generic success fail alert to now having, you know, much more customizable scenarios of knowing what the issue is and potentially remediating that before it becomes an actual problem. [clears throat] in addition to just emails, like I mentioned, we can do other types of alerting. So, we have two specific jobs here as well that we can send based off events called notification and recovery jobs. And they do exactly what you expect them to do based off their name. So notification job is allowed designed to have you send some sort of notification type alert, right? So itโ€™s a job you would build in JAMS that has access to some special variables about the parent job that triggered it. So we can do things like open a ticket in our ticketing system or interact with our paging alerting system perhaps, right? Or send a Slack or a Teams message.

So we can utilize a notification job for that. And again, straightforward setup. We select when we want this job to be triggered based off of some event criteria or some status of the job finishing and then simply select from our list of jobs what job we want to run you know in this location here right and itโ€™s going to do just that. So if this test job were to trigger a service now ticket based off of a low and moderate event we could build that out and have it run here in JAMS. And I think thereโ€™s been a couple questions here in the chats. Iโ€™m try to back up and see if some of these I can answer real quick. Looks like Johnโ€™s handled a few of them. Can this trigger jobs and tools like Azure Data Factory? Definitely. So, thatโ€™s one of the jobs right here. We can do it directly in Azure Data Factory. Weโ€™re also going to come out with a fabric integration here in the next release. So, if you have stuff running in data factory thatโ€™s running through Fabric, weโ€™ll be able to handle that natively as well. But pretty straightforward. We b pick our Azure connection that weโ€™ve already pre-built. Give it our resource group name, the data factory name, the pipeline name. We can utilize parameters. So if those pipelines have custom parameters that are utilized, we can build those here in JAMS and give them information to pass into that in Azure to pull out the data that we need.

55:37 Questions: scripting, holds, and monitoring

So can you set up JAMS jobs using a scripting language set up the guey? Definitely. So, JAMS is super flexible, super customizable, very open, and thatโ€™s one of the nice things that a lot of our customers appreciate is that we donโ€™t lock you into any sort of proprietary scripting language, proprietary really anything. Itโ€™s itโ€™s for you guys, what youโ€™ll appreciate, even the SQL database is open. So, if you want to query directly against the database, by all means, go ahead. But we do have built-in tools. We have a PowerShell module that has over 100 commands in it that you can utilize to do basically the same things Iโ€™ve shown you here in the guey. We also have a REST API that again has onetoone functional equivalency and also a net API. So if you want to build stuff out, you know, via.NET, thatโ€™s also fully supported and allows you to really do some really powerful things with JMS. Is the fabric integration using APIs? I donโ€™t know. I havenโ€™t seen the build of that yet, but I would imagine on the back end thatโ€™s probably what itโ€™s doing. All right. One more thing I want to show you guys or actually two more things I want to show you real quick before we run out of time. So, one of the big pieces again that Brent mentioned was being able to kind of restart a process midstream, right? You have steps A, B, CD, DD, E, step C failed. Maybe I donโ€™t want D to run, but I need to restart from a certain point.

JMS does allow you to do that very easily. So, especially within a sequence, I can submit this and Iโ€™m going to submit it on hold. So, we can cop over to our monitor once again to take a look at it. So, we see this job is on hold. We have another view thatโ€™s super useful for this type of process and thatโ€™s called our diagram view. So, we can see all of these processes here again in one place and we can watch them actually execute live. So, I can rightclick and release this job. Click okay and we can see this actually running in real time. So, again the sequence is meant to handle not running a job until the previous complete. So, we started with our short procedure. Now we got our SSIS and Azure data factory pipeline run then running at the same time. Once those both complete, run that data validation script and then end with our SFTP transfer job. So, weโ€™ll watch and see what we got going on here. But, basically, if any of these steps fail, like I mentioned, itโ€™s going to halt the sequence at that step. Itโ€™s going to allow us to do, you know, a couple different things. So, if the job fails, we can simply retry it. We can also have those built-in retries. So, if we want the job to automatically retry a certain number of times before failing, we can do that. But also here within a step right we can rightclick and we can see we have different actions again. So since this oneโ€™s completed we can see its log file if we wanted to right see all the information about this package and how it ran all the steps that were involved in it statistics about its runtime properties that it ran with its schedule it had any parameters and their values audit trail for actions done job history.

So we can kind of dig into all of these and you notice we did get a fail and I actually got that alert email that we were talking about. So that data validation bad source file. So we exited with a in this instance exit code 13 to say that my file that we were checking had something wrong with it. So I got that that customized alert here. But we can actually again dig into the log of this right from this view and say show detail and kind of get more information about it. So we see weโ€™ve got exit code 13 severity warning and we can see all the information about what the spit what the script itself spit out which was we failed because my row count was 21 and I have a minimum required of 25. So we got that data information here and we know what the problem is. I can go in and potentially fix it maybe rerun the previous step to generate the correct report output. But in this instance, itโ€™s because I designed to do that. Iโ€™m simply going to go ahead and modify the actual source file itself here, which we can actually do right from here as well. I can go to properties. It pulls up that source. And Iโ€™m just going to drop this to 20. We can save it. That saves the base job. And now I can simply retry it. And itโ€™s going to complete successfully and run my SFTP transfer job. So, you know, live troubleshooting, live restartability of processes in our chain.

You donโ€™t have to to wonder or worry about whatโ€™s going on downstream, what happened upstream. Again, everything in in one view, one picture. Simple place to go to to get after all of this information. So, thatโ€™s thatโ€™s kind of the JAM side of things, right? In in a nutshell, is the execution history stored into any relational database? It it is. So, every time a job runs, that entry gets stored in the JAMS database. So the only things that live outside of the database are the physical log files that are also generated and thatโ€™s what this is here. So all the data about the run itself, where it ran, who ran it, when it ran, status, severity data, if it piped out any sort of output or error messages, right? That would all get in here as well. So like we saw previously, this is the, you know, output and error from the script itself. That all gets logged in jams by default. So you can query this out, you can pull it up in any of your reports if you need to. It does get emailed and sent out to your alerts by default as well. It gets attached as a to the email message for example. So all that data would would kind of come with the job here. The last piece I want to show you is how easy it is to actually convert some of these tasks off of SQL Agent into jams.

1:01:00 Converting existing SQL Agent jobs

So, we do have a built-in converter in the desktop client here that allows you to just query a SQL box directly and you can just rightclick and convert these. Does an okay job, but weโ€™ve actually just recently released a newer converter that weโ€™re calling jump thatโ€™s does a little bit better than the native built-in one. So, follow the same procedure here. We select our SQL Agent here, but you can see weโ€™ve got other solutions. So if you have stuff running in taskuler for instance or cron we can easily convert those tasks into jams jobs as well. So we can click click SQL Agent again Iโ€™m going to feed it my server name. We can customize how weโ€™re connecting to this. So if we need to specify a certain username and password we can specify information about where this is going to go in jams such as the agent used the the run as or execute as property. And we can query multiple servers at the same time. So if I have, you know, three, four, five, 10 SQL boxes that I want to pull jobs off of, we can do that here. I can filter by category. If our jobs are referencing script files, you have two options, right? You can keep that file reference where weโ€™re just going to say, you know what, this job is going to call this PowerShell script. We also have the option here just import the contents of that script directly. Thatโ€™s a lot typically how we we recommend that you do things in JAMS is not maintain script files scattered all over the place because JAMS has built-in auditing and revision control.

So itโ€™s going to automatically take any take a revision of anytime someone goes in and makes a change to a job. You can go back, you can view those changes, you can compare them, you can revert back to previous versions as well. So thatโ€™s all kind of built in built in for you. So weโ€™ll say import script contents and then how we want to chain our jobs. If itโ€™s a multi-step agent job, we can do it with completion triggers or we can create a sequence like we were just looking at. So, we extract. We can see all of these processes here. We see any potential warnings about any of these that may come over. We can select, you know, one of these that we want to look at, for instance. Continue. And now we just put in the information about our JAMS environment or we can do a quick dry run to test it out. And itโ€™s going to convert those over into JAMS for us. And I can even check out, you know, report say, hey, this is what happened. I moved these jobs. One manual thing we need to look at. And thatโ€™s just warning us that, hey, we split this into multiple jobs in a sequence. Just go and make sure that theyโ€™re in the right order and everything looks correct. So, but it does also build it out live. So, if we wanted to actually convert it for the real deal, we could do that as well. So if we pick the same one create our sequence and weโ€™ll go ahead and select the same job.

I can actually go ahead uncheck my dry run. I put in the information for my SQL box here. Test our connection out. Weโ€™ll give it a folder prefix to put everything into. And now we can do a live import again. And Iโ€™m actually going to take us over to our new web client because itโ€™s a little faster for me on this machine. Go to our jobs view and we should see our Ozar folder and all of our jobs here within here. So we can see our PowerShell script that weโ€™re running, right? We can see that here. We could see we got a store procedure that weโ€™re going to run. So right all of this gets pulled in by default. We pull in all the information about the database connection and build that automatically. The procedure that was pulled, the schedule if it had any that it ran. Any parameters that are needed, right? So this one had a business entity ID parameter that got pulled in automatically. So we try to make the process as as simple as possible for you to to take these workloads that maybe donโ€™t need to be in SQL Agent and easily build them out into JAM. So we see weโ€™ve got our sequence here. Itโ€™s built in automatically all the steps in the correct order. Again, schedule came comes over by default.

So, now we have a job that is is ready to test and ready to to run here in production in JAMS. So, thatโ€™s thatโ€™s it. I know we think weโ€™ve been a little over time, so I thank you guys for for kind of bearing with me. Anyone have any final questions? I think we looks like going back through the chat and I think we got everything. Yes. So, you know, if if this seems like a tool that could help you guys out, things you guys are struggling with, definitely feel free to to give us a shout. We can walk you through a formal demo, right? Talk through your use cases, you know, how we can help you guys kind of overcome some of the challenges. See last question come in. Does it server side run on Linux? So, today the server itself, so the Jam server components are Windows only on a Microsoft SQL backend database. We are adding or we do have plans to potentially come out with maybe a Linux release at some point in the future and also maybe a Postgress database. But thatโ€™s maybe thatโ€™s a little farther down the road. So today it is a Windows only for the installation of JAMS but we can run on Linux as far as the the jobs go. So we have a Linux agent that supports virtually any Linux or Unix system out there and we can run you know whatever types of jobs we need to on those platforms.

1:06:18 Wrap-up

All right, I think thatโ€™s it. So, unless thereโ€™s any last questions, Iโ€™ll turn it over to I think Bobby or Katie was going to have some final comments for us. >> Yes. Thank you, Darl, and thank you, Brent. I just wanted to say, be on the lookout for a follow-up email from us. Weโ€™re going to have a really fun challenge with a reward for the winner. And I hope you guys have a wonderful rest of your day and thank you guys for your time and we hope we get to see you again soon. So thank you everyone.

How to Measure Your SQL Server

Curious how your SQL Server actually stacks up? Brent walks through the metrics that matter when youโ€™re sizing up server performance and what they tell you about the health of your environment.

Your SQL Server Toolkit Just Got Bigger

Ready to move batch processes off SQL Agent? These two free tools are a good place to start.

Windows Task Scheduler Diagnostics Script

Instantly audit your Windows Task Scheduler jobs and catch problems before they catch you.

JUMP Migration Tool for SQL Agent

Migrate off SQL Agent fast, without manual rework.