Keep Databases on the Right Course with Maintenance Plans
Okay, your databases are patched. Everything should be fine now, right? Yes. For a period of time, they are.
Like cars, databases run at their peak when regularly maintained. When not maintained, databases tend to slow down, clog up bandwidth, get corrupted, & even crash their servers. Sifting through thousands of records all day, every day wears on them.
Scheduling database maintenance cleans them up and protects against a company-fatal loss of data. Think of it as a tune-up – done on a set schedule (weekly/monthly) and handled by an expert.
A thorough maintenance plan should include the following four steps. Most of them can be done by an engineer on staff, or a specialized DBA consultant if you prefer.
A. Investigate
If you’re working with a consultant, the first thing they’ll do is search for all the databases on your network. Not only will this save time, they’re likely to find any ghost databases lingering around (as discussed in Article 1).
B. Check Backups
After you find the databases, check for backups. Are backups scheduled? How often? If not done often enough, the DBA must schedule them so their work isn’t undone later by an unexpected crash. Have the backups been tested? Many companies who backup don’t test their backups fully (i.e., restoring a backup so you’re sure it’ll work in an emergency).
C. Systems Integrity Check
Once all databases are accounted for, the engineer/consultant should set an integrity check to run for several hours. This checks the structure of core database elements like the user tables, the system tables, and the indexes. This prevents breaking in the tables which could lead to corruption.
D. Reorganize the Indexes
After the indexes are checked for faults, the next step is to reorganize them. Since indexes tend to fragment the more you change the data in their tables, reorganizing cuts down on data loss. If they’re badly fragmented, they’ll run a rebuild – dump the old index entirely and build a new one in its place.
DBAs can do reorganizations and rebuilds through wizards (if available) or through custom SQL scripts. Custom scripts are more thorough and have a higher success rate, but they take skill in SQL to make the best use of.
Maintenance plans like this work primarily to remove the potential for failure. While crucial, once it’s done you can turn attention to racing the engine – tuning for optimum performance. We’ll discuss how to do that in Article 3.
Questions? Email our DBA team at info@planetmagpie.com. Or call us at 408-341-8770 to schedule an appointment.
