Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Wednesday, 06 February 2008

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:

--Get SQLBinRoot

declare @ret sysname

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

--Update subsystem_dll to current SQLBinRoot

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

--Stop Sqlagent service if running

--MxDB should restart it automatically

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

--Clean up tmp table

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

drop table #stat

 

Wednesday, 06 February 2008 09:49:41 (Central Standard Time, UTC-06:00) | Comments [0] | Polyserve |  SQL Agent#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
Pied Piper of Grandview II
Buick drops the ball, er...muffler
Recent Posts
Archive
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll