Shared Sequencers

**Ed prochak** · Jun 27 '08, 05:01 PM

Re: Shared Sequencers

nathan_burleson @yahoo.com (Nathan Burleson) wrote in message news:<fcb2e0b8. 0307311252.10b2 ef1a@posting.go ogle.com>...

Hello,
>
Are there any issues with sharing sequences across databases? I want
to be able to move data from Dev. to QA to Staging to Production and
back, but I need to keep sequences intact.
>
BTW: Using Oracle 8i
>
Thanks in advance for any help.
--NB

Dev->QA->Production and back?

If you want to risk hitting the production system I can imagine having
DB links to the production box where the real sequence is kept. But
then DEV and QA testing will not be realistic since access to the
sequence will be thru the like and thus slower.

Are you sure that's what you want?

In similar conditions (conversions), I've used assigned ranges. So
test conditions for both Dev. and QA are similar, and data can still
be merged into production. (you really want to take data from Dev into
prod.?? What kind of system needs that?)

((Why do these questions always sems to lack sufficient context?))

Maybe another idea: how is this data being moved between systems? You
could fix up the sequences during the moves up the ladder. This would
be incremental data so extra processing might not be too heavy.

Moving the data back down might involve taking full exports from prod
to QA and Dev. at least of those tables involved in the sequences.

I still don't like moving data that developers create into the
production DB.

Did any of these suggestions help?

Ed Prochak
Magicinterface, Ltd.
440-498-3700

**Ed prochak** · Jun 27 '08, 05:02 PM

Re: Shared Sequencers

nathan_burleson @yahoo.com (Nathan Burleson) wrote in message news:<fcb2e0b8. 0308041023.67f8 347@posting.goo gle.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

Shared Sequencers

Shared Sequencers

Comment

Comment