Does MSSQL support ACTIVE/ACTIVE with automatic switching

Darin-dubai IV 21 Reputation points
2021-06-22T19:40:19.807+00:00

Just wanted to know if MSSQL supports Active/Active mode without needing to manually change anything to switch the database instance. We have a requirement to setup mssql which supports Active/Active with automatic failover.

Is it correct way to use the name Active/Active incase if a node fails and other instance take over the request? I am bit confused with the term Active/Active. Should it be necessary to have loadbalancing capability to call it as Active/Active?

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,633 questions
0 comments No comments
{count} votes

Accepted answer
  1. Cris Zhan-MSFT 6,601 Reputation points
    2021-06-23T02:31:28.197+00:00

    Hi,

    The Always On availability groups and Always On failover cluster instances(ie SQL Server failover cluster) in SQL Server support automatic failover. After the server failover, the client can automatically switch connections without modifying any configuration. To achieve the automatic failover between servers is completely transparent to the client, Always On availability groups need to rely on the component Listener, the SQL Server failover cluster need to be connected through the virtual network name or IP of the SQL Server failover cluster.

    >Is it correct way to use the name Active/Active incase if a node fails and other instance take over the request? I am bit confused with the term Active/Active. Should it be necessary to have loadbalancing capability to call it as Active/Active?

    -- Active/Passive and Active/Active --

    This is usually used to describe the topology of the SQL Server failover cluster.

    Active/Passive: For a SQL Server failover cluster, such that only one node has the SQL Server service running at any time, and the other node is an inactive node. There is always a node in the idle state, and server resources are wasted.

    Active/Active : In fact, it refers to the configuration of two SQL Server failover clusters on multiple servers. Take a 2-node windows server failover cluster as an example. At this time, the user installs two SQL Server cluster instances on the windows cluster, and the "possible owner" of each instance includes two nodes in the cluster. Under normal circumstances, the two instances are running on different nodes. In this way, both nodes are "active" nodes.

    For Always On availability groups, we use primary/secondary to describe the node (replica) and database. The primary replica and the secondary replica need to be configured as synchronous-commit+ automatic failover mode to achieve automatic failover.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Darin-dubai IV 21 Reputation points
    2021-06-23T06:25:29.977+00:00

    Thank you Criszhan for the quick reply.

    So ideally it means server failover right? Both active/passive and active/active would be having 2 instances running always but have different set of configuration to achieve high availability. Even if we say active/active it basically has connection to one server and incase if a failure occurs it will switch to second one. So the request will be always serving to only one server not to both right?


  2. Darin-dubai IV 21 Reputation points
    2021-06-26T05:57:19.007+00:00

    Thank you crishzha. Now I understood the concept.