1

Beginning a project to create an HA SQL server environment. We are limited to using SQL Server Standard edition, currently 2016. From what I have found, AO(AlwaysOn) for standard edition limits you to basic AG (availability group) and thus only 1 database per AG. This is going to be tough since most of our SQL instances contain 30+ databases. Can you implement AO without AGs? Are there other ways to configure AO to address our needs? All databases on any of our instances are managed similarly and thus wouldn't need the flexibility that managing at an AG provides.

1
  • Please add more tags - for example sql-server if I understood well - to guide the users with the right knowledge to help you to your question
    – andy
    Mar 5 '20 at 19:25
1

If you have an underlying Windows Cluster, and shared storage you could look at using Failover Cluster Instances - this would work at an instance level. Standard edition supports this for two nodes.

1

I have a different suggestion and want to post this as answer. You have 30 databases and you want HA for these databases in form of AG. But since you have standard edition you will have BAG and configuring 30 BAG's would be difficult task plus difficult to maintain. My advise is why go for BAG at all?. Why not configure Transaction Log shipping (TLS) for 30 databases believe me its old technology, works like charm and gives you good control over the mechanism. I could have suggested replication but I do not know your scenario and configuration. With TLS you would also have secondary readable database if you configure in standby mode.

Can you implement AO without AGs? Are there other ways to configure AO to address our needs?

Of course not, like others have suggested.

0

No, you cannot have Always On without Availability Groups. They are the same thing (actually the full name is Always On Availability Group). If you have 30 databases, your best option would be to upgrade to Enterprise Edition.

Note that Basic Availability Group is only available in SQL Server 2016+.

Here's the Microsoft documentation

0

I am not sure what the problem is here. So you can only have one database per AG, that is not a problem.

You can still have multiple AGs in SQL Server Standard edition.

Just use 30 AGs for your 30 databases. Just like mirroring, which is what it is intended to replace.

Unless all of those databases REALLY need to be in the same AG, in which case you need to upgrade to Enterprise Edition because there are no other options.

0

Thanks everyone for your information. Our goal is to be able to patch servers during business hours without any application down time. We actually have 200+ databases over 8 SQL servers with multiple instances on each. Due to the financial implications upgrading to Enterprise is not an option (small IT department\budget). Previously we patched servers quarterly, but now moving to monthly. We are clustering app servers and building IIS farms to address the front end part of our environment and looking for the best and most affordable way to address the SQL side. Thanks again for all your ideas.

-2

Yes or 'it depends'. Microsoft changed the naming some time ago so there's some confusion over Always On. I'm probably oversimplifying but:

Always On Failover Cluster Instances (This is just plain old windows failover clustering) This requires shared disks and might fulfill your HA requirements. Works with standard edition using 2 nodes only.

Always On Aviailability groups (this is more or less mirroring with some bells and whistles). This might cover HA and/or DR depending on how you choose to configure synchronization and automatic failovers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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