Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #186–Agent Jobs

    It’s that time of the month again, when the T-SQL Tuesday blog party takes place. I manage this site, and am looking for hosts all the time. This month I managed to convince Andy Levy to host, and I’m grateful for his participation.

    His invitation is asking about SQL Agent jobs and how they are managed. It’s focused, but he gives a lot of choices for how to examine this subsystem in SQL Server.

    Note: if you work in Oracle or PostgreSQL or anything else, how do you schedule work in an automated fashion? Cron? Something else? You can still write.

    If you want to host, ping me and I’ll get you a month.

    Designing Jobs for an Enterprise

    I used to work in a fairly large enterprise (5,000+ people, 500+ production SQL instances) with a small staff. It was 2-3 of us to manage all these systems, as well as respond to questions/queries/issues with dev/test systems. As a result, we depended heavily on SQL Agent.

    We decided on a few principles which helped us manage jobs, with a (slow) refactoring of the existing jobs people randomly created with no standards. A few of the things we did are listed below. This isn’t exhaustive, but these are the main things I remember.

    Name schedules clearly

    Scheduling gets crazy. As a result, we would try to name with the days and times something ran. For days, we’d use SMTWRFSa. If something ran every day, that was in the name. If it were week days, then it had MTWRF in the name. Thursdays only were R.

    We’d include a time, such as 0200 or 1830 in there. If there were just one or two times, we’d list those. If it were more often, we had “every hour” or “every 15 minutes”.

    This wasn’t perfect, but it made most schedules clear.

    Job Names and Descriptions

    We tried to make job names clear with a starting noun (Backup, Maintenance, Sales), which was a little overloaded. It was a DBA thing for most work that DBAs might run and a department for those business level things.

    Job Steps

    I tried desperately to get away from code in the job step and use stored procedures instead. This helps us tune and watch things that run, and it keeps code in code places.

    For DBA stuff, we had a DBA database on each instance for our procs. We’d put our code in there (Ola’s procs, our own custom maintenance things, checks, ETL, etc.). This way we could more easily run server level stuff.

    For business level jobs or things related to a db, we want a proc in there. Then call that. This also let us often have a logging table alongside the proc where we could track progress.

    Alerts/Operators

    Luckily we had a monitoring solution that notified us when jobs failed. We didn’t use these systems. However, we did have an auditing report that queried DMVs and noted job failures and stored this data in a table (rolling 30 days) and used it to produce a daily report we archived in a folder.

    This was for our ISO compliance and auditors loved it. We would store a daily report and then add a daily note of any actions we took. That way we knew what we did and had a record.

    Summary

    For most of the other options (categories, etc.) we ignored them. The goal was to keep things very simple and streamlined. We had a standard job we deployed to most servers as part of a build process.

    We also drove a lot of activity in code off queries as much as possible and only used a table to log exceptions. We might have a table that stored the “FinanceDW” db name as an exception. The backup process would get a list of all dbs, and then delete those in the exception table. Then run as normal.

    K.I.S.S. worked very well for us.

  • T-SQL Tuesday #185–Being a Video Star

    tsqltuesdayThis month we have an interesting invite. Erik Darling is the host, and since he does a lot of video blogs, he’s asking for a video submission for T-SQL Tuesday. The topic and content is up to each of us, but the format is video.

    That’s funny as I’m writing this blog, but the content below is in the video. What’s in there? You have to watch.

    I’m also looking for hosts for the future and have a few slots in 2025 still available for H2. I’ll also note that any month could be a video post for the topic if you want, though I suspect a few hosts might not love having to watch videos. Some, however, will love it.

    My Submission – Testing the Flat File Wizard

    Here’s my submission:

  • T-SQL Tuesday #184–Mentoring

    This month’s T-SQL Tuesday blog party is hosted by Deborah Melkin, and it’s a good one that asks us where we are making the world better. The topic is mentorship and sponsorship, which is great. We all ought to pay it forward or pay it back, depending on how lucky we are. I believe strongly in this and am glad to see the topic posted.

    I still manage the T-SQL Tuesday list, and I’m always looking for hosts. I have a few scheduled for 2025, but I can use more. If you’re interested in hosting, hit me up at one of these places:

    The Mentoring Experiment

    Andy Warren and I ran The Mentoring Experiment a few times in the past before life got too hard and we decided to pause. I wrote about mentoring as well, and didn’t publish a lot on the experiment as most of the conversations were private.

    I’m somewhat sorry we didn’t continue this, but it proved to be a little overwhelming at that time in our lives.

    My Mentoring Experiences

    I’ve had a few mentors in my life, and while some I’m not comfortable sharing, there are a few that stand out.

    In high school, I had a mentor who was a Navy Pilot. He was a client of my Mom’s and she asked him to give me rides to and from karate, where he was also a student. On our rides, he shared some advice, some thoughts on life, while taking an interest in me. A few things stand out, one of which I still think about today: before I send an email, how would I feel if this were made public? This keeps me from writing too emotionally.

    For the last 20+ years, Andy Warren and I have talked most weeks of the year. We miss a few when we’re on vacation, or I’m traveling, but he has been a great mentor to me, helping me think through life’s challenges. I hope I’ve done the same for him.

    Giving Back

    I think part of what I should do is try and make the world better. Part of that is my trying to get a variety of people to write at SQL Server Central or host here. I’ve reached out through my network to find women or minorities that would participate in our data community, and give them a voice. I’ve been less successful than I would have liked, but I have had a little success.

    I’ve also convinced a few people to speak. I usually look for people at events that are engaged, ask interesting questions, or just have good conversations with me. I’ve found it takes months, but if I encourage them, I’ve gotten some of them to write or speak for free to share their knowledge and grow their own skills. There are a couple successes who many of you likely know their name and a few more that tried it and gave up.

    I think the more you encourage people to engage in life, the more they (and you) get out of it.

    I also try to do this as a coach, getting them to grow and learn more than just the sport. I think it’s worked out well as many kids keep in touch over the years.

    I think many of you can do the same thing. Help others grow and find their own success, with encouragement, support, and a friendly ear.

  • T-SQL Tuesday #183: Improving Permission Management

    This is my (late) answer to my own invitation for T-SQL Tuesday #183. I was very busy a few weeks ago when the invite when out (glad it was scheduled) and I never got this done. This post looks at how I managed permissions in the past.

    Large Enterprises

    Many of us would like to think that large enterprises have standards and they’ve learned about best practices. My experiences in 3 of them were that so often large enterprises were small ones that grew, often with lots of tech debt and busy staff. Even when there are limitations to ensuring good security, often we can’t just fix things because we might break something.

    In one large company (5000+ employees) I found that many of the database servers I managed had permissions set in a variety of manners. Often this included lots of individual permissions granted to logins and users, which was a mess.

    Even when AD groups were in place for departments, they weren’t used as logins in SQL Server since not everyone in a department needed to access the database.

    Cleaning Up

    I hated managing permissions by user. When I started and got a ticket to add a new person to a database for access, I watched someone train me by looking up another similar user in the database, scripting out their permissions, and then search/replace to change the user or login name.

    Not a bad solution, but one that doesn’t scale well over time. What I started to implement was to create an AD group (for Windows users) or a database role (for SQL Users) and then add the login/user to this group or role and assign the permissions from my “other” user to the role. I’d also often move the user to the new role and revoke their specific permissions.

    While this seemed like more work at first, this quickly started to scale well as we had a way to add new users to roles that matched the access they needed.

    Slowly over time we moved a lot of access to AD, which allowed us to remove the burden of disabling users in SQL Server. Plus, we could easily see which access a variety of users had by looking at a role rather than checking multiple accounts. Auditors liked that and it helped us pass various audit checks over time.

    Use roles and groups. They’re not hard and they make things cleaner and easier over time.