Instead of working, I blog.

I have a friend whom I don't get too see hardly ever and he wrote me the othe day and said that he really enjoys my rants here... and he says I'm only half full of it most of the time.

I like that. I don't think I've ever been described as only half full of it.
So thanks man, you know who you are.

One of the biggest problems DBAs face when trying to cleanup an environment is fighting the user base. We're quite often forced to limit access they've grown used to and even to institute policies the limit what they can and can't do within their code.

Now, here I'm talking about SQL code mainly, but it also stretches to things like deploying new reports and the like.

I call this post the speed of stupidity because most of the problems that need to be cleaned up happen because users get an idea and before they even consider whether it's a good idea or not, or whether the way they thought of to do it is the way it should be done, or whether somebody's even already doing it, they bang out the code real quick and push it to the server... where it proceeds to do its particular brand of damage. And this is really the hardest thing to control in any company where people have had full reign to do whatever they want whenever they want. And it's really hard to explain to them and to demonstrate to them that they're doing more harm than good. And what's worse is that they typically have the FULL backing of upper management.

And management is where it gets tricky because quite often it's the directors and VPs who are pushing their people to produce this code right away. They honestly have lost their ability to reason the logistics of their business. They never even stop to think of performance on the server so it never crosses their mind that there may be a reason to wait. They're so concerned with the fact that they had what they consider to be a good idea and they're now tunnel-visioned on that goal. Not only that, but at this point they've actually convinced themselves that their business can't last another day without this code being in place. This is where that reasoning breakdown comes in. Do you honestly mean to tell me that your company has grown and made money and done solid business for 15yrs, but it's all going to crash down because you don't get this new report by noon? Do you really think this report is that important? Is it important enough to bring the other reports to a halt?

And that's at the heart of this post. It takes a while to architect a good report and get the code right and test it and tweak it, but you can be stupid in an instant. This is what DBAs have to fight. We have to find a way to convince upper management that they can push code into production just because it crossed their minds. They have to learn to slow things down and think them through. It's still ok to give their people assignments to write reports, and other SQL code, but they have to understand that there has to be a process to not only make sure that it's going to be done well, but also that it hasn't already been done by someone else.

And I'm telling you, that's a hard attitude to change. So the next question is, how do you go about changing that attitude? Well, it happens very slowly. You first have to determine the problem. Then you have to determine a fix. Then you have to gather stats from the server. And sometimes depending on the nature of the problem, you could need to gather the data for just a day or 2, or a couple months even.
If it's SQL code, you can code your fix and run the 2 blocks of code next to each other and log the performance differences. Then you can pull stats on how often that code gets run and show a definite improvement over a block of time. If that code is causing deadlocking, or blocking, or just other general contention you'll also want to log the amount of time you spend dealing with these issues. This is what can take a month or 2 because you'll need a large enough sample to be conclusive. Then when you've got the performance gains charted out, you can also list the time you spent dealing with the issues and present that to management. It's really hard to ignore the issue when the numbers are right in front of them. Managers respond really well to hard numbers and if you present them with the hard numbers that prove how much time you spend on issues that could have been avoided by following a process, then they're much more likely to listen. And there could be some other factors as well. These issues tend to stack up on each other and can effect SLAs and application uptime. So these are other factors that should be presented if they apply.

When you take these numbers to the manager you'll want to present it in a way that more helpful than offensive. You know he's the one who's been allowing this to go on, and he does to. So there's no need to rub his nose in it. Just present it as a way to solve a problem that's costing the company money. Look, here's all the time I've spent working on these issues in production that could have been avoided had there been a process to review the code beforehand. A manager would be a fool to ignore this level of proof that things need to change.

Look guys, I know having that new idea gets you excited and you want to see it materialized as soon as possible. But that doesn't mean it should be. Take a minute and think whether the company will really fold if your code isn't pushed into prod right away. What's the real business value to what you want to do? Will it really help you make decisions or help you do something you really need, or is it just nifty to be able to do? Don't waste time and resources on something that's just nifty to know. It has to actually provide something useful to your business.

OK, I'm bordering on rambling here but you get the idea. Let's slow down and start doing things right.

Sometimes no matter how hard you try things just don't come together.
We had firewall issues since we switched subnets so we've been working with the firewall team to work them out.

They finally got around to punching a hole for us a couple days ago and I tested it and sent the reply: Just tested the connection and can now connect to the server. Thanks for your patience.

I then went on about my business and all was well. Then I got an email today from the firewall guy saying that he's been pulling his hair out for 2 days trying to figure out why the traffic isn't getting through, but that he's opened up a case with cisco. He just wanted to keep me in the loop.

I initially didn't know what he was talking about because we solved this issue a couple days ago. And in fact, I was in the process of calling him 10-shades of an idiot in my mind when I looked down and saw my previous email in the chain.

My email in fact said:
Just tested the connection and can not connect to the server. Thanks for your patience.

OOPS!!! Sorry dude.

I was interviewing a guy the other day for a DBA position I have open and I like to ask questions that help me determine how much someone's actually worked with SQL. And one of the ways I'll do that is to ask them how to perform an impossible task and see how they deal with it. In this case, I asked how he would restore a single table from a backup.

