Merge replication?

We have 2 sites which are normally connected by a 10mb fixed line, I'm
looking for a backup should this link fail. Both sites use the same software
connected to a sql server database. If the link fails both sites should be
able to continue in a limited capacity and once the link is restored the
data should synchronise.
I've been looking at this for a little while and merge replication seem to
be the right solution, however I would like to know if anyone could give me
any other options?
Thanks
Daleyou have three options
1) merge
2) transactional replication with queued updating - good where most of the
updates happen on the subscriber
3) bi-directional replication - good when your tables are naturally
partitioned.
Merge is the best solution as you can resolve conflicts. When a conflict
occured with queued you can only view the conflict, you can resolve it in
Conflict Viewer. Queued also works best when most transactions occur on the
publisher, not the subscriber.
Bi-directional replication does not have any mechanism to handle conflicts,
the jobs fail and you have to manually fix everything - similar to queued
really. Bi-directional replication is also harder to do schema changes on.
Merge and Queued add a GUID column to your underlying tables, bi-di
transactional doesn't.
"Dale Pitman" <d@.dd.com> wrote in message
news:_1Xec.185$zC5.123@.newsfe1-win...
> We have 2 sites which are normally connected by a 10mb fixed line, I'm
> looking for a backup should this link fail. Both sites use the same
software
> connected to a sql server database. If the link fails both sites should be
> able to continue in a limited capacity and once the link is restored the
> data should synchronise.
> I've been looking at this for a little while and merge replication seem to
> be the right solution, however I would like to know if anyone could give
me
> any other options?
> Thanks
> Dale
>
>|||AND any conflicts which occur with bi-di means you have a MAJOR data cleanup
situation to deal with. You have to manually reconcile everything. That is
why bi-di is NOT recommended unless you do one of two things:
1. Enforce that transaction ONLY occur on one side or the other
2. There is a partition which ensures that no changes on one side will ever
conflict with changes on the other side
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com|||and how so?
When you get a conflict the distribution agent stops on the side which
detects the conflict. You can then go in and manually fix it. My expireience
is that a single conflict does not mask hundereds of conflicts coming after
it, but rather is a single occurence.
The key is, as both you and I have pointed out, to minimize the chances of
conflicts occuring; but when they do; replication does fail so that you can
fix the conflict and continue.
On the bi-directional systems I have worked on, I have delivered sound
solutions which have been relatively problem free. Bi-directional
transactional is not a good fit for all requirements, but it can work.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u7C5gTeIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> AND any conflicts which occur with bi-di means you have a MAJOR data
cleanup
> situation to deal with. You have to manually reconcile everything. That
is
> why bi-di is NOT recommended unless you do one of two things:
> 1. Enforce that transaction ONLY occur on one side or the other
> 2. There is a partition which ensures that no changes on one side will
ever
> conflict with changes on the other side
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>|||Therein lies the problem. While replication may halt, it may not also. If
you have duplicate primary keys, it will halt. If you update a nonexistent
row, update one side and delete other side, it will halt. It will NOT halt
if you update the same piece of data on both sides to different values. The
last one in will win and you will have zero indication that a previously
saved value has now been overridden which can create severe issues with the
business.
Also, replication halts in two cases. This is a very undesirable thing to
do. You can't exactly halt the transaction flow from applications, so
additional transaction begin piling up on both sides because replication is
halted. This not only dramatically increases latency, but it also has an
increasing potential to generate even more conflicts which can put you in
the same situtation with teh agents halting causing a further backup in
transactions, etc. until you hit the point where someone deems replication
to be completely nonfunctional and starts pointing fingers at the
replication engine. It has nothing to do with the replication engine, but
entirely due to the way the engine is being used and the design being
implemented.
The 2 conditions I outlined are the only way I would EVER implement a bi-di
system. I've been doing this since Sybase Rep Server 1.0 against Sybase
System 10. I've watched more than one bi-di system, 2 of them my own
designs, become complete toast because those 2 conditions were violated.
You get burned enough times, you learn not to keep sticking you hand in the
fire.
If you want to deal with conflicts being thrown in a bi-di system, that is
you choice. I certainly wouldn't want to be the one sitting in that seat,
because I know all too well just what kind of havoc a single conflict can
cause in that type of implementation. I also prefer to not have to babysit
a replication system 24 hours a day because I've placed a design that has a
flaw which can only be overcome with manual intervention and a decision by
an IT person who isn't necessarily qualified to make a call on the data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Merge replication?

