2

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:

  1. In the beginning, dbserver1 is acting as a primary, dbserver2 is acting as a secondary
  2. dbserver1 went down and an automatic failover takes place, and dbserver2 has become the primary
  3. dbserver1 is recovered and came up, acting as the secondary since dbserver2 is now the primary
  4. 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)

0
2

Should we manually trigger a failover to the dbserver1 to make it a Primary again?

Since you said that DBserver2 does not have the jobs and other server level objects you must failback the Basic Availability Group to DBServer1. Your worry about missing a job run is correct.

I would also like to say that this is a wrong configuration, you MUST have all jobs and logins created on DBServer2 ( which was originally acting as secondary) missing these would defeat the overall purpose of having HA/DR.

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?

You are correct both nodes should be exactly same. You need to re-create all the logins and jobs and any ssis packages. In short both the SQL Servers should be same in all the aspect

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.