|
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!
_ _ _ _
(_|| | |(_|><
_|
| [reply] |
|
|
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 ;-)
| [reply] |
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.
| [reply]
[d/l] |
|
|
| [reply] |
|
|
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!
| [reply] |
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
| [reply] |
|
|
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!
| [reply] |
|
|
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
| [reply] |
|
|
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... | [reply] |
|
|
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!
| [reply] |
Re: dbi cookbook for database administration
by valdez (Monsignor) on Nov 07, 2002 at 18:21 UTC
|
| [reply] |
Re: dbi cookbook for database administration
by busunsl (Vicar) on Jan 14, 2003 at 07:13 UTC
|
| [reply] |
|
|
| [reply] |
