Does MSSQL support ACTIVE/ACTIVE with automatic switching

Question

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?

Answer 1

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.

Answer 2

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?

Answer 3

Thank you crishzha. Now I understood the concept.