Database administration worst practices
1 / 49

- PowerPoint PPT Presentation

  • Updated On :

Database Administration Worst Practices. A Catalog of Wince Inducing Practices To Be Avoided For Progress OpenEdge , Or Indeed Any, Database. A Few Words About The Speaker. Tom Bascom , Roaming DBA & Progress User since 1987 President, DBAppraise , LLC Remote Database Management Service.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about '' - Samuel

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Database administration worst practices l.jpg

Database AdministrationWorst Practices

A Catalog of Wince Inducing Practices To Be Avoided For Progress OpenEdge, Or Indeed Any, Database.

A few words about the speaker l.jpg
A Few Words About The Speaker

  • Tom Bascom, Roaming DBA & Progress User since 1987

  • President, DBAppraise, LLC

    • Remote Database Management Service.

    • Simplifying the Job of Managing and Monitoring The World’s Best Business Applications.

    • [email protected]

  • VP, White Star Software, LLC

    • Expert Consulting Services related to all aspects of Progress and OpenEdge.

    • [email protected]

Slide3 l.jpg


We won t need that oops l.jpg
We Won’t Need That!“Oops…”

  • Do not EVER destroy your production database.

  • Even if it is Damaged.

  • Never Trust a Backup.

  • Or a Dump & Load.

  • Or any other destructiveactivity.

We won t need that belt and braces l.jpg
We Won’t Need That!“Belt and braces…”

  • NEVER restore on top of your production database.

  • Always restore to a fresh location.

  • Always build a new database.

  • Always defer production db retirement until its replacement has been verified and put into use. It may be your last resort.

Slide6 l.jpg


Mirror mirror what could go wrong l.jpg
Mirror, Mirror“What Could Go Wrong?”

  • The firmware might fail.

  • Or the SAN could just fall through the floor…

  • rm –rf

  • And the ever popular:FOR EACH customer: DELETE customer. END.

  • Mirrors faithfully produce 2 copies of everything.

  • Even when you don’t want them too.

Mirror mirror think outside the box l.jpg
Mirror, Mirror“Think outside the box!”

  • All databases that have business value must have after-imaging enabled.

  • After-image logs must be continuously archived to a safe location. “Safe” usually means a different time-zone.

  • Ideally, after-image logs should be continuously rolled forward against a backup database to prove that both the backup and the ai mechanism are working.

Slide9 l.jpg


Faith based backups our backups are good why worry l.jpg
Faith-Based Backups“Our backups are good, why worry?”

  • Backups aren’t just for last night.

  • Tapes degrade over time.

  • Backup technologies go obsolete quickly.

  • Old backups can be just as dangerous as no backups (think lawsuits and “e-discovery”, or tapes falling off trucks).

  • It used to fit on the tape…

  • The only known-good backup is onewhich has been restored and verified.

  • Or destroyed.

Faith based backups trust but verify l.jpg
Faith-Based Backups“Trust. But Verify.”

  • Test for success, failure and lack of success.

  • Log everything.

  • Have a backup to your backup:

    • Backup to Disk with probkup.

    • Backup to Tape with OS tools.

    • Continuously backup and verify after-image logs.

  • Verify backups:

    • Restore your backup.

    • Roll forward after-image logs.

  • Test Restore & Recovery Procedures (at least) Annually.

  • Certify the destruction of old backups.

Slide12 l.jpg


Great expectations go ahead it will work l.jpg
Great Expectations“Go ahead, it will work.”

  • It might also take 3 days to complete.

  • Or lock a billion or so records.

  • Or consume all of the machine’s resources.

  • Or break code.

  • Or break 3rd party systems…

  • Silver bullets are usually made of tin-foil.

Great expectations curb your enthusiasm l.jpg
Great Expectations“Curb your enthusiasm.”

  • Old DBAs are paranoid DBAs.

  • New DBAs tend to be fearless -- learning from someone else's experience can help instill some much needed paranoia.

  • Create a “sandbox” test environment that closely mimics the production system.

  • Require written plans, with a backout plan, and tested, repeatable scripts for everything.

  • Log everything.

  • Practice, practice, practice.

