Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Production replication issue,Need urgent advice

Posted on 2013-06-06
3
Medium Priority
?
1,245 Views
Last Modified: 2013-06-07
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.
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
Matt Bowler earned 2000 total points
ID: 39227569
Scripting out the replication will have given you create scripts for everything - BUT the passwords won't have been included. You'll need to add those manually.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39227662
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.
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39227675
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...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question