question

Erwin-6674 avatar image
0 Votes"
Erwin-6674 asked BjoernPeters commented

Mircrosoft SQL Server High Availability

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.

sql-server-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

3 Answers

BjoernPeters avatar image
0 Votes"
BjoernPeters answered BjoernPeters commented

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



· 4
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Erwin-6674 avatar image Erwin-6674 ·

Hey,

Thanks for your comments this one is a good article. based on your experience, which one is the best approach (always on FCI /Always on availability group) stable and fewer issues and maintenance? Does the mirror data from node A to B is it full data copy (initial sync. then CDC (change data capture) for the succeeding)?

My current scenario is we have 2 AZ (availability zone 1 & 2) in azure then we have 1 SQL VM server for each AZ for HA setup.

As much as we want to use the SQL database (azure serverless) the Dynamics CRM does not support this service.


Thanks for your help!

0 Votes 0 ·
BjoernPeters avatar image BjoernPeters Erwin-6674 ·

First, the question should be what are the requirements from your business? What are they expecting from the solution?

Are you actually having any problems with that solution? If not, and you are not pushed into something different from your business, then why change it?
That solution is a good one for a normal SQL Server without any special need.

If those requirements push you into something like "max data lost < 5 min" or "max time to recover < 30min," then you have to think about something else.
Depending on those requirements and the budget, you can find a good solution.

The real advantages from AOAGs and Routing only came along with Enterprise Edition... but depending on the requirements, it might be necessary or/and worth it.

PS: AOAG is mirroring => no CDC
Starting with an initial "Copy" (like Full Backup/Restore), then moving every DML statement to all secondary nodes.

0 Votes 0 ·
Erwin-6674 avatar image Erwin-6674 BjoernPeters ·

The current setup is onprem without HA configured.

The requirement is to move in Azure with SQL HA with 99.99% uptime SLA. the design is to use the 2 availability zone in azure 1 SQL server per availability zone (1 & 2).

0 Votes 0 ·
Show more comments
AlexBykovskyi avatar image
0 Votes"
AlexBykovskyi answered Erwin-6674 commented

Hey,

There are 2 options you can go with - Always On Availability Groups or Always On Failover Cluster Instances.
Always On AG:
https://docs.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.

· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Erwin-6674 avatar image Erwin-6674 ·

Hey @AlexBykovskyi

Thanks for your help. will continue reading these articles to fully understand the MS SQL HA world.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered Erwin-6674 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Erwin-6674 avatar image Erwin-6674 ·

Thanks for the help @SeeyaXi-msft. will check on these articles. we trying to enable the setup of 2 SQL servers 1 per availability zone(1,2) in Azure to achieve the 99.99 SLA HA.

0 Votes 0 ·