Re: Shared Sequencers

Ed,

Just wanted to thank you for all your help. This is the solution I suggested to the "powers that be."

It is tough being a developer with little internal support from a true DBA, but your suggestion really saved me.

Thanks again.
--Nathan

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0308050747.16685be4_at_posting.google.com>...
> nathan_burleson_at_yahoo.com (Nathan Burleson) wrote in message news:<fcb2e0b8.0308041023.67f8347_at_posting.google.com>...
> > Ed,
> >
> > Thanks for your help and sorry for the lack of information. I guess I
> > was in a hurry that day, and after looking back the question was very
> > vague.
> >
> > To clarify:
> >
> > Our "internal users" (customer admins.) are creating data on the
> > Staging DB, because we don't want them working directly on Production.
> > When this data is correct it is "published" to the Production DB.
>
> Sounds Good. You didn't mention a staging DB before.
>
> >
> > There may be cases when there is a code change and we want to pull
> > "live" data back for testing against the new code on Dev.
>
> Which means you import production data into DEV. You have all the
> current data by definition. But you NEVER go the other way right?
> RIGHT?
>
> >
> > Also, QA may test new code by creating data for an actual customer. If
> > QA needs to test new code why not have them create a valid dataset. In
> > this case we want to move data from QA into Production.
>
> WHY? You just broke you process of loading into a staging DB. Is
> creating the customer information really that hard that you'll risk
> loading test data from QA into production?
>
> >
> > There is also some data that is created by the end user of the
> > application on Production. This data may need to be moved back to dev.
> > to test against new code or to track problems customers may be having.
> >
>
> Prod to DEV is never a problem in moving data other than time and disc
> space.
>
> > Since there are sequences used on many of the tables and the data is
> > highly interrelated (i.e., for one object the data is stored across 12
> > tables) we basically need to be able to move data anywhere in the
> > system hierarchy without fear of breaking a constraint.
>
> If you move either a complete export from Production to anywhere else,
> then no problem can possibly exist.
>
> If you "publish" complete sets of data from staging into production
> (or some of the other instances),
> then, again, you should never have a constraint problem.
>
> If you try to bring in subsets of data from production into QA or DEV,
> then you may have violations depending on how you extract the data. I
> would think that if the "publish" process used from staging to PROD is
> robust enough for production, it should be robust enough for testing.
> You just turn the programs around to extract FROM Production INTO your
> chosen destination DB.
>
> >
> > I hope this makes more sense rather than adding to the confusion.
> >
> > Thanks again.
> > --Nathan
> >
> []
>
> Well, then I think the assigned range suggestion might be the easiest
> and most realistic solution. Make sure Production is always the
> highest Sequence numbers. You then split how ever much you need among
> the other instances. For example, assuming 9 digit Sequences
>
> start PROD at 300000000 (eight zeroes there)
> start Staging at 200000000
> start QA at 100000000
> start DEV at 1000 (only three zeroes there)
>
> There may be times when you want DEV to miror other systems almost
> exactly, so the sequences on DEV can be set and reset depending on
> what work is being done.
>
> So given data migrates under these rules (I made some assumptions from
> your comments). I use the term migrates basically as refering to ANY
> method that moves data (table to table) between the instances, from
> full export/import to DB links to custom programs. I assum your
> publishing process moves data based on the relations in the data, not
> simply based on its occupying certain tables.
>
> 1 DEV
> Nothing ever migrates OUT of DEV
> DEV can be loaded, or accept published data, from any other instance
> DEV is the universal sink, data goes in but it don't go out
>
> 2 QA
> Nothing ever migrates OUT of QA, QA may publish to Staging or DEV
> QA can be loaded, or accept published data, from Staging or PROD
> instances
>
> 3 Staging
> Staging publishes data to PROD or QA or DEV, migrates to QA or DEV
> Staging can be loaded, or accept published data, from the PROD
> instance or data from client connections (customer admins.)
> Staging can accept published data from QA
>
> 4 PROD
> PROD can migrate to any other instance
> PROD can be loaded from backup, and accepts published data ONLY from
> Staging or data from client connections (customers).
> PROD is the universal data source
>
> The SEQUENCES between each instance do not have to migrate, only the
> data. A set of data relating a given customer's information that meets
> the relational constraints in one of those instances above, especially
> the Staging instance, will also meet all the constraints in the PROD
> instance. Your environment (other than wanting to move data from QA to
> publishing) is normal.
>
> Advantages of this is that you can tell in many instances, where the
> data originated based on its SEQUENCE number value. (make deleting DEV
> data out of PROD a little easier!!)
>
> And tell the QA folks to stick to QA work. They are supposed to break
> things, not enter data for production. (I know, not your call.)
>
> So set your Sequences and start running.
>
> HTH
> ed
Received on Mon Aug 11 2003 - 21:03:00 CEST