DBA Hell

On the first day of DBA hell, the server gave to me
A database with damaged system tables and no good backups (1)

On the second day of DBA hell, the server gave to me
Two databases with widespread corruption, no backups (1, 2)

On the third day of DBA hell, the server gave to me
Three suspect databases, no backups (1,2,3)

On the fourth day of DBA hell, the server gave me nothing, cause I didn’t have a job any longer…

How does one end up with a critical production database that has no backups? I could kinda understand if the backups were damaged, if the corruption went undetected for long enough that it was in the backups as well, but to have no backups at all? Of an important database?

The only excuse for having no backups is if the database can be trivially and completely recreated from another source with minimal impact to the users. This is not the normal scenario.

There’s an immense amount of information available on backup and restore strategies.

That’s just a quick list, there’s far more information available than that. Enough that there’s really no good excuse to not have backups when they’re needed.

As Steve Jones (blog|twitter) is fond of saying “Good backup, good resume. You only need one”

The Root of all Evil

Or “Shot gun query tuning

There have been a fair few forums questions in recent months asking for help in removing index scans, loop joins, sorts or other, presumed, slow query operators. There have been just as many asking how to replace a subquery with a join or a join with a subquery or similar aspects of a query usually for performance reasons.

The first question that I have to ask when looking at requests like that is “Why?”

Why is removing a particular query operator the goal? Why is changing a where clause predicate the goal? If it’s to make the query faster, has the query been examined and has it been confirmed that query operator or predicate really is the problem?

The title of this post refers to a comment I’ve seen again and again in blogs or articles about front-end development. “Premature optimisation is the root of all evils.” It’s true in the database field as well.


Developers vs DBAs

I was listening to a podcast on nHibernate this morning. Most of it was very good, but there was one section that absolutly made me see red.

The guest on the show was asked how the DBAs felt about the use of nHibernate for a new app on an existing (old) database. The reply essentially was ‘We haven’t told them and we’re not going to tell them.’ Apparently the plan was to only tell the DBAs about this app if there was a problem with it in production

Now there’s a whole lot of problems with that scenario and I can sum most of them up in one word – Trust.

If an application was mostly to be written in C#, but there was a critical portion that depended on (say) Biztalk no one would seriously suggest keeping the biztalk expert on staff in the dark and doing it completely themselves. So why do developers do that with databases?

I’ve often heard developers complain that the DBA doesn’t trust them. Guess what, pull this kind of stunt and they’ve got a good reason for not trusting those developers. Go behind someone’s back, hide stuff from them and they’re not going to trust you. No big surprise there.

The whole ‘them and us’ attitude that a lot of developers and DBAs have is, quite frankly, stupid and highly counter productive. The DBAs are the ones responsible for the database portion of custom apps after they become production, they should have some input during the development phase whether it be on the database design, the stored procedures or just ensuring that the app will scale to the required production load.

So, in conclusion…

Developers, speak to the DBAs, get them involved in projects early. You may be surprised how much value they can add to the project and how much smoother things can go when everyone’s working together instead of fighting each other. Of course, if your DBAs are the arrogant, overbearing type that give the profession a bad name, if may not be that easy.

DBAs, if you know that the developers avoid you, hide projects and prefer to go their own way, first take a look at your own behaviour. Ask to be involved in the early stages of development. Chat with the developers, see if there’s anything that you can help out with or any areas that they need assistance. You may be surprised how many problems can be ironed out before they become problems in the production environment. Of course, if your developers are they type that triggered this rant, it may not be that easy.


Bad Advice

It’s no secret that I’ve been fairly active on a couple of SQL forums for a couple of years. In that time I’ve seen all sorts of behaviour on forums, good and bad. By this point, most of that just doesn’t bother me any longer. There is one thing however that still gets me angry when I see it. Blatently bad advice.

Now, I’m not talking about first-attempt solutions that solve half of the query problem, I’m not talking about honest mistakes and I’m not talking about attempted answers to questions so vague they’re near-impossible to understand. I’m talking about advice that’s so bad it’s dangerous. I’m talking about things like this:

Q: My transaction log’s very large. What can I do to fix this?
A: Stop SQL, find the ldf file, delete it and then start SQL

Q: Dropping a clustered index on a large table takes a long time using drop index. Is there a faster way?
A: Run sp_configure “allow updates, 1 and then run delete from sysindexes where id = OBJECT_ID(‘MyTableName’) AND indid = 1


There’s two main problems with bad advice.

Firstly, the person asking possibly doesn’t understand enought to realise the advice is risky, and if they follow it without testing they could end up in a much worse situation than they were. Dependiong on the circumstances they may end up in trouble with their boss, they may even end up getting fired. Whether they realise the advice is bad before or after trying it out, it’ll erode their faith both in the forum and in the person who provided the information. That leads to the the second problem.

The second problem is damaged credibility and reputation. A good reputation is so hard to get in this industry and so easy to lose. Bad advice damages the forum’s reputation and the reputation of the person posting the advice. Brent Ozar puts it very well: “Being an expert means having credibility. It doesn’t matter how much you know if people don’t trust your answers.”

Then, of course, some other person on the forum has to come along and correct the bad advice and explain why it’s bad so that people who find the thread via google won’t think it’s useful

Bottom line, bad advice helps no one and hurts a number of people.


SQL Injection

This is a bit of a rant, so please ignore it you’re looking for technical info.

There’s been a fair bit of news on SQL injection in the last week or so. Mainly cause some people figured out a way to automate the exploit.

What scares me if the widespread lack of knowledge of SQL injection. I’m fairly active on a couple of the SQL forums and on monday this week there were 2 posts by people who have had their databases hacked via a SQL injection exploit.

If this was a new exploit, I wouldn’t be so disappointed, but it’s not. SQL injection’s been around for years. I first read about it in 2001 when I started in web development.

So, why 7 years later are people still being hit with it? Why does a quick google search turn up a number of web sites with entire queries in the url? Sites including some government organisations, a couple of police departments, online stores and the coast guard (No, I’m not posting links. Search for yourself if you’re curious)

Completely preventing SQL injection is not hard. If web pages call the database using properly parameterised calls to stored procedures then SQL injection cannot be done. Set minimum permissions in the database for the web user and it’s even more secure.

So, why is it that so many sites, new and old, are still vulnerable?

Edit: For some in-depth info on preventing SQL injection, see this blog post on Technet

On recoverability

Had a lovely situation this morning.

I can’t go into details, but essentially a database had no off-site backups, no tape backups and the only full backups were on disk. Then the SAN glitched and both the data files and backups were corrupted.

I’m probably preaching to the converted, but I don’t think there are many more important things on a production server than ensuring good backups.

However, having the backups is not enough. Can they be restored? If there’s a complete drive failure of all drives connected to a server, can the databases be recovered?

When the pawpaw hits the fan (as the local saying goes), that’s the only thing that matters.