Merge replication?

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

Merge replication?

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

Merge replication?

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

Merge replication?

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

Merge Replication without network connectivity.

Hi All,
Firstly an apology in advance if this question gets asked 15 times a week -
I'm not normally involved in SQL Server development and a quick scan of the
newsgroup didn't show anything...
Here's the scenario. We will have an application running in a central office
that uses SQL Server. This application will also be distributed to a large
number of remote sites - each with their own copy of the application and
database (possibly MSDE). At irregular intervals they will be asked to
synchronise their data with head office, however there is no network
connectivity AT ALL between these sites (they are really, really remote)
meaning the only way of shipping the changes is via some physial media. At
equally irregular intervals, the head office will ship out the latest copy
of their data which will be merged back to each offices database.
Is this scenario (or anything resembling it) possible using the built-in
features of SQL Server, or should I begin the not-so-fun task of developing
my own solution?
Thanks in Advance,
mr
There is no built in capability. In the intro to SQL Server 2000 Bible Paul
Nielsen mentions someone doing this via email - which you need network
connectivity for.
The name he mentions is Dave Catherman. I can't find any references to him
on the net.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark Richards" <m@.mr.com> wrote in message
news:OdvUwTaGFHA.624@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Firstly an apology in advance if this question gets asked 15 times a
week -
> I'm not normally involved in SQL Server development and a quick scan of
the
> newsgroup didn't show anything...
> Here's the scenario. We will have an application running in a central
office
> that uses SQL Server. This application will also be distributed to a large
> number of remote sites - each with their own copy of the application and
> database (possibly MSDE). At irregular intervals they will be asked to
> synchronise their data with head office, however there is no network
> connectivity AT ALL between these sites (they are really, really remote)
> meaning the only way of shipping the changes is via some physial media. At
> equally irregular intervals, the head office will ship out the latest copy
> of their data which will be merged back to each offices database.
> Is this scenario (or anything resembling it) possible using the built-in
> features of SQL Server, or should I begin the not-so-fun task of
developing
> my own solution?
> Thanks in Advance,
> mr
>
|||Oh well.
Thanks Hilary.
MR
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e4H8IdbGFHA.1932@.TK2MSFTNGP14.phx.gbl...
> There is no built in capability. In the intro to SQL Server 2000 Bible
> Paul
> Nielsen mentions someone doing this via email - which you need network
> connectivity for.
> The name he mentions is Dave Catherman. I can't find any references to him
> on the net.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Mark Richards" <m@.mr.com> wrote in message
> news:OdvUwTaGFHA.624@.TK2MSFTNGP15.phx.gbl...
> week -
> the
> office
> developing
>

Merge Replication in SQL2K5

Normally when snapshot is applied only publisher data is downloaded to
subscriber.
Is there a way to have subscriber upload its table data when
synchronization is done for the first time i.e. when snapshot is applied.
Thanks!!!
Perhaps I've misunderstood you, but when the snapshot is applied, the
subscriber's table will be dropped and the associated data lost. If there is
valuable subscriber data that you don't want to lose, then I'd use a linked
server (or DataCompare) to copy it over before publishing the table from the
publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, Thanks!!!. I want to create the rowguid column at subscriber and start
synchronization without the initial snapshot from publisher.
if i copy the subscribers data to publisher and not apply the initial
snapshot then i think i am ok..
Thanks!!!
"Paul Ibison" <PaulIbison@.discussions.microsoft.com> wrote in message
news:E9219D8F-B39F-4D5B-A20E-1E64477493C9@.microsoft.com...
> Perhaps I've misunderstood you, but when the snapshot is applied, the
> subscriber's table will be dropped and the associated data lost. If there
> is
> valuable subscriber data that you don't want to lose, then I'd use a
> linked
> server (or DataCompare) to copy it over before publishing the table from
> the
> publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You can do a nosync initialization. In this case you'll need to have the
rowguid column on the publisher and subscriber with identical values to start
off with. However, possibly the cleanest and easiest solution is to start
with all the data at the publisher and let replication take care of the
transfer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)