The Seven Habits of Highly Effective DBA's

I was just reading my usual daily digest from SQLServerCentral and it included an article on an interview with a scary DBA.

Now I've worked in IT long enough to have crossed swords with many a DBA. I even had the pleasure once of firing one, (and whilst no-one wants to see someone out of a job, I really do mean pleasure, what a ******* **** that guy was, and I hope he gets all that's coming to him.)

The interview rambles a bit, a one point asking which historical figures would have made good DBA's (!!!), but Grant Fritchey sums up very well the characteristics of a highly effective DBA. You can read the article here, but I quote Grant below;

The first one that comes to mind is grace under pressure. At some point in your career, you'll have a substantial number of managers, probably going very high up the chain of command, standing inside your cubicle. Typing will be difficult because they'll be jostling your elbows to get close enough to read what you're typing, even though they don't understand a word of it. It's all because, for some reason, one of the most important databases in the company is offline or inaccessible, or in an unknown state. You need to fix it quickly and competently, all the while explaining what you're doing in plain English, no techno-babble allowed, and suggesting ways to prevent the problem, whatever it is, recurring in the future. If you can do all this at the same time with those managers in your cube, then you're on your way to becoming an exceptional DBA.

I think in addition to grace under fire, Grant is also explaining several other traits;
Communication - they key to doing well in IT is to be able to communicate simply to non-IT people. So many people get this wrong, but I would almost put my entire success down to this one skill
Humble - Being a DBA involves power, Sysadmin, sysdba etc. People who get off on power make poor dba's. Senior managers are in your cubicle to get the problem fixed. It's your job, your not the worlds greatest man, your just a man who knows a password and some syntax. Remember that and you'll do well!

Next is a willingness to learn. Picking up the new TSQL syntax is only a small, although important part of that. The larger part is an understanding that things change, and a willingness to adapt as necessary. You might need to learn how to work with an agile development team, or implement a third party software package that requires 'sa' privileges, or move your database out into the cloud. It's a willingness to embrace the change, to learn, not just new processes, but new paradigms that will make you more successful as a DBA.

Flexibility is key. Standards, for example, are a means to an end, not the end in itself. If your standards say that apps cannot have 'sa' priviledges, then your business colleagues buy a product that requires them, then remember that they bought it for a reason. To make money for the company that pays your wages, so find a way of dealing with it!


Finally, I think a good DBA has to have a strong backbone. You need to be able to say no. You need to be able to say that the choice made on this database, if implemented, will cause problems, explain why the problems are going to occur, and offer a more-suitable alternative. You will often be under pressure to accept the solution "as is", but if you simply roll over, you're going to pay the price. It won't just be you though; it'll be your database and possibly your company.
There are certain issues on which a DBA must never compromise. For example, there's a good reason why you should do backups before a major modification of the database, and just because someone within the organization thinks it's a silly waste of time to run one, and doesn't want to wait the extra 45 minutes, tough. It's your charge as a DBA to ensure the integrity and protection of the databases under your control.
If someone asks for something silly, say no. You may have to do it anyway but get out there and make sure people understand why you said no. And it shouldn't just be because you want to be able to say "I told you so." If that's why you're doing it, stop being a DBA. You do it so that maybe, the next time, they'll listen.

SUITABLE ALTERNATIVE!! As one of my best colleagues once said, good design is the result of furious debate. If you don't like something, suggest an alternative THAT WORKS. I couldn't count on both hands the number of times I've wanted to physically hurt DBA's because of their 'veto everything that different' attitude that contributes nothing.

If your a DBA, and people hate you, then perhaps you should try incorporating some of these ways of working into your day. Start adding value to the business, and above all, remember that your just a someone who knows a password and some syntax!

SQL Server Include Columns

I've just been refreshing my memory on the basics of indexes after my daily email from SQL Server Central arrived, advertising this excellent beginners guide to SQL Server Indexes from Gail Shaw.

I think it's a great article, covering all the essentials in a clear and precis manner. My only criticism was that she skipped over the value of INCLUDE COLUMNS, without introducing them in the first place.

I'm a well travelled Database professional having started on DB2, moved to IMS, then Teradata before arriving at SQL Server. I'm fortunate to have been able to pick-up SQL Server very quickly through my good friends James Rowland-Jones and Jamie Thomson (aka SSIS Junkie), both SQL Server MVP's. (and if your in the UK, James is speaking at SQLBits, check him out)

I recognised INCLUDE COLUMNS from my Teradata days, and whilst maybe not exactly the same, the Teradata Join Index and even more impressive Aggregated Join Index are similar in that they allow you to specify columns as part odf the index (not the index key), that allows the query to be satisfied from the index alone, without resort to the base table.

Because Gail didn't really cover Include Columns, I did some research on the web and was a bit surprised that the majority of the SQL Server community considered Include columns as a way of getting around the 16 column and 900 byte limitation on a sql server non-clustered index. Despite the fact that almost everyone agreed that these limits are highly unlikely to be reached in normal usage, something that with my 15 years of experience, I'd agree with.

