I have a bunch of SQL Agent jobs that execute T-SQL against databases. A bunch of these databases are mirrored. Of course if the database is the principal then these jobs will work without a problem. But what happens if you failover? Now these jobs will start failing. You can either have the same jobs on both servers and have them enabled or disabled depending where the mirror or principal is. You can very easy enable or disable these whenever you failover. For example

T-SQL
1
2
3
4
5
6
USE msdb
GO
 
UPDATE sysjobs
SET enabled =1
WHERE name IN ('',''....) --use a table that has all the jobs instead
USE msdb
GO

UPDATE sysjobs
SET enabled =1
WHERE name IN ('',''....) --use a table that has all the jobs instead

Or another way would be to check if the database is online to see if the job should continue running

Here is such an example

T-SQL
1
2
3
4
5
6
7
8
IF EXISTS(
SELECT  1  FROM sys.databases
WHERE state_desc = 'ONLINE'
AND collation_name IS NOT NULL
AND name = 'YourDB')
BEGIN
PRINT 'yep, good to go'
END
IF EXISTS(
SELECT  1  FROM sys.databases
WHERE state_desc = 'ONLINE'
AND collation_name IS NOT NULL
AND name = 'YourDB')
BEGIN
PRINT 'yep, good to go'
END

The reason we also check for collation_name in addition to state_desc is documented in Books On Line

A database that has just come online is not necessarily ready to accept connections. To identify when a database can accept connections, query the collation_name column of sys.databases or the Collation property of DATABASEPROPERTYEX. The database can accept connections when the database collation returns a non-null value. For AlwaysOn databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.

Yet another option would be to have a table with the ‘live’ server for the database, this however is more used for jobs, SSIS packages and programs that live on other servers

How do you manage your jobs when dealing with mirroring? Leave me a comment, I am interested in your approach.