We have 2 sites which are normally connected by a 10mb fixed line, I'm
looking for a backup should this link fail. Both sites use the same software
connected to a sql server database. If the link fails both sites should be
able to continue in a limited capacity and once the link is restored the
data should synchronise.
I've been looking at this for a little while and merge replication seem to
be the right solution, however I would like to know if anyone could give me
any other options?
Thanks
Dale
you have three options
1) merge
2) transactional replication with queued updating - good where most of the
updates happen on the subscriber
3) bi-directional replication - good when your tables are naturally
partitioned.
Merge is the best solution as you can resolve conflicts. When a conflict
occured with queued you can only view the conflict, you can resolve it in
Conflict Viewer. Queued also works best when most transactions occur on the
publisher, not the subscriber.
Bi-directional replication does not have any mechanism to handle conflicts,
the jobs fail and you have to manually fix everything - similar to queued
really. Bi-directional replication is also harder to do schema changes on.
Merge and Queued add a GUID column to your underlying tables, bi-di
transactional doesn't.
"Dale Pitman" <d@.dd.com> wrote in message
news:_1Xec.185$zC5.123@.newsfe1-win...
> We have 2 sites which are normally connected by a 10mb fixed line, I'm
> looking for a backup should this link fail. Both sites use the same
software
> connected to a sql server database. If the link fails both sites should be
> able to continue in a limited capacity and once the link is restored the
> data should synchronise.
> I've been looking at this for a little while and merge replication seem to
> be the right solution, however I would like to know if anyone could give
me
> any other options?
> Thanks
> Dale
>
>
|||AND any conflicts which occur with bi-di means you have a MAJOR data cleanup
situation to deal with. You have to manually reconcile everything. That is
why bi-di is NOT recommended unless you do one of two things:
1. Enforce that transaction ONLY occur on one side or the other
2. There is a partition which ensures that no changes on one side will ever
conflict with changes on the other side
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||and how so?
When you get a conflict the distribution agent stops on the side which
detects the conflict. You can then go in and manually fix it. My expireience
is that a single conflict does not mask hundereds of conflicts coming after
it, but rather is a single occurence.
The key is, as both you and I have pointed out, to minimize the chances of
conflicts occuring; but when they do; replication does fail so that you can
fix the conflict and continue.
On the bi-directional systems I have worked on, I have delivered sound
solutions which have been relatively problem free. Bi-directional
transactional is not a good fit for all requirements, but it can work.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u7C5gTeIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> AND any conflicts which occur with bi-di means you have a MAJOR data
cleanup
> situation to deal with. You have to manually reconcile everything. That
is
> why bi-di is NOT recommended unless you do one of two things:
> 1. Enforce that transaction ONLY occur on one side or the other
> 2. There is a partition which ensures that no changes on one side will
ever
> conflict with changes on the other side
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||Therein lies the problem. While replication may halt, it may not also. If
you have duplicate primary keys, it will halt. If you update a nonexistent
row, update one side and delete other side, it will halt. It will NOT halt
if you update the same piece of data on both sides to different values. The
last one in will win and you will have zero indication that a previously
saved value has now been overridden which can create severe issues with the
business.
Also, replication halts in two cases. This is a very undesirable thing to
do. You can't exactly halt the transaction flow from applications, so
additional transaction begin piling up on both sides because replication is
halted. This not only dramatically increases latency, but it also has an
increasing potential to generate even more conflicts which can put you in
the same situtation with teh agents halting causing a further backup in
transactions, etc. until you hit the point where someone deems replication
to be completely nonfunctional and starts pointing fingers at the
replication engine. It has nothing to do with the replication engine, but
entirely due to the way the engine is being used and the design being
implemented.
The 2 conditions I outlined are the only way I would EVER implement a bi-di
system. I've been doing this since Sybase Rep Server 1.0 against Sybase
System 10. I've watched more than one bi-di system, 2 of them my own
designs, become complete toast because those 2 conditions were violated.
You get burned enough times, you learn not to keep sticking you hand in the
fire.
If you want to deal with conflicts being thrown in a bi-di system, that is
you choice. I certainly wouldn't want to be the one sitting in that seat,
because I know all too well just what kind of havoc a single conflict can
cause in that type of implementation. I also prefer to not have to babysit
a replication system 24 hours a day because I've placed a design that has a
flaw which can only be overcome with manual intervention and a decision by
an IT person who isn't necessarily qualified to make a call on the data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
sql

Merge replication?

