Here’s a quote from the Fellowship of the Rings; “Sometimes it’s the little things that change the world.” I have found that to be true when working with databases. There are a few things that can make all of the difference in performance and accuracy. Here are some examples:
- Use appropriate normalization for an application:
- Denormalize may be appropriate for reporting and OLAP
- Normalize for transaction processing.
- Use other engines for document data types.
- When three value options are needed don’t use the Bit Data Type with null as a third option
- Index appropriately:
- As few as possible for OLTP
- As many as necessary for OLAP
- Consistently access tables in the same order, especially when performing updates. This reduces the potential for blocking/locking
- Have and test disaster recovery strategies, even if you have failover
- Restrict the capabilities of accounts accessing your databases for applications to the fewest permissions possible
- Plan as if your primary data center will be completely destroyed
- Monitor your systems regularly and save benchmarks for comparison
- Have a list of contacts for when things go wrong
- You won’t remember everything, so have an index to find things when you need them
- Never stop being a student; you never know so much that you can’t learn from others less experienced than yourself
Fee free to add your platitudes. If you can’t add them here, you can send them to btaylor@sswug.org for inclusion at a later time. In the meantime, don’t give up on the little things.
Cheers,
Ben