Re: Repl Topology
From: Tina Smith (tb.smith_at_earthlink.net)
Date: 03/09/04
- Next message: Mario Splivalo: "Re: Repl Topology"
- Previous message: Tina Smith: "Re: Repl Topology"
- In reply to: Mario Splivalo: "Re: Repl Topology"
- Next in thread: Mario Splivalo: "Re: Repl Topology"
- Reply: Mario Splivalo: "Re: Repl Topology"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 9 Mar 2004 16:30:18 -0500
I really prefer not to manage the counters myself using the SELECT MAX().
I was hoping to set my seed value ranges high enough so that sql server can
manage my IDs for me for the life of the application. I have a site ID (
integer ) column and a ID ( identity integer ) column that make up my
primary key.
Thanks for your help!
"Mario Splivalo" <majk@fly.srk.fer.hr> wrote in message
news:slrnc4sa2o.fbn.majk@fly.srk.fer.hr...
> 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: Mario Splivalo: "Re: Repl Topology"
- Previous message: Tina Smith: "Re: Repl Topology"
- In reply to: Mario Splivalo: "Re: Repl Topology"
- Next in thread: Mario Splivalo: "Re: Repl Topology"
- Reply: Mario Splivalo: "Re: Repl Topology"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
