Polyserve clusters SQL Server instances, and can then rehost them or fail them over to other servers.

One of the issues we've run into with Polyserve is when we move a sql server instance from one machine to another where the binaries change location from mssql.a to mssql.b.

SQL Agent (SQLAgent.exe, SQLAgent), utilizes a system table in msdb for starting and executing jobs, called syssubsystems;

Execute: select * from msdb.dbo.syssubsystems and you will see each sub-system of sql agent and the path to the binaries, if a sql server instance is moved through some technology like Polyserve or through manually moving a msdb database from one server to another and the path to the binaries change, the system tables will need to be updated to the new path.

We've experienced this on Polyserve and when we moved a sql server instance from one server to another and copied msdb.

very easy to fix, but frustrating, below is  a snippet which can be run to correct the issue:

declare @ret sysname

exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Setup','SQLBinRoot',@ret OUTPUT

update sub

set subsystem_dll=@ret+substring(subsystem_dll,charindex('\binn\',subsystem_dll)+5,30)

from msdb..syssubsystems sub

where charindex('\binn\',subsystem_dll)>0

declare @service sysname

select @service = case when charindex('\',@@servername)>0

then N'SQLAgent$'+@@servicename

else N'SQLSERVERAGENT' end

create table #stat(status sysname)

insert #stat

exec master..xp_servicecontrol N'QUERYSTATE', @service

if exists(select * from #stat where status='Running.')

begin

exec master..xp_servicecontrol N'STOP', @service

end

if object_id('tempdb..#stat') is not null

drop table #stat