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.
