In a mirror, how can I keep the SQL Server Agent offline, but have it start automatically in the event of a failover on the Principal? In this scenario there are three MS SQL servers. ServerA has a series of jobs that write data to databases on ServerB. I want to set up ServerC as a mirror of ServerA, with all the same jobs available. I want to keep the SQL Server Agent down on ServerC until such a time as ServerA fails over, at which time I would like the SQL Server Agent on ServerC to automatically start up and continue to service those same jobs writing to ServerB until we get ServerA back online.
Can this be accomplished? And what is the best way to go about it?
You can keep the Agent enabled but disable the individual jobs on ServerB and create a dedicated job on ServerB that continuously runs (or schedule it to run every X amount of time) and checks if the current server is the Primary. If it is, then enable the other jobs with sp_update_job.
You can also check which jobs are enabled with msdb.dbo.sysjobs so your dedicated job only tries to enable them once.
Using this same methodology you could have the dedicated job disable itself and have a secondary job check for when the current server becomes a Secondary Replica so that it can re-enable the dedicated job, or you can just let the dedicated job continually check but do nothing when the current server is the Primary and it already enabled all the jobs for the first time.