SQL Server 2012 – How to Connect Client Applications to Cluster

connectivitysql-server-2012

Following are the environment Details :

Database Server : SQL SERVER 2012
Operating System : Windows Server 2012

Single instance of SQL SERVER 2012 is installed in cluster configuration with 2 nodes. I have 3 IP addresses, one for each node of SQL SERVER and one for the cluster.

I am able to connect to this configuration by providing SQL SERVER instance name to the DataSource of connection string but i am unable to connect using cluster name as data source

so the following is working 

<add name="Default" connectionString="Data source=SQLInstanceName;Initial Catalog=DatabaseNAme;User Id=userid;Password=password;Connection Timeout=120;" providerName="System.Data.SqlClient" />

but changing SQLInstanceName to IP address/Name of cluster does not work

<add name="Default" connectionString="Data source=clustername;Initial Catalog=DatabaseNAme;User Id=userid;Password=password;Connection Timeout=120;" providerName="System.Data.SqlClient" />

Is this by Design?

My understanding is that client application needs to connect to cluster and not to specific instance.

Best Answer

Neither of those should work. If it's a named instance, it should be:

DATA SOURCE=ClusterName\SQLInstanceName;

If it's a default instance, just:

DATA SOURCE=ClusterName;

You are always connecting to an instance of SQL Server, whether it is clustered or not. The cluster will know which node in the cluster is currently hosting the instance, and this should not really be a concern of yours, at least in terms of the connection string - that's kind of the whole point, a failover to a different node in the cluster should not require your application to change anything.

Related Question