Merge Replication with identity keys

Hi ,
I am new in replication area . I have created a merge replication . The
tables which is present in source table has identity key, after creating
replication , I am able to add duplicate values in source as well as
destinations table having same identity values .
Secondly , When new table having identity values are added in article e.g
test1 ,Current Identity seed of test1 in different in destination database
.. I want to synchronise the data in 2 database . ie. when I am inserting
values in Test 1 table on DB1, identity seed should be taken correct seed
automatically and vice versa for destination table . Most of our tables are
containing identity keys , and all reports are based on that key .
Also ,pls let me know is there any helpful articles which simulatiing same
kind of problem.
Pls help me in this regard.
Regards,
Swati
Swati,
when you're ading the table, have a look at the table properties (this is an
elipsis button next to the table). There is a tab for identities. You can
select automatic range management here - ideally select a big enough range
so that there will not be any need to get another. This will ensure no
overlaps.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication with Foreign Keys

I have a database with related tables by foreign keys (Not for Replication),
and I want to use Merge replication, is there any problem to keep the foreign
keys on both sides (publisher and subscriber) or I have to remove them?
Saeed,
you can keep them - as long as they have the NFR attribute.
If you are replicating a huge amount of data, there are issues, and this
article will help explain them:
http://support.microsoft.com/kb/308266/EN-US/
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge replication of Foreign keys

Hi.
I need to add a new foreign key to a merge replicated table (It references
the PK in the same table). Is there anyway of replicating this to the other
subscribers without invalidating the snapshot or applying it separately on
each server?
AFAIK - no!
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"robham" <nospam@.co.uk> wrote in message
news:uXAUdaeiEHA.704@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I need to add a new foreign key to a merge replicated table (It references
> the PK in the same table). Is there anyway of replicating this to the
other
> subscribers without invalidating the snapshot or applying it separately on
> each server?
>
|||Robham,
is the FK to be just a constraint? If so then I don't think it is possible
directly. I suppose you could use a combination of sp_repladdcolumn and
sp_repldropcolumn to achieve your aim but in some cases it is easier to
restart and do a noinit initialization.
If it is a new column, then sp_repladdcolumn should do it.
HTH,
Paul Ibison

Merge Replication does not apply changes in original order!

Has anyone noticed that Merge Replication does not apply changes in original
order?
This causes foreign keys errors but I fixed that by making the foreign keys
'not for replication'.
It also causes unique index errors if I am manipulating the unique values in
an exact sequence - anyone have a solution to this one?
That's correct!
I'm not exactly sure what order it will apply the changes in however. But
what it does is gathers up the changes, figures out what to apply where, and
then applies it one by one. If there is a pk/fk/GodKnowsWhat violation in
the initial attempt, the modification goes into a queue and when the merge
agent has completed the initial pass, it will retry the failures another
time.
For the majority of the cases, this second (or third, forth, etc) retry will
succeed. Sometimes you need to bump up the generationsperbatch switches to
2000 for this to succeed.
Merge replication in SQL 2005 will apply changes which belong to related
tables together in the same batch boundary so you don't have to worry about
this anymore.
Also becareful that you don't enforce cascading updates and deletes for
replication as this can get you into trouble.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Breffni" <Jim Breffni@.discussions.microsoft.com> wrote in message
news:F55EE9FB-7733-4EDD-8E2B-A439978D3924@.microsoft.com...
> Has anyone noticed that Merge Replication does not apply changes in
original
> order?
> This causes foreign keys errors but I fixed that by making the foreign
keys
> 'not for replication'.
> It also causes unique index errors if I am manipulating the unique values
in
> an exact sequence - anyone have a solution to this one?
|||Thanks Hilary - where do I set the checkSortOrder generationsperbatch switch?
Jim.
"Hilary Cotter" wrote:

