Merge Replication Problems

Hi all,
I have a problem by using GPRS in a merge replication.
I have an SQL Servers with 4 publications, these publication have horizontal
filtering, and they have about 20 Articles each of them. I need the
horizontal filtering because the client databases (client laptops use MSDE)
only needs to have the required data.
If I run the replication by using the office network, all of then works
fine. But my problem is by running the replication using GPRS. I have lot of
problems because the connection is broken although there isn't data to be
merged.
Thanks in advance
If the network error is a problem causing the merge agent to fail, then you
can force it to restart: the last step in the merge job should be to return
to step one if it fails.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Automatically is retrying the agent execution, Exactly the error is "General
network error", it seems the connection with distributor is broken when there
is data to be downloaded.
There is any guide to optimize correctly a publication.
I also have change the replication schema, I have configurated an
intermediate database to obtain the required data by a client, and after i
have configurated this database as publiser to send the data to the client
without filters, but when I try to send this data to the client the
replication log says "there isn't data to be downloaded". It is possible to
configure this topology? or it is an erroneus design?
Topology:
ServerDB (publisher) --> Inter.DB (Subscriber/Publisher) --> clientDB
(Subscriber)
Thanks.
|||Please, could anybody help me?
Thanks
|||For the unreliable agent, apart from creating the loop I
referred to earlier, you could change the merge agent's
profile properties to
optimize for an unreliable link - eg reduce
the '...GenerationsPerBatch'
counters.
For the topology, this is vaible and is referred to as
republishing. If you're having issues with it, then
perhaps go down the alternative synchronization partners
route.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have tried to configured the new topology:
ServerDB (publisher) --> Inter.DB (Subscriber/Publisher) --> clientDB
(Subscriber)
But always after I execute the subscription at ClientDB, it says "no data to
be merged", but i have modified data in the Serverdb that has been merged to
the InterDB. I don't understand why. Any idea?
Thanks.
|||OK - I just set this up in the office and it worked fine.
Perhaps you have been put off by the message 'No data
needed to be merged'? This message is the last one to be
recorded and is displayed even when the data has indeed
been merged. Please right-click the merge agent for the
clientDB subscription and examine its history to
determine if the changes really went over.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I have checked the job history, but no data has been trasfered. I have open
tables, but there isn't data.
|||Please can you script it out - the table schema and the 2
publications and subscribers and post it up and I'll take
a look some time tomorrow.
Rgds,
Paul Ibison
|||Table:
CREATE TABLE [dbo].[MST_MANDANTE] (
[ID_CODMANDANTE] [varchar] (3) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[TXT_DESCRIPCION] [varchar] (20) COLLATE Modern_Spanish_CI_AS NULL ,
[ID_CONTROL] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
Publication ServerDB:
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'SFAservidor', @.optname = N'merge
publish', @.value = N'true'
GO
use [SFAservidor]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'SFAservidorMandante',
@.description = N'Merge publication of SFAservidor database from Publisher
PCSAC02.', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true',
@.allow_pull = N'true', @.allow_anonymous = N'false', @.enabled_for_internet =
N'false', @.centralized_conflicts = N'true', @.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'false', @.alt_snapshot_folder = N'd:\snapshot',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.conflict_retention = 14, @.keep_partition_changes = N'false',
@.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false',
@.add_to_active_directory = N'false', @.max_concurrent_merge = 0,
@.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication =
N'SFAservidorMandante',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 500,
@.active_end_time_of_day = 235959, @.snapshot_job_name =
N'PCSAC02-SFAservidor-SFAservidorMandante-21'
GO
exec sp_grant_publication_access @.publication = N'SFAservidorMandante',
@.login = N'BUILTIN\Administradores'
GO
exec sp_grant_publication_access @.publication = N'SFAservidorMandante',
@.login = N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'SFAservidorMandante',
@.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'SFAservidorMandante', @.article =
N'MST_MANDANTE', @.source_owner = N'dbo', @.source_object = N'MST_MANDANTE',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = null,
@.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
Publication InterDB:
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'SFAInt', @.optname = N'merge
publish', @.value = N'true'
GO
use [SFAInt]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'SFAIntMandante', @.description =
N'Merge publication of SFAInt database from Publisher PCSAC02.', @.retention =
14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true',
@.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.centralized_conflicts = N'true', @.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'false', @.alt_snapshot_folder = N'd:\snapshot',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.conflict_retention = 14, @.keep_partition_changes = N'false',
@.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false',
@.add_to_active_directory = N'false', @.max_concurrent_merge = 0,
@.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication =
N'SFAIntMandante',@.frequency_type = 8, @.frequency_interval = 64,
@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 5000,
@.active_end_time_of_day = 0, @.snapshot_job_name =
N'PCSAC02-SFAInt-SFAIntMandante-27'
GO
exec sp_grant_publication_access @.publication = N'SFAIntMandante', @.login =
N'BUILTIN\Administradores'
GO
exec sp_grant_publication_access @.publication = N'SFAIntMandante', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'SFAIntMandante', @.login =
N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'SFAIntMandante', @.article =
N'MST_MANDANTE', @.source_owner = N'dbo', @.source_object = N'MST_MANDANTE',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = null,
@.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
Subscription InterDB:
-- Adding the merge pull subscription: PCSAC02:SFAservidor:SFAservidorMandante
/****** Begin: Script to be run at Subscriber: PCSAC02 ******/
use [SFAInt]
GO
exec sp_addmergepullsubscription @.publication = N'SFAservidorMandante',
@.publisher = N'PCSAC02', @.publisher_db = N'SFAservidor', @.subscriber_type =
N'global', @.subscription_priority = 75.000000, @.sync_type = N'none',
@.description = N'Merge publication of SFAservidor database from Publisher
PCSAC02.'
exec sp_addmergepullsubscription_agent @.publisher = N'PCSAC02',
@.publisher_db = N'SFAservidor', @.publication = N'SFAservidorMandante',
@.distributor = N'PCSAC02', @.subscriber_security_mode = 1,
@.publisher_security_mode = 1, @.distributor_security_mode = 1, @.frequency_type
= 4, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 8,
@.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0,
@.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.enabled_for_syncmgr = N'true', @.alt_snapshot_folder = N'\\pcsac02\snapshot',
@.use_ftp = N'false', @.use_interactive_resolver = N'false', @.offloadagent =
N'false'
GO
/****** End: Script to be run at Subscriber: PCSAC02 ******/
/****** Begin: Script to be run at Publisher: PCSAC02 ******/
use [SFAservidor]
GO
exec sp_addmergesubscription @.publication = N'SFAservidorMandante',
@.subscriber = N'PCSAC02', @.subscriber_db = N'SFAInt', @.subscription_type =
N'pull', @.subscriber_type = N'global', @.subscription_priority = 75.000000,
@.sync_type = N'none'
GO
/****** End: Script to be run at Publisher: PCSAC02 ******/
Subscription ClienDB:
-- Adding the merge pull subscription: PCSAC02:SFAInt:SFAIntMandante
/****** Begin: Script to be run at Subscriber: PCSAC02 ******/
use [sfacliente]
GO
exec sp_addmergepullsubscription @.publication = N'SFAIntMandante',
@.publisher = N'PCSAC02', @.publisher_db = N'SFAInt', @.subscriber_type =
N'local', @.subscription_priority = 0.000000, @.sync_type = N'none',
@.description = N'Merge publication of SFAInt database from Publisher PCSAC02.'
exec sp_addmergepullsubscription_agent @.publisher = N'PCSAC02',
@.publisher_db = N'SFAInt', @.publication = N'SFAIntMandante', @.distributor =
N'PCSAC02', @.subscriber_security_mode = 1, @.publisher_security_mode = 1,
@.distributor_security_mode = 1, @.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'true',
@.alt_snapshot_folder = N'\\pcsac02\snapshot', @.use_ftp = N'false',
@.use_interactive_resolver = N'false', @.offloadagent = N'false'
GO
/****** End: Script to be run at Subscriber: PCSAC02 ******/
/****** Begin: Script to be run at Publisher: PCSAC02 ******/
use [SFAInt]
GO
exec sp_addmergesubscription @.publication = N'SFAIntMandante', @.subscriber =
N'PCSAC02', @.subscriber_db = N'sfacliente', @.subscription_type = N'pull',
@.subscriber_type = N'local', @.subscription_priority = 0.000000, @.sync_type =
N'none'
GO
/****** End: Script to be run at Publisher: PCSAC02 ******/