Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot stop (or start?) synchronization agent

Posted on 2013-11-06
12
Medium Priority
?
479 Views
Last Modified: 2013-12-05
In mwerge replication, I create a publication in SQL 2008R2.  Then, after generating a snapshot, I look at sync. status and see it has "started".  This stays like this for hours. I go to stop it, and after confirming, get an error message saying it can't be stopped because it isn't running??

Any ideas please?  do I need to stop and restart anything?

One thing, I have been creating a new publication, with a subscription, and then been deleting the publication without deleting the subscription first; could this be a factor?

Thanks,

  Col
CaptureReplication45.PNG
0
Comment
Question by:colinspurs
[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
  • 7
  • 5
12 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39627548
If the synchronization was set to be permanent you should never stop it and it should run permanently. Only if you define a schedule for it it will start at the predefined interval and it will run the sync and stop when finished.
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39638952
Hi, thanks for you response and apologies for the delay in replying.

Are you saying that it's normal for the "Starting agent" message to be shown for days on end?

When does it actually start?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39639047
if you don't set an interval for the sync agent then it will run permanently and it will start when you set up the subscription or when you manually started it and then it will show that is running for so much time, which can be days, hours, min... and it will never stop because of the permanent setting.

If you want the sync to run just at some predefined intervals then you will need to setup a schedule for the job and set it to run, let's say, every 15 min. In this case you will see that agent runs for some time, seconds, or minutes depending of the number of transactions, and then it will stop until next schedule.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 39639595
About your other issues in the question:

In your first one it seems you created the publication but you didn't add any subscription to it. In that case there is no sync job.

In the second one, when you delete a publication it will delete its subscriptions as well but it would ask you for login credential to subscriber server. Normally if connection is established the subscriptions will be removed as well. However is preferable to remove the subscriptions one by one first and then the publication.

If you have suspicions that the subscriptions were not deleted then connect to the subscriber server and expand the Subscription node just under the Replication one and remove them from there.
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39651440
OK I will be careful to delete the subscriptions first and then the publication.

Which question are you referring to here...In your first one it seems you created the publication but you didn't add any subscription to it. In that case there is no sync job.

I have rechecked and it is still "starting agent".  I will make some changes to the contents of the publication and check Monday to see if it has replicated.

Thanks,

  Col
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39656063
I checked the subscriber database and it is entirely empty...?
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39689772
Sorry, but this suggests it is hanging to me...same Starting Agent message for 3 weeks with no data exchange between the servers...
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 39689969
I recommend you to start from scratch.:

1. Remove the subscriptions
2. Remove the publications
3. Create a new publication using the wizard.
3. Add subscriptions. When you set up the systematization create a schedule for it like every 5 minutes or so. Also when you create the snapshot set it up to start manually.

After you finished you will go in the Replication Monitor and find the snapshot agent right click on it and Start. Let it finish. After it will finish the sync agent will pick up any new changes and replicate them. Agent will run at the predefined schedule but it won't do anything until the snapshot is finished.

Here is a step by step guide:

http://blogs.technet.com/b/meamcs/archive/2011/01/06/merge-replication-step-by-step.aspx


Also here:

http://technet.microsoft.com/en-us/library/aa337473.aspx

that is merge publication setup. There is a linka at teh bottom for adding a subscription.
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39690198
Thanks again...I think it might be a permissions issue.  

I create the publisher and check the snapshot status (without creating a subscriber) firstly under my own Windows domain name, snapshot is created...and then...

...delete it and create it again under a "more secure" Windows domain set up for the purpose of replication, and hit this issue when I check the snapshot agent status.

Do I need to check read and write permissions on the repldata directory in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata ?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39690377
Just follow the steps to create a merge publication and subscription and you should be fine. The location for the snapshot should be accessible by the account of the distributor server, which can be either the publisher of the subscriber, depending on how you chose.
0
 
LVL 3

Author Closing Comment

by:colinspurs
ID: 39693238
Thanks for your patience, it was the permissions in the end.  

No doubt there will be a few more replication-oriented questions coming up!

Cheers.
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39698411
Hi again.  I ran the sync from the command line and it reported violations of primary key. I suspect this was contributing to the problem all along, just that the GUI doesn't have a neat exit strategy.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

715 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