Slide15 l.jpg


The cart before the horse it s slow let s go buy something l.jpg
The Cart Before the Horse“It’s slow? Let’s go buy something!”

  • We’ve purchased some new hardware!

  • How should we configure Progress to run on it?

  • We have licenses for X. What do we do with them?

The cart before the horse not so fast l.jpg
The Cart Before the Horse“Not so fast…”

  • Determine the best way to invest your money.

  • Consult with experts before you make expensive decisions…

  • Plan first, then spend your money.

Slide18 l.jpg


Raid 5 it won t be a problem l.jpg
RAID 5“It won’t be a problem…”

  • Great performance when there is no load.

  • And when there are no disk failures.

  • And if your database is roughly the same size as the SAN cache.

  • All of the RAM that you can use where it will do you the least amount of good.

  • All of the performance of a single disk with none of the cost savings.

How to saturate a ram cache just say no l.jpg
How to Saturate a RAM Cache“Just Say No!”

  • fillTime = cacheSize / (requestRate – serviceRate)

  • Typical Production DB Example (4k db blocks):

    • 4GB / ( 200 io/sec – 800 io/sec ) = cache doesn’t fill!

  • Heavy Update Production DB Example:

    • 4GB / ( 1200 io/sec – 800 io/sec ) = 2621 seconds (RAID10)

      • 4GB / ( 1200 io/sec – 200 io/sec ) = 1049 seconds (RAID5)

  • Maintenance Example (online backup):

    • 4GB / ( 5000 io/sec – 3200 io/sec ) = 583 seconds (RAID10)

    • 4GB / ( 5000 io/sec – 200 io/sec ) = 218 seconds (RAID5)

Slide21 l.jpg


Laissez fire dba the users will let us know when there is a problem l.jpg
Laissez-Fire DBA“The users will let us know when there is a problem”

Laissez fire dba be prepared l.jpg
Laissez-Fire DBA“Be prepared.”

  • Familiarize yourself with baseline performance so that you will recognize exceptions when they occur.

  • Collect historical statistics to facilitate both forward planning (trending) and forensic performance analysis.

  • Implement availability and performance monitoring systems so that issues are identified and resolved before they cause outages.

Slide25 l.jpg


Kim s game we ll remember l.jpg
Kim’s Game“We’ll remember”

  • Many DBA activities are only rarely executed.

  • Under extreme pressure.

  • While working with hostile and uncooperative 3rd parties. And users.

  • At awkward times of the night.

  • By the backup DBA.

Kim s game put it in writing l.jpg
Kim’s Game“Put it in writing!”

  • Maintain a comprehensive documentation library and activity diary, including a significant level of rationale, syntax, and workflow detail.

  • Use collaboration tools (a Wiki) so that these documents are easily discovered, readily searchable in an emergency and living.

  • Enforce the discipline of documentation and check it periodically:

    • When was this object created, by whom, and with what script?

    • What tasks were performed on a particular day?

    • What tasks need to be performed on some schedule?

Slide28 l.jpg


The blame game it s the developer s fault that query is in production l.jpg
The Blame Game“It’s the developer’s fault that query is in production!”

The blame game a dba is part of a team l.jpg
The Blame Game“A DBA is part of a team!”

  • Cultivate a team attitude by structuring continuous DBA involvement in every project rather than just at project milestones.

  • Make developer and customer support a clear part of the job description linked to performance evaluations.

  • Make sure that developers and testers have access to a full size copy of the db.

  • Avoid post-release software issues by proactively working with developers and testers to ensure that all production software is stable and high-performance.

Slide31 l.jpg


