Instead of working, I blog.

Being a DBA is certainly interesting to say the least. And I can honestly say that one of the things I like the most about it is all the diversified topics we get to cover... esp in data modeling. I've modeled DBs for marketing, scanning, healthcare, killing chickens, restaurant inventory, political contributions, shipping drugs/medical supplies, and I'm currently working on a tax return DB.

You can't help but really pick up some interesting information on these projects. For example, not only can ckickens live a long time after you cut off their heads, large processing operations can kill over 600,000 chickens a day. Just think about how many chickens that is, and since they're only about 6 weeks old at the time, they're easy to replace quickly. I have also learned that there's an acceptable amount of rat feces in canned chili, and the rate at which hospitals give you infections due to mishandling and then charge you for the treatment is alarming.

However, probably my most bizarre encounter with a schema to date happened a few years ago. I only mention it now because I happened across my notes and thought it'd make a cool post. A friend who was in vet school at the time asked me to write him a small DB to keep track of phychological disturbances in animals. At first I was prepared to turn him down because I just didn't have the patience for that kind of nonsense, but my curiousity got the better of me.

Among the things I learned during that project were:
--Why cats suck or chew on wool.
--Why dogs chase their tails.
--Why calves cross-suckle (that's when they suck on each other's different parts instead of their mother's milky part).
--Why horses shake their heads.

I'm really curious... what are some of the more interesting projects you guys have come across in your modeling travels? And what are some of the more interesting things you've picked up along the way?

About a month ago, we finished our last round of audits and I wanted to share a little bit with you about how to talk to auditors, or better yet, how not to talk to them.

Here's a fine example of how you should not talk to an auditor.
One of our guys, I'm sorry to say a DBA, walked into a room where the bosses were holding a meeting and announced that none of our backups across the board were working, and had been failing for a couple days. We're completely unprotected!!

Of course, you guessed it, they were meeting with the auditor from D&T. In his defense, he said he had no idea that was an auditor and he never would have said that if he did. OK guys, let's put this rule on the table right now. Don't go announcing things like that at all. If there's someone in the room you don't recognize, keep your mouth shut, send an email, pull them out of the room, whatever, but don't just announce that you're shop is falling apart.

Most companies will tell you when the auditors are going to be there, and will tell you to refrain from discussing sensitive business outside of your immediate area. This is an excellent tactic, and we do that too, so why this incident happened, I'll never know.

So how should you talk to an auditor then? There are 2 areas you need to worry about.
The first is before and after the interview. Auditors like to come up to your desk or pin you in the hall and ask you questions about your environment. That's fine for them, but you need to get with your managers and decide how you're going to handle this situation... remember, anything you say can and will be used against you in a court of audit. What we do is we refer all questions back to our boss. If an auditor asks me a question outside of the interview, I say, send your question to my boss. He then asks me the question, and I in turn send it back to him. This way, the auditor can't trip you up on the spot, and you won't accidentally say something you'll regret. And it gets to go through the filter of someone else. Even if you know the answer, Don't say anything. Make them go through channels. Now you may not choose to do it this way in your shop, but it's worked very well for quite a few places I've been in.

Second is during the actual interview. Auditors will quite often call you in to ask specific questions. Quite often, you have someone else in your dept sitting in with you to make sure everything goes well... just kind of a witness. When the auditor asks you questions here, you may answer them, but use as few words as possible. Never say 20 words when a yes will do. Treat this just like testifying in court. Answer the question asked, no more, no less. It's tempting sometimes to want to explain yourself or your reasoning for why something's done, but it's not relevant here. In the case from above, I would only hope the the DBA wouldn't answer like this:
Q: Do you backup the DBs every night?
A: Yes... but we quite often go several without our backups working, and we never test restores, and it doesn't matter anyway, because the drive we keep them on is old and slow and will probably die any day now, and since they're not pushed to tape we'd be in real trouble if that happened.

The clear answer is simply yes. Then SHUT UP!!!

Also, don't let them rope you into answering questions that are outside your area. Anything not having to do strictly with DBs is none of your concern. Some sample questions are...

Q: How many users inside Solomon have elevated rights to create accounts?
A: I'm not responsible for Solomon. The Solomon admin would have to field that question.

Q: What method do users use to authenticate to your intranet?
A: You'll have to ask that question to the intranet admin.

Q: How many users have db_owner in the ADP database?
A: At this point I could only guess, but send me that question in email and I'll get you an anwser.

Notice that last question was in your range and it still didn't get answered? Auditors will write down whatever you tell them on the spot, and move on. Don't guess at anything. If you're not sure of an answer, say so, and ask them to submit it in email and you'll verify the answer and send it to them. This is crucial because you won't get a 2nd chance to answer that once they've written something down. You've got a few dozen servers to look after, and nobody expects you to have all the answers off the top of your head.

Ok, that's all I've got... happy auditing!!

Hey guys... sorry it's been so long since I posted, but life stepped in, and then the holidays were upon us... the good news is I've got a real doozy(sp?) for you this time.

I'd like to talk about job steps and what makes them effective.

When you create a job, it's always best if it's doing some actual work. Of course, we all take that as a given, but it apparently isn't. We just picked up a new site to manage at work a couple weeks ago, and yesterday I got an email saying that the data mart loads were failing. Well, actually they're not failing, they're taking so long, the ops guys just cancel them. I asked how long they'd been like that, and they said "ever since we can remember". So what you're telling me then is that you've not only never had a data mart load finish, you waited 2 weeks after I started here to tell me about it. Ummm... ok, (that's another discussion I guess).

So anyway... I go to the jobs that control these loads and they have several steps, one of which pulls from a linked server. I figured that was the main culprit, but I was wrong. That step finishes in just under a minute. It's the rest of the steps that take so long... and here's why!!

All the other steps do is run a series of very long, very complicated selects. The selects themselves don't use any indexes, and they're running against millions of rows. Remember though, these are automated jobs, who's seeing the results of these selects? Nobody, that's who, but it doesn't stop it from running all these selects, and eating up resources. Oh, and did I tell you, they all have TABX locks hardcoded. So, not only are these useless queries taking up all the resources, they're locking like 11 tables for hours.

After explaining this to the guy, I get another email from him shortly after, and he said, so can you also look at why the reports won't run?
Needless to say, cutting those queries from the job fixed the entire problem. Well, almost, I added something like 20 indexes to the DB and it purrs like a kitten.

Ok, so here's the moral of the story... when you put a step in a job, make sure it's actually going to do something. I liken this incident to the time right after we got our ticketing system and we were telling our users they had to submit tickets to get work done. Across the board, they stopped sending us any email whatsoever. Instead, we were getting tickets for everything. Here is an example of some of the complicated problems we were assigned through the new ticketing system:

-I am going through the documentation so I'll know how the DTS process works.
-Didn't you tell me you weren't going to be here next week?
-Do you want me to send you that SP to look at?
-I got your email, thanks.

I only wish I were exaggerating. I actually flagged those tickets as they came in and pulled them up just now... I just knew they'd come in handy some day.

Anyway, my head's exploding so I'll see you guys next week.