This is the third and final installment of my three-part series on database best practices. A best practice is a technique or method that has been proven more effective than others. Best practices are useful in almost every industry. However, it is important to remember that they exist by consensus and are often hotly debated. In this series I share several of the commonly accepted best practices regarding database design and maintenance.
Database Best Practices Series – First Installment
Database Best Practices Series – Second Installment
Note: These practices are intended to be used with a SQL database. They may not apply to other paradigms.
- Test every change to a production database.
Once a change becomes necessary, and if you are in a rush, it may seem like a minor change can be implemented without going through the hassle of thoroughly testing it. Especially in situations where a script hasn’t performed as expected, it may be very appealing simply to fix the script and run it in the production database. This can be a very dangerous practice. The only way to undo an action is to restore the database from a backup. Even this isn’t truly “undoing” the action, and you will lose all the intermediate data. Thoroughly testing a script by running it in test environments reduces the likelihood of failure. If a script does fail, don’t employ slapdash measures or quick fixes. Take your time, fix the database, analyze the problem, and start from the beginning.
- Place operating systems, data, and logs onto different physical disks.
Having things divided in this way improves performance with no other changes in setup. Do this from the beginning. As your system gets bigger, performance often drops very quickly. Another consideration is data loss. If the MDF drive goes down, having the log on a different data channel can save you the cost and embarrassment of losing crucial data. Building your database with performance and damage control in mind can save you tense conversations at board meetings.
- Invest in a SQL recovery tool.
Even backing up won’t necessarily save you from disaster. Backing up can avert most catastrophes, but an effective piece of malicious code can corrupt even backed-up files. In these situations a good SQL recovery tool can recover and repair corrupt MDF and NDF files for a minimum of long-term damage to your data.
Thank you for joining me for my series on database management and creation. Implementing these best practices can increase the performance and reliability of your system and save you time and money in the event of a disaster. Finally, please remember that, as with any industry standard practice, each of these methods should be evaluated on a case-by-case basis. While the advice is general to any relational database, it is important to weigh it against your specific needs and environment.
Alan Chen is President and Chairman of DataNumen, Inc., the global leader in data recovery technologies. Trusted by Fortune 500 companies, we’re poised to promptly help any business help with data corruption and loss. For information on our SQL recovery software, or any of our other data repair programs, visit DataNumen.