The real benefit of include columns is being able to elevate a few select columns from the table to the index, such that the index read alone can satisfay the query. I work in retail (retailmonster get it!) so for example you may have some individual basket data, ie everyitem an individual customer buys in a single trip. This is retail gold dust but also incredibaly space and CPU heavy. You might split this into a basket header record, with an index of storeid/calendardate, but since a lot of people might want the total retail value of the basket, or the number of items in the basket, if you specify totalbasketretailvalue and basketitemcount fields as include columns, then a query asking for the index columns and only these fields, would be satisfied by the index read and not have to go to the table.

James Rowland-Jones has just authored a new book, SQL Server 2008 Internals, in which he covers the benfits of include columns for SQL server.

The Effect of Company Culture on Application Development

How the underlying culture of a company affects design.

My early professional career was all in large, well establsihed (FTSE100) companies, and therefore my early experiences of IT were shaped in what I like to consider a very rigourous and disciplined way. I like to think of myself as old School and proud of it.

However since becoming a consultant I've worked at varying sizes of companies and have therefore become exposed to other ways of working. What strikes me most is how much the underlying culture of the company pervades every way of working. Like some secret predicate that is present on every decision.

Two examples.

I worked for one of the largest retailers in the world for over a decade, where the underlying culture was global expansion, scalable, repeatable etc. This affected every decision in application design and no-one would ever dare not take a decision that was pragmatic or tactical, however appropriate that could have been.

More recently I've worked in a startup organisation with less than 20 employees, where money is tight, the revenue stream is far from garuanteed and it's the corporate equivalent of a hand to mouth existence. This means that every IT decision is pragmatic, tactical, low cost, here and now.

Now in both instances, it was often not an explicit part of the requirements that the solution needed to meet these underlying requirments. It's just that everyone conformed. Further more, the culture wasn't necessarily purposefully dictated. ie the culture formed on it's own, regardless of whether it was in the best interests of the company.

So beware of the underlying culture of an organisation as it might lead to architectural and design decisions that aren't appropriate, just because every does it that way...

Maximum Value Safety Nets

Related to my previous post about parameters in application development, I've recently been incorporating safety nets into my applications to catch those unusual data occurrences that can result in very abnormal results of calculations. Results far outside what was expected and that can have a dramatic effect on the final output of the system.

I've built in safety nets that will trap any outliers and limit them to a maximum, or minimum value. As you've guessed, these safety nets are defined at the grain of that fact to give the maximum level of control of the system.

Hindsight is a wonderful thing and I wish I'd put these in at the start as although the number of these issues that have surfaced have been small. The pain they've caused me has far outwieghed the effort to have designed them into the system in the first place.

The biggest pain has the credibility issue that these instances have caused. We'll all be familiar with the newspaper headlines about people being charged thousands of pounds by mistake (in fact there was one today.. man loses driving license for doing 383mph on a motorbike). These isolated issues, which may be 1 defect in millions of transactions, cause people to lose faith in the system. These can easily get blown out of all proportion by anyone with an axe to grind against a new system.

'If this numbers wrong, then maybe they're all wrong...'

Clearly not the case, but how many you out there have faced this situation.. Safety Nets..!

Parameters in Application Development

This is my first blog post in months and been spurred on by my good friend JRJ blogging here about his latest consultancy jolly to Seattle for PASS09.

I've been getting my fingers dirty for the last few months in hardcore application development. A bit of a blast from the past for me as I've mostly been management for the 5/6 years, but you'll be pleased to know, I've still got it when it comes to application development.

This bueaty of a project is pretty hardcore. A complex statistical database app crunching hourly data for a retailer (no surprises there). Given my background in repeatable application deployment, the application is massively configurable by parameters. However where we've ended up is very different from where we originally started, and has caused me to re-consider my whole approach to defining parameters for applications.

To cut the story short, we originally had parameters defined at higher level, ie system wide parameters. But in almost all instances have now dropped the granularity of the parameters, to match the grain of the fact. This means we have a lot of parameters! and you don't want to be typing them in by hand (or adjust them manually), so along with the increase in granularity, comes along a little sql engine that we use to set and adjust the parameters. If you use SQL to set the parameters, then there's little difference in overhead between a single row where parameter x = 12, or 25000 rows where the value of x = 12 ie

Update Parameter
set x = 12;

You could consider this an overhead, but if you build it in from the start, you never know any different and you build the system to work this way. Then if you need to start setting seperate values, you have the functionality and can go do it. I'm sure this is less effort than changing your mind during the build, or worse, after go -live.

I'm so sure in fact, that this is now my new default approach to designing parameters into applications, and I'll be testing it out in anger as I've now taken on a another apps dev project where I can again flex my system designing muscle.