0

We have Always On Availability Group settings, and we have a preferred DB that needs to be in primary all the time even after it reboots.

Once a month or so, these 2 servers DB1 and DB2 reboot for upgrade/updates, however whichever come back online first becomes the primary with HA on, but we prefer to always set DB1 as primary when it comes back online no matter what.

Are there any configs for this, or should we create a trigger?

Thank you

5
  • 2
    If you use listener, why you care about which one is primary ? – Kin Shah Jul 26 '18 at 13:44
  • that how it should be, DB1 have jobs and DB2 does not, plus some credentials on DB1 is not on DB2, since these don't sync. We also face problems with ETL loading because our Data team uses domain group account that does not exists on DB2. – CyberNinja Jul 26 '18 at 13:50
  • 1
    see if my answer helps you. – Kin Shah Jul 26 '18 at 13:56
  • thanks a lot @kin, looks like he has the same issue. can you put your comment as answer so I can mark it. Thanks – CyberNinja Jul 26 '18 at 13:57
  • I will vote to have this question as dupe of the former one. You can upvote my answer over there if you want. – Kin Shah Jul 26 '18 at 14:14
1

You don't really want to do this automatically because:

  • A long-running transaction could be running on DB2, and you wouldn't want to interrupt it (especially given the problems with single-threaded rollbacks)
  • DB1 might come back online and SQL Server might be available, but the databases might have problems (or just be starting up)
  • DB1 may not be in sync (if DB1 went down for a while, it might take quite some time for it to get back in sync with DB2)
1

We have Always On Availability Group settings, and we have a preferred DB that needs to be in primary all the time even after it reboots.

Once a month or so, these 2 servers DB1 and DB2 reboot for upgrade/updates, however whichever come back online first becomes the primary with HA on, but we prefer to always set DB1 as primary when it comes back online no matter what.

Sounds like the config isn't setup properly to what you're wanting. You can still use Sync Commit, but choose manual failover and not automatic. This way someone or something would have to manually do it, rather than automatic which seems to be how it's setup at this point.

Are there any configs for this, or should we create a trigger?

Assuming you didn't want to just change it to manual failover, which would be the easiest and best solution given the information, I would stay far, far away from these options. While it's technically possible, you're going to run into the items that Brent stated, if not more/worse issues.

0

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