Microsoft SQL server HA design

Aiman Kreidly 21 Reputation points
2022-03-31T08:25:24.09+00:00

Dears,

I want to ask about SQL server options for Database High availabilty, and more about ACTIVE?ACTIVE solutions on which the Database will have 2 nodes and both nodes share the same DB storage (shared) and both nodes are accepting read-write request
is this option available or its just for HA as if the primary node is gone the rest of AG will take over and be turned into read/write instead of only read

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,980 questions
{count} votes

Accepted answer
  1. Seeya Xi-MSFT 16,471 Reputation points
    2022-04-01T05:59:39.357+00:00

    Hi @Aiman Kreidly ,

    Here is a document about What is an Always On availability group: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15
    After reading this, you may have a better understanding about AG.
    Also read this document about options of configuring access on an availability replica: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server?view=sql-server-ver15#SSMSProcedure
    AlwaysOn can support up to five replicas, but only the database running on one availability copy is in a read-write state. The database on the secondary copy may be inaccessible or may only accept read-only operations, depending on the configuration of the availability group. In the event of a failover, any of the secondary replicas can become a new instance of the primary replica. The primary replica continuously sends data changes on the primary database to the secondary replica to synchronize the database between replicas.

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. tibor_karaszi@hotmail.com 4,311 Reputation points
    2022-03-31T11:09:10+00:00

    If you want a RW-RW solution, you will have to do it yourself. I.e., there's no built-in support for this in MSSQL (no distributed lock manager).

    What is available is Always On Availability Groups where you can read and write on the active node and read on the passive nodes (if you want). Of course, with failover abilities.


  2. Alex Bykovskyi 2,091 Reputation points
    2022-04-05T18:28:16.797+00:00

    Hey,

    As mentioned, you can use Always-On Availability Groups to create replicas across your nodes. As another alternative, you can use SQL FCI, which requires shared storage. For shared storage, you can use StarWind VSAN, which provides replicated active-active storage pool for the cluster. The following guide will 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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.