What are the things that you do, or want to do, on a daily basis to manage your database infrastructure? This blog entry is going to be a little different than my regular entries. I’m going to use it as an open-ended introduction to encourage readers to post their favorite best practices. Give the following questions some thought and if you’ve got something to share, post a comment on it below.
What things have you found to be most helpful to automate in administering your databases? Yes, I know that all the DBMS vendors are saying that they’ve created the “on demand” “lights-out” “24×7″ database environment, but we all know that ain’t so! So what have you done to automate (either using DBMS features or scripts) to keep an eye on things?
How have you ensured the recovery of your databases in the case of problems? Application problems? Against improper data entry or bad transactions? Disaster situations? And have you tested your disaster recovery plans? If so, how? And were they successful? Do you set RTOs (recovery time objectives) to ensure that your backup plan is appropriate?
What type of auditing is done on your databases to track who has done what to what data? Do you audit all changes? To all applications, or just certain ones? If only certain ones, is it to support regulatory compliance or some other reasons? Do you audit access, as well as modification? If so how? And do you audit everything done by privileged users like DBAs and SysAdmins?
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?
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?
Is your performance management set up with triggers and thresholds and then farmed out to DBAs by exception or is it all reactive, with tuning tasks being done based on who complains 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 are done using which method? Do you regularly reorganize both table spaces and indexes?
Do you EXPLAIN every SQL statement before it goes into production? Does someone review the acess 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?
Any other questions I should have asked?