We have 2 sites which are normally connected by a 10mb fixed line, I'm
looking for a backup should this link fail. Both sites use the same software
connected to a sql server database. If the link fails both sites should be
able to continue in a limited capacity and once the link is restored the
data should synchronise.
I've been looking at this for a little while and merge replication seem to
be the right solution, however I would like to know if anyone could give me
any other options?
Thanks
Dale
you have three options
1) merge
2) transactional replication with queued updating - good where most of the
updates happen on the subscriber
3) bi-directional replication - good when your tables are naturally
partitioned.
Merge is the best solution as you can resolve conflicts. When a conflict
occured with queued you can only view the conflict, you can resolve it in
Conflict Viewer. Queued also works best when most transactions occur on the
publisher, not the subscriber.
Bi-directional replication does not have any mechanism to handle conflicts,
the jobs fail and you have to manually fix everything - similar to queued
really. Bi-directional replication is also harder to do schema changes on.
Merge and Queued add a GUID column to your underlying tables, bi-di
transactional doesn't.
"Dale Pitman" <d@.dd.com> wrote in message
news:_1Xec.185$zC5.123@.newsfe1-win...
> We have 2 sites which are normally connected by a 10mb fixed line, I'm
> looking for a backup should this link fail. Both sites use the same
software
> connected to a sql server database. If the link fails both sites should be
> able to continue in a limited capacity and once the link is restored the
> data should synchronise.
> I've been looking at this for a little while and merge replication seem to
> be the right solution, however I would like to know if anyone could give
me
> any other options?
> Thanks
> Dale
>
>
|||AND any conflicts which occur with bi-di means you have a MAJOR data cleanup
situation to deal with. You have to manually reconcile everything. That is
why bi-di is NOT recommended unless you do one of two things:
1. Enforce that transaction ONLY occur on one side or the other
2. There is a partition which ensures that no changes on one side will ever
conflict with changes on the other side
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||and how so?
When you get a conflict the distribution agent stops on the side which
detects the conflict. You can then go in and manually fix it. My expireience
is that a single conflict does not mask hundereds of conflicts coming after
it, but rather is a single occurence.
The key is, as both you and I have pointed out, to minimize the chances of
conflicts occuring; but when they do; replication does fail so that you can
fix the conflict and continue.
On the bi-directional systems I have worked on, I have delivered sound
solutions which have been relatively problem free. Bi-directional
transactional is not a good fit for all requirements, but it can work.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u7C5gTeIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> AND any conflicts which occur with bi-di means you have a MAJOR data
cleanup
> situation to deal with. You have to manually reconcile everything. That
is
> why bi-di is NOT recommended unless you do one of two things:
> 1. Enforce that transaction ONLY occur on one side or the other
> 2. There is a partition which ensures that no changes on one side will
ever
> conflict with changes on the other side
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||Therein lies the problem. While replication may halt, it may not also. If
you have duplicate primary keys, it will halt. If you update a nonexistent
row, update one side and delete other side, it will halt. It will NOT halt
if you update the same piece of data on both sides to different values. The
last one in will win and you will have zero indication that a previously
saved value has now been overridden which can create severe issues with the
business.
Also, replication halts in two cases. This is a very undesirable thing to
do. You can't exactly halt the transaction flow from applications, so
additional transaction begin piling up on both sides because replication is
halted. This not only dramatically increases latency, but it also has an
increasing potential to generate even more conflicts which can put you in
the same situtation with teh agents halting causing a further backup in
transactions, etc. until you hit the point where someone deems replication
to be completely nonfunctional and starts pointing fingers at the
replication engine. It has nothing to do with the replication engine, but
entirely due to the way the engine is being used and the design being
implemented.
The 2 conditions I outlined are the only way I would EVER implement a bi-di
system. I've been doing this since Sybase Rep Server 1.0 against Sybase
System 10. I've watched more than one bi-di system, 2 of them my own
designs, become complete toast because those 2 conditions were violated.
You get burned enough times, you learn not to keep sticking you hand in the
fire.
If you want to deal with conflicts being thrown in a bi-di system, that is
you choice. I certainly wouldn't want to be the one sitting in that seat,
because I know all too well just what kind of havoc a single conflict can
cause in that type of implementation. I also prefer to not have to babysit
a replication system 24 hours a day because I've placed a design that has a
flaw which can only be overcome with manual intervention and a decision by
an IT person who isn't necessarily qualified to make a call on the data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Merge replication?

