Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

dbi cookbook for database administration

by busunsl (Vicar)
on Nov 07, 2002 at 13:19 UTC ( #211060=perlmeditation: print w/ replies, xml ) Need Help??

Hi Folks!

I start writing a DBI Cookbook for Database Administration (focus on Sybase).
Big title but not much contents at the moment.

The stuff I have so far covers:

  • monitoring server (is it alive?)
  • monitoring database and transaction log
  • monitoring performance (cpu, io, hot objects)
  • maintenance tasks (dbcc, indexes, partitions)
  • Replication Server life, threads, diskspace
  • working with repdefs and subscriptions
  • monitoring and cycling errorlogs
  • data and schema movement
  • disaster recovery
I'm looking for ideas, scripts, snippets and other stuff you use or need in your daily admin work.

Work has just started and will be available soon on my web site.

Comment on dbi cookbook for database administration
Re: dbi cookbook for database administration
by gmax (Abbot) on Nov 07, 2002 at 15:01 UTC
    A few random ideas from my own experience:
    • Importing from raw data or other databases. Exporting to data warehouses.
    • Cleaning legacy data (Especially unnormalized data. See some inspirational material at Database normalization the easier way. Also, columns with multiple values, such as name and address in one field).
    • Using DBI with cron jobs. Security implications related to the cron user grants. How to test for a cron job with DBI (it works on the command line but fails at 2:00AM).
    • On the same vein, using the DBI in CGI or mod_perl scripts can lead to problems related to user authorizations. Many people here in the Monastery ask why their script runs fine interactively and fails when Apache takes over. (see Protecting your DBI user/password in scripts?)
    • I guess that data and schema movement also implies finding differences between databases and synchronizing them (see DBI -- Synchronize table diffs?).
    • It might be OT, but you may find some ideas from the MySQL Cookbook table of contents.
    Good luck for your enterprise!
     _  _ _  _  
    (_|| | |(_|><
     _|   
    
      Importing from raw data or other databases. Exporting to data warehouses.

      That goes into data and structure movement

      Using DBI with cron jobs.

      Along with using the DBI in CGI is a good idea, it will go to the new miscellaneous section.

      finding differences between databases and synchronizing them

      Yes, will be in data and structure movement

      I will have a look at the MySQL Cookbook.

      Many thanks! You made the first place in the contributors list ;-)

Re: dbi cookbook for database administration
by mpeppler (Vicar) on Nov 07, 2002 at 16:18 UTC
    I posted a space checking script here - see this node.

    I would check out Rob Vershoor's web site. He has a lot of information that you could probably integrate to your scripts.

    Apart from that I think your list looks pretty good - I guess that monitoring databases and log also means running the backups, right?

    Michael

      I posted a space checking script here

      Great, I'll have a look. I suppose it handles mixing of data and log right.

      I know Rob and his site, it is a great source of information.

      I guess that monitoring databases and log also means running the backups, right?

      I will put backups in the disaster recovery section.
      Automatic backups of the transaction log should go into the thresholdaction procedure. I'll think about that.

      Many thanks!

        Automatic backups of the transaction log should go into the thresholdaction procedure.
        I guess it's a matter of taste - but I prefer to have manual tran dumps run every X minutes/hours rather than letting the thresholdaction proc handle it - this gives me a better defined recovery window when handling a site without a warm standby.

        Michael

Re: dbi cookbook for database administration
by VSarkiss (Monsignor) on Nov 07, 2002 at 16:45 UTC

    I have some scripts and modules that I'd be willing to contribute, but I need time to re-write them from scratch (I originally wrote them for a client, so they own it now). I've wanted to do that for a while, so this is good motivation.

    What I would have:

    • Script to invert $SYBASE/interfaces so you can see if you have something double-mapped.
    • Scripts to compare tables, columns, indexes, views in two databases, then either report them or generate "candidate" SQL to resolve differences.
    • A fairly plain "schema reporter".
    I may have a couple of other goodies laying around, if you're willing to wait a few weeks.

      A fairly plain "schema reporter"
      Are you aware of the dbschema.pl script that I first wrote many years ago, and that is currently maintained by David Owen (see www.midsomer.org). It uses the Sybase::DBlib module, but it generates a nearly perfect schema from the database (including users, logins, devices, etc).

      Other reference sites for Sybase DBA information (which includes monitoring) include Todd Boss' site (www.bossconsulting.com and Ed Barlow's www.edbarlow.com (although I haven't really looked at his perl code...)

      Michael

      Script to invert $SYBASE/interfaces so you can see if you have something double-mapped.

      Cool, would go into the miscellaneous section.

      Scripts to compare tables, columns, indexes, views in two databases, then either report them or generate "candidate" SQL to resolve differences.

      That would be great.

      A fairly plain "schema reporter".

      If plain means it is only covering the basic structure, I'd prefer dbschema.pl as Michael suggests.
      But I'd like to see it nevertheless.

      if you're willing to wait a few weeks

      I just started having ideas and some pieces of code. I'll be glad to get some more pieces in a few weeks, in the meantime I can put together what I have so far.

      Many thanks!

Re: dbi cookbook for database administration
by valdez (Monsignor) on Nov 07, 2002 at 18:21 UTC
Re: dbi cookbook for database administration
by jplindstrom (Monsignor) on Nov 07, 2002 at 20:14 UTC
    The local King of the Replication Server says:

    • Repserver queuesize and alert thresholds
    Let me add a few things that I possibly may contribute:
    • select2insert -- Create insert statements from a table.
    • Dependency mapping using GraphViz -- Visualize dependencies between SPs calling each other and selects, updates etc. on tables.

    /J

    Update: Please, please let me know how I managed to get a negative reputation on this node. I'm just so fascinated...

      Repserver queuesize and alert thresholds

      That's in RepServer Monitoring.

      select2insert

      Yes! I need that!

      Dependency mapping using GraphViz

      Sounds great, please contribute!

      Update: Please, please let me know how I managed to get a negative reputation on this node. I'm just so fascinated...

      Probably some troll, it has changes by now.

      Many thanks!

Re: dbi cookbook for database administration
by busunsl (Vicar) on Jan 14, 2003 at 07:13 UTC
    I put together the first twenty recipes.
    They probably need a lot more explanation, but should be usable as they are.

    Please comment on these and send more ideas and code ;-)

      Good start!

      I'll contribute a "generate replication definitions and subscriptions from an existing database" script in the next few days (been doing a fairly complex migration from WinNT/11.9.2 to linux/12.5.x using rep server)...

      Michael

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://211060]
Approved by broquaint
Front-paged by gmax
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (15)
As of 2014-08-27 20:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (252 votes), past polls