Re: Repl Topology
From: Mario Splivalo (majk_at_fly.srk.fer.hr)
Date: 03/09/04
- Next message: Tina Smith: "Re: Repl Topology"
- Previous message: Paul Ibison: "RE: Identity full SQL 2000 SP3 bug ?"
- In reply to: Tina Smith: "Repl Topology"
- Next in thread: Tina Smith: "Re: Repl Topology"
- Reply: Tina Smith: "Re: Repl Topology"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 9 Mar 2004 20:26:00 +0000 (UTC)
On 2004-03-09, Tina Smith <tb.smith@earthlink.net> wrote:
> Identity Columns
> -----------------
> I plan to implement identity ranges myself, independent of replication since
> all my reading tells me there are issues with letting sql server handling
> this for me. Each identity column will have the "NOT FOR REPLICATION"
> option set. I haven't totally figured out how to come up with a unique
> seed methodology for a large number of subscribers we'll have.
You said that each of your subscribers will have an unique id. You can use
that ID for creating the identity values. You can (and you should) create a
stored procedure that would return the next available identity value, and
prepend that value (or append it) to the siteID. You don't need an NOT FOR
REPLICATION clause when creating the tables.
Create the table where you will store taken seed values. The column for the
seed value should be the primary key, or at least have the UNIQIE index on
it.
First, youd do the SELECT MAX() to see what is the highest value already
taken, than inncrese that value by the increment (wich can be stored in
separate table containing the meta-data), and then try to insert that value
into the seed table. If you get an error caused by the constraint (primary
key or UNIQUE index), it means that some other user was faster. Repeat the
SELECT MAX() step until you can INSERT that value to the table. That way
you'll ensure no two (or more) concurent users get the same seed value.
After that just prepend (or append) the siteID, and that's it. The table
containing used seed should not be contained in the replication.
If your siteIDs are, for instance, like this: ST1, ST2, ST3, your identity
values would become:
ST100001, ST100002, ST100003, ST100004, ... for site ST1
ST200001, ST200002, ST200003, ST200004, ... for site ST2
and so on.
You can safely declare the identity column in your data tables as UNIQE
since no duplicates would get created by the system.
Another thing about NOT FOR REPLICATION clause mentioned earlier. If you
have master/detail pairs of tables, you'd normaly have foreign key on the ID
column in detail table pointing to the ID columnt in the master table. When
creating that FOREIGN KEY constraint you should use NOT FOR REPLICATION
clause! Otherwise, you'd get an constraint violation error when merge job
tries to insert data into the detail table - there's no guarantee merge
agent would first do the master and then the detail table.
> I'll need to implement a filtered publication so that each subscriber only
> gets their data. Type 1 & 2 are candidates for transactional replication
> but am I better off going with merge replication for all three table types
> since I'm dealing with disconnected subscribers?
Most of my publications are merge publications. The other type of
replication I use is snapshot, for various index and meta tables.
For the filtering purposes see reply to your original post, or see the
thread I started - Merge replication and dynamic filters.
Now I'm using two publications with static filters - each site has its own
publication where i explicitly added to WHERE clause something like:
e.g.: WHERE siteId = 'ST1'
In the thread mentioned just above is explained (i belive the guy who helped
me is Paul, but i can't check that now - i'm on UNIX with 'slrn' -
appologies if I remebered the wrong name) on how to create dynamic filters.
I would go for them because they reduce the number of publications (you
mentioned something about 20 subcscribers if I remember correctly), wich is
much easier to maintain/administrate.
Mike
-- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo msplival@jagor.srce.hr
- Next message: Tina Smith: "Re: Repl Topology"
- Previous message: Paul Ibison: "RE: Identity full SQL 2000 SP3 bug ?"
- In reply to: Tina Smith: "Repl Topology"
- Next in thread: Tina Smith: "Re: Repl Topology"
- Reply: Tina Smith: "Re: Repl Topology"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
