As a trainer that focuses on beginning to intermediate students it's always

interesting to see which questions and concerns come up again and again across

classes. One of the items at the top of the list has to do with the fact that

most DBA's work alone, that is, with no peers. That often leads them to worry

that they aren't doing everything they should be, especially if the nature of

the business precludes using all of the published 'best practices' we hear so

much about. For those that ask the question the training is a much a 'health

check' as it is a learning experience! An article won't replace the value of

having a peer or attending a class (I hope, but I'm biased!), but here's my list

of what I think should be top on the list for DBA's.

#1 - Backup. Job one is to back up the data and be able to restore it when

needed. That means a full backup at least once a week and if possible every day,

and log backups in between at a decent interval, probably 5 to 30 minutes.

Whether you use the maintenance plans or write your own, there is nothing more

important than guaranteeing you can provide a copy of the data if the worst

happens. The hard part is that important as this task is, most of us set up the

job and at most monitor it. Entirely human and pragmatic, but it's worth

checking up on manually once in a while. It's also important to do the

occasional point in time restore so that you're ready to go if the need arises.

Not many things will get a DBA fired but not having a backup is one that will.

#2 - House Keeping. I'll talk more about time management in a moment, but the

key to sanity as a DBA is to set aside 30-60 minutes per day for house keeping.

For me it's being at the office 30 minutes before the bulk of the

employees/users starts work to make sure the servers are up (alerts are good,

checking is better), then I check all my nightly jobs, email alerts and

notifications, event logs, and free space. During this time I also review my

calendar for the day and remainder of the week, and then a few minutes on the

daily version of my professional development plan reading various newsletters

and blogs, and sometimes listening to the

Voice of the DBA. Don't let

anyone deny you this time. You may have to reschedule it on rare occasion, but

just force it to be a routine and usually the business will accept it. Sometimes

during big projects and periods of stress you'll be tempted to skip house

keeping - don't! It's the path to the dark side. I would argue that this item is

the primary reason you collect your paycheck. Better to work an extra hour to

get it done than to skip it.

#3 - Best Practices. Best practices aren't one size fits all. If you're in an

environment where best practices don't seem to fit, find a peer to discuss it

with. See if you can convince them that the alternate practice makes sense in

the context of the business. For example, recently I had a student working at a

very specialized company where there was a good sized group of non DBA's that

had the ability to create tables within certain databases. Definitely not a

recommended practice but based on the situation it was a valid solution. Note

that I'm not recommending you disregard best practices. Just understand that the

realities of the world don't always make it possible to implement them all.

#4 - Security. This is a little bit about best practices, but it's also about

common sense. Only DBA's should have sysadmin access, and no one should be using

the actual SA account. Assign permissions to roles and put users in roles. If at

all possible grant permissions on stored procedures only. Avoid use of the

Public role.

#5 - Gatekeeper. This is the main time that DBA's are visible to developers

and the rest of the business, when a change needs to be deployed. Change

absolutely has to be controlled and tracked. As a DBA it's our job to make those

changes within a reasonable amount of time and to let the requester know when

the change will be made. The 'reasonable time' portion of that is one place

where good communication and setting expectations is important. We don't want

non DBA's making changes because if something goes wrong, we need to know very

clearly what recent changes have occurred. Logging can be as complex as checking

deployment packages into source control or as simple as saving the change script

in an email folder. A DBA should always be able to answer the question "what

changes have been made recently?".

#6 - Baselineing. The least fun time in the life of a DBA is when a major

performance event occurs. You start Profiling and Perfmoning with the boss

anxiously looking over your shoulder, and then you see a statement that takes

10,000 reads. Boss asks "does it always take that many reads" and your not so

exciting answer is "I don't know". Capturing a weekly or monthly baseline using

Profiler and Perfmon gives you a way to research that question. Not just to give

the boss an answer, but to decide if that's the right item to spend your time on

right now. It's also useful for assessing how your environment has changed,

answering questions like "how many connections did we average from the

application a year ago?".

#7 - Not Just a Gatekeeper. It's not always possible for us to look at stored

procedure and tell a user that it won't return the correct answer. It is

possible for us to make sure it conforms to our coding standards (requires

tables to be schema qualified, no goto, no cursors, comments required, etc) and

to just apply the "is it stupid" test. Beyond that, the biggest mistake I see

DBA's make is not also assessing the performance implications of the change. If

it's a change to an existing stored procedure I want to see what the current one

costs versus the new one. A change of more than 10% might indicate it needs

tuning, or that I need to work on the query plan and indexes to maintain

performance at current levels. If you don't look at every single change

this way you'll eventually suffer performance issues due to the overall entropy

that results. It's a little more work, but it's absolutely worth it.

#8 - Customer Service & Consulting. Think of yourself as a service provider.

Businesses pay you to keep the database servers running and the data backed up

and secure. They want you to do that without impeding their ability to do things

that will lead to revenue generation. This sets up a natural tension between

your job to do the above and their need to move fast. Sometimes it means you

have to modify or abandon a best practice. If the business doesn't make money

you don't have a job. If you lose the data you don't have a job. Try to avoid

extremes and understand the needs of the business and you'll go further (and

make more money) than those that see the world in black and white. Get involved

with your developers and remember that their job is at least as hard as ours,

and they are just trying to do what they were hired to do (solve problems) as

best as they know how. Take the time to understand their challenges and to help

them understand yours, and you'll have the beginning of a dialog that can lead

to some very good results. As a DBA I try to become the internal data

consultant, not just the guy who does the backups.

#9 - Learn Beyond. I meet a lot of DBA's that are very successful in their

current job but I think would struggle to get the next job. Why? They are

working in a specialized environment and aren't maintaining all the general

skills they need. For example, I see DBA's that express no interest in

connection pooling and how it affects them because they only use off the shelf

software that can't be altered. Or that don't want to learn replication because

their employer doesn't use it. It's definitely hard to learn some things without

a project to drive them, or having the right hardware in some cases, but don't

become a one trick DBA.

#10 - Whack a Mole. This is my secret to success, I put it at #10 so only the

most curious will learn it! Once a week run a couple Profiler sessions, one to

capture queries with more than x reads, and another to capture queries over x

duration. I start at 10k reads and 10 seconds. Some of these will be jobs and

similar one off occurrences, but the rest represent tuning opportunities. Add

them to your list and set aside an hour on Friday afternoon to try to get one of

them to run faster. Eventually you'll get all queries below the threshold. Then

it's time to reset the bar at 9k reads and 9 seconds and go again. This forces

you to once a week look at queries that perhaps used to perform ok but as the

data has grown things have slowly declined, and fixing them helps maintain a

steady load on the server. Combine this with #7 and you'll have a lot fewer

performance issues.

It's not as easy as just doing my 10 steps of course, but if you're doing all

that stuff you're not in bad shape. Build yourself a formula, stick to it, and

reevaluate it once a year to see what you need to change. I look forward to your

comments.