asked on

Production replication issue,Need urgent advice

I have breaked the replication and restore the database.
Before I broke,i did -right click on replication folder -> generated the script. i have aslo re-created logins.No orpan users in the database. But when i tried re-executing the replication scripts, I gives error like below.

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
 
Creating distribution tables
 
Creating table MSrepl_version
Creating table MSpublisher_databases
Creating clustered index ucMSpublisher_databases
Creating table MSpublications
Creating clustered index ucMSpublications
Creating table MSarticles
Creating clustered index ucMSarticles
Creating table MSsubscriptions
Creating clustered index ucMSsubscirptions
Creating index iMSsubscriptions
Creating index iMSsubscriptions2
Creating table MSmerge_subscriptions
Creating clustered index ucMSmerge_subscriptions
Creating table MSrepl_transactions
Creating clustered index usMSrepl_transactions
Creating table MSrepl_commands
Creating clusterd index ucMSrepl_commands
Creating table MSrepl_orginators
Creating clustered index usMSrepl_originators
Creating table MSsubscriber_info
Creating clustered index ucMSsubscriber_info
Creating table MSsubscriber_schedule
Creating table MSsnapshot_history
Creating clustered index ucMSsnapshot_history
Creating table MSlogreader_history
Creating clustered index ucMSlogreader_history
Creating table MSdistribution_history
Creating clustered index ucMSdistribution_history
Creating table MSsnapshot_agents
Creating clustered index ucMSsnapshot_agents
Creatingindex iMSsnapshot_agents
Creating table MSlogreader_agents
Creating clustered index ucMSlogreader_agents
Creatingindex iMSlogreader_agents
Creating table MSdistribution_agents
Creating clustered index ucMSdistribution_agents
Creatingindex iMSdistribution_agents
Creating table MSmerge_agents
Creating clustered index ucMSmerge_agents
Creating table MSrepl_identity_range
Creating table MSpublication_access
Creating clustered index ucMSpublication_access
Creating table MSqreader_agents
Creating unique index ucMSqreader_agents
Creating table MSqreader_history
Creating clustered index ucMSqreader_history
Creating table MSrepl_backup_lsns
Creating clustered index ucMSrepl_backup_lsns
Creating table MSpublicationthresholds
Creating clustered index ucmspublicationthresholds
Creating table IHpublishers
Creating table IHpublishertables
Creating table IHarticles
Creating table IHpublishercolumns
Creating table IHcolumns
Creating table IHindextypes
Creating table IHpublisherindexes
Creating table IHpublishercolumnindexes
Creating table IHpublications
Creating table IHextendedArticleView
Creating table IHconstrainttypes
Creating table IHpublisherconstraints
Creating table IHpublishercolumnconstraints
Creating table IHsubscriptions
Creating table sysschemaarticles
Creating table MScached_peer_lsns
Creating view IHextendedSubscriptionView
Creating view syssubscriptions
Creating view syspublications
Creating view sysarticles
Creating view sysarticlecolumns
Creating view IHsyscolumns
 
Dropping all distribution stored procedures and functions that are now in resource or are obsolete
 
 
Dropping all distribution stored procedures and functions that are created locally
 
Creating 'fn_MSmask_agent_type'.
Creating 'sp_MSset_syncstate'.
Creating 'sp_MSadd_repl_commands27'.
Creating 'sp_MSadd_replcmds'.
Creating 'sp_MSremove_published_jobs'.
Creating 'sp_MSsubscription_cleanup'.
Creating 'sp_MSdelete_dodelete'.
Creating 'sp_MSdelete_publisherdb_trans'.
Creating 'sp_MSmaximum_cleanup_seqno'.
Creating 'sp_MSdistribution_delete'.
Creating 'sp_MSdistribution_cleanup'.
Creating 'sp_MShistory_cleanup'.
Creating 'sp_MSget_repl_version'.
Creating view MSdistribution_status
Creating 'sp_MSlog_agent_cancel'.
 
Adding user 'guest'.
 
 
Adding role 'replmonitor'.
 
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 21678, Level 16, State 1, Procedure sp_addqreader_agent, Line 106
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".
Msg 21678, Level 16, State 1, Procedure sp_MSrepl_addlogreader_agent, Line 102
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".
Msg 21678, Level 16, State 1, Procedure sp_addqreader_agent, Line 106
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".
Job 'publisher-SSM_OLTP-1' started successfully.
Warning: The logreader agent job has been implicitly created and will run under the SQL Server Agent Service Account.
Msg 21678, Level 16, State 1, Procedure sp_MSrepl_addpublication_snapshot, Line 123
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Job 'publisher-SSM_OLTP-SSM_OLTP-subscriber-1' started successfully.
Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.
Job 'publisher-SSM_OLTP-SSM_OLTP-subscriber-1' started successfully.
Msg 21678, Level 16, State 1, Procedure sp_MSrepl_addpublication_snapshot, Line 123
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Job 'publisher-SSM_OLTP-SSM_REPORTING_FOOD-subscriber2-2' started successfully.
Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.
Job 'publisher-SSM_OLTP-SSM_REPORTING_FOOD-subscriber2-2' started successfully.

Open in new window

Why I'm getting this error. Cleaned up older replication setup before I ran the script.

ASKER CERTIFIED SOLUTION

Matt Bowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VIVEKANANDHAN_PERIASAMY

ASKER

exec [SSM_OLTP].sys.sp_addlogreader_agent @job_login = N'domain\vivekanandhan', @job_password = nul

I have to re-change the script as
exec [SSM_OLTP].sys.sp_addlogreader_agent @job_login = N'domain\vivekanandhan', @job_password = mypassword

And similarily for all password present in the script.

Matt Bowler

Yes, it's trying to create the jobs to run under the domain\vivekanandhan account - but the password has not been included in the scripts for security reasons.

Either manually add the password to the script if you have it, accept the sql server agent service account job owners created, or manually go back and change the job owners.

You could try just removing the @job_password=nul parameter entirely...