He thought about it and went through different scenarios, all of which I kept narrowing down until he was forced to deal just with the backup file itself with no other parameters. He kept trying to think of the flag that he would use to just pull a single table out of that file and he just couldn't think of it. He finally said that he didn't know of a way to do it, but he was willing to learn.

He failed that question in a couple of ways. First and foremost, he had to thing too long and hard about something he should've known for as long as he's been a DBA. So there's that. 2nd, he failed because he didn't have the courage to tell me that it wasn't possible and that as a user, I'm screwed. The data can't be recovered that way. Now, this is a very valid question and tells me a lot about a DBA because we're quite often called on to deliver bad news and if you're not able to do that, then you may not be as strong as you thought.

What he needs is the courage to stand up and tell me that what I'm asking for isn't possible, but he was too caught up in the fact that he's in an interivew and I wouldn't be asking a question like that if I didn't know of a way to do it. I actually explained all this to him and he agreed and said that he thought it was a trick question, but didn't want to say anything.

I've been in that position before where I was thrown by a T-SQL question like that. And initially I reacted the same way because there are so many pitfalls and neat little tricks in the sql lang that I could easily have missed something really big. Or there could have been some obscure legacy trick that still worked. So I get where he's coming from. I really do. However, backup syntax isn't like that at all. It all fits on one page so it's a lot easier to lookup.

And not to go off on too big of a tangent, sales guys are the same way. They hate to say no to a client. They feel it's the worst thing they could do, but it's not. The worst thing they can do is to make promises they can't keep. I saw this at a gig I had about 4yrs ago a lot. The sales guys were always making promises and then expecting the devs to deliver on time. It's messy, and it promotes nothing but crap code. So while they always made their deadline the software was so buggy and so poor with concurrency, it spent more time down than anything. So while they met the letter of the contract, they've greatly harmed their reputation and pissed off the client because now they've gotta take a couple more weeks to fix issues. And that could really harm the client's data as well. Because you could have to make changes to the underlying schema and now you're in the middle of a conversion right after you go live. So I realize you don't want to have to tell a customer no, it's best to tell him the truth. As much as I'd like to get this entire app architected and fully written by this time next month, that's just not realistic. Why don't I get back to you on when we could have a complete product ready, or we can work out a release schedule and we can get you the most critical functions right away and then bring others online as they become available?

So while nobody wants to say no, or that's not possible, or your data's gone for good, it's necessary sometimes and your customers (whoever they are) will appreciate your honesty. And they'll appreciate that what you give them will actually work when it's released.

And by all means, if you're interviewing and you're put in an impossible situation, don't be afraid to say it's an impossible situation. Acknowledge to the interviewer that you realize your choices are limited and that your actions will be factored by that limitation. Then present them with the possible solutions and leave it at that. In this exact case I gave above, the answer would have been something like this:

Well, you can't restore a single table from a native sql backup. If you had some kind of 3rd party product like LiteSpeed or Red-Gate or Hyperbac I could do something for you. But as it stands, you have to restore the entire DB. If you were lucky enough to at least have filegroup backups We might even be able to get away with a partial restore if the table's in the right filegroup. But the situation as you've outlined is pretty bleak.

That answer tells me a couple things. It tells me that you know right away that the task is impossible. So you've worked with sql backup before. It also tells me that you know the situations where what I'm asking would become possible and you've done it enough to know those other solutions immediately. And it shows me that you're willing to be honest with me about my situation. That kind of answer will win a lot of points because if I actually had this problem, you would be the one I wanted to clean up my backups going forward because you've clearly been there before and are quite capable of putting me in a better position. And as an interviewer I may or may not realize that's what's going on, but I'm digging you pretty well right now.

And don't be afraid to tease a little. I had an interview once where they were having problems with SQL memory. They wanted to access more than 4GB, and had the RAM installed, but couldn't get SQL Server to recognize it. I gave him some guidance on how he would see that memory used in SQL and he said that it was perfect for his scenario. Then he asked what they did wrong and how to fix it. I said, well sounds like you need a DBA and if you bring me aboard I'll be happy to help you with that.

So there ya go. There's a little how and why on answering interview questions. There are ways to answer questions that tell them you have experience and ways to answer that don't tell them much about you at all.

Good luck.

We're having a problem with BOE right now and our BOE admin has been on with their support tech for weeks now. The guy keeps insisting that the problem is our DB backups. He says that taking offline backups takes the DB offline so BOE loses connection during that time. The problem is that we don't take offline backups and I don't know anybody in the mssql world who does. That's a fairly common occurance in the Oracle world, but we just don't do it. Now you take that and combine it with the fact that the backup only lasts a few secs and is over 40mins before the problem occurs every day and you've got a nice recipe for moving on and trying something else. But does he do that? Nooooo... of course not. Instead he keeps insisting that the DB is going offline somehow.

So I wrote a quick powershell ping script that pings every 60secs and writes the result to a file. And I ran it from 2 separate servers, including the BOE server. And the DB never faltered once. And again, you've got the pefect ingredients for moving on, but is that the route we're taking? Of course not. So I told our BOE admin to have the issue escalated and to most importantly, never mention the DB as a problem with this issue again. I'm not going to say it again... it's not the DB.

Let's hope the next tech can see past the DB and actually try to solve the issue.