This time of year I spend a lot of time in the garden. Early mornings are peaceful and quiet, and the perfect time to reflect on the garden and clean it up. This is a discipline that I also apply to the databases that I manage. I will describe the process that I follow when tending to the garden, and then talk about how a similar process can help keep your database healthy, functional, and efficient.
How I maintain the garden
I look carefully at each bed, and think about what belongs and what does not. What is contributing to a healthy garden, and what is sucking energy away from what I really want to grow. There are multiple levels to this process:
I start by defining the edges of each bed, making sure that there is a well defined edge where this bed ends, and the lawn begins. Then I remove the weeds. Weeds of course are just plants that I don’t want there. They are not bad things, they just don’t contribute to the overall purpose of the bed, and they suck energy from the plants that do. They also make things look messy and compromise both the maintenance of the bed and enjoyment or utility of it by causing us to think about what belongs and what does not belong each time we look at it or need to work in it.
After we are done with this defining the edge, we drill into each individual plant in the bed. We look for dead blooms or branches that can be removed so they don’t suck energy from the living parts of the plant. We stand back and determine how we want the plant to look and we prune so it does not get messy. Essentially, we are ensuring that each plant plays the part we wish in the overall function of the bed.
As we do this, we ask questions like “what is the function of the bed, is it to screen for privacy, or to provide color, or to absorb wind?” And “what is the function of each plant within the bed?” The answer to these questions determine how tall or wide we let the bushes grow, how close together we let them get, and which ones will be allowed to take the most room.
Finally we lay mulch to minimize the weeds and to conserve the moisture in order to minimize maintenance costs and to protect these valuable plants.
What does this have to do with a database?
Managing a database is similar to managing a garden bed. Let’s look at how the above process is similar to proper and thorough database maintenance.
Defining the edges
This is a critical step for securing and maintaining our databases. There are at least three ways that the edges of our databases get messy (compromised).
User Access
Users of course are given access to our data. Initially this is done with intention. But invariably, someone needs some data for a report, a request is made for access, and the access is granted, often with the intent of the access being temporary. From then on, data may leak from our database to external systems without real intention. In a well secured database, all access must be identified, documented, approved, and intended.
Power User Data
Often, data must be combined with external data for a specific report. A power user will build a table in our database for that purpose, and a script will be created to keep that “external table” up to date in our database. This external table may grow in unexpected ways because the application owner does not control it. In a well managed database, all data coming into the database must be identified, documented, approved, and intended.
Database links
Links may be set up to enable another application to either feed data to the subject database or to query data from it. These links must be examined on a regular basis to ensure they are still necessary, and that the linked database is as secure as the subject database is.
Removing the weeds
Once the edges are defined, we must look at all database objects and make sure they are contributing to the purpose of the database. We want to remove any tables, stored procedures, or functions that were introduced during tests or trials, any that serve obsolete functions, and any that were created by power users and are no longer needed or were not authorized. This is both for security purposes and to ensure that no resources are being wasted.
Pruning
Now that we only have valid objects in the database, we apply the same process to the remaining objects. This is a more time consuming and difficult process, but it is important to ensure that the system continues to remain secure and valuable to the organization. This means ensuring that tables only contain data that is relevant and that contribute value. There are two dimensions to this and they are most easily thought of in slightly technical terms: Rows and Columns.
Rows (records)
Are all rows or records in the database valuable and relevant. For the most part, this will be time based. If the system is now 10 years old, do we need all 10 years of data in the database? If not, can we purge some of that old data, or can we archive it so that it is out of the primary database environment in order to improve performance and decrease resource consumption? Beyond time based concerns, there may be other examples, such as products that are no longer sold for which we store a lot of data, can these be purged? Or perhaps we spun off an entire division of the company, but the old data remains, taking up space and impacting performance.
Columns (fields)
Are there unnecessary data points stored in any of our tables? In customer data there may be PII data that is not necessary and poses a security risk. In chemical lab data there may be unnecessary test results in a large table that will never be used but that are taking up space in the database and dragging down performance needlessly.
Laying the mulch
After we have cleaned up a garden bed, we lay mulch to reduce weeds, to improve the overall function and appearance of the bed, and to both protect the health of the bed and improve the efficiency of the bed by conserving water.
The equivalent to mulch in database maintenance is a well designed and implemented maintenance plan. This means regularly scheduled health checks (looking for weeds), maintenance jobs (pruning the dead branches), and validation of backups and disaster recovery processes (Ok, I am struggling for an analogy for that one – maybe ensuring that the sprinkler system works in case there is no rain?).
It’s worth it
We hope you found these database management tips useful. A well managed database is like a beautiful garden. It requires hard work and discipline but the rewards are plentiful. A well maintained garden provides healthy fruits and vegetables and beautiful colors and scents. It attracts songbirds and butterflies, enhances curb appeal, and adds value to your home. A well maintained database provides accurate and timely information to your team to accelerate growth and ensure excellence in execution, and provides critical insights to management that are necessary to keep the business healthy and responsive to changing markets and regulations.
So get out there and bring your clippers!