Re: Shared Sequencers

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 Tue Aug 05 2003 - 17:47:45 CEST