I know…HUGE topic. I have thought about this topic often in my travels, especially after reading Brad McGehee’s book How to Become an Exceptional DBA. Brad’s book will give you a lot to think about, especially in the area of “soft skills” needed to do the job. Regarding technical skills and tasks this book only scratches the surface. Brad does dive into the topic a bit, basically saying that there are too many skills for one person to be an expert at; so find your niche and “specialize”. While I agree with Brad I also am sometimes responsible for the “overall” database environment, so I try to make sure all of the technical areas are covered (by me or by someone else). Until now I haven’t written down a list of these technical areas or functions that need to be managed. To put it another way, this post is about the “A” in “DBA”; i.e. what functions make up “administration” in “data base administration”?
Like many DBAs I have a list of functions that I need to perform, some explicitly assigned to me and others that I just “know” I need to do. Being a consultant this list changes from site to site, and, over time I have created an overall list of essential DBA functions or tasks. Please don’t think that you have to perform all of these functions yourself; there aren’t that many hours in a day for that! No, I really use this list as a starting point to make sure all of the bases are covered by someone in the organization. When asked to consult as a DBA I try to use this list to make sure that someone or group is responsible for each function. The order of this list is not important; I started to put them in order by (what I thought was) importance but quickly realized that the “importance level” of each function changes based on your current situation. If your work location is anything like mine the “importance level” changes day-to-day!
This post has the potential of vying for the title of “longest post ever” based on the subject I just introduced…but…do not fear; I’m really only going to provide my list and a brief description of each. Entire books are written on some of these functions/technical areas (or could be). I’ll probably add posts in the future on some of these topics when I think I have something meaningful to add; otherwise please use your googling skills to find out more specifics on each one as your time permits. I split my list into 2 sections, enterprise/server-level functions and database-level functions. I find that at most small work sites these functions are intertwined, but at larger sites the functions tend to be split somewhere near where I have split my list. Each site is different; I just try to make sure that someone or some group is responsible for each function. If each function is covered then the workplace is usually quite a stress-free place to work; if not then the site is usually one of those “fire-fighting” work locations that most of us have seen or been a part of.
Let’s start with enterprise/server-level functions. These administrative functions are a necessary yet (sometimes) mundane part of the job; sometimes these functions are even overlooked or ignored and cause great pain for the organization when something goes awry. Many times there can be quite a bit of automation built to perform these functions and the DBA needs to monitor/review the results of the automation, but the most important thing to remember is that these functions NEED to happen, and they NEED to be monitored so that they do happen…your job may depend on it!
- Database Backups – I put this one first because I have seen people get fired for this one. The DBA needs to ensure they happen, ensure they are valid, and ensure they are safe. This is really all about disaster recovery. Ask yourself, if server x was suddenly not available (for any reason), do I have backup files available to re-create server x and make the database(s) available? This function also includes keeping secure backups of encryption keys and documenting your backup and restore processes.
- Document your environment – Again, this is really a disaster recovery function. Document your server hardware/software configuration and keep it up-to-date. Ask yourself, do I have all of the information I need to completely re-create server x if needed?
- Monitor database integrity (DBCC) – Run regular integrity checks of the physical database pages to ensure that 1) the users can access all of the data, and 2) that the integrity of the pages in your backup files are also intact. Ask yourself, if there is an integrity issue left unresolved how does it affect the user community? Does it point to a hardware error that needs to be corrected?
- Capacity Planning – Keep track of how much hard drive space each database is using and track it over time. Not just for yourself, but for management. I bet all of us have been asked some or all of the following questions: “How much space are we using?”, “How fast is database z growing?”, “When will we need to requisition more hard drive space?”, “Can we/should we move database z from server x to server y?” While capacity planning is often mostly about storage space I like to also keep an eye on CPU and memory usage for the same reasons. Ask yourself, when it’s time to order new hardware, do you have the information you need to provide specifications for it?
- Monitor overall server performance – Keep track (over time) of overall performance statistics for your server(s). I like to run PerfMon for a specified amount of time during regular work periods and capture summary statistics – say once a quarter or once every 6 months. This is related to capacity planning and can help answer some of the same questions. How do you know if you need bigger hardware if you don’t track the usage over time? Are there performance issues on your server that need to be dealt with? Part of monitoring performance should also include monitoring blocking/deadlocking issues.
- Security of the server/database – From a security standpoint the DBA gets the blame if there is unauthorized access to information from the database. The DBA needs to manage all access to the databases. Do you have auditing in place to capture both authorized and unauthorized access, including your own access? Do you limit access to only what is required by each user? Can you tell if there has been unauthorized access?
- Maintain the database software – Keep track of the versions of SQL Server used on each server. Apply new versions/service packs/patches. Keep abreast of the release schedule for service packs/patches and determine when or if they need to be applied to your servers. This also applies to all other software used, including the operating system and third party software related to the database. If a new release comes out, do you have a “testbed” where you can test it out and make recommendations?
- Install/configure hardware – When new hardware does come in the door, install and configure it, or make recommendations to the folks that do. Are the hard drives configured correctly for SQL Server? Am I sharing this server and need Resource Governor? Do I need high availability? Clustering? If you don’t perform the physical installations yourself, double-check the work before you start using the server.
- Disaster recovery management – Create or manage the disaster recovery plan (the database portion). The hope is that you will never have to use it, but executing the plan in a disaster is part of the job as well. Do you have a plan for different disaster situations that might occur? Have you simulated or tested any of the scenarios recently?
- Managing related software – This is a catch-all function that encompasses Microsoft and third party software. If used at your site you may have administrative duties related to SSRS, SSIS, SSAS or third party tools for backup, performance monitoring, productivity tools, etc.
Database-level functions comprise the second part of my essential functions list. These functions usually have much more attention drawn to them since, more often than not, they impact the users of your database(s) directly. I often spend the bulk of my time performing these tasks, but that doesn’t make them any more or less important than the server-level functions above.
- Data modeling/change management of current database(s) – Responsible for developing new database structures (logical and physical) and changing existing structures for custom software applications, or managing the “change” for COTS (commercial off-the-shelf) products when required by the product vendor. Yes, this is a large function that may involve different responsibilities depending on your work site or project. This function may be your full-time job if the site or project is a large one.
- Production change management – Execute/manage data fixes, DDL and SQL code object changes in your production database(s)…make that in all of your environments. Also ask yourself if you have a “back out” plan if things don’t work out each time you execute the change scripts.
- Statistics and index management – This function consists of several task items related to database indexing. First, manage the updates to index statistics. Managing statistics/index fragmentation is one easy, proactive way to keep your database performance on an even keel. Second, monitor and correct index fragmentation. These first 2 items are applicable even for COTS databases; your vendor may even have best practices to follow for their database. Third, monitor index usage, removing unused or underutilized indexes. Work with your application development team to identify and remove unneeded indexes. Fourth, monitor possible new indexes. Using DMVs you can (as of SQL 2008) monitor possible new indexes and use the information to determine if new indexes can or should be added to your database. There are many ways to perform these tasks and many tools and scripts available on the internet to help. The last 2 items here can make you look like a rock star if you can tune some area of performance without being asked first! I’d advise against changing any index structures in COTS databases unless you have permission first from the vendors; most do not appreciate changes to their schemas without permission.
- Monitor database performance – I separated this function out from server-level performance because it uses a different set of tools and a different mindset. Monitor the performance of user database(s) including application access and their specific calls. Manage performance tuning of database code (ad hoc T-SQL as well as stored procedures and functions). I would monitor the performance of your custom user databases as well as COTS databases because there are things you can do to affect the performance of COTS databases without changing their database objects.
- Archiving Data – Databases tend to grow and grow…this function is included because I believe it is an essential function. The answer may be that no archiving is required, but many times there is no real need to keep old data. Develop data archival rules and routines; execute the archival routines as required. Ask yourself, how much space can I save? How much will performance improve if the size of table z is cut by 40 percent?