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
Showing posts with label line. Show all posts
Showing posts with label line. Show all posts
Friday, March 30, 2012
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
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
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
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
Monday, March 19, 2012
Merge Replication on SQL Server 2000 -- too slow?
I'm setting up a Merge replication over a very high bandwidth line, and I'm
running into many performance problems.
1. I'm publishing the data one-way from the Publisher to one Subscriber at
this time. Once the data are synchronized, it's taking forever to push delta
data since the snapshot. I changed to high volume Merge profile, and it's
still way behind. Millions of transactions are waiting at the Publisher.
2. After the initial data synchronization, MSMerge_contents at the
Subscriber contains millions of rows. Why is that? There's no activity at
the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
UPDATE data pushing back to the Publisher from the Subscriber. I don't know
why what needs to be updated? There's no application or query running at the
Subscriber. This scares me.
3. Because there's millions of entries in MSMerge_contents table, add/drop
articles from replication causes major problem. What's the best way to
handle this data? I'm replicating 200+ tables and some tables have 10+
millions rows.
Thanks,
Hung
Hi. if you have to do ONE way replication, go for transactional replication
instead; it's way faster than merge replication.
Since it's merge replication, the subscriber db's msmerge_contents table
would grow as every single dml would be recorded into it and would remain
depending upon the retention period u have specified.
Also schedule to defrag the msmerge_contents, msmerge_gen_history,
msmerge_tombstone tables as they are likely to get large.
"Hung" wrote:
> I'm setting up a Merge replication over a very high bandwidth line, and I'm
> running into many performance problems.
> 1. I'm publishing the data one-way from the Publisher to one Subscriber at
> this time. Once the data are synchronized, it's taking forever to push delta
> data since the snapshot. I changed to high volume Merge profile, and it's
> still way behind. Millions of transactions are waiting at the Publisher.
> 2. After the initial data synchronization, MSMerge_contents at the
> Subscriber contains millions of rows. Why is that? There's no activity at
> the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
> UPDATE data pushing back to the Publisher from the Subscriber. I don't know
> why what needs to be updated? There's no application or query running at the
> Subscriber. This scares me.
> 3. Because there's millions of entries in MSMerge_contents table, add/drop
> articles from replication causes major problem. What's the best way to
> handle this data? I'm replicating 200+ tables and some tables have 10+
> millions rows.
> Thanks,
> Hung
>
>
|||well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
pushing it one-way from Production server only. Are you saying that
MSMerge_Contents table at the Subscriber contains all rows from the intial
snapshot as well? If so, can I clear it after the intial snapshot synch
because data are pushing one-way right now from the Publisher? I don't want
or expect to see data merging back from the Subscriber at this point. The
one time I saw data propagating back from the Subscriber, I was scared at
the least and didn't understand why that would be possible.
After the inital data synching from the snapshot, ongoing data synching just
can't seem to keep up. We have a big pipe open between the two servers.
Another scare I had was that I got duplicated data at the Publisher itself.
I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
For that one day, for every row from TableB, there were two identical rows
in TableC. How could this be possible? If the trigger fired on TableA fired
twice for some reason, data from TableB should already be cleared from the
first trigger fire. Merge Replication trigger doesn't move data from TableB
to TableC. I couldn't explain that behavior, and I'm scare to turn
replication back on now. Any idea?
Thanks,
Hung
"T" <T@.discussions.microsoft.com> wrote in message
news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...[vbcol=seagreen]
> Hi. if you have to do ONE way replication, go for transactional
> replication
> instead; it's way faster than merge replication.
> Since it's merge replication, the subscriber db's msmerge_contents table
> would grow as every single dml would be recorded into it and would remain
> depending upon the retention period u have specified.
> Also schedule to defrag the msmerge_contents, msmerge_gen_history,
> msmerge_tombstone tables as they are likely to get large.
> "Hung" wrote:
|||Hi.
The subscriber's msmerge_contents should not contain data from the snapshot
file, it contains transactions after the snapshot.
data being pushed back from subscriber is likely the case with merge
replication and u can't stop this beahvious.
for second prob., you would be getting duplicate records in TableC if it
were also published and trigger also exist at subscriber for TableA.
When record gets inserted in publisher tableC through TableA delete, same
event will fire at subscriber's TableA and will cause the trigger at
Subscriber also insert into TableC.
now when u replicate changes, publisher TableC record merges with
Subscriber's TableC record and you get two records at both sides.
Disable that trigger at subscriber, you will get TableC updated at
subscriber if it's included in replication.
"Hung" wrote:
> well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
> pushing it one-way from Production server only. Are you saying that
> MSMerge_Contents table at the Subscriber contains all rows from the intial
> snapshot as well? If so, can I clear it after the intial snapshot synch
> because data are pushing one-way right now from the Publisher? I don't want
> or expect to see data merging back from the Subscriber at this point. The
> one time I saw data propagating back from the Subscriber, I was scared at
> the least and didn't understand why that would be possible.
> After the inital data synching from the snapshot, ongoing data synching just
> can't seem to keep up. We have a big pipe open between the two servers.
> Another scare I had was that I got duplicated data at the Publisher itself.
> I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
> For that one day, for every row from TableB, there were two identical rows
> in TableC. How could this be possible? If the trigger fired on TableA fired
> twice for some reason, data from TableB should already be cleared from the
> first trigger fire. Merge Replication trigger doesn't move data from TableB
> to TableC. I couldn't explain that behavior, and I'm scare to turn
> replication back on now. Any idea?
> Thanks,
> Hung
> "T" <T@.discussions.microsoft.com> wrote in message
> news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...
>
>
|||"T" <T@.discussions.microsoft.com> wrote in message
news:0AAC406D-BD4B-4F8C-A034-3C454A22AFA5@.microsoft.com...
> Hi.
> The subscriber's msmerge_contents should not contain data from the
> snapshot
> file, it contains transactions after the snapshot.
> data being pushed back from subscriber is likely the case with merge
> replication and u can't stop this beahvious.
>
I'd expect the same thing that the subscriber's msmerge_contents table
should be empty since there's no activity at the Subscriber. But I do get
millions of rows in the Subscriber's msmerge_contents table after the first
initialization. I should have queried back to the source tables to figure
out where these rows in msmerge_contents belong.
I understand about data pushing back from the Subscriber is part of
Merge Replication. In my case, the Subscriber isn't taking any live traffic
from the web or query, so I don't expect the Subscriber to have any data
change to push back to the Publisher. Because after the initialization, the
Subscriber's msmerge_contents contains millions, those entries probably get
pushed back? Again, I don't understand why subscriber's msmerge_contents
would have data. I've tried to clear and reinitialize many times. The one
time where I allowed the Subscriber to push data back, it had millions
UPDATE to upload and with 100 entries batch, that seemed to be eternity.
> for second prob., you would be getting duplicate records in TableC if it
> were also published and trigger also exist at subscriber for TableA.
> When record gets inserted in publisher tableC through TableA delete, same
> event will fire at subscriber's TableA and will cause the trigger at
> Subscriber also insert into TableC.
> now when u replicate changes, publisher TableC record merges with
> Subscriber's TableC record and you get two records at both sides.
> Disable that trigger at subscriber, you will get TableC updated at
> subscriber if it's included in replication.
This is a logical explanation. I don't remember if I allowed Subscriber
to push data back during this time or not. I probably did. Otherwise, it
shouldn't happen. Disabling trigger at the Subscriber would create problem
when both Publisher and Subscriber are Live at the same time. For example,
there's web orders inserting into the Subscriber when it's in production,
then I do want to subscriber's trigger on TableA to move data from TableB to
TableC and upload all data to the Publisher. So, if I disable Subscriber's
trigger, this would create a problem. I thought Merge Replication are
triggers-aware and wouldn't fire twice? It knows that data are pushing from
the Publisher to the Subscriber, therefore, Subscriber's trigger won't fire?
or I misunderstand it. How do I avoid this without disabling Subscriber's
triggers because both servers might be in Production at the same time.
Thanks.
[vbcol=seagreen]
> "Hung" wrote:
running into many performance problems.
1. I'm publishing the data one-way from the Publisher to one Subscriber at
this time. Once the data are synchronized, it's taking forever to push delta
data since the snapshot. I changed to high volume Merge profile, and it's
still way behind. Millions of transactions are waiting at the Publisher.
2. After the initial data synchronization, MSMerge_contents at the
Subscriber contains millions of rows. Why is that? There's no activity at
the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
UPDATE data pushing back to the Publisher from the Subscriber. I don't know
why what needs to be updated? There's no application or query running at the
Subscriber. This scares me.
3. Because there's millions of entries in MSMerge_contents table, add/drop
articles from replication causes major problem. What's the best way to
handle this data? I'm replicating 200+ tables and some tables have 10+
millions rows.
Thanks,
Hung
Hi. if you have to do ONE way replication, go for transactional replication
instead; it's way faster than merge replication.
Since it's merge replication, the subscriber db's msmerge_contents table
would grow as every single dml would be recorded into it and would remain
depending upon the retention period u have specified.
Also schedule to defrag the msmerge_contents, msmerge_gen_history,
msmerge_tombstone tables as they are likely to get large.
"Hung" wrote:
> I'm setting up a Merge replication over a very high bandwidth line, and I'm
> running into many performance problems.
> 1. I'm publishing the data one-way from the Publisher to one Subscriber at
> this time. Once the data are synchronized, it's taking forever to push delta
> data since the snapshot. I changed to high volume Merge profile, and it's
> still way behind. Millions of transactions are waiting at the Publisher.
> 2. After the initial data synchronization, MSMerge_contents at the
> Subscriber contains millions of rows. Why is that? There's no activity at
> the Subscriber. Oddly enough, if I were to turn on two ways merge, I'd get
> UPDATE data pushing back to the Publisher from the Subscriber. I don't know
> why what needs to be updated? There's no application or query running at the
> Subscriber. This scares me.
> 3. Because there's millions of entries in MSMerge_contents table, add/drop
> articles from replication causes major problem. What's the best way to
> handle this data? I'm replicating 200+ tables and some tables have 10+
> millions rows.
> Thanks,
> Hung
>
>
|||well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
pushing it one-way from Production server only. Are you saying that
MSMerge_Contents table at the Subscriber contains all rows from the intial
snapshot as well? If so, can I clear it after the intial snapshot synch
because data are pushing one-way right now from the Publisher? I don't want
or expect to see data merging back from the Subscriber at this point. The
one time I saw data propagating back from the Subscriber, I was scared at
the least and didn't understand why that would be possible.
After the inital data synching from the snapshot, ongoing data synching just
can't seem to keep up. We have a big pipe open between the two servers.
Another scare I had was that I got duplicated data at the Publisher itself.
I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
For that one day, for every row from TableB, there were two identical rows
in TableC. How could this be possible? If the trigger fired on TableA fired
twice for some reason, data from TableB should already be cleared from the
first trigger fire. Merge Replication trigger doesn't move data from TableB
to TableC. I couldn't explain that behavior, and I'm scare to turn
replication back on now. Any idea?
Thanks,
Hung
"T" <T@.discussions.microsoft.com> wrote in message
news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...[vbcol=seagreen]
> Hi. if you have to do ONE way replication, go for transactional
> replication
> instead; it's way faster than merge replication.
> Since it's merge replication, the subscriber db's msmerge_contents table
> would grow as every single dml would be recorded into it and would remain
> depending upon the retention period u have specified.
> Also schedule to defrag the msmerge_contents, msmerge_gen_history,
> msmerge_tombstone tables as they are likely to get large.
> "Hung" wrote:
|||Hi.
The subscriber's msmerge_contents should not contain data from the snapshot
file, it contains transactions after the snapshot.
data being pushed back from subscriber is likely the case with merge
replication and u can't stop this beahvious.
for second prob., you would be getting duplicate records in TableC if it
were also published and trigger also exist at subscriber for TableA.
When record gets inserted in publisher tableC through TableA delete, same
event will fire at subscriber's TableA and will cause the trigger at
Subscriber also insert into TableC.
now when u replicate changes, publisher TableC record merges with
Subscriber's TableC record and you get two records at both sides.
Disable that trigger at subscriber, you will get TableC updated at
subscriber if it's included in replication.
"Hung" wrote:
> well, my goal is to get Merge Repl to work. I'm doing a testbed right now by
> pushing it one-way from Production server only. Are you saying that
> MSMerge_Contents table at the Subscriber contains all rows from the intial
> snapshot as well? If so, can I clear it after the intial snapshot synch
> because data are pushing one-way right now from the Publisher? I don't want
> or expect to see data merging back from the Subscriber at this point. The
> one time I saw data propagating back from the Subscriber, I was scared at
> the least and didn't understand why that would be possible.
> After the inital data synching from the snapshot, ongoing data synching just
> can't seem to keep up. We have a big pipe open between the two servers.
> Another scare I had was that I got duplicated data at the Publisher itself.
> I have an on Insert trigger on TableA to _move_ data from TableB to TableC.
> For that one day, for every row from TableB, there were two identical rows
> in TableC. How could this be possible? If the trigger fired on TableA fired
> twice for some reason, data from TableB should already be cleared from the
> first trigger fire. Merge Replication trigger doesn't move data from TableB
> to TableC. I couldn't explain that behavior, and I'm scare to turn
> replication back on now. Any idea?
> Thanks,
> Hung
> "T" <T@.discussions.microsoft.com> wrote in message
> news:18A1F015-179D-405D-B48A-A95F56C777B7@.microsoft.com...
>
>
|||"T" <T@.discussions.microsoft.com> wrote in message
news:0AAC406D-BD4B-4F8C-A034-3C454A22AFA5@.microsoft.com...
> Hi.
> The subscriber's msmerge_contents should not contain data from the
> snapshot
> file, it contains transactions after the snapshot.
> data being pushed back from subscriber is likely the case with merge
> replication and u can't stop this beahvious.
>
I'd expect the same thing that the subscriber's msmerge_contents table
should be empty since there's no activity at the Subscriber. But I do get
millions of rows in the Subscriber's msmerge_contents table after the first
initialization. I should have queried back to the source tables to figure
out where these rows in msmerge_contents belong.
I understand about data pushing back from the Subscriber is part of
Merge Replication. In my case, the Subscriber isn't taking any live traffic
from the web or query, so I don't expect the Subscriber to have any data
change to push back to the Publisher. Because after the initialization, the
Subscriber's msmerge_contents contains millions, those entries probably get
pushed back? Again, I don't understand why subscriber's msmerge_contents
would have data. I've tried to clear and reinitialize many times. The one
time where I allowed the Subscriber to push data back, it had millions
UPDATE to upload and with 100 entries batch, that seemed to be eternity.
> for second prob., you would be getting duplicate records in TableC if it
> were also published and trigger also exist at subscriber for TableA.
> When record gets inserted in publisher tableC through TableA delete, same
> event will fire at subscriber's TableA and will cause the trigger at
> Subscriber also insert into TableC.
> now when u replicate changes, publisher TableC record merges with
> Subscriber's TableC record and you get two records at both sides.
> Disable that trigger at subscriber, you will get TableC updated at
> subscriber if it's included in replication.
This is a logical explanation. I don't remember if I allowed Subscriber
to push data back during this time or not. I probably did. Otherwise, it
shouldn't happen. Disabling trigger at the Subscriber would create problem
when both Publisher and Subscriber are Live at the same time. For example,
there's web orders inserting into the Subscriber when it's in production,
then I do want to subscriber's trigger on TableA to move data from TableB to
TableC and upload all data to the Publisher. So, if I disable Subscriber's
trigger, this would create a problem. I thought Merge Replication are
triggers-aware and wouldn't fire twice? It knows that data are pushing from
the Publisher to the Subscriber, therefore, Subscriber's trigger won't fire?
or I misunderstand it. How do I avoid this without disabling Subscriber's
triggers because both servers might be in Production at the same time.
Thanks.
[vbcol=seagreen]
> "Hung" wrote:
Monday, March 12, 2012
Merge replication issue - The subscription to publication is invalid
I am trying to configure a merge replication of publication with 151 table-articles over 128 kbps line via proxy (Win XP, WinProxy), which is used for connection to internet as well.
The whole snapshot runs well, all the data are pumped to the subscriber and all dri, trg etc. scripts are executed without problem, but instead of the message "No data needed to be merged" comes out this error message:
Last Command: {call sp_MSgetreplicainfo(?,?,?,?,?,?,?)}
The subscription to publication 'Katalog' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
--------------------------
General network error. Check your network documentation.
(Source: FANTOM (Data source); Error number: 11)
--------------------------
When I try to replicate the small Northwind database, everything works fine, no errors. When I try to replicate only a few tables from my big database, everything works fine.
Thank you very much for any ideas.The problem is probably in WinProxy. When comunicating without Proxy everything goes OK.|||Hi,
I had seen your psting and asking your help.
How do I use the FTP option in Merge Replication
I am using Merge Replication on SqlServer 2000 with SP2 and MDAC2.7 loaded on my system.
My database version is 8.00.534 on Windows2000 Advancedserver.
Both Publisher and Subscriber are running with same versions.
I created a publication and I created a pull subscription using internet.
I want the subscriber machine to access the Publisher
machine via ftp and get the Snapshot and do
Synchronization.
I had setup the following parameters at the Publisher side
for the publication.
1.Generate snapshots in the following location as
\\myservername\D$\Microsoft SQL Server\MSSQL\REPLDATA\ftp which is UNC shared with full access.
2.Checked - Subscribers can access this folder using FTP
ftpservername -myservername
port-21
Login-Anonymous, no password
3.At subscriber side I had setup SnapshotLocation property as
Download the folder using File Transfer Protocol - checked
I get the following errors at Synchronization time
After configuring subscriber for merge replication i am getting staus as succeeded at the subscriber.
when performing data modifications I am getting following errors during synchronisation time.
Errors
Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script '' could not be propagated to the subscriber.
Category:AGENT
Source: SRVH
Number: 0
Message: The process could not connect to FTP site 'HERITAGEAMERICA' using port 21.
Category:OS
Source:
Number: 12007
Message: The server name or address could not be resolved
The schema script '' could not be propagated to the subscriber. The step failed.
Microsoft conformed this is a bug and that details are
Question number is Q318592.
BUG #: 356652 (SHILOH_BUGS) BUG #: 356652 (SHILOH_BUGS)
Microsoft confirmed that this is a bug when we use different versions check with the Question in microsoft site in fixes.
I am getting same error for same versions also.
Can anyone help me on how to use FTP for accessing
Snapshots?
Thanks|||Hello,
I have not any experience with using FTP for merge replication over internet, we are using VPN connection to the server on port 1433.
But as far as I understood, you do not get the error message when transferring a snapshot but at the moment, when the distributor needs to propagate data changes between publisher and subscriber.
I suppose it is important when do you get the error message: whether on the very beginning of applying the snapshot or at the end.
Also would be useful to check, whether the FTP connection is reliable (proxies, switches etc.).
Caruso|||Hi
May be you can help me wit a minor problem in Merge replication. I am trying to perform merge replication on two SQL Server 2000 databases on Windows Nt 4.0 platform using TCP/IP and FTp. Have set everything in Publication and Subscriber for FTp and the subscriber can connect to publisher.
I am getting the followiing error message afetr running replmerg.exe withh all required parameters for FTP.
Microsoft SQL Server Merge Agent 8.00.194
Copyright (c) 2000 Microsoft Corporation
Connecting to Subscriber 'RAJ'
Connecting to Distributor 'VTPL'
Initializing
Connecting to Publisher 'VTPL'
Retrieving publication information
Retrieving subscription information
Snapshot files will be downloaded via ftp
Connecting to ftp site 'VTPL'
The subscription has been marked for reinitialization. Reinitialization will occur the next time you synchronize this subscription.
The schema script '\\VTPL\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\ftp\VTPL_Caryaire_F2\2003010 7120306\RightMain_1.sch' could not be propagated to the subscriber.
Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script '\\VTPL\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\ftp\VTPL_Caryaire_F2\2003010 7120306\RightMain_1.sch' could not be propagated to the subscriber.
Category:AGENT
Source: VTPL
Number: 0
Message: The process could not retrieve file 'VTPL_Caryaire_F2\20030107120306\RightMain_1.sch' from the FTP site 'VTPL'.
Category:OS
Source:
Number: 12003
Message: 200 Type set to I.
200 PORT command successful.
550 VTPL_Caryaire_F2\20030107120306\RightMain_1.sch: The system cannot find the path specified.
550 VTPL_Caryaire_F2\20030107120306\RightMain_1.sch: The system cannot find the path specified.
The process finished. Use CTRL+C to close this window.
I am desparately in need of a solution. Can anyone help me out?
Thanks and regards,
Anamika|||Hi,
Anamika, first thing I would like you to do is check the verison of SQL server 2000. Merge Replication module has some problems. It have been fixed in the SQL server SP3. It is essential to apply this pack on SQL server.
Secondly you need to check ftp settings. your ftp service or root directory of ftp is not configured properly. Due to this you are getting "The process could not retrieve file 'VTPL_Caryaire_F2\20030107120306\RightMain_1.sch' from the FTP site 'VTPL'. "VTPL_Caryaire_F2" parent folder should be root of ftp site.
Details are available in windows or in IIS about ftp configuration.
The whole snapshot runs well, all the data are pumped to the subscriber and all dri, trg etc. scripts are executed without problem, but instead of the message "No data needed to be merged" comes out this error message:
Last Command: {call sp_MSgetreplicainfo(?,?,?,?,?,?,?)}
The subscription to publication 'Katalog' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
--------------------------
General network error. Check your network documentation.
(Source: FANTOM (Data source); Error number: 11)
--------------------------
When I try to replicate the small Northwind database, everything works fine, no errors. When I try to replicate only a few tables from my big database, everything works fine.
Thank you very much for any ideas.The problem is probably in WinProxy. When comunicating without Proxy everything goes OK.|||Hi,
I had seen your psting and asking your help.
How do I use the FTP option in Merge Replication
I am using Merge Replication on SqlServer 2000 with SP2 and MDAC2.7 loaded on my system.
My database version is 8.00.534 on Windows2000 Advancedserver.
Both Publisher and Subscriber are running with same versions.
I created a publication and I created a pull subscription using internet.
I want the subscriber machine to access the Publisher
machine via ftp and get the Snapshot and do
Synchronization.
I had setup the following parameters at the Publisher side
for the publication.
1.Generate snapshots in the following location as
\\myservername\D$\Microsoft SQL Server\MSSQL\REPLDATA\ftp which is UNC shared with full access.
2.Checked - Subscribers can access this folder using FTP
ftpservername -myservername
port-21
Login-Anonymous, no password
3.At subscriber side I had setup SnapshotLocation property as
Download the folder using File Transfer Protocol - checked
I get the following errors at Synchronization time
After configuring subscriber for merge replication i am getting staus as succeeded at the subscriber.
when performing data modifications I am getting following errors during synchronisation time.
Errors
Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script '' could not be propagated to the subscriber.
Category:AGENT
Source: SRVH
Number: 0
Message: The process could not connect to FTP site 'HERITAGEAMERICA' using port 21.
Category:OS
Source:
Number: 12007
Message: The server name or address could not be resolved
The schema script '' could not be propagated to the subscriber. The step failed.
Microsoft conformed this is a bug and that details are
Question number is Q318592.
BUG #: 356652 (SHILOH_BUGS) BUG #: 356652 (SHILOH_BUGS)
Microsoft confirmed that this is a bug when we use different versions check with the Question in microsoft site in fixes.
I am getting same error for same versions also.
Can anyone help me on how to use FTP for accessing
Snapshots?
Thanks|||Hello,
I have not any experience with using FTP for merge replication over internet, we are using VPN connection to the server on port 1433.
But as far as I understood, you do not get the error message when transferring a snapshot but at the moment, when the distributor needs to propagate data changes between publisher and subscriber.
I suppose it is important when do you get the error message: whether on the very beginning of applying the snapshot or at the end.
Also would be useful to check, whether the FTP connection is reliable (proxies, switches etc.).
Caruso|||Hi
May be you can help me wit a minor problem in Merge replication. I am trying to perform merge replication on two SQL Server 2000 databases on Windows Nt 4.0 platform using TCP/IP and FTp. Have set everything in Publication and Subscriber for FTp and the subscriber can connect to publisher.
I am getting the followiing error message afetr running replmerg.exe withh all required parameters for FTP.
Microsoft SQL Server Merge Agent 8.00.194
Copyright (c) 2000 Microsoft Corporation
Connecting to Subscriber 'RAJ'
Connecting to Distributor 'VTPL'
Initializing
Connecting to Publisher 'VTPL'
Retrieving publication information
Retrieving subscription information
Snapshot files will be downloaded via ftp
Connecting to ftp site 'VTPL'
The subscription has been marked for reinitialization. Reinitialization will occur the next time you synchronize this subscription.
The schema script '\\VTPL\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\ftp\VTPL_Caryaire_F2\2003010 7120306\RightMain_1.sch' could not be propagated to the subscriber.
Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script '\\VTPL\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\ftp\VTPL_Caryaire_F2\2003010 7120306\RightMain_1.sch' could not be propagated to the subscriber.
Category:AGENT
Source: VTPL
Number: 0
Message: The process could not retrieve file 'VTPL_Caryaire_F2\20030107120306\RightMain_1.sch' from the FTP site 'VTPL'.
Category:OS
Source:
Number: 12003
Message: 200 Type set to I.
200 PORT command successful.
550 VTPL_Caryaire_F2\20030107120306\RightMain_1.sch: The system cannot find the path specified.
550 VTPL_Caryaire_F2\20030107120306\RightMain_1.sch: The system cannot find the path specified.
The process finished. Use CTRL+C to close this window.
I am desparately in need of a solution. Can anyone help me out?
Thanks and regards,
Anamika|||Hi,
Anamika, first thing I would like you to do is check the verison of SQL server 2000. Merge Replication module has some problems. It have been fixed in the SQL server SP3. It is essential to apply this pack on SQL server.
Secondly you need to check ftp settings. your ftp service or root directory of ftp is not configured properly. Due to this you are getting "The process could not retrieve file 'VTPL_Caryaire_F2\20030107120306\RightMain_1.sch' from the FTP site 'VTPL'. "VTPL_Caryaire_F2" parent folder should be root of ftp site.
Details are available in windows or in IIS about ftp configuration.
Saturday, February 25, 2012
Merge Replication Conflict Problem
exec sp_helpmergeconflictrows @.conflict_table = 'CMCustomer'
Msg 207, Level 16, State 1, Line 1
Invalid column name 'origin_datasource_id'.
===================================
CMCustomer is neither a DataColumn nor a DataRelation for table summary. (System.Data)
Program Location:
at System.Data.DataRowView.get_Item(String property)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.GetWinnerSQL(DataRow loserRow, Boolean blockFetch)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.GetSourceRow()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.FillDetailData()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.FillDetail()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.summaryInfoGrid_SelectionChanged(Object sender, SelectionChangedEventArgs args)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.OnSelectionChanged(BlockOfCellsCollection selectedCells)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.SelectedCellsInternal(BlockOfCellsCollection col, Boolean bSet)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.set_SelectedCells(BlockOfCellsCollection value)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.ResetSummaryGrid()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.LoadConflict()
When I try to "Remove" then conflict (Conflict type - "4(Update/Delete, Update wins)") I receive the following...
===================================
Column 'CMCustomer' does not belong to table summary. (System.Data)
Program Location:
at System.Data.DataRow.GetDataColumn(String columnName)
at System.Data.DataRow.get_Item(String columnName)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.SetLogInfo(DataRow logRow, String sql)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.btnClearUpper_Click(Object sender, EventArgs eg)
Grant,
Please let me know if my assumptions are incorrect:
I believe CMCustomer is a rowguid column.
This column was added by the user and snapshot did not add this.
This happens on delete conflicts.
If these are correct, then it is a known issue and should be fixed in SP2.
|||Yes, CMCustomer is the rowguidcol of the CMCustomer table.
"This column was added by the user and snapshot did not add this." Are you saying the column metadata changed at some point?
|||No, what I am saying is when you created the table with this column and it was of type uniqueidentifier and it was designated as the rowguidcol. In case such a column does not exist in the table, snapshot agent will add one and name it as a rowguid. The bug happens because the name is not rowguid and UI was trying to find a column with this name.|||Ok. Thanks.
Subscribe to:
Posts (Atom)