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. – Simplifying the Job of Managing and Monitoring The World’s Best Business Applications. – email@example.com • VP, White Star Software, LLC – Expert Consulting Services related to all aspects of Progress and OpenEdge. – firstname.lastname@example.org 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…” • 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. 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. • 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. 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 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) 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, 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!” • 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 “There’s an App for that!” • 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, 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!