The DBA Checklist Adam Backman President – White Star Software Session 139

Words To Live By Your greatest strength is your #1 blind spot - Tony Mayo 2

The Invisible DBA Goals: Keep the database running – No credit Keep the database running predictably – No credit Protect the data – Off to tape - off site – No credit When these things do not happen, DBA pays Being invisible is the real goal A day with no calls is a good day 3

Responsibilities Of The DBA Protect the data Data loss prevention Control the data Security (Not application security) OS Security (May be shared responsibility with SA) Physical Security (Machine, Backup Tapes, …) Make the data available Predictable down time Predictable performance 4

Morning Rituals Did the backups run? Log what got backed up Success or Failures Log timings Be sure to make your nightly window Did the nightly jobs run? Success or Failure and Timings Did the production schedule run? i.e., any gatekeeper job that must run prior to the day Post nightly processing and pre-daily 5

About Backups Backups are the most important part of a DBAs job No credit for doing them Youre only as good as your last tested backup Label Backups: Date and time Who did the backup Command to restore or reference to where to find this information in the run manual. Always disable writes to the media 6

More About Backups Tape Rotation – What do you keep? 1 week of daily backups 5 weeks of weekly backups 13 months of monthly backups N years of annual backups Varies by geography and local laws US is 7 years 7

Throughout The Day Performance statistics Remember to zero statistics out every night Easier comparison from day to day Collect and log statistics throughout the day Table/Index Statistics Buffer Hit Rate/Commit rate Operating System Statistics Know the pulse of your system Heavy hitters Canary (the person who complains first) Performance of your top 5-10 most important activities 8

Tools For Throughout The Day ProTop and Protop for DotNET Free download (DBAppraise.com) Detail information Excellent Performance Debugging sar/vmstat – O/S information Part of the O/S Use 5 minute samples Non-Free Tools OpenEdge Management ProMonitor 9

Evening Routine Before the stampede out the door Check that the nightly schedule is ready to run Final check of your backup media Correct media Write enabled Check system for misbehaving processes Runaways Zombies 10

Weekly Tasks Full System Backup Virtual Machine Snapshot Database Analysis Look for Index utilization rate Thousands of blocks (then move down to 500 or more) Less than 70% utilization (then move to

Index Compaction Index Compacts based on last weeks DB analysis Usually done off hours or slow hours For 24 hour shops spread throughout the week Monitor before image growth due to a long running transaction (_Trans) Have a good understanding of the amount of after image growth AI file(s) can grow equal to the number of blocks traversed in the index compact Note: The index compact will traverse (n +1) * number of blocks in the index. n = Index block levels 12

Monthly Tasks Complete ALL weekly tasks Before month end Index compacts to help with month end performance Make sure logging includes timings so month to month timings can be compared Gather performance statistics Full system image for audit, post closing of the books 13

Yearly Tasks Do the weekly tasks including the backup Do the monthly tasks including the system image Do a pre-yearend database backup Do year end processing Get post yearend image of the system for audit Why so many backups? When was the last time you tested yearend? Backup media is cheap You dont want to get audited by the tax man and not have your financials properly recoverable 14

Data Archiving (periodic task) Cold Archiving Archive records to ASCII Archive the ASCII dump to durable media Delete the archived records from production Warm Archiving Archive records to ASCII Delete records Have application option to read ASCII records on demand Hot Archiving Archive records to archive database Delete records from production 15

Data Archiving Restrain the business to "do without" old detail data in production Overcome this by Warm/Hot Archiving Creation of rollup records Take detail records Roll them up into weekly/monthly/yearly records Report of roll up records to reduce I/O Delete the detail records or archive them upon rollup 16

Ultimate Yearend Archive Dump everything to ASCII Keep schema and a DB backup Keep the application By having everything in ASCII you will not need to reassemble a system you had 5 years ago. Place everything on durable media (Archival Gold – The 100 year disc) In 100 years you will be dead so it will be someone else's problem 17

How To: Annual ASCII Dump After your final closure of the books Backup the database or use the one you already took Restore this backup to a second system Start the ASCII dump process Make sure you have large files enabled OpenEdge File systems Dump to ASCII, do not use binary dump as you are dumping into a proprietary format. Make sure to dump you data definitions – possibly a data model diagram to go with the data 18

Points To Remember Create routine Daily Weekly Monthly Create check lists or sign off lists Create log files with timings of each step of your routine to insure time windows are met Know your system so you can easily answer the What changed? question 19

The DBA Checklist Adam Backman President – White Star Software Session 139