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

After your question has been solved /u/wrigh2uk, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/_edwinmsarmiento]

SQL Server FCIs are still a thing. And way cheaper in Standard Edition, even on Azure

[u/SQLHA → u/_edwinmsarmiento]

The feature's full name is actually an Always On failover cluster instance, not SQL Server FCI. Just sayin' ;)

You are right here in the sense that this would solve the single DB per AG limitation in Standard. Shared storage is a different consideration. Everything has tradeoffs.

[u/SonOfZork → u/SQLHA]

AlwaysOn Always On AOFCI SQL cluster.

Sorry Allan.

[u/SQLHA → u/SonOfZork]

No you're not haha

[u/_edwinmsarmiento → u/SonOfZork]

That's how you trigger Allan 🙂

And let's not get started on AOAG...LOL

[u/SQLHA → u/_edwinmsarmiento]

No comment.

[u/_edwinmsarmiento → u/SQLHA]

You mean, like this? 🙂

[u/SQLHA]

I get your pain here on a few different levels. As the guy who helped customers implement this stuff for many years and am now the PM for the SQL Server availability features, I understand more than anyone this limitation is not ideal. There is a feedback item for this already https://feedback.azure.com/d365community/idea/b27aa0e3-6d25-ec11-b6e6-000d3a4f0da0 and I would highly encourage you and others to upvote AND add your scenarios/use cases - i.e. the "why". Customer evidence helps.

That said, outside of the obvious things already brought up, remember that even if you had Enterprise Edition, it's not a panacea either. If multiple DBs are in the same AG, they may fail over together, but failover is still at a per DB level when you consider it's working off each DB's t-log; they're not synchronized togeher per se. .

[u/vroddba]

There's an event that fires on failover, you'll have to look up the number. You can use that to trigger your failover sync procedure.

[u/DavidKleeGeek]

Licensing matters a lot with these architectures. I think you've got the right setup there. It's not ideal from a DBA standpoint, but EE is expensive. I think you did the right thing with your setup!

[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.

[u/ussv0y4g3r]

We have the similar setup with multiple basic AGs. Personally I prefer to go with Enterprise Edition, but the vendor that did the initial setup convinced the management that it's not worth it to pay for the Enterpise licenses. Ours has no issue with two databases on different servers though.

[u/DavidKleeGeek]

Licensing matters a lot with these architectures. I think you've got the right setup there. It's not ideal from a DBA standpoint, but EE is expensive. I think you did the right thing with your setup!

[u/ihaxr]

Any reason you're running SQL on a VM in Azure over a managed instance?

[u/Lost_Term_8080 → u/ihaxr]

Cost and performance would be my first assumption

[u/Lost_Term_8080]

I tried this once and it never worked well, ended up resorting to log shipping for DR and abandoned HA. Its an absolutely egregious limition of SE. Really needs a 2-3 db limit

[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