Replication methods on unreliable network???

[rico...@gmail.com]

Hi Everyone,

I'm inquiring in the possiblity of using the built-in components/tools
of Microsoft SQL Server 2000 or if necessary, 2005 for replication on
an unreliable network. I have a machine out in the middle of nowhere
that has a local MSDE installed and data from the field goes onto this
machine. In the office, I have a SQL Server 2000 setup and I would
like to synchronize data from the field with the data in the office.

The network connection can be really bad so we usually expect
downtimes. Is there any built-in component that can support
replication on this type of network? I've been looking at Microsoft
Message Queue and I'm wondering if I can incorporate this into it...
If need be, I can also upgrade to SQL Server 2005.

Any comments/suggestions would be much appreciated!

Thanks in advance,

Eric

[Hilary Cotter]

Replication is designed for lossy networks. For merge replication use the
slow link profile. With transactional replication drop your packet size. In
SQL2005 the agents will restart themselves on failure and will autosense a
network connection. This has advantages over SQL 2000 replication.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

<rico...@gmail.com> wrote in message
news:1157659847....@h48g2000cwc.googlegroups.com...

[rico...@gmail.com]

Thanks for the response Hilary.

Here's a little more detail to my situation: there will be 3 scenarios:

1.) tables will be need to be synchronized across multiple fields and
the head office.

2.) tables from multiple fields must be able to update the head office
(one-way data flow)

3.) tables from head office must be able to update the fields (one-way
data flow)

Also, the connection is extremely bad, we're talking about dial-up
connection over satellite phones. Not only is it slow but the
connection disrupts often and must start all over. I've heard of
message based in SQL '05 using service broker for handling data
updates. Hilary, in your opinion, which one do you suggest that we
use?

Thanks so much in advance for all your help!

Eric

[Hilary Cotter]

I would use transactional replication. It looks like its different sets of
tables so you could have the field office subscribers also being publishers
publishing back to the head office.

Replication is resilient to lossy connections. You will have to have the
agents restart themselves as with such lossy connections they will fail
frequently.

While service broker is great for asynchronous messaging, it will not be a
good fit here due to the distributed nature of your environment.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

<rico...@gmail.com> wrote in message
news:1157987045.3...@i42g2000cwa.googlegroups.com...

[rico...@gmail.com]

Thanks Hilary

I'm thinking if I'm going down the route of replication, I would like
to take advantage of the Web Synchronization (as we currently use VPN
in the field to connect). This would require Merge replication.
You're correct, we have different sets of tables for
synchronization/replication, some for synchronization, and others for
one-way replication going downstream and upstream. Can merge
replication acheive that task? Or would Transactional replication be
the only one to do it? Sorry, I'm really new to replication :(

Thank you

Eric

[Paul Ibison]

If you're using merge on SQL Server 2000 the EXCHANGETYPE parameter can
determine unidirectional data flow, while in SQL Server 2005, the
@subscriber_upload_options parameter is an optimisation to be used here.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

[Hilary Cotter]

With SQL 2005 you can use merge replication over https and specify that
certain articles are download only, or you can selectively make your entire
publication upload, download or read only,

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

<rico...@gmail.com> wrote in message
news:1158076022....@d34g2000cwd.googlegroups.com...