In addition to the automatic failover that occurs for Always On setup, these jobs also needs to be created on both the Primary and the Secondary server to help with the automatic failover.
In the event of a failover, all user jobs should be disabled on the Secondary server and enabled on the Primary server automatically.
The “Replica Failover Status Check” job needs to be running at all times and will detect the failover when it happens on both servers.
| Jobs | Description |
| DBA – Weekly Stop Replica Failover Status Check Job | This job runs weekly and stops the all the jobs DBA – Replica Failover Status Check so that it doesn’t run for months on end. Should run mid-week. This job needs to be running on both Primary and Secondary server. |
| DBA – Replica Failover Status Check: Group1 | AvailabilityGroup specific failover check. This job needs to be running on both Primary and Secondary server. The stored procedure that it execute needs to be running on a non-AG database like the master database. This jobs detects failover status. |
| DBA – Assumed Primary AG Role: Group 1 | Sync Logins EXEC msdb.dbo.sp_start_job @job_name = ‘DBA – Update Login Permissions After Failover’. Enable jobs in category: Group1_AvailabilityGroup. Enable all ‘Group1_AvailabilityGroup’ group jobs. Fix App User Permissions EXEC msdb.dbo.sp_start_job @job_name = ‘DBA – Fix App User Permissions’. Final Step – Check status of previous steps. Send Email. This job should be enabled but not have schedule. |
| DBA – Assumed Secondary AG Role: Group 1 | Disable jobs in category: Group1_AvailabilityGroup. Disable all ‘Group1_AvailabilityGroup’ group jobs. Final Step – Check status of previous steps. Send Email. This job should be enabled but not have schedule. |
| DBA – Update Login Permissions After Failover | Syncs SQL Users and Logins. This job should be enabled but not have schedule. |
| DBA – Fix App User Permissions | Add the service account AD credentials. This job should be enabled but not have schedule. |
Example:
| Job | Enable | Status | Scheduled |
| DBA – Weekly Stop Replica Failover Status Check Job | Yes | Idle | Yes |
| DBA – Replica Failover Status Check: Group1 | Yes | Always Running | Yes |
| DBA – Assumed Primary AG Role: Group 1 | Yes | Idle | No |
| DBA – Assumed Secondary AG Role: Group 1 | Yes | Idle | No |
| DBA – Update Login Permissions After Failover | Yes | Idle | No |
| DBA – Fix App User Permissions | Yes | Idle | No |
Comments