Replication changing PK

I have a very strange thing happening where the primary key field is getting
changed when synchronizing with a subscriber. Background:
-Using SQL 2k and merge replication
-Testing with Publisher and Distributor on same server.
-Setup only 1 subscriber to test.
-Added a record into the Publisher into 2 tables (People and ApplicantInfo)
with a PK-FK link (1 to 1) on a field named PersonID. I also entered 9
records into a table with 1-many relationship to the ApplicantInfo table
(but not really relevant here).
-I check the records in the tables before I synchronized and both had a key
value of 195134582.
-I then synchronized from the subscriber and got the following conflict
error:
"The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted at
'DELLGX260.MCFIData'. The record can't be added or changed. Referential
integrity rules require a related record in table 'People'."
-I then opened EM and searched the People table on the Publisher and the
PersonID now has a value of 211587197.
-There is no update trigger on the People table, only delete trigger and
insert trigger. Below is the insert trigger on the People table. Could it
somehow affect the PersonID when inserting into subscriber? I hope not.
CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT PersonID FROM inserted)
UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
Can anyone shed light on this as it is critical to implementing replication.
Thank you.
David
Turns out, the problem had to be the trigger as it fired again when synching
and changed the PersonID again. I removed the trigger and defaulted the
PersonID to random number and now it works fine.
David
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OA4dtoDJGHA.3120@.TK2MSFTNGP10.phx.gbl...
>I have a very strange thing happening where the primary key field is
>getting changed when synchronizing with a subscriber. Background:
> -Using SQL 2k and merge replication
> -Testing with Publisher and Distributor on same server.
> -Setup only 1 subscriber to test.
> -Added a record into the Publisher into 2 tables (People and
> ApplicantInfo) with a PK-FK link (1 to 1) on a field named PersonID. I
> also entered 9 records into a table with 1-many relationship to the
> ApplicantInfo table (but not really relevant here).
> -I check the records in the tables before I synchronized and both had a
> key value of 195134582.
> -I then synchronized from the subscriber and got the following conflict
> error:
> "The row was inserted at 'LIFEDEVTEST.MCFIData' but could not be inserted
> at 'DELLGX260.MCFIData'. The record can't be added or changed. Referential
> integrity rules require a related record in table 'People'."
> -I then opened EM and searched the People table on the Publisher and the
> PersonID now has a value of 211587197.
> -There is no update trigger on the People table, only delete trigger and
> insert trigger. Below is the insert trigger on the People table. Could
> it somehow affect the PersonID when inserting into subscriber? I hope
> not.
> CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
> SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT PersonID FROM inserted)
> UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
>
> Can anyone shed light on this as it is critical to implementing
> replication. Thank you.
>
> David
>
|||This is actually a trigger ordering issue. Merge triggers are system
triggers and fire first. Than means your insert hit and fired the merge
trigger. Your trigger then fired causing an update to the same row which
was inserted. This will almost always cause a conflict to be thrown.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:epRwmORJGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Turns out, the problem had to be the trigger as it fired again when
> synching and changed the PersonID again. I removed the trigger and
> defaulted the PersonID to random number and now it works fine.
> David
> "David Chase" <dlchase@.lifetimeinc.com> wrote in message
> news:OA4dtoDJGHA.3120@.TK2MSFTNGP10.phx.gbl...
>

Replication - changing a server

This is very important.

I have merge replication with publisher and distributor on the same server and about 20 Subscribers all over the country.
We are moving to another building, with new equipment, so Publisher will be on different server. How to make this move easily, without recreating new publication and pushing new subscribers and implementing initial snapshot, because it will destroy my network.

Is there any possibility to do something like attach/detach DB (distibution and source DB), do few steps more, and to have all like it was before.
Must be.

Thanks in advance.

If you are going to change the publisher server name, you will need to remove replication from the old server and reconfigure it on the new server.

There is another way which you can try:

1) Stop SQL Server on your publisher machine (Machine 1)
2) Rename the machine to something else
3) Copy all the database data files and log files from the old publisher machine to a second machine (Machine 2)
4) Install your new machine (Machine 3), give the old publisher server's name to this new machine
5) Install SQL Server on Machine 3 (which has the old publisher server's name). Make sure the installation directory is the same as the old machine. Bring SQL Server to the same service pack level.
6) Ensure any NT users and logins, AD settings on the new machine is the same as the old machine.
7) Stop SQL Server on Machine 3. Copy over the database data and log files from Machine 2.
8) Restart SQL Server on Machine 3.

Now you should be ready to sync the subscriptions.