Everyone talks about “best practices” but what exactly are they?
A "best practice" is a professional procedure or tactic that is accepted or prescribed as being correct or most effective.
Take a moment to think about the things that you do, or want to do, on a daily basis to manage your database infrastructure. What are the main categories of “things” that you want to tackle with best practices?
Keeping Databases Up to PAR
In 2002, I introduced the concept of keeping your databases up to PAR, an acronym for performance, availability, and recoverability. This is a good place to start when defining best practices, but almost 20 years later it is no longer the entire story because technology has advanced and DBAs have been charged with doing more.
Performance best practices are required for ensuring acceptable performance at the application, database, and system levels. Application performance focuses on efficient program code and database access routines. Database performance focuses on the database implementation and configuration. And system performance focuses on the hardware and system software required to run the database and the application. Best practices are required to test and monitor the ongoing performance of all three of these areas. Is your performance management set up with triggers and farmed out to DBAs by exception or is it all reactive, with tuning tasks being done based on who complains the loudest?
Availability best practices are needed to ensure the ongoing accessibility of systems and applications that support the business. High availability best practices include monitoring and building redundancy into the system to prevent outages.
Recoverability best practices are necessary to be able to quickly resume operations in the event of a failure, whether physical or logical. This requires planning for backup and copying of data, as well as testing the viability of backups. How have you ensured the recovery of your databases in the case of hardware problems or application problems? What about protection against improper data entry or bad transactions? Have you planned for disaster situations? And have you tested your disaster recovery plans? If so, how? Were they successful?
All three of these areas are essential and must be built in sync with your organization’s service-level requirements and budget.
Additional Considerations
Another area not mentioned in the above rundown is database schema change management. How do you manage change? Do you use a change management tool or do it all by hand? Are database schema changes integrated with application changes? Does your development team use agile techniques and DevOps and, if so, is the DBA team integrated into that methodology? And, regardless of whether you have embraced DevOps, how do you coordinate processes to keep the applications synchronized with the databases?
Let’s not forget data protection and security. Although it is not the duty of DBAs to determine the level of security for each piece of data, it is their job to help ensure that the needed level of protection is implemented. Do you have best practices defined for how and when to use all of the security-focused features of your DBMS?
How has regulatory compliance impacted your database administration activities? Have you had to purchase additional software to ensure compliance? How is compliance policed at your organization? And what about auditing data access to track who has done what to which data? Do you audit all changes, and to all applications, or just certain ones? Do you audit access, as well as modification? If so, how?
Let’s not forget about storage management. Do you actively monitor disk usage of your table spaces and indexes? Do you have alerts set so that you are notified if any object is nearing its maximum size? Do you monitor extents and periodically consolidate? How do you do it? And do you have a capacity plan in place that tracks when you will need additional storage or processing capacity?
Two Approaches
These are the primary areas for which you should be formulating best practices to ensure the ongoing efficacy of applications that access databases systems. But now that we have taken a look at what we need to tackle, how can we determine which practices are "best"? I guess there are two options: test everything yourself or rely upon the trials and tribulations of others. I know which approach I would take. There are tons of best practices guidelines available in books, online, at conferences, and from your peers. Start there … and maybe publish your experiences to help others with their best practices, too!