Question :
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
Answer :
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)
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.