In my company, every two years, we have a tradition to collectively ponder at the technology road map for the next two years, this may include what OS / server products / development tools / management tools / hardware architecture we will adopt, and this road map may also include what resources (both human and capital) we may need.
As a production DBA, however, other than the technology, I am more interested in the database administration domain, from operation practice / methodology to operation tools / framework, so I coined the phrase “DBA Operation Road Map” and outline the following items in my Road Map, which is driven by my work challenges.
1. Auto snapshotting the sql server ecosystem during the troublesome time
Explanation: during the trouble times, such as, long-time CPU spike, tempdb load pressure and log space used up etc, we want to know exactly what is happening inside and outside the sql server from different perspectives, such as what SQL statements are running and their execution plans, how many connections exist, the values of various performance counters (CPU / Memory / IO / Network) and whatever else that may be of interest (usually application related).
If we can snapshot all of these data of interest, it will no doubt facilitate the quick findings of the root causes.
2. DBA script library
Explanation: In my daily work, I will use lots of scripts for various purpose, e.g. trouble-shooting, reporting, replication setup, partition maintenance, index maintenance, backup/restore and other daily routine work (health check, login setup, job creation etc), and other DBAs, because of their responsibilities, have other types of scripts.
From my own experience, it is tough for me to manage all these scripts in the sense that I cannot find the right script quickly enough (need to go through a deep folder tree to finally find the right script), and I always dream I can put all these scripts into a table, and with a column dedicated for key-words so I can get everything quickly using a sql statement, and I can backup this database and bring it with me in a USB drive.
(No, I do not like source control software to manage my scripts as it ends up almost the same as a folder structure)
From the company perspective, with this library in place, the db administration best practices / knowledge (specific to the company’s own environment) will be kept intact and can be “inherited and enriched” down the road.
3. Smart system capacity planning / forecast model
Explanation: One of my annual tasks is to do capacity planning for storage / memory / CPU and it is really tough to get an accurate number because of the # of input variables and also the capacity demand is not linear to the input variables. What I really want to achieve is to have an self-adaptive capacity forecast model that will adjust itself (or at least can be manually tuned easily) based on the forecast result vs. the reality data.
4. Leveraging the use of third party tools
Explanation: there are lots of good (and free) third party tools there, such as sp_whoisactive (by Adam Machanic), but to make the best use of such tools, we need to embed it into our own monitoring framework. One usage case here is whenever a CPU spike (lasting longer than 30 seconds), we will call this tool to automatically do a snapshot of the sql server environment and log the info to a table and we can then do more analysis on the collected data.
I believe “our arms are as long as our tools can reach” and the importance of the tools for work can be evidenced in one of my favourite blogs “The $1,000/Hour Consultants”.
I have been a DBA in many different types of organizations, one thing I feel common is we DBAs are usually burdened with various daily work and there is not much time left for us to think about our future road map. It would be very helpful if we DBA community can share our big picture “road map” projects so we may leverage each other’s efforts to make our daily work easier and more pleasant.
