What are the things that you do, or want to do, on a daily basis to manage your database infrastructure? This column is going to be a little different than usual … I’m going to use it as an open-ended introduction to the questions you need to answer in order to implement best practices for database administration. Give the following questions some thought and consider whether your organization has paid an appropriate amount of attention to each matter.
What are the practices and procedures that you have found to be most helpful to automate in administering your databases? Yes, I know that automation has been a standard claim for most DBMS vendors, as well as third-party DBA tool vendors, for many years. But are you really anywhere closer to an “on demand,” “lights-out,” “24/7” database environment yet?
So, what have you done to automate (either using DBMS features or scripts) to keep an eye on things? And, have you looked into how AI and machine learning might be able to assist with DBA chores?
How do you manage change? Are database schema changes integrated with application changes? If so, how? If not, how do you coordinate things to keep the application synchronized with the databases?
And what about DevOps? If you want to bring your DBA practices into the modern era, you need to automate and integrate database application testing and change management to enable continuous delivery. If you do not have the tools in place to automate DBA procedures, implementing DevOps for database applications will be difficult to impossible.
How have you ensured the recovery of your databases in the case of problems? And are you certain that you have thought about all the different types of problems that can arise? Sure, you may have taken a backup last night so that you can recover if the disk fails, but what about application problems? What if you need to recover from an improper data entry or a series of bad transactions? This can require tools that can make specific excisions and modifications to data. And don’t forget about disaster situations. Have you tested your disaster recovery plans? If so, how? And were they successful?
What type of auditing is done on your databases to track who has done what to what data? Are you aware of the industry and governmental regulations that demand tracking changes? Do you audit all changes? To all applications, or just certain ones? Do you audit read access, as well as modification? If so how?
What about patch management? How do you determine when to apply DBMS fix packs and patches? And then manage which servers have been patched and which have not? If you are managing applications that use cloud databases, are you tracking when the DBaaS provider applies patches, as well as the potential application impacts that a fix pack can cause?
Are performance requirements matched up to SLAs so that DBAs do not waste their time tracking down issues for performance that fall within the required service level? Do you have performance monitoring set up with triggers and alarms when service-level thresholds are not being met? And only then are alerts sent out to DBAs by exception? Or is your performance management all reactive, with tuning tasks being done based on which users complain the loudest?
When do you reorganize your data structures? On a pre-scheduled regular basis or based on database statistics? Or a combination of both? And how do you determine which applications and table (spaces) are done using which method?
Do you explain every SQL statement before it goes into production? Does someone review the access plans, or are they just there to be reviewed in case of production performance problems?
Does your chewing gum lose its flavor on the bedpost overnight? (Just checking to see if you are still paying attention to these questions.)
How is security managed? Do the DBAs do all of the GRANTs and REVOKEs or is that job shared by security administrators? Are database logons coordinated across different DBMSs? Or could I have an operating system userid that is different from my SQL Server logon that is different than my Oracle logon—with no capability of identifying that the user is the same user across the platforms?
And how involved do your DBAs get in database design? Are there data architects and data modelers who build conceptual and logical models before any DDL (data definition language) is written? Or are your DBAs and developers creating database objects on-the-fly?
And now, after you’ve read through these questions, and hopefully given some thought to your organization’s current situation, how do you feel about your DBA “best” practices? Are you satisfied? Or is it time to make some changes?