Documentation – The ‘Hit-by-a-Bus’ Handbook

A huge part of success in fishing is preparation. Without careful preparation fishing will be slow, you can lose or break equipment, and you can even put yourself in danger. Several of my previous posts have detailed how preparing by looking at weather, depths, fishing reports, etc. can improve your fishing “luck”. Another part of preparation is inspecting your fishing equipment before use. I can remember at least three times where I’ve casted my lure right off the end of my line. It’s great to see a bomb cast really sail out there, but it’s not so great the moment you realize it’s no longer attached to the end of your line. Lastly, let’s not forget the thought of unhooking that deep hookset on a northern pike when you neglected to make sure your pliers was in your tackle box.
I was considering preparation this week after I read this great post by Monica Rathbun on how to survive as a Lone DBA. One of the great tips she has is to create a ‘Hit-by-the-Bus’ handbook. This would have all the information you need to pass on to the next DBA if you were to suddenly leave the job. With this documentation, the next DBA should be able to step right in without missing a beat. As I deeply considered my current environment, it became clear to me that, were I to suddenly leave my position, the next DBA would have a lot of questions that he or she would have to do some deep and time consuming investigation to answer. As such, I’ve decided to make a major commitment to improving my documentation. From a DBA perspective, this will include detailed documentation on:

  • Daily/Weekly/Monthly/Ad-Hoc tasks. How to do them and why they are done.
  • SQL Agent Jobs. What are they doing and how to troubleshoot if they break. How urgently do they need to be fixed?
  • SSIS packages. What are they doing at a quick glance? Stepping through an SSIS package to figure out what it is doing can be all kinds of frusterating.
  • Backups. What is our strategy? Where are backups going and how would I address various restore situations?

I believe this documentation will bring value both for myself (because my memory is not great) and for my successor, whenever that day may come.

Advertisements

SQL Agent Properties

It’s been a rather windy summer thus far, making it less fun to be out on the water.  I don’t know many guys who enjoyed being blasted by the wind while bobbing up and down on the waves for hours on end.  I went out on Pewaukee Lake a few weeks ago with a buddy from work.  We had picked the day in advance since it was supposed to be dry and calm.  We got the dry, but not the calm.  We had a stiff wind blowing out of the west that drove us back into the western half of the lake after trying to fish the narrows in the middle.

I spent the day focusing my fishing efforts on making the bait look real.  I tried hard to avoid retrieving the lure in a rhythmic fashion.  I was paid off with a nice upper 30s muskie:

June 2017 Pewaukee Muskie.jpg

My fishing buddy hooked one a short time later, but couldn’t keep it pinned and we lost it.

Recently, I blogged about migrating the SQL Server installation onto a different drive.  I did find one problem after this move that I had to address.  I ran into a problem with the SQL Agent and I wasn’t able to diagnose the issue.  If I remember correctly it was actually an SSRS subscription that failed, and I needed details to find out why.  I found that the SQL Agent has properties, and the error log was still pointing back at the previous location on the C: drive, which no longer existed.  There is a stored procedure you can execute to see those properties, in addition to looking at them in the SSMS UI:

EXEC msdb..sp_get_sqlagent_properties

20170712 SQL Agent Properties.PNG

Lastly, just update the value with the corresponding SET stored procedure and restart the SQL Agent:

EXEC msdb..sp_set_sqlagent_properties 
   @errorlog_file = N'D:\SQLSERVERINSTALL\SQLAGENTERRORLOG.log'

Now your SQL Agent properties have been update.

Finding SSIS package passwords in SQL Agent jobs

It can be a daunting experience to try fishing a new lake. We can see what the lake looks like from above the water, but below there are innumerable structures and features to consider. Last summer I fished lake Winnebago for the first time with my pop and brother. Lake Winnebago is the 21st largest natural lake in the United States, covering about 215 square miles. As we motored out from the boat launch, the only thought was “where to start?” The vast lake was stretched out before us, but since we could only see the top of the water we had to use other clues to figure out where to start fishing.
It’s tough heading into the unknown. Earlier this year I started a new job that used SQL Server Integration Services (SSIS) jobs saved in the file system as steps in various SQL Agent jobs. This job was at a small shop where documentation was not well kept. The SSIS packages were password protected, but nobody knew the password! Fortunately I was able to find the passwords stored in the SQL Agent job steps using this SQL command:

SELECT  sj.name ,
        sjs.command
FROM    msdb.dbo.sysjobs sj
        JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE   sjs.subsystem = N'SSIS'

After running, I was able to find the /DECRYPT password part of the command, which I used to decrypt and view/edit the packages using BIDS.  In the image below, you can see the password is “ssis”.

20141229 SSIS Package Passwords

Winnebago turned out to be a fun lake to fish.  We were catching fish all day, and ended up with a good amount to take home, after throwing back all the nasty sheepshead.

Sheepshead