What Happens to the Connections When we Move or Failover SQL Server Group to Another Node
It is common to see many people ask this question, What happens to the connections from Application to SQL Server, when we move or failover SQL Server Group from one node to another Node?
If we understand what happens when we Failover or Move SQL cluster instance from one node to other, we will get the answer to the question.
We we initiate a failover, All the resources in that group which include(SQL Server resource, SQL Server Agent resource, Multiple Disk Resources, Network Name and IP Address resource, other third party resources like backup, fileshare resources) will be taken OFFLINE on that node, then these resources are moved to the new node and then all these resources are brought ONLINE. Once all the resources are ONLINE, we can make connections to the SQL Server.
As we can see from the above process, SQL Server will be stopped, which means that any existing connections will fail, it may be possible that these failed transactions already would have modified some data in the database, but as per design of SQL Server engine, this ingormation is also logged into SQL Server Transaction log file(.ldf). When SQL Server starts on new node, it will read the transaction log file to see which transactions were committed and which were not, based on that it will rollback any incomplete work done by uncommitted transactions.
Hope this answers the question.