Proc FB_AGPostFailover - SQL-FineBuild/Common GitHub Wiki

Previous Proc FB_AGFailover Proc FB_DBRestore Next

The FB_AGPostFailover procedure performs a number of tasks needed following an Availability Group state change

The tasks included in FB_AGPostFailover are:

Reset Log Usage Alerts

If Configure Generic Maintenance Processes was included in the SQL FineBuild inatall, Alerts will have been created for log usage for all databases. These Alerts will be enabled for a Primary server and Disabled for a Secondary server

Reset Job Schedules

In order for a Job to run automatically, there are two SQL Agent flags that must be set. Both of these flags must be set to Enabled to allow a Job to run automatically:

  • Job Enabled flag
  • Job Schedule Enabled flag

SQL FineBuild will set the Job Enabled flag on Secondary servers to match that on the Primary server. This allows the intended capabilities of a Job to be viewed in Management Studio for both the Primary and Secondary servers.

The execution of Jobs is controlled by SQL FineBuild by setting the Job Schedule Enabled flag to Enabled for the Primary server and Disabled for all Secondary servers. This setting is not immediately visible in Management Studio, but can be seen when looking at the Schedules section of Job Properties. It is easier to control the automatic running of Jobs on Primary and Secondary servers by globally changing the Schedule Enabled flag than by maintainging some sort of log about which Job should have its own Enabled flag set

The schedules of all Jobs on the Primary Server are set to Enabled, and the schedules of all jobs on the Secondary Servers are set to Disabled. This work is done using sp_update_jobschedule.

If a specific Job should be allowed to run on a Secondary server, this processing is performed by Proc FB_AGSystemData

Reset Database Synchronisation

For Secondary servers, Availability Group synchronisation is checked for all databases and if restarted if necessary

For a Primary server, Database Ownership is updated

Reset Database User Mapping

It is sometimes required to remap users within a Database to a SQL Server Login following a Failover. This is always required for Data Quality databases and the SSIS Catalogue database

SQL FineBuild will perform this mapping automatically for all users listed in [Table FB_PostFailoverDBUsers]

Copyright FineBuild Team © 2019-2020. License and Acknowledgements

Previous Proc FB_AGFailover Top Proc FB_DBRestore Next