We have 2 sites which are normally connected by a 10mb fixed line, I'm
looking for a backup should this link fail. Both sites use the same software
connected to a sql server database. If the link fails both sites should be
able to continue in a limited capacity and once the link is restored the
data should synchronise.
I've been looking at this for a little while and merge replication seem to
be the right solution, however I would like to know if anyone could give me
any other options?
Thanks
Daleyou have three options
1) merge
2) transactional replication with queued updating - good where most of the
updates happen on the subscriber
3) bi-directional replication - good when your tables are naturally
partitioned.
Merge is the best solution as you can resolve conflicts. When a conflict
occured with queued you can only view the conflict, you can resolve it in
Conflict Viewer. Queued also works best when most transactions occur on the
publisher, not the subscriber.
Bi-directional replication does not have any mechanism to handle conflicts,
the jobs fail and you have to manually fix everything - similar to queued
really. Bi-directional replication is also harder to do schema changes on.
Merge and Queued add a GUID column to your underlying tables, bi-di
transactional doesn't.
"Dale Pitman" <d@.dd.com> wrote in message
news:_1Xec.185$zC5.123@.newsfe1-win...
> We have 2 sites which are normally connected by a 10mb fixed line, I'm
> looking for a backup should this link fail. Both sites use the same
software
> connected to a sql server database. If the link fails both sites should be
> able to continue in a limited capacity and once the link is restored the
> data should synchronise.
> I've been looking at this for a little while and merge replication seem to
> be the right solution, however I would like to know if anyone could give
me
> any other options?
> Thanks
> Dale
>
>|||AND any conflicts which occur with bi-di means you have a MAJOR data cleanup
situation to deal with. You have to manually reconcile everything. That is
why bi-di is NOT recommended unless you do one of two things:
1. Enforce that transaction ONLY occur on one side or the other
2. There is a partition which ensures that no changes on one side will ever
conflict with changes on the other side
--
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com|||and how so?
When you get a conflict the distribution agent stops on the side which
detects the conflict. You can then go in and manually fix it. My expireience
is that a single conflict does not mask hundereds of conflicts coming after
it, but rather is a single occurence.
The key is, as both you and I have pointed out, to minimize the chances of
conflicts occuring; but when they do; replication does fail so that you can
fix the conflict and continue.
On the bi-directional systems I have worked on, I have delivered sound
solutions which have been relatively problem free. Bi-directional
transactional is not a good fit for all requirements, but it can work.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u7C5gTeIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> AND any conflicts which occur with bi-di means you have a MAJOR data
cleanup
> situation to deal with. You have to manually reconcile everything. That
is
> why bi-di is NOT recommended unless you do one of two things:
> 1. Enforce that transaction ONLY occur on one side or the other
> 2. There is a partition which ensures that no changes on one side will
ever
> conflict with changes on the other side
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>|||Therein lies the problem. While replication may halt, it may not also. If
you have duplicate primary keys, it will halt. If you update a nonexistent
row, update one side and delete other side, it will halt. It will NOT halt
if you update the same piece of data on both sides to different values. The
last one in will win and you will have zero indication that a previously
saved value has now been overridden which can create severe issues with the
business.
Also, replication halts in two cases. This is a very undesirable thing to
do. You can't exactly halt the transaction flow from applications, so
additional transaction begin piling up on both sides because replication is
halted. This not only dramatically increases latency, but it also has an
increasing potential to generate even more conflicts which can put you in
the same situtation with teh agents halting causing a further backup in
transactions, etc. until you hit the point where someone deems replication
to be completely nonfunctional and starts pointing fingers at the
replication engine. It has nothing to do with the replication engine, but
entirely due to the way the engine is being used and the design being
implemented.
The 2 conditions I outlined are the only way I would EVER implement a bi-di
system. I've been doing this since Sybase Rep Server 1.0 against Sybase
System 10. I've watched more than one bi-di system, 2 of them my own
designs, become complete toast because those 2 conditions were violated.
You get burned enough times, you learn not to keep sticking you hand in the
fire.
If you want to deal with conflicts being thrown in a bi-di system, that is
you choice. I certainly wouldn't want to be the one sitting in that seat,
because I know all too well just what kind of havoc a single conflict can
cause in that type of implementation. I also prefer to not have to babysit
a replication system 24 hours a day because I've placed a design that has a
flaw which can only be overcome with manual intervention and a decision by
an IT person who isn't necessarily qualified to make a call on the data.
--
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Merge replication subscription error with proxy

