question

DarindubaiIV-6092 avatar image
0 Votes"
DarindubaiIV-6092 asked Criszhan-msft commented

Does MSSQL support ACTIVE/ACTIVE with automatic switching

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-general
5 |1600 characters needed characters left characters exceeded

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

Criszhan-msft avatar image
1 Vote"
Criszhan-msft answered Criszhan-msft edited

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.



5 |1600 characters needed characters left characters exceeded

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

DarindubaiIV-6092 avatar image
0 Votes"
DarindubaiIV-6092 answered Criszhan-msft commented

Thank you crishzha. Now I understood the concept.

· 1
5 |1600 characters needed characters left characters exceeded

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

Hi,
Thanks for your reply. I am glad that my answer is helpful to you.

Please kindly do "Accept Answer" on my answer, by doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
DarindubaiIV-6092 avatar image
0 Votes"
DarindubaiIV-6092 answered Criszhan-msft edited

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?

· 1
5 |1600 characters needed characters left characters exceeded

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

Hi,

For a SQL Server failover cluster, the topology can only be "active/passive". There is always only one node running the SQL Server service, when the node running the SQL Server service fails, another node will host the SQL Server service.. This is the characteristic of this technology.

Active/Active in a SQL Server failover cluster means to create two SQL Server failover cluster instances on the same multiple servers. These are two separate instances..


Active/Active or Active/Passive has nothing to do with automatic failover in SQL Server. They only describe the structure/topology or methods of SQL Server failure cluster deployment.

You don’t have to focus too much on this term. As I said in the above answer, the Always On availability groups and Always On failover cluster instances(ie SQL Server failover cluster) in SQL Server support automatic failover.


1 Vote 1 ·