Merge Replication through FTP/Emails

NNTP-Posting-Host: 61.95.221.55
Path: TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.connect:42289 microsoft.public.sqlserver.dts:47716 microsoft.public.sqlserver.programming:440288 microsoft.public.sqlserver.replication:50654 microsoft.public.sqlserver.server:339057 microsoft.public.s
qlserver.xml:23307 microsoft.public.sqlserver.clients:28442
Hi,
I want to use SQL Server replication in a scenario where the Publisher and
multiple subscribers are connected over a very low bandwidth.
Is that possible to write down some scripts/programs which does the
following
1. Create a file containing changes made locally during that day on each of
the subscribers. (This size of this file should be as small as possible)
2. Compress these files and Upload to the Publisher throught FTP or by
emails.
3. Publisher after receiving files from all the subscribers will merge the
changes from each of the subscribers.
4. Publisher then makes a fresh file containing changes for each of the
subscriber. This would contain collective changes from all the subscribers
which are releavant to each of the subscribers.
5. These files are then downloaded by each of the subscribers and the
changes are merged into their copy.
I'd prefer to use it in a scenario where data is partially replicated at
each of the subscribers however even a full replication is better than not
having anything.
Thanks in Advance,
Puneet
Puneet,
you require updating subscribers who have autonomy. In replication topologies, this limits the choice to merge replication or transactional replication with queued subscribers. In either case you can't take the changes and email them - they must be read
by the appropriate agent. You could implement what you want using DTS to export/import CSV files. Obviously it would be a highly customised solution, but I have done similar things this way.
HTH,
Paul Ibison
|||Hi Paul,
I understand it requires an agent which is capable of understanding the
format in which the changes have been recorded and put into the file. The
point I'm trying to make is the requirement that the agent should be able to
do its work using one server at a time. The problem area is access to both
the servers together is not possible, or if it is possible, it is over a
very slow connection.
Using DTS would be a huge task. I strongly feel that it is possible to
develop a standard solution for requirements like mine and hope one already
exists.
Thanks,
Puneet
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:C156E629-4BDE-4EF4-B338-95F1888CA619@.microsoft.com...
> Puneet,
> you require updating subscribers who have autonomy. In replication
topologies, this limits the choice to merge replication or transactional
replication with queued subscribers. In either case you can't take the
changes and email them - they must be read by the appropriate agent. You
could implement what you want using DTS to export/import CSV files.
Obviously it would be a highly customised solution, but I have done similar
things this way.
> HTH,
> Paul Ibison
|||Puneet,
such a solution is not one that I know of in straightforward replication. I
suppose you could cheat a little and use snapshot replication (although this
ends up doing the same as the DTS suggestion). You'd need to flag new
records and reset the flag after replication. If data was partitioned
according to server, then each server could be designated as a publisher,
and records appended to each subscriber. This will only work if the
partitions are strict and data cannot be changed in more than one place.
HTH,
Paul Ibison

merge replication over internet

Hello!
I'm doing replication over non-trusted domains. I'm using FTP server to do
that.
I've also read Paul's articel about replication over non - trusted domains.
I'm using snapshot and merge replication.
Snapshot replication works OK, without any problems, but MERGE replication
gives me this error:
The schema script '' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
The process could not connect to FTP site 'PC' using port 21.
(Source: STREZNIK\SAOP (Agent); Error number: 0)
------
Can you help me,
Uros
Uros,
this type of replication normally falls down due to
configuration/connectivity issues that have to be looked at individually.
Please try using an FTP tool to test transferring the snapshot files.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul!
Does MERGE replication need any different settings than snapshot. Because
snapshot over FTP works without any problems.
What are the most common settings that make troubles for MERGE via FTP?
Thanks.
"Paul Ibison" wrote:

> Uros,
> this type of replication normally falls down due to
> configuration/connectivity issues that have to be looked at individually.
> Please try using an FTP tool to test transferring the snapshot files.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Sorry to ask the obvious, but are your FTP settings done correctly on the
merge publication (inc folder paths).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Well, I've made it as for SNAPSHOT.
Here are my settings:
I have path for ftp \SAOPReplication\ftp
Just the same as for SNAPSHOT.
Should tha path be different?
"Paul Ibison" wrote:

