[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Starting a merge agent

I have managed to set up SQL merge replication between two servers in different locations. The merging seems to be going perfectly, with one minor hitch.  Just about every evening, the merge agent on my publisher doesn't seem to be able to find my subscriber. While this, in itself, is not too much of a problem, as there are tends to be no data to merge at these times, but it means that the identity ranges fill themselves up, and it becomes impossible to post records in one of the articles.

As far as I can tell, there are two potential work-arounds to this - either stop the servers from timing out, or set up a program or stored procedure to check if the agent is running, and if it isn't, start it again. In order to do either of these,  need three pieces of information:

1)   Is there a stored procedure to find the status of a merge agent?
2)   Is there a stored procedure to start a merge agent?
3)   Is there a setting somewhere, presumably in the merge agent profile, that detemrines how long it will attempt to conect to a subscriber before timing out?

Thanks in advance

ajb
0
alanjbrown
Asked:
alanjbrown
  • 3
  • 2
1 Solution
 
alanjbrownAuthor Commented:
Quick clarification - when the publisher cannot find the subscriber, it stops attempting to connect, so merges cannot take place.
0
 
SatyabodhiCommented:
create procedure p_start_merge_job
    @publication_name sysname,
    @debug bit = 0
as
-- Start the merge replication job associated with the parameter name
-- 10SEP00 Bodhi
declare @jobname sysname
select @jobname = name from msdb..sysjobs
where name like '%'+@publication_name+'%'
if @debug <> 0 begin
    print @jobname
end
if @jobname is null begin
    RAISERROR ('No job found for publication name, "%s"',16,1, @publication_name)
    return 16
end
exec msdb..sp_start_job  @jobname
go
0
 
alanjbrownAuthor Commented:
Thank you, Bodhi. Taht deals with question 2) perfectly. Any chance you can answer parts 1) and 3) as well?

In addition, is there a similar stored procedure for re-starting a connection between two servers? I am prepared to award more points for this.
0
 
SatyabodhiCommented:
Part 1) use sp_help_job.
I'm leaving part 3 unanswered.
0
 
alanjbrownAuthor Commented:
Okay. Bodhi, you will definately get the answer here. Out of interest, can you do the extra one as well?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now