Gary Affonso wrote:
>On 5/29/04 10:47 PM, "m christensen" <dfs@xmission.com> wrote:
>
>
>
>>First Off I'll ASSUME y'all are not falling for the Micro$oft pitfall of
>>bastardizing an
>>official 30 year old standard definition, i.e."SQL" into a closed source
>>proprietary brand name for
>>Microsoft SQL Server.
>>
>>
>
>Good lord, man. What do you think I am? Of course I know the difference.
>Does anybody on this list really not?
>
>
>
Well, that was a joke. mostly...
Previous posts used the term "Sproc" while the concept of Database Side
Stored Procedures is rather
common with high-end RDBMS systems. the term SPROC is usually used in
reference to MS SQL Server.
If there is any doubt about the misuse/abuse (IMHO) of the term SQL,
reply to job postings simply listing
SQL as a main requirement. With 20 years experience in
Oracle/Informix/DB2/Sybase. Odds are you'll get a
bunch of "You are clearly clueless and UNQUALIFIED We obviously mean
SQL Server. Sheesh we SAID 'SQL'"
>>The approach taken will depend on the target of your development effort.
>>The 2 general targets I see are
>>a shipped product and incremental in-house system modification.
>>
>>
>
>We are an incremental, in-house project so I'll respond to that portion of
>your email.
>
>
>
>>A. Actual backup copies of the Production database are restored as a
>>starting point for databased used in part D.
>>B. No Diff.
>>C. All SQL EVER run against The Production database are checked in as
>>patches. Both DDL & DML are included.
>>
>>
>
>So to rebuild the database schema in this scenario, you'd have to run the
>initial "create" script and then run the series of alter scripts that
>brought everything "current"? Is that right?
>
>
>
No.
In this situation you would NEVER rebuild the database schema from your
code.
As I said in point A.You will only Restore it to a point-in-time via.
the recovery tools specific to the
RDBMS in question.
>> In other words, I don't care if it's 'Just a data change' or major
>>table schema change.
>> It's a script, it's checked in, and it has been tested or it
>>doesn't run in production, Period.
>>
>>
>
>I'm confused by this. You say above that you script (and revision-control)
>all changes to the db including those for data. But then you say:
>
>
>
>>It's hard enough to track system schema changes as shown here and in
>>previous posts, but it is nearly IMPOSSIBLE to
>>track production database data changes from a live system, after all the
>>application is constantly changing data by definition.
>>
>>
>
>Can you clarify?
>
>
>
Those scripts are checked in for several reasons.
--->None of those reasons are to rebuild the database from scratch.<---
The Reasons to archive them in my experience are to track who is doing
what to MY database.
Interactive system modification leaves no audit trail. Some bonehead
developer dropping a table using a tool like
TOAD or Via an interactive database login would NEVER just Drop a table,
or at least never admit to it.
Boy, do I have war stories to back-up this personal requirement but I'll
spare y'all.
When the system suddenly fails after a patch They are VERY valuble for
figuring out what went wrong and
what actions to take to fiix the problem
>>D. ALL Modern Decent Production level RDBMS Systems have a "Point in
>>Time" recovery capability. IMHO.
>>
>>
>
>We are, of course, doing incremental backups. My desire for a DDL "script
>history" isn't just for their recovery value.
>
>A DDL script history also lets us:
>
>1) Easily setup duplicate test/development systems or build from scratch if
>we need/want to.
>
>
>
As I stated, I only build test and development systems from Production
backups.
I can't stress enough the DATA in the database is as critical as the
schema itself.
Without that data, development and testing is done in a vacum, all too
often I've had patches
kill the PRODUCTION system because they were 'tested" against a customer
table with 200 dummy
records.Poorly constructed update statements or logic can choke and run
out of memory or take
several days to run when they hit REAL production data or relationships
the developer or builder of
dummy data didn't anticipate.
"Really, a customer can have multiple addresses, or even NONE ?!?!?. I
didn't know that."
Now I have a dead/hung or very SLOW production system to recover and as
the DBA, I'm the one the
users blame.
>2) They provide clear and detailed documentation on the evolution of the
>database schema
>
>
>
My current system is 16 months old. I have 983 incremental changes. I
HAVE all those scripts.
Using them alone to deduce a general picture of the evolution of the
database schema is of no value to me.
Those 5-6,000 script snippets may as well be printed and piled in the
middle of the floor. Without some other
method to relate those scripts to the database itself that are worthless.
Automated tools that run on a schedule and document schema changes to an
archive for later reference
is handy, As are good design tools like ERwin or Oracle Designer, if
kept current.
>3) They let us correlate this evolution to issues in our issue tracker.
>
>
>
Yes, they are critical to deduce what was done and why. All versions of
code in my repository are tied to
Specific SCRs in out problem tracking and assignment system.
>4) They give us a good starting point for building scripts that implement
>the database schema on other servers (something we're likely to be faced
>with this summer).
>
>
>
I have tools that will generate ALL the SQL required to build an emply
copy of a database based on
extracting schema info from a running system. That is how I build
databases in this case.
>>It's hard enough to track system schema changes as shown here and in
>>previous posts, but it is nearly IMPOSSIBLE to
>>track production database data changes from a live system, after all the
>>application is constantly changing data by definition.
>>I think the only real approach is to track overt system changes from the
>>developers for other purposes but to admit the
>>only way to get a copy of a database at a specific point is thru system
>>backups and point-in-time recovery capabilities
>>of the RDBMS itself.
>>
>>
>
>Thanks for your detailed reply.
>
>I don't think all the aspects of your system apply to us (we are not keeping
>configuration data in the DB) but it's helpful to see that I'm not missing
>"easy" solution. Misery loves company, I guess.
>
>
>
I don't know what you mean by "keeping configuration data in the DB".
Marc
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Received on Mon May 31 00:02:09 2004