> Sorry to ask the obvious, but are your FTP settings done correctly on the
> merge publication (inc folder paths).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Uros,
please can you post up the results of sp_helpmergepublication and
sp_helppublication for the merge and snapshot publications.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul!
Here are the results of those store procedures.
I hope you can sort this out on notepad. I've tried and it worked.
id name
description
status retention sync_mode allow_push allow_pull allow_anonymous
centralized_conflicts priority snapshot_ready
publication_type pubid snapshot_jobid
enabled_for_internet dynamic_filters has_subscription
snapshot_in_defaultfolder alt_snapshot_folder
pre_snapshot_script
post_snapshot_script
compress_snapshot ftp_address
ftp_port ftp_subdirectory
ftp_login
conflict_retention keep_partition_changes
allow_subscription_copy allow_synctoalternate validate_subscriber_info
backward_comp_level publish_to_activedirectory max_concurrent_merge
max_concurrent_dynamic_snapshots
------
-----------
-- -- -- -- -- --
-- -- --
-- --
-- -- --
-- --
-----------
-----------
-----------
------
-----------
------
-- -- --
-----------
-
-- -- --
1 MERGE_STRANKE_SAOPsf
Merge
publication of SAOPsf database from Publisher PC.
1 14 0 1 1 1
1 100.0 1 0
817F7880-1B66-4C7E-8D8D-1AD3270328B7
0x679F4FFFFA5CAE418DC4919151EBD05A 1 0 0
0 \\PC\D$\SAOP\Replikacije
NULL
NULL
1 PC
21 \SAOPreplikacije\ftp
SQLRemote
14 0 0
0 NULL
40 0
0 0
pubid name
restricted
status task replication frequency synchronization method description
immediate_sync enabled_for_internet allow_push allow_pull
allow_anonymous independent_agent immediate_sync_ready allow_sync_tran
autogen_sync_procs snapshot_jobid retention has
subscription allow_queued_tran snapshot_in_defaultfolder alt_snapshot_folder
pre_snapshot_script
post_snapshot_script
compress_snapshot ftp_address
ftp_port ftp_subdirectory
ftp_login
allow_dts
allow_subscription_copy centralized_conflicts conflict_retention
conflict_policy queue_type backward_comp_level publish_to_AD
------
-- -- -- -- --
-----------
-- -- -- -- --
-- -- -- --
-- -- --
-- --
-----------
-----------
-----------
------
-----------
------
-- -- -- --
-- -- -- --
2 SNAPSHOT_ARTIKLI_SAOPsf
0 1
1 1 0 Snapshot
publication of SAOPsf database from Publisher PC.
1 1 1 1 1
1 1 0 0
0x30961C9177CEEF4AA8EDF9FE69CC7E01 336 1 0
0 \\PC\D$\SAOP\Replikacije
NULL
NULL
1 PC
21 \SAOPReplikacije\ftp
SqlService
0 0 NULL
14 NULL NULL 10 0
|||Can the subscriber resolve the host name PC to the FTP Server? It probably
needs to be a Fully Qualified Domain Name like PC.MyDomain.Com, or you need
a hosts file entry mapping PC to the IP address of the FTP server.
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
"uros" <uros@.discussions.microsoft.com> wrote in message
news:3D947F9B-2DA3-4C75-9860-1401E33EEB3C@.microsoft.com...
> Hello!
> I'm doing replication over non-trusted domains. I'm using FTP server to do
> that.
> I've also read Paul's articel about replication over non - trusted
> domains.
> I'm using snapshot and merge replication.
> Snapshot replication works OK, without any problems, but MERGE replication
> gives me this error:
> The schema script '' could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> The process could not connect to FTP site 'PC' using port 21.
> (Source: STREZNIK\SAOP (Agent); Error number: 0)
> ------
> Can you help me,
> Uros
|||Hilary!
I've done that. I've updated the host file. And as I said, SNAPSHOT
replication works fine via FTP, but merge replication fails with that error.
I don't know, how to continue and I have a deadline.
U.
"Hilary Cotter" wrote:

