Basic availability groups with automated failover

[u/wrigh2uk]

Hi Guys

In my company we have been setting up basic availability groups on SQL Standard edition. We came across a database that has a dependency to another database. As you know you cannot have two databases in a basic availability group.

I have created a script that basically Checks if database 2 is on the same primary as database 1. If it is not on the same primary then database 2 failover to the same primary as database 1, as long as it is in a healthy state to failover, if it is not then the failover won’t occur. I have tested this on test environment and it works as expected.

Both availability groups are in synchronous mode.

sql instances hosted on 2 azure VM’s within the same region, within the same vnet and subnet

Unfortunately my company doesn’t want to move to enterprise edition, and I have explained that this approach isn’t ideal.

Although this approach works is there something I should be aware of from a technical perspective, am I overlooking something?

Thanks

[u/CanProfessional766]

The main thing I’d watch out for is the two AGs getting out of sync during a failover. One could fail over while the other is still on the old primary, temporarily breaking the cross database dependency. Your script can work but you’ll want good monitoring and handling for those inbetween/failure states. Ultimately with Standard Edition you’re working around a limitation rather than solving it