How can I automatically trigger a script when my DB fails over?

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.

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:

• https://sqlblog.org/2009/09/18/managing-multi-instance-cluster-failovers-with-different-hardware

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:

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. :-)

StackExchange Database Administrators Q#17980, answer score: 4