> Can the subscriber resolve the host name PC to the FTP Server? It probably
> needs to be a Fully Qualified Domain Name like PC.MyDomain.Com, or you need
> a hosts file entry mapping PC to the IP address of the FTP server.
> --
> 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
> "uros" <uros@.discussions.microsoft.com> wrote in message
> news:3D947F9B-2DA3-4C75-9860-1401E33EEB3C@.microsoft.com...
>
>
|||Uros,
sorry - even in notepad this is pretty difficult. Please can you set the
query analyser to output the results to text and use a Pipe delimiter, then
repost the results.
Cheers,
Paul Ibison
"uros" <uros@.discussions.microsoft.com> wrote in message
news:3D470926-AEBC-48BB-9875-F2FA879D2CA0@.microsoft.com...
> Paul!
> Here are the results of those store procedures.
> I hope you can sort this out on notepad. I've tried and it worked.
> id name
> description
>
> status retention sync_mode allow_push allow_pull
> allow_anonymous
> centralized_conflicts priority snapshot_ready
> publication_type pubid snapshot_jobid
> enabled_for_internet dynamic_filters has_subscription
> snapshot_in_defaultfolder alt_snapshot_folder
>
> pre_snapshot_script
>
> post_snapshot_script
>
> compress_snapshot ftp_address
> ftp_port ftp_subdirectory
>
> ftp_login
> conflict_retention keep_partition_changes
> allow_subscription_copy allow_synctoalternate validate_subscriber_info
>
> backward_comp_level publish_to_activedirectory max_concurrent_merge
> max_concurrent_dynamic_snapshots
> --
> ------
> ----------
--
> -- -- -- -- -- --
> -- -- --
> -- --
> -- -- --
> -- --
> ----------
--
> ----------
--
> ----------
--
> --
> ------
> --
> ----------
--
> ------
> -- -- --
> --
> ----------
--
> -- -- --
> --
> 1 MERGE_STRANKE_SAOPsf
> Merge
> publication of SAOPsf database from Publisher PC.
>
> 1 14 0 1 1 1
> 1 100.0 1 0
> 817F7880-1B66-4C7E-8D8D-1AD3270328B7
> 0x679F4FFFFA5CAE418DC4919151EBD05A 1 0 0
> 0 \\PC\D$\SAOP\Replikacije
>
> NULL
>
> NULL
>
> 1 PC
> 21 \SAOPreplikacije\ftp
>
> SQLRemote
> 14 0 0
> 0 NULL
>
> 40
> 0
> 0 0
> pubid name
> restricted
> status task replication frequency synchronization method
> description
>
> immediate_sync enabled_for_internet allow_push allow_pull
> allow_anonymous independent_agent immediate_sync_ready allow_sync_tran
> autogen_sync_procs snapshot_jobid retention has
> subscription allow_queued_tran snapshot_in_defaultfolder
> alt_snapshot_folder
>
> pre_snapshot_script
>
> post_snapshot_script
>
> compress_snapshot ftp_address
> ftp_port ftp_subdirectory
>
> ftp_login
> allow_dts
> allow_subscription_copy centralized_conflicts conflict_retention
> conflict_policy queue_type backward_comp_level publish_to_AD
> --
> ------
> -- -- -- -- --
> ----------
--
> -- -- -- -- --
> -- -- -- --
> -- -- --
> -- --
> ----------
--
> ----------
--
> ----------
--
> --
> ------
> --
> ----------
--
> ------
> -- -- -- --
> -- -- -- --
> 2 SNAPSHOT_ARTIKLI_SAOPsf
> 0
> 1
> 1 1 0 Snapshot
> publication of SAOPsf database from Publisher PC.
>
> 1 1 1 1 1
> 1 1 0 0
> 0x30961C9177CEEF4AA8EDF9FE69CC7E01 336 1 0
> 0 \\PC\D$\SAOP\Replikacije
>
> NULL
>
> NULL
>
> 1 PC
> 21 \SAOPReplikacije\ftp
>
> SqlService
> 0 0 NULL
> 14 NULL NULL 10 0
>