Database administration worst practices
Download
1 / 49

- PowerPoint PPT Presentation


  • 220 Views
  • 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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

0


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

1


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

2


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

3


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

4


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

5


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

6


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

7


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

8


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

9


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...




ad