Techno bust upgrade we don t need no steenkin upgrade l.jpg
Techno-Bust“Upgrade? We don’t need no steenkin upgrade!”

  • Version 9 is:

    • Ancient. It was released in 1999.

    • Obsolete. Lacking Type 2 Areas, DateTime, Multi-Core support, Security, SAX, OO, .NET, Eclipse, Pro Datasets, LOBs, 64 bit goodness, Diagnostics and a few hundred other major enhancements.

    • Unsupported. 9.1E04 is the very last release of v9 ever. It is 5 years old. There will never be another update, bug fix or enhancement to v9.

Techno bust ii upgrade we don t need no steenkin upgrade l.jpg
Techno-Bust II“Upgrade? We don’t need no steenkin upgrade!”

  • Increased Risk:

    • Exposure to unfixable bugs.

    • Exposure to security breaches.

  • Decreased Productivity:

    • Foregone performance enhancements.

    • Unrealized improvements in functionality.

    • Unresponsive development.

  • Increased Cost:

    • Using labor to work around all of the above.

    • Eventual re-work when you finally do upgrade.

    • Major license costs if maintenance has lapsed.

Techno bust an ounce of prevention is worth a pound of cure l.jpg
Techno-Bust“An ounce of prevention is worth a pound of cure.”

  • Apply patches routinely.

  • Keep your licenses and maintenance up to date.

  • Escalate Vendor FUD.

  • Take advantage of new features when it is appropriate.

  • Don't be afraid to acquire the right technology.

Techno lust there s an app for that l.jpg
Techno-Lust“There’s an App for that!”

  • Things would be so much betterif only we had the latest gizmo!

  • New isn’t always better.

  • Or cost-effective.

  • Specious complexity multipliestrouble in every direction.

  • Not all that long ago enormous enterprises were run on servers with the capacity of your BlackBerry.

Techno lust look before you leap l.jpg
Techno-Lust“Look before you leap…”

  • Don’t blindly upgrade your hardware infrastructure without first considering tuning opportunities.

  • Understand the ongoing maintenance commitment and costs of new systems and features before you put them into production.

Eye candy hello sailor l.jpg
Eye Candy“Hello Sailor!”

  • Watch out for DBA support software that presents friendly GUI interfaces for difficult tasks:

    • Inhibits learning.

    • Hides risk.

    • Difficult to automate.

    • Enables irreversible damage by point-and-click.

  • False sense of security.

Eye candy it takes more than a pretty face l.jpg
Eye Candy“It takes more than a pretty face.”

  • Good DBA tools help you to:

    • Alert & Inform

    • Log & Analyze

    • Automate

    • Manage

  • Graphics should support and reinforce data.

  • DBA Tools should be meaningful and insightful without being noisy – not just a metrics browser.

The lone ranger i know what i m doing and i don t need any help l.jpg
The Lone Ranger“I know what I’m doing and I don’t need any help!”

  • Database Administration is complex. Everyone needs a sanity check.

  • Even the most senior DBAs can'tpossibly know every last detail.

  • No single person can match theexpertise and experience ofeven a relatively small group.

  • And then there is that “bus”thing!

The lone ranger even the lone ranger had tonto l.jpg
The Lone Ranger“Even the Lone Ranger had Tonto…”

  • Foster a culture where it is acceptable for DBAs to admit they don't know the answer and to ask for help.

  • Provide a safety net of tech resources such as outside experts and consultants on call.

  • Participate in PUGs, PSDN, PEG and ProgressTalk.

Hero worship gus said it it must be true l.jpg
Hero Worship“Gus said it, it must be true!”

  • Gus, Dan, Adam et al are great but they are mortal 

  • Context is everything.

  • Your hero probably isn’t workingon (or writing about) your system.

  • The situation may have changedconsiderably over time.

Hero worship nobody is perfect l.jpg
Hero Worship“Nobody is perfect.”

  • It is more important to understand the reasoning than it is to blindly implement a rule of thumb.

  • Take everything with a grain of salt – no matter the source.

  • Test, test, test...