Voice of the DBA

Tag: administration

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.

May 13, 2025
Create a Linked Server: #SQLNewBlogger
I had a customer recently that was asking about Linked Servers and some development advice. I was going to show them a few things and realized I hadn’t created a linked server in my demo environment, so I did it and decided to create a quick post on this.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

I have a few demo instances of SQL Server in my local environment: Aristotle and Aristotle\SQL2022. In this case I was connected to the named instance, and decided to create a connection to Aristotle. As you can see, I don’t have any linked servers in the named instance.

To create a linked server, I can use this simple code:
```
EXEC master.dbo.sp_addlinkedserver   
     @server = N'Aristotle',   
     @srvproduct=N'SQL Server';  
GO
```
This creates the linked server (as you can see below), with a number of defaults. In this case, the security is made with whatever login queries the linked server.

You can see the security properties here:

This might be OK in your enviroment, or it might not be. Perhaps you need to ensure everyone querying the remote server uses the same login. In which case, the sp_addlinkedserver procedure doesn’t do this. You would need to use sp_addlinkedsrvlogin to do that. That’s for another post.

NOTE: Be sure you understand what a linked server does, how to use it, and the downsides. There are many and this can slow down your application or overload servers

I can test this connection by right clicking the Linked Server in SSMS:

This works, as expected.

I can also run a query through the linked server, using 4-part naming with the linked server, then the database, schema, and table. This also works:

That’s it to get started. I recommend you be careful when using linked servers as this creates a bit of a tight coupling and makes development harder. I might recommend you get away from querying database server to database server when possible and let an application do this work if it’s possible.

SQL New Blogger

Linked Servers aren’t that common, but they aren’t rare. This is a skill that SQL Server people should have and understand a bit about. This post is very basic, but it provides a jumping off point where I could write a number of other posts related to linked servers and perhaps guide an interviewer along a path of asking me about them. I certainly showcase some knowledge here if someone asks me if I’ve ever created one.

This post took me about 10 minutes to test and write, and you could probably do this in your environment. You don’t even need to servers, as you could create a loopback linked server.
May 5, 2025
Index Maintenance Can Change NORECOMPUTE Settings
I had a customer recently ask about a change in one of their constraints on production, where a new option appeared when they went to deploy some changes from QA. They asked how this could happen, and I’ll show how in this post.

Suppose I create a table like this in a development environment.
```
CREATE TABLE [dbo].[Logger](
     [LogID] [INT] NOT NULL CONSTRAINT LoggerPK PRIMARY KEY,
     [LogDate] [DATETIME] NULL,
     [LogMsg] [VARCHAR](2000) NULL
     )

GO
```
I (hopefully) have a process to get this to production (version control, automation, etc.). Once in production, if I were to script this on SQL Server 2022, I’d get this from SMO.
```
CREATE TABLE [dbo].[Logger](
     [LogID] [INT] NOT NULL,
     [LogDate] [DATETIME] NULL,
     [LogMsg] [VARCHAR](2000) NULL,
  CONSTRAINT [LoggerPK] PRIMARY KEY CLUSTERED 

(
     [LogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO
```
This looks different, but really this includes the defaults that existed in dev, and also in production. Hopefully all my SETtings and configuration is the same, to ensure no surprises.

Now, let’s imagine a DBA has some index maintenance, perhaps Ola’s scripts or some other script that works through all tables and indexes. If a DBA decides they’d like to edit the script to change a setting, they might end up running this code for my Logger table:
```
ALTER INDEX ALL ON dbo.logger

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
```
There’s a small change in here from the defaults, which I’d see if I were to run a SQL Compare comparison. Now I’d see this type of change, which might not be a problem, but it might be an issue where each deployment wants to reset this setting.

If you don’t think this is a big deal, here’s the deployment code:

I would not want this going through my deployments. And it might if our team were no diligent in looking at the deployment script.

Be explicit with defaults, and be careful about making changes in production. You might end up creating problems in your update process if you don’t feed these changes back to development.
April 30, 2025
Part-Time DBAs

Some of you reading this are database administrators (DBAs) who manage systems as their full-time job. Others of you might be developers, analytics people, or someone else who has another job, but you get stuck with managing the database somehow. I’ve seen a receptionist and a dental hygienist act in this role. We may call you the accidental DBAs, though that doesn’t imply you are good or bad at managing databases. I got into this line of work as an accidental DBA who was also a developer.

No matter what your job title, my guess is that you aren’t over-staffed at your organization. Likely you wish you had one (or more) more person to help keep up with the work. It seems that we never have enough time to get everything done in a week. And that’s with a full staff. What do you do when someone is sick or goes on vacation? If you’re like me, you get further behind and feel extra stress while your coworker is out of the office.

There is another way, and I thought this piece had a great title: Having a Part-time Database Administrator Can Help Improve your Bottom Line. It’s from DCAC, a consulting company that provides remote DBA services. There are other companies like this, such as Procure SQL, Straight Path Solutions, Dallas DBAs, and more. All of these companies are available to help augment or relieve pressure on your staff.

The piece makes a good argument that often your staff is busy and might not have some of the specialized training or advanced skills that might help solve complex performance issues, architect HA solutions, perform cloud migrations, and more. For many companies, it can be hard to acquire these types of skills, and even if you have a plan, it can be expensive. What if your expert is out of town when you need them. What if you train them and they leave? I believe in training people, but I also know that you have to be able to augment your staff at times.

Using trusted partners to help you improve parts of your business is something companies do in many ways. We might employ a handyman, but would still hire an outside plumber for some work, especially if it is a large job. Why not do the same thing with your technical staff?

These companies might seem expensive when you look at their rates, but using them part-time, in strategic places, can often help your bottom line.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

April 9, 2025