We have an SQL Server 2016 standard edition with Basic Availability Groups(BAG) setup between dbserver1 and dbserver2. This is our first time to implement the BAG and wanted to know what to do in a failover scenario like below:
- In the beginning, dbserver1 is acting as a primary, dbserver2 is acting as a secondary
- dbserver1 went down and an automatic failover takes place, and dbserver2 has become the primary
- dbserver1 is recovered and came up, acting as the secondary since dbserver2 is now the primary
- Should we manually trigger a failover to the dbserver1 to make it a Primary again?
The reason is that any database objects (SQL agent jobs, maintenance jobs and such) only exist in the dbserver1, and if we keep dbserver2 running as a primary node, then it can potentially miss the jobs that were supposed to run.
Should we always target to have a particular node to be Primary (in this case dbserver1), or each node should be exactly the same including all the objects outside of Availability Groups so that any node can take primary role at any time, and should be kept being primary node once a failover occurred? (possibly using DBAtools to keep objects in-sync)