I have 2 nodes in my SQL server fail over cluster. I use SQL server 2012 always on feature for HA purpose.
The problem is that when one my server goes down the automatic fail over does not happen!
How can I enable automatic fail over?
Asked
Active
Viewed 971 times
1 Answers
3
If the failover is not happening, the first thing to check is whether AlwaysOn is really enabled. Perhaps the simplest way to test this is by looking in SQL Management Studio at the AlwaysOn Dashboard. Your Availability Group (AG) should have at least two active nodes. Does it indicate your AG is synchronized? Note that unlike previous HA solutions, the SQL service should be running on both servers all the time.
From the dashboard, you can use the Failover Wizard to request an immediate failover. Does this succeed? Does the formerly-primary node resynchronize correctly in a reasonable amount of time for the current traffic? This is obviously something you would want to test after hours. If you don't have at least one node which shows as synchronized, you want to resolve that first to be sure you don't lose data.
Next, look to the client. Client applications must use the SQL Server Native Client 11.0 (or later) to gain the ability to reconnect automatically. Earlier versions will simply disconnect.
It's also important for the clients to connect to the Listener for the AG. If they are connecting directly to one server or the other, they will have no way to discover the newly-active server when a failover happens.
Note that AlwaysOn does not require special network routing or DNS changes as some previous solutions required. The client should have an open network path to all AG nodes at all times. The Listener will inform the client which node to use at any given time. Don't forget to look inside the SQL client configuration utility (Start > Run > cliconfg.exe) for aliases that may be directing traffic in unexpected directions.
Bruce
- 45
- 4