Written by Merrill Spendlove
Friday, 27 July 2007
Over the years working as a DBA, I've come up with some simple suggestions that make life as a DBA easier.
- "Right" and "wrong" choices apply only to moral issues. In database administration, remember that “your way” and “my way” are just different ways, and neither may be right or wrong.
- Normalization is a must. Avoid having data stored in more than one place. A database that is properly constrained and indexed will seldom have performance problems due to normalization.
- Integrity should be independent from the applications using the database. Data corruption is a database administrator’s worst nightmare and can ruin an application. Properly constrained data cannot become corrupted. It’s that simple.
- Understand how to properly use indexes and partitions. Indexes can increase performance a thousandfold. Partitions are usually saved for large tables and are not needed often, but when they are, they can make you look like a miracle worker.
- Understand queries and query structures. Learn how to use them when using indexes and partitions. Know how functions affect which indexes are used in queries and how query relationships impact performance. There are standard ways to build a query to run efficiently; perfect them.
- Be smart about performance tuning. A one-time procedure may not need performance tuning because it may take more time to tune than to run. A single transaction that lasts a half-second does not need tuning, but millions of half second transactions do. Be selective in your tuning and understand what you are doing.
- Keep security tight. You have heard of “too many cooks spoil the broth.” When you allow too many individuals to help in database creation, everything you have worked for in tips one through six (above) can “spoil the broth”!
- Documentation can be as simple as being descriptive in your naming and coding. Whatever your documentation method is, do it!
- You can't be too descriptive. It may take a little longer to fully describe what is taking place in your database, but you will be glad you did when you come back to it in a month or so. Your progeny will thank you.
- A good business analyst will make design easy because most data elements will be caught up-front. A bad one will cause thrashing as you refactor over and over.
- The first choice is usually the best choice. Experience has revealed that if a process is running and performance is not an issue, then don't change a thing. Chronic refactoring has caused many projects to fail.
- Only put business logic in the database when absolutely necessary. Most business logic should reside in the application. However, there are cases when it is in the best interest of the application to put it in stored procedures and functions. These should be determined on a case-by-case basis.
- Keep it simple. A good logical flow in your code, with descriptive fields and documentation, will make it much more maintainable by you and others in the future.
- Share your knowledge. Don't be afraid to give and accept suggestions. There are many brilliant, yet foolish people. A wise man will accept and share knowledge.
- Learn from the experiences of others. Know that many have passed this way before and many will again.
Merrill Spendlove is a senior database and software engineer for the Church.