> That's correct!
> I'm not exactly sure what order it will apply the changes in however. But
> what it does is gathers up the changes, figures out what to apply where, and
> then applies it one by one. If there is a pk/fk/GodKnowsWhat violation in
> the initial attempt, the modification goes into a queue and when the merge
> agent has completed the initial pass, it will retry the failures another
> time.
> For the majority of the cases, this second (or third, forth, etc) retry will
> succeed. Sometimes you need to bump up the generationsperbatch switches to
> 2000 for this to succeed.
> Merge replication in SQL 2005 will apply changes which belong to related
> tables together in the same batch boundary so you don't have to worry about
> this anymore.
> Also becareful that you don't enforce cascading updates and deletes for
> replication as this can get you into trouble.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jim Breffni" <Jim Breffni@.discussions.microsoft.com> wrote in message
> news:F55EE9FB-7733-4EDD-8E2B-A439978D3924@.microsoft.com...
> original
> keys
> in
>
>
|||you don't. What you do is make the generationsperbatch switches very large
in hopes that the pk fk modifications will occur in the same batch.
You should set
MaxDownloadChanges
MaxUploadChanges
UploadGenerationsPerBatch
DownloadGenerationsPerBatch
UploadReadChangesPerBatch
DownloadReadChangesPerBatch
UploadWriteChangesPerBatch
DownloadWriteChangesPerBatch
to 2000


Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Breffni" <JimBreffni@.discussions.microsoft.com> wrote in message
news:BD122CA6-5721-428B-8F1E-791E99CE1E72@.microsoft.com...
> Thanks Hilary - where do I set the checkSortOrder generationsperbatch
switch?[vbcol=seagreen]
>
> Jim.
>
> "Hilary Cotter" wrote:
But[vbcol=seagreen]
and[vbcol=seagreen]
in[vbcol=seagreen]
merge[vbcol=seagreen]
will[vbcol=seagreen]
to[vbcol=seagreen]
about[vbcol=seagreen]
values[vbcol=seagreen]

Merge Replication and Keys

A couple of questions about Merge Replication (involving two 2000 servers):
1. From what I read Merge Replication requires a uniqueidentifier column
and will create one if you do not already have one. Correct?
2. If #1 is true, then do you have to have primary keys for every table
involved in Merge Replication?
Thx.1. Yes
2. Not for merge since it will use rowguid columns, but you really should
have PK on every table in your database.
MC
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F06DE00E-F49B-49A9-90BA-7415204A4E99@.microsoft.com...
>A couple of questions about Merge Replication (involving two 2000 servers):
> 1. From what I read Merge Replication requires a uniqueidentifier column
> and will create one if you do not already have one. Correct?
> 2. If #1 is true, then do you have to have primary keys for every table
> involved in Merge Replication?
> Thx.

Merge Replication and Keys

A couple of questions about Merge Replication (involving two 2000 servers):
1. From what I read Merge Replication requires a uniqueidentifier column
and will create one if you do not already have one. Correct?
2. If #1 is true, then do you have to have primary keys for every table
involved in Merge Replication?
Thx.
1. Yes
2. Not for merge since it will use rowguid columns, but you really should
have PK on every table in your database.
MC
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F06DE00E-F49B-49A9-90BA-7415204A4E99@.microsoft.com...
>A couple of questions about Merge Replication (involving two 2000 servers):
> 1. From what I read Merge Replication requires a uniqueidentifier column
> and will create one if you do not already have one. Correct?
> 2. If #1 is true, then do you have to have primary keys for every table
> involved in Merge Replication?
> Thx.

Merge Replication and Keys

A couple of questions about Merge Replication (involving two 2000 servers):
1. From what I read Merge Replication requires a uniqueidentifier column
and will create one if you do not already have one. Correct?
2. If #1 is true, then do you have to have primary keys for every table
involved in Merge Replication?
Thx.1. Yes
2. Not for merge since it will use rowguid columns, but you really should
have PK on every table in your database.
MC
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F06DE00E-F49B-49A9-90BA-7415204A4E99@.microsoft.com...
>A couple of questions about Merge Replication (involving two 2000 servers):
> 1. From what I read Merge Replication requires a uniqueidentifier column
> and will create one if you do not already have one. Correct?
> 2. If #1 is true, then do you have to have primary keys for every table
> involved in Merge Replication?
> Thx.