Transcript Progress OpenEdge DBA Worst Practices

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; Progress 4gl coder & roaming
DBA since 1987
• President, DBAppraise, LLC
– Remote database management service for OpenEdge.
– 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]
2
0
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 destructive
activity.
We Won’t Need That!
“Belt and braces…”
• Always restore to a fresh location.
• NEVER restore on top of your production
database.
• 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.
1
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!”
• 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.
• After-image logs should be continuously rolled
forward against a backup database to prove that
both the backup and the ai mechanism are
working.
2
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 one
which has been restored.
• And verified.
• Or destroyed.
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.
3
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.”
• 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.
4
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…”
• Determine the best way to invest your money.
• Consult with experts before you make
expensive decisions…
• Plan first, then spend your money.
5
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 – cleverly
placed 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!”
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)
BAARF.com - Enough is Enough
• Battle Against Any Raid (Free, Four, Five or Fix)
•
•
•
•
http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt
http://www.facebook.com/pages/BAARF
http://gurucollege.net/rants/baarf-or-why-raid5-isnt-safe/
http://www.unitrends.com/documents/whitepapers/unitrendswp-russian-roulette-and-raid-5.pdf
6
Laissez-Fire DBA
“The users will let us know when there is a problem”
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.
Shameless Plug
7
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!”
• 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?
8
The Blame Game
“It’s the developer’s fault that query is in production!”
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.
9
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, MultiCore 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!”
• 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.”
• 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.
10
Techno-Lust
“Keeping up with the Jones’ …”
• Things would be so much better
if only we had the latest gizmo.
• New isn’t always better.
• Or cost-effective.
• Specious complexity multiplies
trouble 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…”
• 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.
11
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.”
• 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.
Shameless Plug
12
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't
possibly know every last detail.
• No single person can match the
expertise and experience of
even a relatively small group.
• And then there is that “bus”
thing!
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.
13
Hero Worship
“Gus said it, it must be true!”
• Gus, Dan, Paul, Adam et al are great but they
are mortal 
• Context is everything.
• Your hero probably isn’t working
on (or writing about) your system.
• The situation may have changed
considerably over time.
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...
Questions?
Thank-you!