merge replication mess with identity fields

Our application makes extensive use of Identity fields as primary/foreign
keys. One client decided to use merge replication between two sites. They
brought in a consultant who set it up for them. The guy didn't know what
identity fields were. He thought our application filled them in from some
table he couldn't find.
When records turned up missing, the client called us back. Finding that new
records on both ends were getting the same identity, then SQL Server was
keeping the record on one side & throwing out the one of the same number on
the other side, we had them stop editing on the subscriber side until we got
the thing corrected. I thought if all edits were done on the publisher, it
could keep pushing the changes down to the subscriber without causing
problems.
Records are still getting deleted by replication, so the other consultant
has turn it off.
My job is to fix the mess. Identity ranges using NOT FOR REPLICATION are
the answer. But how do I get to that point from where I am? ALTER TABLE
won't work on these tables that are set up to be replicated.
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
I don't think that the NFR switch will be the best option for you.
I think you need different seeds on both sides, ie on the publisher has a
seed of 1 and in increment of 2. On the subscriber have a seed of 2 and an
increment of 2.
Check out this link for more info on how to set the NFR property on identity
columns, however I think you are best to rip everything out and start again.
sp configure 'allow updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <>
0 and
colstat & 0x0008 =0
go
sp configure 'allow updates', 0
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:ObsGdn0yEHA.344@.TK2MSFTNGP10.phx.gbl...
> Our application makes extensive use of Identity fields as primary/foreign
> keys. One client decided to use merge replication between two sites. They
> brought in a consultant who set it up for them. The guy didn't know what
> identity fields were. He thought our application filled them in from some
> table he couldn't find.
> When records turned up missing, the client called us back. Finding that
> new
> records on both ends were getting the same identity, then SQL Server was
> keeping the record on one side & throwing out the one of the same number
> on
> the other side, we had them stop editing on the subscriber side until we
> got
> the thing corrected. I thought if all edits were done on the publisher, it
> could keep pushing the changes down to the subscriber without causing
> problems.
> Records are still getting deleted by replication, so the other consultant
> has turn it off.
> My job is to fix the mess. Identity ranges using NOT FOR REPLICATION are
> the answer. But how do I get to that point from where I am? ALTER TABLE
> won't work on these tables that are set up to be replicated.
> Thanks.
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
|||Different seeds sound like a good solution. Since I expect to have 2
subscribers, I think I Iike an increment of 3.
So ... how do I get to that? Even if I delete the existing publication, how
do I get different seeds on the subscriber(s) than on the publisher? The
default action when the initial snapshot is taken is to DROP and recreate
the table ... obviously according to the same schema as that on the
publisher. Am I even looking in the right area?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIz8dn1yEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I don't think that the NFR switch will be the best option for you.
> I think you need different seeds on both sides, ie on the publisher has a
> seed of 1 and in increment of 2. On the subscriber have a seed of 2 and an
> increment of 2.
> Check out this link for more info on how to set the NFR property on
identity
> columns, however I think you are best to rip everything out and start
again.[vbcol=seagreen]
> sp configure 'allow updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <>
> 0 and
> colstat & 0x0008 =0
> go
> sp configure 'allow updates', 0
>
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:ObsGdn0yEHA.344@.TK2MSFTNGP10.phx.gbl...
primary/foreign[vbcol=seagreen]
They[vbcol=seagreen]
what[vbcol=seagreen]
some[vbcol=seagreen]
it[vbcol=seagreen]
consultant[vbcol=seagreen]
are[vbcol=seagreen]
TABLE
>
|||Create the tables on the publisher the way you want them. Then do the same
on the subscriber. Then sync them up. One option is to use snapshot
replication making sure you use the article property keep the existing table
intact and delete all the data.
When you have finished this, manually change all the identity fields for the
seeds and increments you want. Then do a no sync subscription.
Don't use an increment of 3 as it will overlap with the identity values on
the publisher. Here is an example
Publisher Susbcriber
1 3 - will collide
2 6 - will collide
3
4
5
6
7
You could also try to back up the publication database, and then restore it
on the subscriber. Make the changes to the identity increments/seeds and
then do the nosync subscription.
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:u3Jyn81yEHA.1412@.tk2msftngp13.phx.gbl...
> Different seeds sound like a good solution. Since I expect to have 2
> subscribers, I think I Iike an increment of 3.
> So ... how do I get to that? Even if I delete the existing publication,
> how
> do I get different seeds on the subscriber(s) than on the publisher? The
> default action when the initial snapshot is taken is to DROP and recreate
> the table ... obviously according to the same schema as that on the
> publisher. Am I even looking in the right area?
>
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIz8dn1yEHA.3908@.TK2MSFTNGP12.phx.gbl...
> identity
> again.
> primary/foreign
> They
> what
> some
> it
> consultant
> are
> TABLE
>
|||I think it's possible to do it with increment 3, of course if it is set not
only on subscriber but also on publisher. For example setup like that:
Publisher: Seed = 1 Increment = 3
Publisher identity values: 1, 4, 7, 10, 13...(1+n*3)
Subscriber1: Seed = 2 Increment = 3
Subscriber1 identity values: 2, 5, 8, 11, 14...(2+n*3)
Subscriber2: Seed = 3 Increment = 3
Subscriber2 identity values: 3, 6, 9, 12, 15...(3+n*3)
I agree with Hillary that probably the best option is to configure publisher
database (set NFR flags on identity columns, seed 1, increment 3), backup
it, compress, transfer to subscribers locations, restore, change identity
columns seed to 2 and 3, initialize replication with "no sync"
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u9s4cD2yEHA.2040@.tk2msftngp13.phx.gbl...
> Create the tables on the publisher the way you want them. Then do the same
> on the subscriber. Then sync them up. One option is to use snapshot
> replication making sure you use the article property keep the existing
table
> intact and delete all the data.
> When you have finished this, manually change all the identity fields for
the
> seeds and increments you want. Then do a no sync subscription.
> Don't use an increment of 3 as it will overlap with the identity values on
> the publisher. Here is an example
> Publisher Susbcriber
> 1 3 - will collide
> 2 6 - will collide
> 3
> 4
> 5
> 6
> 7
> You could also try to back up the publication database, and then restore
it[vbcol=seagreen]
> on the subscriber. Make the changes to the identity increments/seeds and
> then do the nosync subscription.
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:u3Jyn81yEHA.1412@.tk2msftngp13.phx.gbl...
recreate[vbcol=seagreen]
a[vbcol=seagreen]
that[vbcol=seagreen]
publisher,
>
|||This is an excellent solution again Kestutis, but are there 2 or 3 sites? I
thought there were only 2, a publisher and a single subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:uDVOQ%236yEHA.1412@.tk2msftngp13.phx.gbl...
> I think it's possible to do it with increment 3, of course if it is set
not
> only on subscriber but also on publisher. For example setup like that:
> Publisher: Seed = 1 Increment = 3
> Publisher identity values: 1, 4, 7, 10, 13...(1+n*3)
> Subscriber1: Seed = 2 Increment = 3
> Subscriber1 identity values: 2, 5, 8, 11, 14...(2+n*3)
> Subscriber2: Seed = 3 Increment = 3
> Subscriber2 identity values: 3, 6, 9, 12, 15...(3+n*3)
> I agree with Hillary that probably the best option is to configure
publisher[vbcol=seagreen]
> database (set NFR flags on identity columns, seed 1, increment 3), backup
> it, compress, transfer to subscribers locations, restore, change identity
> columns seed to 2 and 3, initialize replication with "no sync"
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u9s4cD2yEHA.2040@.tk2msftngp13.phx.gbl...
same[vbcol=seagreen]
> table
> the
on[vbcol=seagreen]
> it
and[vbcol=seagreen]
publication,[vbcol=seagreen]
The[vbcol=seagreen]
> recreate
has[vbcol=seagreen]
> a
and[vbcol=seagreen]
<>[vbcol=seagreen]
sites.[vbcol=seagreen]
know[vbcol=seagreen]
from[vbcol=seagreen]
> that
Server[vbcol=seagreen]
until[vbcol=seagreen]
> publisher,
causing[vbcol=seagreen]
REPLICATION
>
|||Hi , even I am facing the same problem . I have implemented below soluion of
set up of seeds.
Can u pls tell me how to set up replication with nosync . I know there is
one option of when we initiaze the subscription i.e when subscriber has its
own schema and data . But when i choose this option following error come in
merge agent : "Invalid column name 'rowguidcol'."
Pls help
Regards,
Swati
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:uDVOQ#6yEHA.1412@.tk2msftngp13.phx.gbl...
> I think it's possible to do it with increment 3, of course if it is set
not
> only on subscriber but also on publisher. For example setup like that:
> Publisher: Seed = 1 Increment = 3
> Publisher identity values: 1, 4, 7, 10, 13...(1+n*3)
> Subscriber1: Seed = 2 Increment = 3
> Subscriber1 identity values: 2, 5, 8, 11, 14...(2+n*3)
> Subscriber2: Seed = 3 Increment = 3
> Subscriber2 identity values: 3, 6, 9, 12, 15...(3+n*3)
> I agree with Hillary that probably the best option is to configure
publisher[vbcol=seagreen]
> database (set NFR flags on identity columns, seed 1, increment 3), backup
> it, compress, transfer to subscribers locations, restore, change identity
> columns seed to 2 and 3, initialize replication with "no sync"
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u9s4cD2yEHA.2040@.tk2msftngp13.phx.gbl...
same[vbcol=seagreen]
> table
> the
on[vbcol=seagreen]
> it
and[vbcol=seagreen]
publication,[vbcol=seagreen]
The[vbcol=seagreen]
> recreate
has[vbcol=seagreen]
> a
and[vbcol=seagreen]
<>[vbcol=seagreen]
sites.[vbcol=seagreen]
know[vbcol=seagreen]
from[vbcol=seagreen]
> that
Server[vbcol=seagreen]
until[vbcol=seagreen]
> publisher,
causing[vbcol=seagreen]
REPLICATION
>
|||Daniel Wilson wrote:
> Different seeds sound like a good solution. Since I expect to have 2
> subscribers, I think I Iike an increment of 3.
Daniel mentioned 2 subscribers, if I understood correctly from following
sentence.
So this approach of different identity seeds on every "end-point" of
replication topology and identity increment equal to number of "end-points"
of replication is OK for that case. The problems will come if some more
subscribers will have to be added.. Then all system will have to be
reinitialized with the increased number of "replication end-points".
Regards,
Kestas
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:etRzhI9yEHA.336@.TK2MSFTNGP10.phx.gbl...
> This is an excellent solution again Kestutis, but are there 2 or 3 sites?
I
> thought there were only 2, a publisher and a single subscriber.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
|||Hi , Do we have to explicitily restore the whole database . I have tried
import/export option . Rowguidcol and it's property is same for both
subscriber and publisher , so ideally it should not give this error . Pls
suggest .
Now I am trying option 1 : restoration of subscrition database.
Regards,
Swati
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:O0uYaY9yEHA.3844@.TK2MSFTNGP12.phx.gbl...
> When you create merge publication, ROWGUID column is automatically added
to
> all published tables (if there is no column with property ROWGUIDCOL). If
> your subscriber database is restored from a backup of "publisher DB before
> creation of merge publication", then these columns will be missing on
> subscriber DB. You have to make sure that those columns are there - do one
> of the following:
> 1. Backup publisher DB after creation of publication and then restore it
as[vbcol=seagreen]
> a subscriber
> or
> 2. Add ROWGUID columns to all replicated tables on subscriber (make sure
> that this column has all the same properties as on subscriber DB)
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:efTqeK9yEHA.1192@.tk2msftngp13.phx.gbl...
soluion[vbcol=seagreen]
> of
is[vbcol=seagreen]
> its
> in
set[vbcol=seagreen]
> backup
> identity
the[vbcol=seagreen]
existing[vbcol=seagreen]
> for
> values
> restore
have[vbcol=seagreen]
> 2
publisher?[vbcol=seagreen]
the[vbcol=seagreen]
you.[vbcol=seagreen]
publisher[vbcol=seagreen]
2[vbcol=seagreen]
on[vbcol=seagreen]
> start
0x0001[vbcol=seagreen]
> Finding
same
> ALTER
>
|||I'm not totally convinced, and I really hate to quibble with you as your
answers though infrequent are spectacular!
However, if more subscribers are coming into the mix Daniel will have to
implement some form of automatic identity range management.
This would be best deployed by configuring the publication for automatic
identity range management and then deploying the snapshot to each subscriber
on an ad hoq basis.
Granted if there is a known number of subscribers for the lifetime of the
project such a manual partitioning solution such as the one you have
recommended.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:%23bRhYb9yEHA.3836@.TK2MSFTNGP12.phx.gbl...
> Daniel Wilson wrote:
> Daniel mentioned 2 subscribers, if I understood correctly from following
> sentence.
> So this approach of different identity seeds on every "end-point" of
> replication topology and identity increment equal to number of
"end-points"[vbcol=seagreen]
> of replication is OK for that case. The problems will come if some more
> subscribers will have to be added.. Then all system will have to be
> reinitialized with the increased number of "replication end-points".
> --
> Regards,
> Kestas
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:etRzhI9yEHA.336@.TK2MSFTNGP10.phx.gbl...
sites?
> I
>