May 8, 2008 at 2:40 pm
I have setup transactional replication from a publisher/distributer (same box) on a domain over to an offsite standalone machine that is a subscriber. It has a dedicated line from one box to the other and no firewall in between.
The transactional replication is a pull subscription. There is a share on the publisher which is used for a snapshot folder. I set up a user, .\user1 on the subscriber that runs the SQL Server agent on the subscriber. I setup a user on the publisher, .\user1, that has the same password as the .\user1 on the subscriber. The .\user1 has read,change permissions on the snapshot folder. Is there anyway to apply a snapshot using the passthrough authentication? Or is using ftp the only way to apply a snapshot in this scenario?
It is running fine since I have set it up, but I never initialized the database with a snapshot. If I need to run the snapshot when items get way out of sync for any reason is this possible?
May 9, 2008 at 9:45 am
Sorry to go off topic, but how exactly do you (re)start a replication without a snapshot? I know it is possible, but cannot figure out all the steps necessary to script this out.
May 9, 2008 at 9:55 am
You manually take a backup and restore it to the subscriber. Drop and re-add the subscription and when you re-add the subscription uncheck the initialize button and this allows you to bypass the initial snapshot to sync the replication.
May 9, 2008 at 10:23 am
Thanks for your response.
Are you using SS2005? I'm unable to uncheck the initialize button in the 2005 wizard (haven't tried Enterprise Manager from another box), which is why I'm trying to script the process.
Also, an added complication is that I would like to either not use a backup or use a backup from the subscriber as my replication stored procedures have been altered and the publisher database and the subscribers are in no way synced due to these changes.
It sounds like you are saying that I can simply drop the subscription, restore the subscriber from a backup and then restart the subscription using something like the following:
use [Publisher]
exec sp_addsubscription @publication = N'PublishMe', @subscriber = N'HQ5', @destination_db = N'Subscriber2', @subscription_type = N'Push', @sync_type = N'none', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PublishMe', @subscriber = N'HQ5', @subscriber_db = N'Subscriber2', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'sa', @subscriber_password = null, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20080328, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
where the key piece being @sync_type = N'none'. Is this correct? Thanks again!
May 12, 2008 at 12:43 pm
Okay, this actually worked with a slight modification. The above sql started the replication over but all of the replication stored procedures were missing. So it initially looked like nothing was being replicated. Once I recreated the necessary stored procedures everything worked like a dream.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply