4

I have some Windows services that I'd like to restart after a database failover but I can't find any details on triggering external actions after a failover. I'm working with SQL Server 2012 database mirroring.

  • Are you talking about a cluster failover, Availability Group failover, mirror failover, ...? With a cluster you can rely on the service(s) to restart when the failover occurs. For the others it might be a bit more tricky (event notifications perhaps?). – Aaron Bertrand May 16 '12 at 15:10
  • I'm talking about SQL Server 2012 database mirroring. Sorry for the confusion. – William Jens May 17 '12 at 15:08
4

For a cluster failover you can use a startup procedure. Assuming you have xp_cmdshell enabled and the service account has adequate permissions to start services:

USE [master];
GO

CREATE PROCEDURE dbo.StartService
AS
BEGIN
  SET NOCOUNT ON;

  EXEC master..xp_cmdshell 'net start servicename';
END
GO

EXEC [master].dbo.sp_procoption 
   'dbo.StartService', 
   'startup', 
   'true';

I blogged about this for different reasons here:

You could also not use the startup option and instead just call the stored procedure from a job, and schedule the job to run when SQL Server Agent starts:

enter image description here

Of course this depends on the SQL Agent service starting successfully, and also assumes that the Agent or SQL Server services do not depend on the service you're trying to start. :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.