I'm very new to SQL and I have the following queries:
I have a 2 node SQL 2012 server using Windows Failover cluster (2012) that uses shared storage for its database and I have a single instance running on the primary node. I have some SQL scripts generating tables, running queries etc. from an external client that connects to the DB engine using the SQL server name- so the external client runs IO to the SQL servers through IP traffic.
Now, when I fail the primary node (say a hard reboot or even a manual failover) where the SQL server role is running, my external client loses its connection to the database engine/FCI. Once the services are up and running on the secondary node after failover, I'm able to re-connect to the server and start IO again. My question is- 1) Is this by design? Meaning the external clients running IO to an SQL server instance will lose connection in case of the primary node failure? 2) Is there any way I can tweak some parameters/settings etc. such that I will have no downtime to the external client and they don't lose connectivity throughout the failover process?
No, there is no way to avoid the downtime, there will always be some failover time involved.
You could use Mirroring/Availability Groups in HA mode, and that would minimize the downtime during failover to just a second or 2, but it will still be there.
SQL server primary node failure
Posted - 03/11/2013 : 07:55:53
