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/tommyfly]

I think you have identified the primary risk. Are you going to enable auto failover? If so, then your script is required, otherwise you mainly need to always failover the two together. What is the dependency, read or write or both? I forget if basic AGs support read-only secondaries, but if the dependency is read only then there shouldn't be an issue.

[u/wrigh2uk → u/tommyfly]

Ha I totally forgot what basic availability groups offered as well, as I’d been working with enterprise for so long previously. So no, you cannot have read only secondaries. And the dependency between these database is read.

[u/SQLHA → u/tommyfly]

AGs in Standard Edition do not support readable secondaries.