T-SQL- SQL Agent Owners and the Lotto

Here’s a sad scene: You’ve been creating and scheduling SQL Agent jobs for years. You’ve got database maintenance jobs set up, backups, SSIS Packages to import and export data, SQL-only jobs to build datamarts. A whole ecosystem of finely tuned, perfectly running activities … all keeping your DR in tip-top condition.

Then you go down to the corner store and buy a Lotto ticket.

And you win! (Cue happy, joyful music)

And you retire to live in luxury on Whitsunday Island or Bermuda or the British Virgin Islands.

Back at the hospital, however, things are not going so well. (Cue sad, unhappy music)

All of your SQL Agent jobs died the moment your AD account was set inactive. All of the reports running under your AD credentials also died, but that’s more obvious as to why. (Unless, of course, you did schedule them as yourself… in which case that killed them too).

The backup DBA swears bitterly because all of those SSIS packages, for example, are running using a Proxy account which accesses a service AD account which did not get disabled when you traipsed off into the sunset. (of course… if you didn’t do that already? They die two times.)

Why did these SQL Agent jobs stop working? Because… this field marked in red. It had your AD account name in it:

How lovely!

That field gets filled in for every SQL Agent job you create.

What to do?

You switch the ownership of every job to the AD credential used for the SSIS proxy account, which is a non-person, service-style account. That account will never expire, so even if you win the lotto, the jobs will keep running.

OR

You can assign ownership to the SA user, which will also always be present.

Extra Credit

In addition to checking all of your SQL Agent jobs for person-specific owners, you will want to also review your Schedules in SQL Agent to identify SQL Agent jobs sharing schedules, as that kind of cross-over has unintended consequences when you enable/disable/delete Schedules without realizing what is associated with them. 

Want more DR-related tips, knowledge-base articles, how-to videos, standard reports and a whole constellation of pre-built functions, module-specific views, stored procedures and additional capability for your DR? Consider the DR Resource Center!

And…

If you need help setting up an SSIS Proxy Account, we have a best practice we can share with you. Just let us know!

If you’d like automatic index optimization and a lovely SQL Agent job monitoring dashboard that also shows you the health of your DR (or any other SQL Server you may have in-house), give us a shout! We have a packaged deployment of code and dashboard that consolidates all of that for you.

And if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!