Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 593

Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 710

Strict Standards: Redefining already defined constructor for class wpdb in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/wp-db.php on line 58

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/cache.php on line 99

Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/cache.php on line 404

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/query.php on line 21

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/theme.php on line 576
Database Design
Jun 15

I have always enjoyed the teaching and wisdom of Dr. Steven Covey (especially if he does not litigate for derivative works!). He has a real knack for capturing introspective how-to lessons detailing the simplicity of living a good and productive life.

In homage to Dr. Covey’s amazing work, I’d like to narrow the scope, but offer lessons with a similar impact for database administrators – Expanding on the inobviously obvious to illuminate the good path to success.

Habit One - Multiplex and Mirror Everything!

Mirror, Mirror...

Multiplex and mirror all of your critical files – Is there a reason not to? Today’s SANs have gone a long way to provide for redundancy and reduce I/O contention, but they are definitely not an excuse to abandon this basic key to database survivability!

The SAN Trap: SANs are often used as a panacea for data availability. However, have you taken a close look at your SAN to determine how robust and survivable it really is?

  • How many LUNS are your files spread across?
  • What RAID level are you using and how many simultaneous disk failures will it take to make your files irretrievable? (Anything under 20% incurs quite a bit of risk).
  • Do you have redundant controllers?
  • Redundant switches?

Even the most survivable storage setup is still vulnerable to logical corruption, and the vastly more common, human error (“I just deleted all the .LOG files to save some space!”).

Conversely, for very slim installs, you may only have a single disk or LUN – While there is greatly increased risk when in such a situation, reality dictates that sometimes the circumstances are unavoidable. Until you can grow your storage footprint, multiplexing and mirroring (across directories) becomes even more critical as

Mirroring and multiplexing your control files, redo logs, archived redo logs, and RMAN backups will significantly increase the likelihood of a successful recovery, should the need arise (See Habit 5 – Preparation). The procedure is extremely easy, and the files generally take up very little space, if properly scaled and tuned to your needs.

Here are some best practices for you to tailor to your needs:

  • Control Files: Multiplex two to three times and mirror over two to three disks/LUNs/directories
  • Redo Logs: Three to four members per group with two to three groups spread across disks/LUNs/directories
  • Archived Redo Logs: Mandatory mirroring between at least 2 disks/LUNs/directories
  • RMAN Backup Files: Mirror between at least two disks/LUNsdirectories
  • SPFILE: Periodically create a PFILE from the SPFILE and archive it, along with your backups and control file snapshots

A database administrator worth their salt NEVER loses data, and the best way to maintain this is to avoid a position where data loss is likely. Mirroring and Multiplexing are one of our most effective tools to reduce risk.

Brian Fedorko

May 27

A planned installations always requires...  Plans!Designing the data structure

If there were a more crucial time for a Database Administrator to team with and guide the application developers, I can not think of one. Getting this first step as correct as possible will save rework ranging from an inordinate amount of time dedicated to tuning to total application overhaul. This translates into your company/client hemorrhaging thousands of hours/hundreds-of-thousands-of dollars of dollars of unnecessary spending… or saving that very same amount. This is what a Professional DBA brings to the table. But how do you know if you are doing it well?
You design the database for the data. It is ALWAYS about the data, and how the user interacts with the data. Requirements are a great place to start if they are well-written, but mapping out use cases with the developer and the user is simply the best way to go. By exhaustively examining all the use cases, your structure will practically write itself. A solid understanding of the use cases will tell you:

  • How transactional and dynamic your database will be
  • What data will be input, when, and how
  • Where relationships and data constraints need to be implemented
  • What data will be extracted and how it will be grouped
  • Where locking issues will manifest
  • What data may need special handling (HIPAA, SOX, DoD Sensitive, Privacy Act, etc.)

The use cases, combined with a bit of foresight and communications, you can determine if the data will need warehousing in the future, if the system will require inordinate scalability, and/or the necessity of alternate operational sites. Initially designing the data system for end-game use will help you evolve the system as it is developed, rather than bolting on solutions in an ad-hoc manner as the needs become critical.

Common Pitfalls to Avoid:

Over-Normalization: There is no shame in under-normalizing your database if you have a solid reason to skip some normalization opportunities. Commonly, you can improve performance and maintainability – And if your data will eventually be warehoused, it will need to be (sometimes greatly) denormalized. Being able to efficiently convert your transactional data storage structure into a warehoused structure, optimized for data mining and reporting truly requires a planned, engineered effort.

The Developer Mindset: An excellent developer with a focus on efficiency and optimization is careful to only create and use resources a long as is absolutely necessary. However, an excellent data structure must be extremely static. Creation and destruction of tables is not only a hallmark of suspect design, but also creates a host of security and auditing challenges.

Data Generation: Any data created for storage must be carefully and thoroughly scrutinized. Fields of created data, stored to increase application performance, can reduce the performance of the entire database. If this practice is prevalent enough, storage requirements can increase dramatically! I have seen very few instances where the data manipulation is not best handled during retrieval.

Incremental Primary Keys: Iterative ID fields (‘Auto-Number’) in transactional tables must be avoided! Not only does it compromise our goal of not creating or destroying stored data, but it wreaks havoc on any sort of multi-master, bi-directional replication (ex. Oracle Streams, Advanced Replication, etc.). For example, if two sites are being used to accept transactions, the chances are excellent that the sites will receive separate transactions at the same time. If both create their Primary Key from the last record, incremented by one, they will BOTH have the same ID and a collision will occur.

Sure, you could design logic to constantly monitor for this issue, and gain additional overhead. I’ve also seen the transactions staggered by ‘odds and evens’. But what happens when you add an additional site? Your scalability is inherently limited.

There are very few instances where a natural key cannot be drawn from existing data. Usually, a timestamp combined with 1 or 2 data fields (ex. PRODUCT_ID, LOCATION, SSN - if protected, etc.) will produce an excellent, unique key. In the very RARE cases that it is impossible to generate a unique natural key, the Universal/Global Unique Identifier (UUID/GUID) is a viable alternative. All major databases support the generation of this ID, based on Timestamp, MAC address, MD5 Hash, SHA-1 Hash, and/or Random numbers depending on the version used. Given that there are 3.4 × 10^38 combinations, it is unlikely that you’ll run out. Ever. Every major DBMS has a utility to generate a UUID/GUID - SYS_GUID() in Oracle, UUID() in MySQL, and NEWID() in TSQL. There are also implementations for creating the UUID/GUID in C, Ruby, PhP, Perl, Java, etc.

This is just a light touch of creating a solid, production-grade data structure, but it is a good start. We’ll have plenty of room to explore some additional facets and expand on some of the items mentioned in further articles. Always remember, a good DBA must synergize with the development team, bringing different mindsets with distinct goals together to provide a robust, efficient solution

Brian Fedorko