Let's say I have a database with three tables. One for customers, and two
for the invoices. Customers have cust_id field, wich is 'surogate' key, it
contains the org_id where the customer was created. That table should be in
merge replication because I need to be able to add data and make changes on
all locations in the replication system.
The invoices are referenced between them self with foreign key (you can't
insert invoice_id in the detail table if the invoice_id doesn't exsist in
master table), and the invoices table is referenced with customer table with
foreign key, so you can't add cust_id to invoices wich doesn't exsist in
customers table).
The DDL is like this:
CREATE TABLE [dbo].[customers] (
[cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[cust_name] [char] (50) COLLATE Croatian_CI_AS NOT NULL ,
[cust_address] [char] (150) COLLATE Croatian_CI_AS NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[invoice_details] (
[invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[item_id] [int] NOT NULL ,
[item_description] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL ,
[item_price] [decimal](18, 11) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[invoices] (
[invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[invoice_date] [datetime] NOT NULL ,
[remark] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[customers] WITH NOCHECK ADD
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[cust_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[invoice_details] WITH NOCHECK ADD
CONSTRAINT [PK_invoice_details] PRIMARY KEY CLUSTERED
(
[invoice_id],
[item_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[invoices] WITH NOCHECK ADD
CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED
(
[invoice_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[customers] ADD
CONSTRAINT [DF__customers__rowgu__49C3F6B7] DEFAULT (newid()) FOR
[rowguid]
GO
CREATE UNIQUE INDEX [index_357576312] ON [dbo].[customers]([rowguid]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[invoice_details] ADD
CONSTRAINT [FK_invoice_details_invoices] FOREIGN KEY
(
[invoice_id]
) REFERENCES [dbo].[invoices] (
[invoice_id]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[invoices] ADD
CONSTRAINT [FK_invoices_customers] FOREIGN KEY
(
[cust_id]
) REFERENCES [dbo].[customers] (
[cust_id]
) NOT FOR REPLICATION
GO
For the purpose I'm creating two publications. One for the customers, with
no filtering, because I need to have all the subscribers share the same
data. The other one is for the invoices, but those are filtered within
invoice_id, because I don't want one subscriber to have data that doesn't
belong to it.
So, I create the first merge publication, and at the end SQL server gives me
this:
This publication contains references to foreign keys outside the
publication. The following tables are outside the publication, but contain
foreign keys that are referenced from inside the publication:
invoices (references 'customers')
To add tables to the publication, select the publication in the Create and
Manager Publications dialog box, and then click Properties & Subscriptions.
Why do I need to add invoices table to the publication?
The other one, when creating publication for the invoices gives me more
headache:
This publication contains references to primary keys outside the
publication.
The following tables are outside the publication, but contain primary keys
that are referenced from inside the publication:
-- customers (referencing table is 'invoices')
Although you can change existing data in the referencing tables, you will
not be able to add rows to those tables. If you want to add rows to the
referencing tables, include the referenced tables as articles in the
publication.
To add tables to the publication, select the publication in the Create and
Manager Publications dialog box, and then click Properties & Subscriptions.
Again, why do I need to have customers table in the same publication with
invocies' tables? Since I have several publications for the invoices, each
filtering for one particular subscriber, if I add customers to the
publication, I need to add it for every publication I create. This seems
like a waste of resources. Isn't it easier to have just one publication for
the customers?
This is, of course, just a small example derived from the real world
situation. I have several 'primary key' tables (customers, articles, users,
delivery_rates, tax_rates, organizational departments, stocks,
blaha-blaha-blaha), and several dozens of 'foreign key referencing' tables
(invocies, stock documents, bills, ...). And, somehow, putting ALL those
tables within the same publication seems a bit messy. I prefer having
similair groups of tables together (for instance, publication for stock
documents has 22 articles, but, the 'primary key tables' that those tables
reference to are in it's own seperate publications).
Am I doing something wrong with my design?
Any help much appreciated!
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
The reason you would need to include all articles related by fk pk
constraints is that you might add an row to a child table which you are
replicating on the publisher where the row exists on the parent table. Then
this child row travels to the subscriber where the parent row does not
exist, and when the constraint is enforced, the transaction is rolled back
on the subscriber and publisher.
Similarly you might delete a parent record on the subscriber, and then when
it hits the publisher it might want to delete all child records belonging to
that parent row if you are not enforcing the constraint for replication, and
if you have cascading deletes and updates.
Under some circumstances you can ignore this warning.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrncqtm2o.pol.majk@.fly.srk.fer.hr...
> Let's say I have a database with three tables. One for customers, and two
> for the invoices. Customers have cust_id field, wich is 'surogate' key, it
> contains the org_id where the customer was created. That table should be
> in
> merge replication because I need to be able to add data and make changes
> on
> all locations in the replication system.
> The invoices are referenced between them self with foreign key (you can't
> insert invoice_id in the detail table if the invoice_id doesn't exsist in
> master table), and the invoices table is referenced with customer table
> with
> foreign key, so you can't add cust_id to invoices wich doesn't exsist in
> customers table).
> The DDL is like this:
> CREATE TABLE [dbo].[customers] (
> [cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_name] [char] (50) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_address] [char] (150) COLLATE Croatian_CI_AS NOT NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[invoice_details] (
> [invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [item_id] [int] NOT NULL ,
> [item_description] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL ,
> [item_price] [decimal](18, 11) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[invoices] (
> [invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [invoice_date] [datetime] NOT NULL ,
> [remark] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[customers] WITH NOCHECK ADD
> CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
> (
> [cust_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoice_details] WITH NOCHECK ADD
> CONSTRAINT [PK_invoice_details] PRIMARY KEY CLUSTERED
> (
> [invoice_id],
> [item_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoices] WITH NOCHECK ADD
> CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED
> (
> [invoice_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[customers] ADD
> CONSTRAINT [DF__customers__rowgu__49C3F6B7] DEFAULT (newid()) FOR
> [rowguid]
> GO
> CREATE UNIQUE INDEX [index_357576312] ON [dbo].[customers]([rowguid]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoice_details] ADD
> CONSTRAINT [FK_invoice_details_invoices] FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[invoices] (
> [invoice_id]
> ) NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[invoices] ADD
> CONSTRAINT [FK_invoices_customers] FOREIGN KEY
> (
> [cust_id]
> ) REFERENCES [dbo].[customers] (
> [cust_id]
> ) NOT FOR REPLICATION
> GO
>
> For the purpose I'm creating two publications. One for the customers, with
> no filtering, because I need to have all the subscribers share the same
> data. The other one is for the invoices, but those are filtered within
> invoice_id, because I don't want one subscriber to have data that doesn't
> belong to it.
>
> So, I create the first merge publication, and at the end SQL server gives
> me
> this:
> This publication contains references to foreign keys outside the
> publication. The following tables are outside the publication, but
> contain
> foreign keys that are referenced from inside the publication:
> invoices (references 'customers')
> To add tables to the publication, select the publication in the Create
> and
> Manager Publications dialog box, and then click Properties &
> Subscriptions.
> Why do I need to add invoices table to the publication?
> The other one, when creating publication for the invoices gives me more
> headache:
> This publication contains references to primary keys outside the
> publication.
> The following tables are outside the publication, but contain primary
> keys
> that are referenced from inside the publication:
> -- customers (referencing table is 'invoices')
> Although you can change existing data in the referencing tables, you
> will
> not be able to add rows to those tables. If you want to add rows to the
> referencing tables, include the referenced tables as articles in the
> publication.
> To add tables to the publication, select the publication in the Create
> and
> Manager Publications dialog box, and then click Properties &
> Subscriptions.
> Again, why do I need to have customers table in the same publication with
> invocies' tables? Since I have several publications for the invoices, each
> filtering for one particular subscriber, if I add customers to the
> publication, I need to add it for every publication I create. This seems
> like a waste of resources. Isn't it easier to have just one publication
> for
> the customers?
> This is, of course, just a small example derived from the real world
> situation. I have several 'primary key' tables (customers, articles,
> users,
> delivery_rates, tax_rates, organizational departments, stocks,
> blaha-blaha-blaha), and several dozens of 'foreign key referencing' tables
> (invocies, stock documents, bills, ...). And, somehow, putting ALL those
> tables within the same publication seems a bit messy. I prefer having
> similair groups of tables together (for instance, publication for stock
> documents has 22 articles, but, the 'primary key tables' that those tables
> reference to are in it's own seperate publications).
> Am I doing something wrong with my design?
> Any help much appreciated!
> 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
|||On 2004-12-02, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> The reason you would need to include all articles related by fk pk
> constraints is that you might add an row to a child table which you are
> replicating on the publisher where the row exists on the parent table. Then
> this child row travels to the subscriber where the parent row does not
> exist, and when the constraint is enforced, the transaction is rolled back
> on the subscriber and publisher.
Yes, I'm aware of that. But, both tables do exsits on both publisher and
subscriber. The application run at subscriber can't violate constraint, so
can't application on the publisher. Constraints are created with NOT FOR
REPLICATION, so if replication job first inserts child table - it will work.
> Similarly you might delete a parent record on the subscriber, and then when
> it hits the publisher it might want to delete all child records belonging to
> that parent row if you are not enforcing the constraint for replication, and
> if you have cascading deletes and updates.
Yes, the same thing.
> Under some circumstances you can ignore this warning.
Thank you for your response. I recreated publisher/subscriber situation in
'lab', and it seems to be working fine (i just ignored the warning). I'm
able to insert data into foreign key table (as long as I have primary key
table with up-to-date data), replication works fine, everything is ok.
But, now I have another issue. Since the number of subscribers is going to
be rather high, some 200-300 subscribers (all MSDEs on laptops) i wanted to
script the subscriptions. In snapshot options for the particular publication
i choose the snapshot agent to DROP and recreate tables on the subscriber,
referential integrity included. But, publication wizzard tells me it can't
create foreign key constraints because the reffered tables are not in that
publication. Wich I understand, because, if I first push the invoices
publication to the subscriber, there is customers table missing, so, realy
no constraints to that tabe could be created.
Is there workaround for this? I need to have initial snapshots to delete all
the data on the remote side. But, putting all the tables to one publications
seems like a LOT of mess here. In my example, if I have 50 subscriptions I
should publish the customers table 50 times, instead of just once.
So, my question is again, am I doing something wrong when designing the
replication?
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
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Wednesday, March 28, 2012
Merge replication with autoincrement field
Hi guys,
I have two separate system with some data common between them.
For simplicity let's say System A contains some data that is same with
System B. Now I want to get the data from System A to System B by
merge replication (dont want to send data from System B to System A -
using merge replication for filtering using "join". if this can be
done in some other way using transactional replication please let me
know).
But in System A there are few tables with autoincrement fields set for
the primary key field. I am entering data in those tables in System B
also.
Now, how can I handle this - insert records in both system avoiding
clash for the primary key.
PS: I know in MySQL they handle this case by setting up the
replication and then change the seed value and increment value for
different database systems and by thus avoid the clash.
Thanks,
Rakesh.
Rakesh,
on the article properties form there is a tab to manage the identity values.
In SQL 2005 this is set automatically but in SQL 2000 you have to enable it
manually. Once a large range has been selected, there should be no issues as
the ranges are partitioned.
HTH,
Paul Ibison
I have two separate system with some data common between them.
For simplicity let's say System A contains some data that is same with
System B. Now I want to get the data from System A to System B by
merge replication (dont want to send data from System B to System A -
using merge replication for filtering using "join". if this can be
done in some other way using transactional replication please let me
know).
But in System A there are few tables with autoincrement fields set for
the primary key field. I am entering data in those tables in System B
also.
Now, how can I handle this - insert records in both system avoiding
clash for the primary key.
PS: I know in MySQL they handle this case by setting up the
replication and then change the seed value and increment value for
different database systems and by thus avoid the clash.
Thanks,
Rakesh.
Rakesh,
on the article properties form there is a tab to manage the identity values.
In SQL 2005 this is set automatically but in SQL 2000 you have to enable it
manually. Once a large range has been selected, there should be no issues as
the ranges are partitioned.
HTH,
Paul Ibison
Monday, March 26, 2012
merge replication w PocketPC and trigger order
Hi
We've got a problen in a customer with SQL Server 2000 SP3 and merge
replication with PocketPC
We have some triggers FOR UPDATE on the same field used to distribute in
replication settings, so I suspect sometimes our triggers didn't work, and
sometimes the first trigger that has been executed were the replication
trigger.
Can I use the sp_settriggerorder to set the order to execute, setting the
replication trihgger as last in execution?
Thanks
Yes you can, I normally make them execute first though.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ricardo Snchez" <Ricardo Snchez@.discussions.microsoft.com> wrote in
message news:2BA27F4E-0454-4DC0-9DC2-46CD14C7E368@.microsoft.com...
> Hi
> We've got a problen in a customer with SQL Server 2000 SP3 and merge
> replication with PocketPC
> We have some triggers FOR UPDATE on the same field used to distribute in
> replication settings, so I suspect sometimes our triggers didn't work, and
> sometimes the first trigger that has been executed were the replication
> trigger.
> Can I use the sp_settriggerorder to set the order to execute, setting the
> replication trihgger as last in execution?
> Thanks
sql
We've got a problen in a customer with SQL Server 2000 SP3 and merge
replication with PocketPC
We have some triggers FOR UPDATE on the same field used to distribute in
replication settings, so I suspect sometimes our triggers didn't work, and
sometimes the first trigger that has been executed were the replication
trigger.
Can I use the sp_settriggerorder to set the order to execute, setting the
replication trihgger as last in execution?
Thanks
Yes you can, I normally make them execute first though.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ricardo Snchez" <Ricardo Snchez@.discussions.microsoft.com> wrote in
message news:2BA27F4E-0454-4DC0-9DC2-46CD14C7E368@.microsoft.com...
> Hi
> We've got a problen in a customer with SQL Server 2000 SP3 and merge
> replication with PocketPC
> We have some triggers FOR UPDATE on the same field used to distribute in
> replication settings, so I suspect sometimes our triggers didn't work, and
> sometimes the first trigger that has been executed were the replication
> trigger.
> Can I use the sp_settriggerorder to set the order to execute, setting the
> replication trihgger as last in execution?
> Thanks
sql
Monday, March 12, 2012
Merge Replication Indentity Semi-Automatic Range allocation
I have a publisher and 2 subscribers on the network and the table I am merge
replicating has an identity field, with NOT FOR REPLICATION set, to allow me
to use identiy ranges which it does very well, however I have 1 problem and 1
question.
Problem 1
When I reach my threshold (80%) it stops that server from creating new
records and an error is produced until the merge agent has run and the best
that can be is once a minute. How can I configure the system to not wait for
the next time the merge agent runs, as this system will be creating 100's of
transactions a minute.
Question 1
If the threshold is set to 80% and I have a range of 1000, then when it gets
to 800 it stops and sets the next id number to the first in the next block
meaning that the remaining 200 are redundant.
I would have expected that when the threshold is reached that until the new
numbers are received then the server would use the range after the threshold
until it receives the next range otherwise why not always set the threshold
to 100% as it just errors when it hits the threshold anyway? Looking at BOL
it says for transactional then the agent runs continously, but with merge no
such statement, and as its merge based the subscribers could be offline for
some time, so shouldn't there be a more controlled process when they reach
their threshold.
I think I am missing an option on the publication maybe
thanks in advance.
Neil.
Thanks for the kb's Paul but the threshold value seems to be completely
pointless to me
In KB 322910 it says
Before you implement ranged identity management with merge replication, you
must first consider how many inserts will be performed by users and how
frequently they will be merging their changes. The primary goal when defining
the identity options for an article is to make the ranges large enough that
the Subscriber will not run out of values before the next merge.
Which suggests to me that the subscribers should get a new block of identity
values when they merge, however in my testing, a new range is only produced
when the subscriber has reached the threshold level and an error is produced
and they are not allowed to create any new records until the merge which does
mean that the threshold value is pointless and should be set to 99% or 100%
I hope this makes sense.
Neil.
"Paul Ibison" wrote:
> Neil,
> there are some problems with what you are trying to do
> that are documented:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;304706&Product=sql2k
> http://support.microsoft.com/?kbid=310540
> I would suggest one of 2 options:
> (a) set the range size so large that it will never need
> to reseed
> or
> (b) use manual range management and a simple algorithm eg
> in the simplest case odd and even values for the case
> where there is just a publisher and subscriber.
> Regards,
> Paul Ibison
> (The ONLY sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Neil,
if you are regularly synchronizing, a new range will be
requested as a part of the synchronization process. This
is before the check constraint that controls the upper
boundary reports an error when a subscriber who performs
an insert reports an error. So although there will be
unused identity values, this should lead to a smooth
running of the subscriber.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think your problem is because you are over running your threshold in the
batch. The way it is designed is that you pick a range which is
representative of the largest batch which could occur on your
publisher/subscriber and then adjust it upwards so that the threshold lies
within this batch.
Here are some examples
1) update 10,000 rows in a batch, max range is 10,000 threshold is 80% -
results range exceeded transaction rolled back
2) update 10,000 rows in a batch, max range is 20,000 threshold is 50% -
threshold exceeded, range adjusted on publisher and subscriber
3) update 10,000 rows in a batch, max range is 20,000 threshold is 80% -
threshold not exceeded, with first batch. range not adjusted on publisher.
Second batch runs, range exceeded, transaction rolled back.
4) update 10,000 rows in a batch, max range is 100,000, threshold is 80%,
threshold not exceeded until the 8th run, range adjusted on publisher and
subscriber, AND the publisher can still accept to more 10,000 batches before
blowing up.
The way most dba implement this is the set it and forget it philosophy. They
look at their publisher and subscriber and they parcel out the ranges that
they believe will not be exceeded during the lifetime of their replication
solution, and they don't have to worry about it.
One more point when you run into problems with automatic identity range
management you can use the following proc to fix things.
sp_mScheckidentityrange with the @.checkonly parameter having a value of 1
Some people have complained to me that the @.checkonly parameter is ignored
and the range is not updated. I havent' been able to repro this yet, but you
can always sp_MSadjustmergeidentity directly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neil" <Neil@.discussions.microsoft.com> wrote in message
news:7A350532-D333-4436-9035-87A376B935BC@.microsoft.com...
> Thanks for the kb's Paul but the threshold value seems to be completely
> pointless to me
> In KB 322910 it says
> Before you implement ranged identity management with merge replication,
you
> must first consider how many inserts will be performed by users and how
> frequently they will be merging their changes. The primary goal when
defining
> the identity options for an article is to make the ranges large enough
that
> the Subscriber will not run out of values before the next merge.
> Which suggests to me that the subscribers should get a new block of
identity
> values when they merge, however in my testing, a new range is only
produced
> when the subscriber has reached the threshold level and an error is
produced
> and they are not allowed to create any new records until the merge which
does
> mean that the threshold value is pointless and should be set to 99% or
100%[vbcol=seagreen]
> I hope this makes sense.
> Neil.
> "Paul Ibison" wrote:
replicating has an identity field, with NOT FOR REPLICATION set, to allow me
to use identiy ranges which it does very well, however I have 1 problem and 1
question.
Problem 1
When I reach my threshold (80%) it stops that server from creating new
records and an error is produced until the merge agent has run and the best
that can be is once a minute. How can I configure the system to not wait for
the next time the merge agent runs, as this system will be creating 100's of
transactions a minute.
Question 1
If the threshold is set to 80% and I have a range of 1000, then when it gets
to 800 it stops and sets the next id number to the first in the next block
meaning that the remaining 200 are redundant.
I would have expected that when the threshold is reached that until the new
numbers are received then the server would use the range after the threshold
until it receives the next range otherwise why not always set the threshold
to 100% as it just errors when it hits the threshold anyway? Looking at BOL
it says for transactional then the agent runs continously, but with merge no
such statement, and as its merge based the subscribers could be offline for
some time, so shouldn't there be a more controlled process when they reach
their threshold.
I think I am missing an option on the publication maybe
thanks in advance.
Neil.
Thanks for the kb's Paul but the threshold value seems to be completely
pointless to me
In KB 322910 it says
Before you implement ranged identity management with merge replication, you
must first consider how many inserts will be performed by users and how
frequently they will be merging their changes. The primary goal when defining
the identity options for an article is to make the ranges large enough that
the Subscriber will not run out of values before the next merge.
Which suggests to me that the subscribers should get a new block of identity
values when they merge, however in my testing, a new range is only produced
when the subscriber has reached the threshold level and an error is produced
and they are not allowed to create any new records until the merge which does
mean that the threshold value is pointless and should be set to 99% or 100%
I hope this makes sense.
Neil.
"Paul Ibison" wrote:
> Neil,
> there are some problems with what you are trying to do
> that are documented:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;304706&Product=sql2k
> http://support.microsoft.com/?kbid=310540
> I would suggest one of 2 options:
> (a) set the range size so large that it will never need
> to reseed
> or
> (b) use manual range management and a simple algorithm eg
> in the simplest case odd and even values for the case
> where there is just a publisher and subscriber.
> Regards,
> Paul Ibison
> (The ONLY sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Neil,
if you are regularly synchronizing, a new range will be
requested as a part of the synchronization process. This
is before the check constraint that controls the upper
boundary reports an error when a subscriber who performs
an insert reports an error. So although there will be
unused identity values, this should lead to a smooth
running of the subscriber.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think your problem is because you are over running your threshold in the
batch. The way it is designed is that you pick a range which is
representative of the largest batch which could occur on your
publisher/subscriber and then adjust it upwards so that the threshold lies
within this batch.
Here are some examples
1) update 10,000 rows in a batch, max range is 10,000 threshold is 80% -
results range exceeded transaction rolled back
2) update 10,000 rows in a batch, max range is 20,000 threshold is 50% -
threshold exceeded, range adjusted on publisher and subscriber
3) update 10,000 rows in a batch, max range is 20,000 threshold is 80% -
threshold not exceeded, with first batch. range not adjusted on publisher.
Second batch runs, range exceeded, transaction rolled back.
4) update 10,000 rows in a batch, max range is 100,000, threshold is 80%,
threshold not exceeded until the 8th run, range adjusted on publisher and
subscriber, AND the publisher can still accept to more 10,000 batches before
blowing up.
The way most dba implement this is the set it and forget it philosophy. They
look at their publisher and subscriber and they parcel out the ranges that
they believe will not be exceeded during the lifetime of their replication
solution, and they don't have to worry about it.
One more point when you run into problems with automatic identity range
management you can use the following proc to fix things.
sp_mScheckidentityrange with the @.checkonly parameter having a value of 1
Some people have complained to me that the @.checkonly parameter is ignored
and the range is not updated. I havent' been able to repro this yet, but you
can always sp_MSadjustmergeidentity directly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neil" <Neil@.discussions.microsoft.com> wrote in message
news:7A350532-D333-4436-9035-87A376B935BC@.microsoft.com...
> Thanks for the kb's Paul but the threshold value seems to be completely
> pointless to me
> In KB 322910 it says
> Before you implement ranged identity management with merge replication,
you
> must first consider how many inserts will be performed by users and how
> frequently they will be merging their changes. The primary goal when
defining
> the identity options for an article is to make the ranges large enough
that
> the Subscriber will not run out of values before the next merge.
> Which suggests to me that the subscribers should get a new block of
identity
> values when they merge, however in my testing, a new range is only
produced
> when the subscriber has reached the threshold level and an error is
produced
> and they are not allowed to create any new records until the merge which
does
> mean that the threshold value is pointless and should be set to 99% or
100%[vbcol=seagreen]
> I hope this makes sense.
> Neil.
> "Paul Ibison" wrote:
Monday, February 20, 2012
merge replication and identity field problems.
Hi there,
I have converted MS access database to sql 2000 database and front end
is in adp. i our db all of our table contain identity field as a
primary key and forgine key. I am using merge replication with the
publisher and distributer in the same server where original db is and
may have many subscriber (pull subscription) using msde who will
synchronize on demand. i change all the autonumber field in access as
identity field (not for replication) and relationship between table
(not for replication) is clear off. I am very much worried now if i
just publish the database and subscript is whether i am going to have
conflict with identity field which are primary key or its going to
workin fine. Actually i donot know how sql handel those identity field
with so many copy of subscriber. please give me some information how
should i proceed.
I have table call Job, jobcarrier, jobshots, joblogs, jobpersonnel,
etc where primary key is identity field and all the table contain
forgain key from job table. and our replicated database using the same
front end as we are using. please give me inf. how it work and what i
should do.
Thansk very much
Indra.
Indra,
you have a choice to either let SQL Server manage the identity ranges or do
it manually. If you select to synchronize your tables on initialization,
different seeds will be selected for each subscriber, and the size of the
allocated range is determined by yourself (on clicking the article
properties elipsis button a configuration form appears). This is probably
the easiest method. If you want to do it manually you might be interested in
Michael Hotek's algorithms to ensure no overlap
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Indra,
It would be easier for you to let SQL handle the identity values. Also I hope you are taking of specifying "Not for replication" for all your relationships.
Regards,
Karthik.
|||HI Paul,
I check all the information, BOL, artical, knowledgebase etc and try
to publishe merge replication with pull subscribtion and its not
working as the way it should work.
1. All the identity field has been assign as not for replication
2. all the relationship has been clear off the option (enforce
relationship for replication).
3. I could run the subcription and synchornzed the data.
4. i inserted in subscriber and in the publisher database it both give
the same identity field.
5. When i synchronized, it doesnot display any error message but the
data inserted at the subscriber has been deleted and data inserted at
the publisher has been trasfer to subscriber (the conflict with pk
data in subscriber has been deleted.)
6. when i check the pulication property the option for automatic
identity assign and maintain is not highlighted.
Could you please help me where am i making wrong and how i can do
this.
I will appreciate your help.
Thanks.
Indra.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<##LLZhNSEHA.3988@.tk2msftngp13.phx.gbl>...
> Indra,
> you have a choice to either let SQL Server manage the identity ranges or do
> it manually. If you select to synchronize your tables on initialization,
> different seeds will be selected for each subscriber, and the size of the
> allocated range is determined by yourself (on clicking the article
> properties elipsis button a configuration form appears). This is probably
> the easiest method. If you want to do it manually you might be interested in
> Michael Hotek's algorithms to ensure no overlap
> (http://www.mssqlserver.com/replicati...h_identity.asp).
> HTH,
> Paul Ibison
|||Indra,
automatic range management is not enabled by default. Using:
exec sp_MShelp_identity_property @.tablename = N'TestIdent', @.ownername =
N'dbo'
will let you know if it is enabled. To get SQL Server to manage the range
you'll need to put a check in the box on the article properties, Identity
range tab. As far as I know, this isn't possible to do retrospectively, so
you'll need to recreate the publication.
HTH,
Paul Ibison
I have converted MS access database to sql 2000 database and front end
is in adp. i our db all of our table contain identity field as a
primary key and forgine key. I am using merge replication with the
publisher and distributer in the same server where original db is and
may have many subscriber (pull subscription) using msde who will
synchronize on demand. i change all the autonumber field in access as
identity field (not for replication) and relationship between table
(not for replication) is clear off. I am very much worried now if i
just publish the database and subscript is whether i am going to have
conflict with identity field which are primary key or its going to
workin fine. Actually i donot know how sql handel those identity field
with so many copy of subscriber. please give me some information how
should i proceed.
I have table call Job, jobcarrier, jobshots, joblogs, jobpersonnel,
etc where primary key is identity field and all the table contain
forgain key from job table. and our replicated database using the same
front end as we are using. please give me inf. how it work and what i
should do.
Thansk very much
Indra.
Indra,
you have a choice to either let SQL Server manage the identity ranges or do
it manually. If you select to synchronize your tables on initialization,
different seeds will be selected for each subscriber, and the size of the
allocated range is determined by yourself (on clicking the article
properties elipsis button a configuration form appears). This is probably
the easiest method. If you want to do it manually you might be interested in
Michael Hotek's algorithms to ensure no overlap
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Indra,
It would be easier for you to let SQL handle the identity values. Also I hope you are taking of specifying "Not for replication" for all your relationships.
Regards,
Karthik.
|||HI Paul,
I check all the information, BOL, artical, knowledgebase etc and try
to publishe merge replication with pull subscribtion and its not
working as the way it should work.
1. All the identity field has been assign as not for replication
2. all the relationship has been clear off the option (enforce
relationship for replication).
3. I could run the subcription and synchornzed the data.
4. i inserted in subscriber and in the publisher database it both give
the same identity field.
5. When i synchronized, it doesnot display any error message but the
data inserted at the subscriber has been deleted and data inserted at
the publisher has been trasfer to subscriber (the conflict with pk
data in subscriber has been deleted.)
6. when i check the pulication property the option for automatic
identity assign and maintain is not highlighted.
Could you please help me where am i making wrong and how i can do
this.
I will appreciate your help.
Thanks.
Indra.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<##LLZhNSEHA.3988@.tk2msftngp13.phx.gbl>...
> Indra,
> you have a choice to either let SQL Server manage the identity ranges or do
> it manually. If you select to synchronize your tables on initialization,
> different seeds will be selected for each subscriber, and the size of the
> allocated range is determined by yourself (on clicking the article
> properties elipsis button a configuration form appears). This is probably
> the easiest method. If you want to do it manually you might be interested in
> Michael Hotek's algorithms to ensure no overlap
> (http://www.mssqlserver.com/replicati...h_identity.asp).
> HTH,
> Paul Ibison
|||Indra,
automatic range management is not enabled by default. Using:
exec sp_MShelp_identity_property @.tablename = N'TestIdent', @.ownername =
N'dbo'
will let you know if it is enabled. To get SQL Server to manage the range
you'll need to put a check in the box on the article properties, Identity
range tab. As far as I know, this isn't possible to do retrospectively, so
you'll need to recreate the publication.
HTH,
Paul Ibison
Subscribe to:
Posts (Atom)