Mirroring - After an Automatic Failover

So get this, our principal database failed in our mirrored pair. Thats cool, it automatically failed over. What isnt so cool is that the jobs that run every 5 minutes weren't enabled on the mirror, nor were they disabled on the under-the-weather principal. Of course this happened in the middle of night. Alerts are being spewed forth from everywhere and the jobs are not running on the now-live database, causing further problems.

To prevent this happening in the future, I replaced the job with 'starter' jobs that determine if they should run the job for that server, by querying what role that database has in the mirror - principal or mirror. This allows us to leave the jobs on both servers enabled and scheduled. A scalar function achieves this, and the obvious modifications to any jobs that already exist.

create function IsMirrorRole (@database varchar(255), @role varchar(255))
returns tinyint
as

begin

    declare @response int

    select @response = count(*) from sys.database_mirroring
    where database_id = DB_ID(@database)
    and mirroring_role_desc = @role

    return @response
end

And this can then be used with the following additional to any jobs, or by creating a 'starter' job that invokes any other jobs that need to be called.

-- is payment the principal?

-- a non-zero is true
if dbo.IsMirrorRole('payment', 'principal') != 0
begin
    EXEC msdb.dbo.sp_start_job 'Check Point'
end