MSSQL Availability
(learn.microsoft.com)
AlwaysOn
SQL Server AlwaysOn is a marketing term for the HA and DR solutions introduced when SQL Server 2012 was launched. Specifically:
AlwaysOn Failover Clustering Instances (AlwaysOn FCI) - new in SQL2012
AlwaysOn Availability Groups (AlwaysOn AG) - new in SQL2012
AlwaysOn Basic Availability Groups (AlwaysOn BAG) - new in SQL2016
AlwaysOn Distributed Availability Groups (AlwaysOn DAG) - new in SQL2016
AlwaysOn & VMWare
VMware does not support VMware vSphere vMotion® or DRS on clustered SQL Server virtual machines within a shared disk architecture (such as AlwaysOn Failover Cluster Instances). This restriction does not apply to AlwaysOn Availability Groups that are built on a non-shared disk architecture. VMware fully supports using vSphere HA, vSphere vMotion, and DRS with AlwaysOn Availability Groups. (1)
"The HA choice comes down to where you want your HA to be managed. VMware HA pushes the high availability question out of the SQL Server realm and into the VMware infrastructure. More than anything else, this is a business decision – just be sure you’re happy with the decision of which team is managing your uptime." (2)
Licensing
SQL2017
For each server licensed with SQL Server 2017 and covered by active SA, customers can run up to the same number of passive failover instances in a separate, OSE to support failover events. A passive SQL Server instance is one that is not serving SQL Server data to clients or running active SQL Server workloads.
http://download.microsoft.com/download/7/8/c/78cdf005-97c1-4129-926b-ce4a6fe92cf5/sql_server_2017_licensing_guide.pdfSQL2019
For each on-prem server OSE licensed with SQL Server 2019 and covered by active SA, customers can use the following passive replicas in anticipation of a failover event:
One passive fail-over replica for High Availability in a separate OSE
One passive fail-over replica for Disaster Recovery in a separate OSE
One passive fail-over replica for Disaster Recovery in a single VM on Azure
AlwaysOn AG vs VMWare Stretch Cluster
VMWare Stretch Cluster
AlwaysOn Availability Groups
Pros
Storage manages storage replication - bi-directional
Pros
Readable
Backup
CheckDB
Corruption Repair
Query (needs license)
Flows Primary to Secondary - resync can be more difficult
Bibliography
https://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/https://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/https://www.brentozar.com/sql/sql-server-alwayson-availability-groups/https://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-serverhttps://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groupshttps://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availabilityhttps://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-interoperability-sql-serverhttps://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groupshttps://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groupshttps://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver16#limitations
https://joeydantoni.com/2012/03/29/alwayson-versus-active-data-guard-a-value-proposition/
https://techcommunity.microsoft.com/t5/azure-sql-blog/high-availability-in-azure-sql-mi-general-purpose-service-tier/ba-p/3298977
https://dba.stackexchange.com/questions/197647/sql-failover-clustering-with-availability-groups-single-or-multiple-databases?rq=1
SQL Listenerhttps://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-listener-overviewhttps://www.sqlservercentral.com/articles/sql-server-always-on-availability-group-ag-listener-step-by-step-guide
ApplicationIntenthttps://stackoverflow.com/questions/15347541/what-does-applicationintent-readonly-mean-in-the-connection-stringhttps://web.archive.org/web/20130125015414/http://connect.microsoft.com/SQLServer/feedback/details/697217/applicationintent-readonly-allows-updates-to-a-database
Log Shippinghttps://github.com/trimble-oss/sql-log-shipping-service
References
(2) https://www.brentozar.com/archive/2013/08/common-sql-server-clustering-alwayson-and-high-availability-answers/ (Q: Is VMWare HA a good alternative to use instead of a Microsoft Cluster?)
(3) https://www.brentozar.com/archive/2012/09/why-your-sql-server-cluster-shouldnt-be-virtualized/