September 5, 2009 at 4:37 am
We recently have come across an issue when rebuilding a large SQL 2000 replication. The original issue was that the connection is too slow to replicate the original snapshot successfully, so we have copied the snapshot folder across and configured a pull instead of a push subscription (so we can alter the snapshot location and also run the merge agent from the remote server which works really well). This was all done manually through Enterprise Manager.
This is all fine until we come to try to script this as we have a number of identical systems and similar databases we would like to make this standard across. I have followed all MSDN of the instructions to the letter (I think), but when creating the subscription I end up with either the subscription not registered on the publisher or a merge agent created on the publisher too.
I am concerned that if we do not register the subscription, it makes management of subscribers much harder and also am not sure how it will handle identity range management if it does not know who is subscribing.
To script this we are running:
PRINT 'Creating Subscription at subscriber'
--RUN against subscriber
exec sp_addmergepullsubscription @publication = @mypublication, @publisher = @mysourceserver, @publisher_db = @mysourcedb, @subscriber_type = N'global', @subscription_priority = 0.000000, @sync_type = N'automatic'
PRINT 'Creating Agent at subscriber'
--RUN against subscriber
exec sp_addmergepullsubscription_agent @publisher = @mysourceserver, @publisher_db = @mysourcedb, @publication = @mypublication, @subscriber = @mytargetserver, @subscriber_db = @mytargetdb, @distributor = @mysourceserver, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = @mypublicationanddistributorserversapswd,
@subscriber_security_mode = 1, @distributor_security_mode = 0, @distributor_login = N'sa', @distributor_password = @mypublicationanddistributorserversapswd, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @enabled_for_syncmgr = N'false',
@alt_snapshot_folder = @snapshotlocation,
@use_interactive_resolver = N'false', @offloadagent = N'false'
PRINT 'Registering Subscription at Publisher'
--RUN against publisher
exec sp_addmergesubscription @publication = @mypublication, @subscriber = @mytargetserver, @subscriber_db = @mytargetdb, @subscription_type = N'pull'
If we run the first 2 SPs all is OK, but there is no mention of this subscription at the publishing server. On the subscriber however, there appears to be a merge agent and a subscription setup.
However, when we run the 3rd SP it appears as though a Pull subscription is then setup at the publisher, but a merge agent is also (incorrectly?) setup at the distributor...
Has anyone any ideas what I am doing wrong.
September 10, 2009 at 9:03 am
Hi,
Unfortunately, no one seems to have an answer for this issue, so I am really struggling to get this back up and running without manually configuring all our servers. If anyone has any clues, please drop me a line...
I also have one minor correction - the subscriber does create a merge agent, but it is not visible in the merge agent list. It is visible in the jobs list however on the server.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply