Share via

Always On - Active/Passive Config

Veena Srinivas 251 Reputation points
2021-02-08T19:52:56.567+00:00

Hello,

Please clarify for me this doubt I have about Always On - Active/Passive.

I have 2 servers which are part of a WSFC.

I have installed SQL 2017 Enterprise on both nodes separately. Not as a failover cluster.

I set up an AG group, added the replicas (node 1, node 2) , added the databases (x,y,z) , and created a listener.

I have set it up so that the node 1 is primary, and node 2 is secondary. Not Readable.

Is this correct for Active/Passive ?

Please advise.

Thanks,
V

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,841 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sean Gallardy - MSFT 1,886 Reputation points Microsoft Employee
    2021-02-08T20:40:36.7+00:00

    Please clarify for me this doubt I have about Always On - Active/Passive.

    There is no such things as Active/Passive. It's an x number of instance and x number of node setup. If you had 4 instances on 5 nodes, what would you say? Active/Passive/Active+Active+Active/Passive/Passive? That doesn't make any sense.

    I have set it up so that the node 1 is primary, and node 2 is secondary. Not Readable.

    Pretty normal setup, nothing wrong with that.

    Is this correct for Active/Passive ?

    See above. This is correct if you're looking to have a primary with a non-readable secondary.

    1 person found this answer helpful.
  2. Erland Sommarskog 102.1K Reputation points MVP
    2021-02-08T22:26:36.357+00:00

    If you have set up the replica as synchronous with automatic failover, the expected behaviour is that there is an automatic failover.

    I recommend that you try to find a lab environment where you can play around with different configurations, and where you can do evil things like powering off one node etc. (VMs are great for this!)

    1 person found this answer helpful.
    0 comments
  3. Cris Zhan-MSFT 6,606 Reputation points
    2021-02-09T02:11:24.217+00:00

    Hi,

    The "Active/Passive" is more suitable for describing certain topology in SQL Server failover cluster.For a two-node SQL Server failover cluster instance, only one node has the SQL Server service running at any time, and the other node is the "passive" node.

    An availability group is a group of databases that fail over together. The availability group performs health detection and failover in units of a collection of user databases, instead of using the entire instance as a unit like the SQL Server failover cluster.

    >What will happen if Node 1(Primary) goes offline? Node 2 will become Primary at the OS level right?

    The type of AlwaysOn failover is determined by the mode of the primary replica and the failover target. To perform automatic failover between two replicas, both replicas must be configured in synchronous-commit mode + automatic failover mode.
    https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15

    0 comments