Promote ALL Mirrors After a Single Fail-Over

Whilst on the subject of making jobs mirror aware. Often a single database (or even application) is logically split into multiple databases on a single server. From SQL Servers perspective they are two distinct entities. This means a single database failing-over to its mirror partner would render the application unusable. Its back end is now straddling two different servers.

To get around this, I have an alert using a WMI query monitoring for a failover event, should any single database failover, the remaining databases should then be failed across with it.

You'll need a job that's fired when a fail-over event is detected, without a schedule. This example sends an email and then proceeds to fail over any abandoned databases.

EXEC msdb..sp_send_dbmail
    @profile_name=''OT-FUSION'',
    @recipients=''alex.wilson@mysqlserver.com'',
    @subject = ''Database Mirror Failover Event Occurred'',
    @body = ''A database just failed over, the abandoned databases will be failed over now...''

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N''MyPokerDB'' AND mirroring_role_desc = ''PRINCIPAL'')
     ALTER DATABASE MyPokerDB SET PARTNER FAILOVER

This doesnt have a schedule, its execution is based on the Alert created below that execute this job, when a failover event is detected.

EXEC msdb.dbo.sp_add_alert @name=N'Mirror Check',
        @message_id=0,
        @severity=0,
        @enabled=1,
        @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
        @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8'

Here we query for the mirror state 7 or 8, a full list of states can be found here

You may want to create this job and alert on both the principal and the mirror, if you suspect they are going to fail back and forth.