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 fail. Show all posts
Showing posts with label fail. 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
Wednesday, March 28, 2012
merge replication with filters
I have a merge replication the last weekend, these fail and send me the
message error "you would reinitialice the snapshot because the snapshot was
obsolete", but whe I try rerun the snapshot, it send me another error like
"i couldn't find the jobid @. x x x x x x x x x ".
whatever I made a new subscription and restore my 26 subscribers, and now my
central store fail because the range of the identity is full and don't want
renew the range I used sp_adjustpublisheridentityrange but nothing happends
some body heard somthing like that , I'm gonna crazy now and doesn't work
I check the MSrepl_identity_range and it is updated but not my subcribers
Regards, David
Sounds like your subscriptions have expired and have been dropped. When you
set your retention period (right click on your publication, select
publication properties, and note the value in teh Subscription expiration
section. This should be something small, but large enough that it will be
longer than a long weekend. So try 5 days.
Then make sure your history is also retained for this period. Right click on
replication monitor, select distributor properties, click on properties, and
then retain the history for 5 days also.
Now your challenge is to make sure your agents run and connect with the
subscriber at least once every 5 days. Put your replication agents in
infinite loops so that they will retry on failure.
That's the good news. The bad news is that you are going to have to redeploy
the snapshot to all of your subscribers again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"David" <davbarquero@.hotmail.com> wrote in message
news:uLTDI$xwEHA.392@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication the last weekend, these fail and send me
the
> message error "you would reinitialice the snapshot because the snapshot
was
> obsolete", but whe I try rerun the snapshot, it send me another error
like
> "i couldn't find the jobid @. x x x x x x x x x ".
> whatever I made a new subscription and restore my 26 subscribers, and now
my
> central store fail because the range of the identity is full and don't
want
> renew the range I used sp_adjustpublisheridentityrange but nothing
happends
> some body heard somthing like that , I'm gonna crazy now and doesn't work
> I check the MSrepl_identity_range and it is updated but not my subcribers
>
> Regards, David
>
message error "you would reinitialice the snapshot because the snapshot was
obsolete", but whe I try rerun the snapshot, it send me another error like
"i couldn't find the jobid @. x x x x x x x x x ".
whatever I made a new subscription and restore my 26 subscribers, and now my
central store fail because the range of the identity is full and don't want
renew the range I used sp_adjustpublisheridentityrange but nothing happends
some body heard somthing like that , I'm gonna crazy now and doesn't work
I check the MSrepl_identity_range and it is updated but not my subcribers
Regards, David
Sounds like your subscriptions have expired and have been dropped. When you
set your retention period (right click on your publication, select
publication properties, and note the value in teh Subscription expiration
section. This should be something small, but large enough that it will be
longer than a long weekend. So try 5 days.
Then make sure your history is also retained for this period. Right click on
replication monitor, select distributor properties, click on properties, and
then retain the history for 5 days also.
Now your challenge is to make sure your agents run and connect with the
subscriber at least once every 5 days. Put your replication agents in
infinite loops so that they will retry on failure.
That's the good news. The bad news is that you are going to have to redeploy
the snapshot to all of your subscribers again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"David" <davbarquero@.hotmail.com> wrote in message
news:uLTDI$xwEHA.392@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication the last weekend, these fail and send me
the
> message error "you would reinitialice the snapshot because the snapshot
was
> obsolete", but whe I try rerun the snapshot, it send me another error
like
> "i couldn't find the jobid @. x x x x x x x x x ".
> whatever I made a new subscription and restore my 26 subscribers, and now
my
> central store fail because the range of the identity is full and don't
want
> renew the range I used sp_adjustpublisheridentityrange but nothing
happends
> some body heard somthing like that , I'm gonna crazy now and doesn't work
> I check the MSrepl_identity_range and it is updated but not my subcribers
>
> Regards, David
>
Wednesday, March 21, 2012
Merge Replication Problems
I am having a heck of a time trying to get merge replication to work. I have two tables that consistently fail validation - even just rowcount after applying a snapshot that I created seconds before and at a time when there is no activity on the tables.
Is there a guide/info somewhere about how to troulbeshoot this? The tables are fairly straightforward (but 35,000 rows) and there are no filters set up. The remote database is connected over the internet and I was hoping to be able to get the two databases to synch continuously.
Also, when I create the merge agent, the wizard doesn't show the schedule option (i.e. whether to run continuously or on a schedule) and therefore I have to set the -continuous option on the merge agent manually.
I have been struggling with this for 2 weeks so any help/pointers would be most appreciated.
After more playing around, it appears that if I create a merge publication to a database that already has a continuous transactional subscription, I don't get the option to set the merge subscription to update the merge subscription continuously. I've now put all my tables into one big merge publication.... hopefully that will work.
Am I on the right track: in sql2000 is there some limitation that doesn't allow a continuous merge and a continuous transactional publication/subscription to and from the same publisher/subscriber database pair?
thanks....
|||I don't know if this is the answer to my own question but: it appears that trying to get continuous merge and continuous transactional replication to coexist from the same publisher database to the same subscription database was my problem.
I have no idea why, nor whether this is documented somewhere, but now I have made all of my publications continuous merge (even though transactional would seem to be the reight choice for most of the tables) gets over all the problems I was having with the merge publications continually failing.
Can anyone confirm this? Or did I unknowingly stumble upon some other fix while reconfiguring all replication from scratch?
|||if you want to do validation, you need to do it when activity on the subscriber and publisher are minimal, otherwise if changes are continuously happening on one end while validation is occuring, there's a chance you'll never be in sync.
If you stop the transactional replication, and stop all other changes, I bet your validation will pass.
Merge Replication Problems
I am having a heck of a time trying to get merge replication to work. I have two tables that consistently fail validation - even just rowcount after applying a snapshot that I created seconds before and at a time when there is no activity on the tables.
Is there a guide/info somewhere about how to troulbeshoot this? The tables are fairly straightforward (but 35,000 rows) and there are no filters set up. The remote database is connected over the internet and I was hoping to be able to get the two databases to synch continuously.
Also, when I create the merge agent, the wizard doesn't show the schedule option (i.e. whether to run continuously or on a schedule) and therefore I have to set the -continuous option on the merge agent manually.
I have been struggling with this for 2 weeks so any help/pointers would be most appreciated.
After more playing around, it appears that if I create a merge publication to a database that already has a continuous transactional subscription, I don't get the option to set the merge subscription to update the merge subscription continuously. I've now put all my tables into one big merge publication.... hopefully that will work.
Am I on the right track: in sql2000 is there some limitation that doesn't allow a continuous merge and a continuous transactional publication/subscription to and from the same publisher/subscriber database pair?
thanks....
|||I don't know if this is the answer to my own question but: it appears that trying to get continuous merge and continuous transactional replication to coexist from the same publisher database to the same subscription database was my problem.
I have no idea why, nor whether this is documented somewhere, but now I have made all of my publications continuous merge (even though transactional would seem to be the reight choice for most of the tables) gets over all the problems I was having with the merge publications continually failing.
Can anyone confirm this? Or did I unknowingly stumble upon some other fix while reconfiguring all replication from scratch?
|||if you want to do validation, you need to do it when activity on the subscriber and publisher are minimal, otherwise if changes are continuously happening on one end while validation is occuring, there's a chance you'll never be in sync.
If you stop the transactional replication, and stop all other changes, I bet your validation will pass.
Friday, March 9, 2012
Merge Replication Fail
Hello
I am face the following problem.
Invalid object name 'sysmergearticles'.
The merge replication is falling please help me.
Best regards,
FDB
Is this failure when setting up replication or did it cause the merge agent
to fail suddenly?
Sysmergearticles is an important system table, Did you apply any service
packs patches before you saw this error?
thanks
gopal
|||This failure happen when i setting up replication.
I have SQL Server 2000 Personal Edition SP3.
My server is a subscriber.
"Gopal Ashok [MS]" <gopal@.online.microsoft.net> wrote in message
news:XJjN7gMIEHA.3568@.cpmsftngxa06.phx.gbl...
> Is this failure when setting up replication or did it cause the merge
agent
> to fail suddenly?
> Sysmergearticles is an important system table, Did you apply any service
> packs patches before you saw this error?
> thanks
> gopal
>
I am face the following problem.
Invalid object name 'sysmergearticles'.
The merge replication is falling please help me.
Best regards,
FDB
Is this failure when setting up replication or did it cause the merge agent
to fail suddenly?
Sysmergearticles is an important system table, Did you apply any service
packs patches before you saw this error?
thanks
gopal
|||This failure happen when i setting up replication.
I have SQL Server 2000 Personal Edition SP3.
My server is a subscriber.
"Gopal Ashok [MS]" <gopal@.online.microsoft.net> wrote in message
news:XJjN7gMIEHA.3568@.cpmsftngxa06.phx.gbl...
> Is this failure when setting up replication or did it cause the merge
agent
> to fail suddenly?
> Sysmergearticles is an important system table, Did you apply any service
> packs patches before you saw this error?
> thanks
> gopal
>
Merge replication fail
Hello
I am face the following problem.
Invalid object name 'sysmergearticles'.
The merge replication is falling please help me.
Best regards,
FDB
Are you getting this while deploying your snapshot?
If so go to your publishing database, expand your table collection, right
click on the table sysmergearticles and select copy. go to your subscribing
database, open up isqlw and change to your database, select Edit, Paste,
execute it, and start your merge agent again
"news.microsoft.com" <fabiobarion@.hotmail.com> wrote in message
news:%23Jhjq6$GEHA.2052@.TK2MSFTNGP12.phx.gbl...
> Hello
> I am face the following problem.
> Invalid object name 'sysmergearticles'.
> The merge replication is falling please help me.
> Best regards,
> FDB
>
I am face the following problem.
Invalid object name 'sysmergearticles'.
The merge replication is falling please help me.
Best regards,
FDB
Are you getting this while deploying your snapshot?
If so go to your publishing database, expand your table collection, right
click on the table sysmergearticles and select copy. go to your subscribing
database, open up isqlw and change to your database, select Edit, Paste,
execute it, and start your merge agent again
"news.microsoft.com" <fabiobarion@.hotmail.com> wrote in message
news:%23Jhjq6$GEHA.2052@.TK2MSFTNGP12.phx.gbl...
> Hello
> I am face the following problem.
> Invalid object name 'sysmergearticles'.
> The merge replication is falling please help me.
> Best regards,
> FDB
>
Subscribe to:
Posts (Atom)