Instead of working, I blog.

I just got my copy of Ken Henderson's new book and dispite everything I have to do, I've already started tearing into it. Understand though the he just edited it, he didn't write it. This promises to be every bit as significant as his other books though and it'll be the next book I review. I just pushed it up in the queue (since MSPress is taking so long to send the others I've ordered).

Anyway, once I get it posted, I'll let you guys know.

We recently ran into a very interesting anomoly here upgrading to Yukon. We have the following query.


Select * from DWStsging.dbo.history

Of course, you all know the 3 part naming convention, so you know that DWStsging is the database name. However, what you may not know is that it's misspelled. The real name is DWStaging, and the one in the query is mistyped.

So what makes this interesting? SQL2K runs it just fine, and has for months, while Yukon spits it back at us. It's these little things that upgrading interesting.

These are the kinds of things I really don't mind though. Sure, it's nice that 2K does what you mean, and not necessarily exactly what you say, I'd rather my DB not make assumptions like that. So if anything, Yukon is forcing us to clean up our code where we didn't even know we had problems.

I just ran across this in an SP and I thought I'd share it with you guys...

Select RegionName from Regions
where RegionName IN ('SC', 'MW', 'NE', 'NC', 'NW')


For some reason, I just love the uselessness of this whole thing.

I’m sitting here playing with the new Word 2007 and you can set it up to post blogs directly. This is my first blog written and posted in Word 2007… now this is cool.

There's no such thing as varchar(1).

I just started a new project with a new group of devs. I feel like Jerry Springer because they're just spoiled children. They half-ass everything they do, and then run to the boss when things don't get sorted out right away.
It's amazing to me that these people can even function in society. I spend my time settling petty disputes, and fixing things that should have been thought through to begin with.

They constantly overwrite each other's code and blame it on someone else. They never backup tables before making major changes. They never verify SQL statements before asking me to run them. And most of it's just ridiculous crap.

You would think that since they have to work in their own dev environment, that they would manage it the way they need to, but instead they do the DB equivalent of sleeping with their cousins, and cheating on their sisters with their moms.

Let's grow up a little bit and start acting like we know what we're doing people. If you know the statement isn't correct then don't send it anyway. If you know there's a possibility you'll need to roll back a change, then backup the table first.

Here's the code for you:
Select * into NEWtable from ORIGtable

you can get fancier if you like, but that basic statement will get you by. Of course, if you wanna actually backup the DB first, that would be even better. LiteSpeed has object-level recovery so you should be able to pull back individual tables and schema code.

Stop running around like children making half-ass changes. Learn from your mistakes. If it keeps coming back to bite you (and it does), then stop doing it. You'll find you'll start making your deadlines and even getting to bed on time.

Man, this kind of thing just doesn't go public very often, but it's just great. I often wonder how many people sleep their way to the top because of the complete lack of skill they show in their position. Anyway, not only do I maintain Tonya's Darwin Award for extremely horrific decisions, I now have to come up with some other award... something that captures the amount of sleeze that Tonya appears to be. What do you say guys... maybe a Technology Pornstar Award, or maybe the Wettest Technology Award... I look forward to getting some good suggestions from you guys, and I'll assign the award once we land on a name.

Anyway, a reader brought this to my attention. It was printed in the Dallas Observer. Here's the link to the actual article though I've copied it below.
http://www.dallasobserver.com/blogs/?p=180


March 22, 2006
Communication Breakdown
Filed under: News
In a rare piece of good news for the Dallas County Jail, InfoIntegration, the infamously incompetent computer firm that lost track of inmates like they were old pairs of tube socks, will not renew its contract when it expires in July. Tonya Brenneman, the founder of the firm, got the lucrative contract the old-fashioned way—on her back, according to a fine story in D magazine that had her admitting to sleeping with the man who wrote the contract for the county.

Whistling, as always, past the graveyard, Dallas County Commissioner Mike Cantrell told The Dallas Morning News that InfoIntegration was the victim merely of bad publicity–as opposed to, say, an unqualified CEO. “In my opinion, they did a good job,” he said.

Unable to adjust his opinions to the wealth of information at hand, Cantrell sets the bar rather low considering that thanks to InfoIntegration, dozens of inmates languished in the jail long after their sentence was completed. The News’ James O’Neill did a Pulitzer-worthy piece exposing the company, jail and county for this total system breakdown that in essence imposed an additional punishment above and beyond what the judge ordered. (Attorney David Finn reprints the story on his blog.)

Last December, we chronicled the sad plight of Rhenia Chavers, incarcerated at the jail for the high crime of driving with a suspended license. Chavers was behind bars while the InfoIntegration system was just working out a few kinks–no big deal, really, except that her own son couldn’t locate her. He called the jail staff, and they said they had no idea where she was. She kind of needed to talk to him. While at the jail, Rhenia Chavers did not receive her Lupus medication and suffered a stroke shortly after her incarceration. When I last talked to her in December, she still had trouble walking.

Then there was the plight of Scott Williams, who wound up at the jail last February on a DUI charge. InfoIntegration lost track of him too, and he wound up at the jail for over a week. We chronicled how good a time he had while he was incarcerated without his HIV medication and his partner unable to come to his aid. A few highlights:

“Williams says that inmates wrote their names in shit on the walls, and a water fountain was the waste receptacle of choice for one inmate with diarrhea.

‘There was shit on the toilets. When I’m talking shit, I’m talking an inch of shit,’ he says. ‘I just squatted over it and pushed and tried to aim as best I could.’”

Williams said that because he wasn’t eating sandwiches provided to him, he was forced to strip naked and move to a suicide cell. He shivered for 12 hours, lying on the floor without a blanket while trying to avoid shattered glass on the floor of his cell. Because he hadn’t been receiving his medicine for depression and anxiety, he suffered through an agonizing withdrawal. At night, he’d hear inmates who weren’t receiving their prescribed drugs bang noisily on their cells in protest. –Matt Pulle

This is the first Darwin award I've handed out and boy is it a good one.

This post will have 3 parts: The award, the background, the article.


AWARD:

My first Darwin award goes to Tonya Brenneman, President of InfoIntegration in Dallas. Not only did Tonya fail to gather requirements for her system, but she ignored the advice of her DBAs and Microsoft. Read the article below and you'll see what I mean.


BACKGROUND:

Back a couple years ago my wife got a job at InfoIntegration as a DBA on the project mentioned in the news article below. She soon started coming home with reports of how poor the design was and how things were going to fall apart. I took a look at a couple things with her to give it a 2nd pair of eyes, and she was not only right, the problem was worse than even she thought. On my own time I benchmarked a couple of the problems with the projected usage for the system. My benchmarks showed that once it went live, the system would suffer from such severe performance issues that it would be all but completely unusable in under a day. This would put the system having maintenance on it all day long just to keep up. I don't know about you guys, but I don't know any production system that can have defrags run against it 24/7. Anyway, when she went to her bosses with our findings, they not only ignored her, they actually blew her off and said that not only did they know what they were doing, but even if she were right, that portion of the system had already been written and it would be too much trouble to do it again. She kept making waves and was soon fired. She's not the only one who left that job for a similar reason.

This should go to prove to you guys that taking database issues lightly isn't a good idea. When your DBAs tell you something, listen. They're warning you of danger for a reason, and if you're so freaking smart then why do you even have a DBA? Trust your people and they'll do good by you.

OK, with that said, here's the newspaper article that came out just today on this. Trust me... read it... it's a great article, and not only is it written well, but it really goes to show how important your databases can be.

ARTICLE:

Computer firm to let jail contract expire
Dallas County: In-house employees to take over inmate tracking system



05:45 AM CST on Wednesday, March 22, 2006
By KEVIN KRAUSE / The Dallas Morning News


The company that built the computer system for Dallas County that caused chaos in the courts and left some inmates languishing in jail for too long will not renew its contract when it expires at the end of July.

The county plans to hire its own staff to run the troubled system, which officials say will save money.

InfoIntegration's president, Tonya Brenneman, told county officials in a letter that her company would help with the transition. She could not be reached for comment Tuesday.

A multitude of problems surfaced after the January 2005 launch of InfoIntegration's $9 million Adult Information System, or AIS, which tracks inmates from booking to final case disposition.

"They felt it was in their best interest to sever this relationship and develop business in outside venues," said Robert Clines, the county's technology chief.

The county had been planning to rebid the AIS contract in October, he said.

"I don't think it was a secret that we were looking at other options for support of the system," he said.

Commissioner Mike Cantrell said InfoIntegration officials thought it was a good time to step aside, "given the history of what's taken place and the publicity."

"In my opinion, they did a good job."

Mr. Clines said he will ask county commissioners for money to create five to seven positions to take over operation of the AIS system, which he said has overcome earlier problems.

The county still plans to contract for help in fixing minor problems, he said. But handling system operations in-house will be cheaper for the county, he said.

The county had been paying InfoIntegration about $460,000 for renewable six-month contracts, he said. Mr. Clines said he is trying to determine with the budget office how much the proposed new positions will cost.

"We will not be spending near that much money," Mr. Clines said.

He wants the new employees hired at least 30 to 45 days before the end of InfoIntegration's contract so they can be trained on the system. Mr. Clines estimates that 120 hours of training will be needed.

The county awarded the AIS contract to InfoIntegration in 2003. At the time, Ms. Brenneman had just formed the company after leaving the firm that handled the computer system for the juvenile department.

The new system failed to transfer information to the county's old mainframe system that the courts use, causing some inmates to remain in jail for weeks or months after they posted bail or their cases were dismissed.

Microsoft was hired to evaluate what went wrong and issued a scathing report in October that detailed a list of mistakes by InfoIntegration and the county, including failing to determine what kind of system was needed and how it would function.

Mr. Clines said most of the more serious problems, such as security issues, have been fixed. He said a governance committee staffed by judges and representatives of the district attorney's office and sheriff's office was formed to review changes.

"All of the glaring problems, we have addressed," he said. "These are the nuisance problems."

In February 2005, the help desk was averaging 1,000 calls a month. It's now down to between 300 and 350 monthly calls, Mr. Clines said.

He estimated it will take up to four months to iron out remaining problems.

Since 1992, all computer support in the county has been outsourced, Mr. Clines said. He called the new arrangement a hybrid, with some services to be contracted by outside firms and others being handled in-house.

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.