I learn things every day. One of the great things about the SQL community is the willingness to share their learning experiences and today’s episode is an example of this. I chat with Mark Wilkinson about the SQL Server Agent–some best practices and MSX–a feature I have not used in the past. MSX is a feature that allows you to keep your agent jobs in synch, but like always–there is a catch.
If you are interested in expanding your SQL Server agent horizon, this episode is for you.
About Mark Wilkinson
Mark is a father of 4, and lives in Raleigh North Carolina. In his spare time he likes to read, speak, and learn new things. Mark currently works as a Database Administrator at ChannelAdvisor where he solves fairly interesting problems on fairly large databases.
Transcription: SQL Server Agent
Carlos Chacon: This is SQL Data Partners podcast. My name is Carlos L. Chacon, your host. This is episode 16. Compañeros, welcome to another great edition. I’m super excited to have Mark Wilkinson on the show today. As this goes to show you, you can always learn something new. This is one of the great things about the SQL Server community is their willingness to share.
Our topic today is the SQL Server Agent. That may seem a little bit boring, a little bit dry, but I want you to hang in here with me. Now, if you know what MSX is then maybe you can turn it off. I’m sure Mark has something that’s going to be very interesting to you. I personally didn’t know what MSX was. It’s very interesting to me you’re going to be able to learn something new about this technology.
As always, if you like the show, you like what you’re hearing, we want you to subscribe in iTunes, or on Stitcher, or some of the other channels, as well. You can hit us up on Twitter. I’m @CarlosLChacon, or you can hit me up on email, at [email protected].
The notes from today’s session will be available at sqldatapartners.com/podcast. You can get the information for today’s session there. There will be lots of things we’ll talk about, that you’ll want to deep dive into a little bit later, and so we’ll make sure that that information is available to you there.
Another exciting episode. We’re definitely looking forward to it. I hope you’ll stick around until the end. As always, Compañeros, welcome to the show.
Recorded Voice 1: SQL Data Partners.[music]
Carlos: Today, I have Mark Wilkinson with me. He’s Database Administrator, at Channel Advisors. We’ve seen each other on the SQL trail, a couple of times, but we happened to here, at a SQL Saturday, in Raleigh, North Carolina, looking at his session, talking a little bit about SQL Server agent, and again when he was talking about MSX, TSX, I thought, “Hmm. That’s something interesting.”I thought it would be interesting to you Compañeros, and so I wanted to have him on. He is on Twitter, @m82labs, and he currently hails from Raleigh, Virginia. Mark, welcome to the show.
Mark Wilkinson: Thank you very much. Raleigh, North Carolina.
Carlos: What did I say?
Mark: Raleigh, Virginia.[laughter]
Carlos: No. Now you know all the roads lead to Virginia. I guess this is the way I think about that, so I apologize. Raleigh, North Carolina. My brother in law will have me hide for that. Ultimately today we want to talk a little about SQL Server agent and why we want to talk about the SQL Server agent?
Mark: I think it’s really one of those features that the people use every day and they must spend a lot of time digging in. It runs your jobs. Why would you do anything else right?
Carlos: There you go. There are some things that you could do to make that a little bit better particularly I think as we look into a larger scale environments those are some of the ideas that you have.
Mark: Exactly. In my particular environment, we manage hundreds of instances. Some of them have got a 150 plus jobs on them, so scripting all that staff out and keeping it all in sync can be a big task.
Carlos: That’s right. Being able to kind of wrap your head around that if there are some tools or some tricks and we can get to help automate that or make it little bit more convenient then those are some of the things we’re going to share today. Let’s get into that. Best practices when it comes to SQL Agent Server jobs.
Mark: There’s a lot of opinion on our best practices. I guess I’ll share my opinions.
Carlos: There you go.
Mark: Service accounts first thing you got to deal with when you’re setting up SQL agent. You should not be using a built-in account. Don’t use a domain admin account. Best idea is just to use a domain account created specifically for the agent that gives you a few things.If you have a lot of instances, you can have all of your agents using the same account. It makes permissions a lot easier if they need to access a file chair things like that.
Carlos: I think that for Microsoft standards the best practices that the SQL service account and the agent account should be different. Agree? Disagree? Do you follow that practice probably better…?
Mark: We used a different account.
Carlos: Maybe I guess I’m alone wolf in that regard, but I guess I’ve been in a hold out and we’re still using the same thing.
Mark: I don’t think there’s any problem with it. We just happen to use different things.
Carlos: I think it just the security rights differentiating what those accounts can do and providing a better auditing.
Mark: Oh, definitely for auditing purposes it’s nice, especially views like SQL audit because you can see the user account that is doing whatever you’re auditing.
Carlos: I think I could probably be better than that. It’s not that hard, right? right?
Mark: No, it’s not that hard.[crosstalk]
Carlos: Service accounts and we want to create something domain account, doesn’t have too many permissions but has enough to get through work what we need to go.
Carlos: I think you bring up a good point. You want to be consistent on your environment that is one thing what I am doing is all the same.
Mark: That makes things a lot easier especially when you got a couple hundred instances that you’re trying to keep your hands around.
Carlos: That’s right. You create your job is going to run under that service.
Mark: It depends.
Carlos: It always depends.
Mark: Depends on the job owner and the type of step that it is. If the job is executing a T-SQL step, it will run in the security context of the job owner. Pretty much anything else will run in the context from the service account.
Carlos: I see and maybe that’s why I’m thinking it has to go outside the SQL Server it’s going to use that account.
Mark: Which actually brings up another, I don’t know if it’s the best practice, but it might be something to look into is proxy accounts. If you’ve got a command exec step that’s executing a batch file or something else that’s outside the SQL Server, you can set up a proxy account specifically for that step that has different permissions than the maybe the SQL agent has.If you don’t want all the jobs have access to a file share, you can create a proxy account specifically for one job so that this one job can access the file share.
Carlos: It’s actually on that step. It’s not even a job itself. Another step in that job so HR systems maybe come into play, personal data, things like that.
Mark: Yeah, exactly.
Carlos: Now, you have this job. We put it out there. Those are the permissions it’s going to execute under, but then the ownership of that job, this is actually something I see sometimes in our SQL server audits, the owners of their job.
Mark: This is where I kind of veer. For example, [inaudible 7:45] and his team, they recommend essay as your job owners. We use a local account created specifically to be a job order just so we can keep security under control.
Carlos: There you go.
Mark: Because the job owner doesn’t need to be assisting them.
Carlos: There is no function or role of the job owner other than just the owner of the job.
Mark: The owner of the job, but that’s the security context like the T-SQL job will run under is the job owner. Every other step runs under the service account with the service account credentials, but the T-SQL jobs will run as the job owner.
Carlos: Hence the other account, local SQL Server account, to do that. The downside of using your domain account for your job owners is that that person leaves, they just say the count and then your job start working.
Mark: Which is exactly why a lot of people recommend using Essay because that’s an account that’s always going to exist.
Carlos: It’s never going to go away.
Mark: But if you want an account that’s always going to exist, and you want to tighten security a little bit, you can just create your own specialized account for that.
Carlos: That’s something we flag on our SQL server audits is that if they’re owned by a domain account, I guess it’s not generic, and we like them to change that because we have had issues with they get walked out.
Mark: Domain accounts always change eventually. [laughs]
Carlos: What are the nice things about the jobs? Is it they will alert us, notify us when things go bumping the night?
Mark: Yes, a lot of people they think that when they get a monitoring system in place maybe they don’t need to set up database mail, maybe they don’t need to set up alerts with the agent anything like that, but as we know with everything things fail including your monitoring system.This is actually another tip from Grant, from a recent Twitter conversation. It’s free to set up database mail. It’s free to set up performance alerts and stuff to the agents so why not do it. You can just filter it off into a folder in your inbox and maybe check it once a week just to make sure there’s nothing weird.
I know that you’re not seeing or monitoring, but it’s kind of an insurance policy. Your monitoring system isn’t picking something up for some reason and you glance in that folder or in your inbox and say, “Hey. What’s going on here?”
Carlos: It is there. I need to look in a little bit deeper, some specific alerts. I didn’t look up the numbers so I want to say it’s like 16 through 21 or something like that.
Mark: Yeah, there’s also some specific ones, which I am completely trying to blank on now.
Carlos: We’ll put those in the show notes and make sure you get those individual alerts or the individual error numbers that you should be alerting on.
Mark: There’s a few blog post out there on that on some good ones that it’s good to set up just by default.
Carlos: We set up our mail. We have some alerts. Now, there’s something called the failsafe operator, which again, I’m a little bit ashamed to admit that I’m not super familiar with, so tell us about the failsafe operator.
Mark: First, the whole concept of the operator. The operator is what the SQL agent will contact, who the SQL agent will contact. You can set up multiple operators. You can assign an operator per job if you want to. When you’re configuring an operator, you configure an email address, a pager address, because everybody carries a pager…[laughter]
Mark: …and some other things in there. But you can also specify what time of day those operators are available, and if you screw that part up, and you make it so your operator’s never available, you’re never going to get email alerts. The failsafe operator is there for those cases.SQL knows when there’s not an operator available, so it will send those messages to your failsafe operator. Another thing about the failsafe operator is that if your MSDB database goes down, the failsafe operator will still work. It’s an extra protection in case something terrible happens.
Carlos: Again, we’re not in front of a computer screen to look at it, but I can’t put name on the top my head where I would set the failsafe operator.
Mark: That may have to be in the show notes as well.[laughter]
Mark: I can’t remember up the top of my head, either.
Carlos: Because it’s not in a nowhere place. My job, you click on the notifications tab, there’s the operator option.
Mark: It’s in that same area. I can’t remember exactly where it’s at.
Carlos: That’s something we’re going to blog about and put there on the show notes. OK, very good. We have all that together, we’re getting notified of our alerts, right? But in the instances that we have, lots of instances to monitor, it can be a little bit overwhelming, right? When you have a lot of repeat jobs that run individual instances, how do we go about managing the sheer number of instances?
Mark: That’s where what’s called Multi-server Administration comes in. A lot of people know it as MSX/TSX…by a lot of people, I mean like the five people that actually use it.[laughter]
Mark: It’s a really old technology. It’s been around since SQL 7, but it just doesn’t get a lot of use. There’s actually a…
Carlos: Why do you think that is?
Mark: I don’t know. Part of it could be because Microsoft doesn’t really push it. There’s a story about the product manager for MSX not knowing that it was in 2012, thinking that the product have been deprecated, so it [chuckles] doesn’t get a lot of attention.
Carlos: Should we be fearful about it going away? You think it’s going to stick around?
Mark: It’s not on any of the lists to be deprecated, so I don’t think so, but you never know.
Carlos: What are the advantages of using MSX?
Mark: MSX is really nice because you…for those of you who don’t know what it is, you have a master server, and then any number of target servers. You configure your jobs at once on the master server, including schedules, and then it will push those jobs out to whatever target servers that you specify, and then the jobs will run there.
Carlos: That sounds familiar to my content management server, like a CMS.
Mark: Central Management Server?
Carlos: Central Management Server. [foreign] Central Management Server, right? It’s not using that functionality to do that.
Mark: It’s not. It has some advantages over…you can certainly use CMS to push jobs out. This has a few advantages in that. When you use the MSX/TSX system, when you create a job on the master and it’s pushed out to the targets, you can’t modify the jobs on the targets. If you’re looking for consistency in your jobs, this is the system to use. You configure it once in the MSX and all the TSXs get it, and you can’t change it.
Carlos: Now, can my MSX or my master server…can that be a production server that’s still usable with other agents or my…basically nailing that down and saying…
Mark: No, you can certainly use it for other purposes. There’s no restrictions around that. The MSX can have local jobs, as well as its multi-server jobs. There’s no restrictions around that. One nice thing about MSX though is that…it’s available on standard edition, so if you wanted to spend up, in instance, just for managing that, it wouldn’t be crazy expensive.
Carlos: One of the things that we’ve had to sort out, for example, [inaudible 15:34] on the show, we’ve talked a little bit about automation. I tend to think about those jobs as being a SQL Server set up routine, so I install SQL Server the way that I wanted my instant…my servers the way that I want it, and then I push my jobs out. How do I use MSX in an environment where…Let’s say I [inaudible 15:54] a new server? Is it going to pick that up automatically? How do I go about…?
Mark: It’s not. There’s some built-in systems or procedures to enlist in MSX server, so from the target server, you run this procedure and it will join the master server. Then, on the master server, there’s another procedure you can run to add that target server to any jobs that are already there.One way you might do that is if you use job categories of the master to categorize your instances by instance type, because we have 107 production instances, we only have maybe four or five types of instance, so if you categorize each of those into a category on the master, you could easily write code that would loop through that category and add your new instance to those jobs. There’s a lot of room for automation there.
Carlos: All right. I can see an advantage over keeping the jobs or applying those jobs as a script, then if they change, you can easily push that out to multiple servers at once versus…OK, now I have this new server, it is up to date, but now you got to go and…
Mark: Yeah, you just change it once and then pushes out to all of them.
Carlos: OK, so that concept of multi-server, the TSX, is there anything to joining that group? You set up the multi-server, and it goes out and says, “Hey, there’s a target,” and you just become a target or…?
Mark: No. You have to configure it as a master. I can’t remember the exact steps, it’s pretty simple. I think you right click on your SQL agent on the master, and there’s a multi-server administration option right in there, and most people probably have never seen it there. [laughs] It’s right there.You can say, make this a master, probably not the exact option. But when you go through it, it’s a wizard that you step through, and this is one of the few wizards in SQL that you actually want to use.[laughter]
Mark: During that process, you specify what the target servers are that you want. It will connect to those, it will enlist them in your master. For future instances that you want to add, you can do it through SSMS or there’s a procedure that you can run on the target to enlist it.
Carlos: I guess in thoughts which one are you using, that you currently use.
Mark: We have so much stuff to manage that we do everything through scripts, and if any time we can write a script and automate it, we’ll do that.
Carlos: Good practice. Very good. That sounds super nice, I can keep consistent with my jobs, almost sounds like group policy for agent jobs.
Mark: That’s a good way to think about it.
Carlos: In a sense. As anything, there’s no free lunch. There’s a couple of issues…
Mark: There’s really no free lunch with MSX.[laughter]
Mark: One thing to mention too, really quick though, about it, not only can you create a central repository for your jobs but from the MSX server you can also…When you start a job on your MSX, it will start the job on all of your instances, all of the target-setter that are specified. You can actually start, stop, and disable jobs across all of your instances at the same time if you wanted to.
Carlos: If I wanted to piecemeal that…I guess one example that I’m thinking about, for example, is a [inaudible 19:26] on the backup ride jobs. I run my backups all the same time, [inaudible 19:30] .
Mark: Yes. It’s some of the caveat of MSX but it’s definitely something to look out for. The way that we manage that is we have like a poor man’s hashing algorithm that we were on. A random number is generated for each execution on each instance. I don’t want to get too complicated, I’m sure you’re into it, but we’ve written a way where when each backup fires off, it waits for a random amount of time based on an algorithm that we wrote.
Carlos: So all the jobs start at the same time, but then nothing start…it’s the tier when the actual function start putting into place. That’s good, that’s interesting. I have seen that…actually [inaudible 20:19] , a local user group from CarMax did something very similar to it. You don’t have to code all of that. I think doing this route would be much better.
Mark: Back to the no free launch. Even though you’re using this to keep all your jobs in sync, jobs can be out of sync. [laughs] There’s a few tables that you can use to see how out of sync things are. There’s a sys download list I’ve used, what it’s called, on the master server. When you query that, it will list all of your target servers, the last time instruction was posted to the server, and the last time it downloaded that instruction.If you check that table out and for the date downloaded column, there’s no values, that means that that instruction never got to the target. But there is a procedure that you can run to re-synchronize. It will try to push all those instructions out to the targeting…
Carlos: Give me some ball park numbers where you have a couple hundred instances. How often are you saying that happens?
Mark: I don’t know. Do you know why I don’t know? Because I’ve automated the re-synching.[laughter]
Mark: It’s fairly trivial to write something, a small script that runs every once in a while.
Carlos: It’s another job…[crosstalk][laughter]
Mark: But the job…
Carlos: Down the rabbit hole we go.
Mark: Yes, so it checks to see if there’s any instances that have got instructions that have not downloaded, and not have then cued up for download for more than 10 minutes or so. If it sees that, it will re-synchronize the target, and if it continues to see that, then we’ll get paged, but I have not got paged about that in a long time. It seems to work pretty well for us.
Carlos: Very good. I have curiosity, how often are you changing your jobs? I guess I think a lot. We’re going to caveat that and say, so administration or maintenance-type jobs, and obviously there are jobs that you could…reporting jobs or whatever, those things would change little bit more often. Those core jobs, are they changing often?
Mark: No, not really. It’s more of a jobs going away and new jobs replacing them. Maybe there was a job to do some backfill because of a new table that got created that needs to be filled up with data, but there’s so much data that we can’t just run a procedure. [laughs]
Carlos: OK, so maybe I misunderstood that. In that case, if I wanted to change my job, are you not updating the job? Are you actually disabling job number one, and then introducing job number two?
Mark: No. You can certainly update the job, and that will push out the updates. We just tend to do more…jobs get deprecated and new jobs get added.
Carlos: OK. These things change…policies or whatever. Functionality gets added to the system.
Mark: One thing this is really good for, and this actually came up in my session, is for your maintenance scripts. Your index defrags, your index rebuilds. Things like that, that you would want to run every instance.
Carlos: [inaudible 23:24] administration-type jobs. That’s where is the bread and butter of these environments. OK, very good. I think it’s always good to get some different thoughts or opinions to what other people are doing, and we’ve definitely been able to do that. There are a couple of things around the show in the show notes.Again, those are going to be available at sqldatapartners.com/podcast. This is episode 16, and you’ll be able to get some of those things. Before we switch gears, are there any last thoughts that you want to put in about managing your…?
Mark: I would just say that if you do manage a lot of instances and you’ve got a lot of common jobs, I would definitely look into MSX. There’s a lot of weird caveats. It takes a little getting used to but it really is worth it. It makes managing things a lot easier.
Carlos: Very cool. Compañeros, you can use some of that. If you implement that, I’d love to hear about your experience. You could hit me up on twitter, @carloschacon, or by email, [email protected].Mark, thanks again for being here. Before we let you go, we would want to ask a couple of questions. We want to provide some additional value. Now, sure, maybe MSX is that tool, and that’s OK, earlier we’ve talked about it, but what’s your favorite SQL tool? [inaudible 24:49] pay tool, free tool, but what’s your favorite tool, why do you like it and how do you use it?
Mark: I don’t know if this really falls on the definition of a tool, [laughs] but I find myself using PowerShell every day. If I want to do something quick and I want it to hit a lot of instances, I want to automate something, PowerShell is my go-to. I even use PowerShell to prototype stuff.If I’m going to write a C# app, I’ll use PowerShell for us just to get the logic out of my head, just test and see how it’s running. If it runs fast enough in PowerShell, I’ll leave it there. If not, I’ll develop something else. Yeah, I use PowerShell a lot.
Carlos: We are original chapter leaders, Ron [inaudible 25:30] in Richmond, Virginia, he said this about a year ago, that his thinking was is that if you’re DBA and you’re not using PowerShell, within five years, you won’t be a DBA anymore. He thought from administration perspective, the divide is coming. PowerShell [inaudible 25:50] .
Mark: Yeah, it’s a major tool. I couldn’t imagine doing my job without it.
Carlos: Very good. OK, so now we’re looking for your favorite story, right? It’s an illustration or example that tells us why you enjoy being a data professional and doing what you need to do.
Mark: I’m a problem solver, so this really fits my personality well. I like to develop but I don’t like working for customer requirements, so I’m not really going to be a developer. But I like being able to dive into something, a big problem that’s happening and fix it or make it run better.I get an opportunity to do that at my job where there’s a piece of code that’s dragging a server down, SQL code, I can dive in and optimize it, and make everything run better. That’s the stuff I really like to do.
Carlos: Very cool. Being able to provide, show that value, right? That’s something that was in this and now you improved it to do well.
Mark: And there’s the education piece of that, too, to show people what they might do differently next time they write some code.
Carlos: Very good. Mark, we had a great time with you here. Before we ask you our last question, let’s hear about one additional way our listeners can learn more about SQL Server.
Recorded Voice 2: Hello there, compañeros. I want to tell you about a unique SQL Server training opportunity unlike anything you’ve encountered. As a listener of this podcast, you’re entitled to a special offer. SQL Cruise is a premiere training experience set in the most unlikely places, a cruise ship. [inaudible 27:24] forward have created a wonderful culture on SQL Cruise.With Fiber Six technical leads from various industry sectors, you and roughly 20 other students will talk shop in classes while at sea, and while you are in port, you can enjoy the wonderful world we live in, either by yourself or with a group. The beauty of SQL Cruise is the unique relationship you will form with other SQL Cruisers.
Worried about leaving your spouse? Bring them along. I did, and she had a great time with the group. In fact, I have been on SQL Cruise twice, and I’m looking to go at third time. You may be wondering if this is serious training, and I can assure you it is as good as any conference you will attend on land.
It never crossed my mind that I could be talking about SQL Server with several Microsoft MVPs on the beaches of St. John’s. I know you must have other questions, so I suggest you head over to sqlcruise.com. Check out the site. Once you are satisfied and you want to sign up, when you get to the question, would you be so kind to share the name of who referred you and what source material led you to us?
You enter SQL Data Partners, and Tim will ensure you get $100 off the training registration. This is a great offer, and I hope this $100 saving will entice you to check out the site. I did, and went on a Carribean cruise and had a great experience, and I know you will, too. Maybe I’ll see you on board. Head on over to sqlcruise.com and use the code SQL Data Partners to save $100.
Carlos: OK, Mark. If you could have one superhero power, what would it be and why would you want it?
Mark: [laughs] It’s probably kind of lame, but I think I would just like the ability to fly.
Carlos: That’s mine.[laughter]
Mark: Go check out the Grand Canyon. You wouldn’t get a better view of that. It could come in handy.
Carlos: Yeah, so I think that’s one of my thoughts around folks that say they want to be able to teleport, is that I envision the Superman portion like, will you take Louis Lane? Takes Louis Lane up there dancing for the moon type of thing. I guess you could maybe teleport to middle of the Grand Canyon, and then while you’re falling, teleport out. I want to stay and sit there for a while.
Mark: I’m a fan of a journey, so I think flying would be what I want. [laughs]
Carlos: Very good. Mark, thanks again for being here, for telling us about MSX. It’s something you definitely have to go out and try. We appreciate you being here.
Mark: Thanks for having me.
Carlos: OK, compañeros. We’ll see you in SQL trail.[closing music]
Recorded Voice 1: SQL Data Partners.