SQL Server 2005 Peer to Peer replication script problem
-
I set up 3 servers (A,B,C) database p2p replication and generate replication scripts on each server.
I assume server A is crash (delete database A), and backup form server B and restore on server A.
When I use the SQL Server Management Studio configure the p2p for server A again, it's working pretty good. However, when I run the script which I generated on server A, it's not working well. When I update data on server A, same change on server B, C; when I update data on server B,C, it cannot impact on server A. It seems on server A, publichion is working and subscribtion is not working.
Thanks for any helps!
-Bob
Following is the generated script on server A:
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Begin: Script to be run at Publisher: WS3A84VDM01 ******/
/****** Installing the server WS3A84VDM01 as a Distributor. Script Date: 9/10/2007 5:08:59 PM ******/
use
master
exec
sp_adddistributor @distributor = N'WS3A84VDM01', @password = N''
GO
-- Adding the agent profiles
-- Updating the agent profile defaults
exec
sp_MSupdate_agenttype_default @profile_id = 1
GO
exec
sp_MSupdate_agenttype_default @profile_id = 2
GO
exec
sp_MSupdate_agenttype_default @profile_id = 4
GO
exec
sp_MSupdate_agenttype_default @profile_id = 6
GO
exec
sp_MSupdate_agenttype_default @profile_id = 11
GO
-- Adding the distribution databases
use
master
exec
sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data', @data_file = N'distribution.MDF', @data_file_size = 68, @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data', @log_file = N'distribution.LDF', @log_file_size = 38, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
exec
[distribution].sys.sp_addqreader_agent @job_login = N'ws3a84vdm01\administrator', @job_password = 'admin123456'
GO
GO
-- Adding the distribution publishers
exec
sp_adddistpublisher @publisher = N'WS3A84VDM01', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'\\ws3a84vdm01\Share\Sql 2005 ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
exec
sp_adddistpublisher @publisher = N'WS3A84VDM02', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'\\ws3a84vdm02\Share\Sql 2005 ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
exec
sp_addsubscriber @subscriber = N'WS3A84VDM02', @type = 0, @description = N''
GO
exec
sp_addsubscriber @subscriber = N'WS3Y96WDEVMGR', @type = 0, @description = N''
GO
/****** End: Script to be run at Publisher: WS3A84VDM01 ******/
-- Enabling the replication database
use
master
exec
sp_replicationdboption @dbname = N'P2PScriptImplement', @optname = N'publish', @value = N'true'
GO
exec
[P2PScriptImplement].sys.sp_addlogreader_agent @job_login = N'ws3a84vdm01\administrator', @job_password = 'admin123456', @publisher_security_mode = 1
GO
exec
[P2PScriptImplement].sys.sp_addqreader_agent @job_login = N'ws3a84vdm01\administrator', @job_password = 'admin123456', @frompublisher = 1
GO
-- Adding the transactional publication
use
[P2PScriptImplement]
exec
sp_addpublication @publication = N'P_P2PScriptImplement', @description = N'Transactional publication of database ''P2PScriptImplement'' from Publisher ''WS3A84VDM02''.', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'true', @enabled_for_p2p = N'true', @enabled_for_het_sub = N'false'
GO
exec
sp_addpublication_snapshot @publication = N'P_P2PScriptImplement', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
exec
sp_grant_publication_access @publication = N'P_P2PScriptImplement', @login = N'sa'
GO
exec
sp_grant_publication_access @publication = N'P_P2PScriptImplement', @login = N'NT AUTHORITY\SYSTEM'
GO
exec
sp_grant_publication_access @publication = N'P_P2PScriptImplement', @login = N'BUILTIN\Administrators'
GO
exec
sp_grant_publication_access @publication = N'P_P2PScriptImplement', @login = N'WS3A84VDM01\SQLServer2005SQLAgentUser$WS3A84VDM01$MSSQLSERVER'
GO
exec
sp_grant_publication_access @publication = N'P_P2PScriptImplement', @login = N'WS3A84VDM01\SQLServer2005MSSQLUser$WS3A84VDM01$MSSQLSERVER'
GO
exec
sp_grant_publication_access @publication = N'P_P2PScriptImplement', @login = N'distributor_admin'
GO
-- Adding the transactional articles
use
[P2PScriptImplement]
exec
sp_addarticle @publication = N'P_P2PScriptImplement', @article = N'Email', @source_owner = N'dbo', @source_object = N'Email', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Email', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboEmail1262369878]', @del_cmd = N'CALL [sp_MSdel_dboEmail1262369878]', @upd_cmd = N'SCALL [sp_MSupd_dboEmail1262369878]'
GO
use
[P2PScriptImplement]
exec
sp_addarticle @publication = N'P_P2PScriptImplement', @article = N'UserInfo', @source_owner = N'dbo', @source_object = N'UserInfo', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'UserInfo', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboUserInfo1206736387]', @del_cmd = N'CALL [sp_MSdel_dboUserInfo1206736387]', @upd_cmd = N'SCALL [sp_MSupd_dboUserInfo1206736387]'
GO
-- Adding the transactional subscriptions
use
[P2PScriptImplement]
exec
sp_addsubscription @publication = N'P_P2PScriptImplement', @subscriber = N'WS3A84VDM02', @destination_db = N'P2PScriptImplement', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec
sp_addpushsubscription_agent @publication = N'P_P2PScriptImplement', @subscriber = N'WS3A84VDM02', @subscriber_db = N'P2PScriptImplement', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'sa', @subscriber_password = 'P@ssword123', @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
use
[P2PScriptImplement]
exec
sp_addsubscription @publication = N'P_P2PScriptImplement', @subscriber = N'WS3Y96WDEVMGR', @destination_db = N'P2PScriptImplement', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec
sp_addpushsubscription_agent @publication = N'P_P2PScriptImplement', @subscriber = N'WS3Y96WDEVMGR', @subscriber_db = N'P2PScriptImplement', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'sa', @subscriber_password = 'P@ssword123', @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
-
I assume the passwords you put in the script are not your production passwords?
Fire up replication monitor on server a. When you make a change on server a, do you see a transaction/command delivered in the Publisher To Distribution History tab? If so, then check the Distributor To Subscriber History tab. If you see it in the Distributor To Subscriber History tab then server A is executing the associated SP on server b/c, so check with profiler to see why it is not having the effect you want.
In my config I explicitly set @status=N'active' in sp_addsubscription, but I do see that BOL says it should default to it for @sync_type of 'replication support only'. You might double check the status though..
-
The passwords is correct.
I already figure it out. For the sys.sp_addloggreader_agent, I explicitly add @publisher_loging and @publisher_password. I also add explicitly @status=N'active' in sp_addsubscription.
Thank for your suggestion!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply