Solved

Production replication issue,Need urgent advice

Posted on 2013-06-06
3
1,091 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
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
MattSQL earned 500 total points
Comment Utility
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
Comment Utility
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 9

Expert Comment

by:MattSQL
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Updating variable table 9 17
GRANT, REVOKE, DENY 4 16
BULK LOGGED - log full 9 12
Access Migration to Sql Server 2 18
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now