Ok, perhaps I'm a moron.. believe me, I'd be glad to admit it if I
could just get this fixed.
We're trying to merge replicate data via https, we have installed the
CA certificate and can connect to the replisapi.dll through IE. I used
the following script to generate the subscription:
DECLARE @.publication AS sysname;
DECLARE @.publisher AS sysname;
DECLARE @.publicationDB AS sysname;
DECLARE @.websyncurl AS sysname;
DECLARE @.login AS sysname;
DECLARE @.password AS nvarchar(512);
SET @.publication = N'tables';
SET @.publisher = N'server';
SET @.publicationDB = N'database';
SET @.websyncurl = 'https://webserver/WebSync';
SET @.login = N'websql\user';
Set @.password = N'pass';
USE [LocalDB]
EXEC sp_addmergepullsubscription
@.publisher = @.publisher,
@.publication = @.publication,
@.publisher_db = @.publicationDB,
@.subscriber_type = N'anonymous';
EXEC sp_addmergepullsubscription_agent
@.publisher = @.publisher,
@.publisher_db = @.publicationDB,
@.publication = @.publication,
@.distributor = @.publisher,
@.job_login = @.login,
@.job_password = @.password,
@.use_web_sync = 1,
@.internet_url = @.websyncurl,
@.internet_login = @.login,
@.internet_password = @.password,
@.enabled_for_syncmgr = 'true';
GO
This creates the subscription locally but gives me the error
"websql\user not a valid Windows account". I tried changing the
job_login & job_password to a local account and the subscription was
added without errors. When I go to start the syncronization I get the
error: "The system cannot find the file specified." The underlying
error here is with the proxy. For some reason when it tries to connect
it isn't passing the proper credentials or something.
Anyway, any I'd appreciate any help or direction. If I find out what's
wrong I'll go ahead and post it.
JC
That's because it is creating the agent at the subscriber and when doing so,
you are specifying an account of websql\user which does not exist on the
subscriber. If you change this to a local account and then try to
synchronize, you will get security errors, because the SID of the local
account you set it to does not have a valid scope outside of the local
machine. The job login and password can be a local account and it dosn't
affect anything. However, take a look at the other credential. You are
setting the synchronization context to websql\user and that account, since
it doesn't exist on the local machine can't generate the file which is used
to synch via https. (The more accurate error message would be user doesn't
exist or access denied.)
I don't have a system setup to be able to configure https synchronization
right now. I've done this before and your issue is with the security
context. I just can't remember what I did before to fix this particular
error.
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.
<jbzcooper@.gmail.com> wrote in message
news:1138315588.689076.326990@.g14g2000cwa.googlegr oups.com...
> Ok, perhaps I'm a moron.. believe me, I'd be glad to admit it if I
> could just get this fixed.
> We're trying to merge replicate data via https, we have installed the
> CA certificate and can connect to the replisapi.dll through IE. I used
> the following script to generate the subscription:
> DECLARE @.publication AS sysname;
> DECLARE @.publisher AS sysname;
> DECLARE @.publicationDB AS sysname;
> DECLARE @.websyncurl AS sysname;
> DECLARE @.login AS sysname;
> DECLARE @.password AS nvarchar(512);
> SET @.publication = N'tables';
> SET @.publisher = N'server';
> SET @.publicationDB = N'database';
> SET @.websyncurl = 'https://webserver/WebSync';
> SET @.login = N'websql\user';
> Set @.password = N'pass';
> USE [LocalDB]
> EXEC sp_addmergepullsubscription
> @.publisher = @.publisher,
> @.publication = @.publication,
> @.publisher_db = @.publicationDB,
> @.subscriber_type = N'anonymous';
> EXEC sp_addmergepullsubscription_agent
> @.publisher = @.publisher,
> @.publisher_db = @.publicationDB,
> @.publication = @.publication,
> @.distributor = @.publisher,
> @.job_login = @.login,
> @.job_password = @.password,
> @.use_web_sync = 1,
> @.internet_url = @.websyncurl,
> @.internet_login = @.login,
> @.internet_password = @.password,
> @.enabled_for_syncmgr = 'true';
> GO
> This creates the subscription locally but gives me the error
> "websql\user not a valid Windows account". I tried changing the
> job_login & job_password to a local account and the subscription was
> added without errors. When I go to start the syncronization I get the
> error: "The system cannot find the file specified." The underlying
> error here is with the proxy. For some reason when it tries to connect
> it isn't passing the proper credentials or something.
> Anyway, any I'd appreciate any help or direction. If I find out what's
> wrong I'll go ahead and post it.
> JC
>
|||Mike,
Appreciate the reply and you are correct. I split the credentials into
two, one for the machine and one for the web. This brought up a second
error which I pondered for some time until a colleage suggested I look
at the security permissions for the login. I tried several things but
this is what I was doing when I got it to work:
0. Ensure that windows account SQL run under has proper authority to
execute job
1. Log into windows using the windows account SQL is using
2. Log into SQL using Windows Authentication
3. Execute the above script (after adding a second set of credentials
for the job)
4. Run replication
Some of the above steps may not be necessary.. it just worked for me.
HTH someone else.. man, I was in a bad spot.
JC

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]