Mircrosoft SQL Server High Availability

Question

Hi,
We have plan to move our SQL server (Azure VM) with high availability setup. apologize for being a bit confused about the SQL term below my open questions in mind.

Does the SQL always on different from the SQL always on availability group?

How do the data replications work for HA setup and type of data replication(only the updated records or full replication)?

Does the HA setup only have one connection strings or endpoint during the fail-over no need to change the application configuration?

Does have a manual and automatic fail-over from primary to secondary SQL server?

Thanks for your help.

Answer 1

Hi there,

Always On is the "Buzz Word"
the HA technology behind it are two things ... Always On Failover Cluster and Always On Availability Groups
So you are talking about AOAGs

AOAG -> It is not a replication; I would prefer to call it "Mirroring on steroids."
So, it mirrors your complete data from node A to B.

In a normal/general setup, it only has one connection string, and you do not need to change your application. But to get the full advantages of an AOAG, the application should be able to differ between Read and Write Intents. So will be able to route your Read-Workload to a secondary node, all Write-workload will be routed to the primary node.
https://www.sqlshack.com/how-to-configure-read-only-routing-for-an-availability-group-in-sql-server-2016/

Failovers - Both are possible!
Automatically - depending on node count - in case of a failure, the role of the primary server will failover automatically to another (secondary) node.
In case of maintenance... you can do this manually (SSMS, Batch, Powershell...)

Answer 2

Hey,

There are 2 options you can go with - Always On Availability Groups or Always On Failover Cluster Instances.
Always On AG:
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview?view=azuresql

As for FCI, it requires shared storage. You can use StarWind VSAN, which will replicate data between the VMs, providing shared storage for the cluster. The following article should help with configuration: https://www.starwindsoftware.com/resource-library/starwind-virtual-san-installing-and-configuring-sql-server-2019-tp-failover-cluster-instance-on-windows-server-2016/

Cheers,

Alex Bykvoskyi

StarWind Software

Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Answer 3

Hi @ErwinBainto-6674,

Welcome to Microsoft Q&A!
SQL Server AlwaysOn consists of two technologies:

• AlwaysOn Failover Clustering Instances (AlwaysOn FCI)
• AlwaysOn Availability Groups (AlwaysOn AG)
  AlwaysOn FCI needs shared storage like an iSCSI or Fibre Channel SAN that can be accessed by all of the nodes in the cluster. It supports multisite clustering across subnets which enables failover of SQL Server instances across data centers, but this requires replication of the data between the shared storage in each of the data centers.
  AlwaysOn FCI is available on both SQL Server Standard and Enterprise Edition such as a 2-node limit.
  AlwaysOn AG does not require shared disk storage for the server hosting the SQL Server. This SQL Server high availability technology has been an Enterprise feature. But in the standard edition, its counterpart is the basic availability group. There are also some limitations.

For more information, please read this article. And there is a table at the end of this article.: https://www.mssqltips.com/sqlservertip/4717/what-is-sql-server-alwayson/
Here are two official documents for you:
Always On Failover Cluster Instances (SQL Server)
What is an Always On availability group?

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.