July 12, 2013 at 6:39 am
I will build up to the question...
If we have a two node Active/Active fail over cluster set up like the following:
Node1:
Server Name - CLSQL01
SQL VCO - SQLBox1
Instance Name - SQLBox01
Database - DB1
Node2:
Server Name - CLSQL02
SQL VCO - SQLBox2
Instance Name - SQLBox02
Database - DB2
Our Connection Strings in the web.configs would like this:
Provider=SQLOLEDB;Server=SQLBox1\SQLBox01;Database=DB1;Uid=User1;Pwd=Password123;
AND
Provider=SQLOLEDB;Server=SQLBox2\SQLBox02;Database=DB2;Uid=User2;Pwd=Password321;
In the situation of a failure, it is my understanding that the whole instance will fail over to the other node, be it 1 database or 10 databases. Using the information above, if SQLBox2\SQLBox02 has a failure and and that instance fails over to Node 1, that should change the second connection string to the following:
Provider=SQLOLEDB;Server=SQLBox1\SQLBox02;Database=DB2;Uid=User2;Pwd=Password321;
If this were to happen, it is my understanding that all of this automatic, but how does the web.config know that there was a failure and needs to change its connection string?
My understanding has been that there will be a "Cluster Name" (for lack of better of term) that will be in the connection string that knows of both SQL Instance's and in case of a failure the web.config does not care what Node it is running on.
In the above situation, how does the application/web.config know to change the server portion of the connection string?
Thanks in Advance for your Help!
July 12, 2013 at 7:20 am
In a cluster you'll have a cluster name, a virtual domain object. This domain object will have its own IP assigned and will connect to whichever server is hosting the resources.
The setup would look more like this
Node1:
Server Name - CLSQL01
SQL VCO - SQLBox1
Instance Name - SQLBox01
Database - DB1
Node2:
Server Name - CLSQL02
SQL VCO - SQLBox2
Instance Name - SQLBox02
Database - DB2
Cluster Info:
Cluster name (domain object) - clusName
Your connection string in the web config should be:
Provider=SQLOLEDB;Server=clusName\SQLBox01;Database=DB1;Uid=User1;Pwd=Password123;
Provider=SQLOLEDB;Server=clusName\SQLBox02;Database=DB2;Uid=User2;Pwd=Password321;
Each instance would be its own application within the cluster feature and can be handled independent of the other.
When an event occurs that results in a resource outage, the clustering feature will automatically move all the resources from that node to the secondary node. The connection string stays the same and the cluster's domain object handles pointing to which server hosts the resources requested.
July 12, 2013 at 7:38 am
Thanks for the reply Calvo, while I 100 percent agree with you, that is not how our clusters are set up. The information within my Node1, Node2, and the connection strings (in my first post) are actually how our environment is set up and it doesn't make sense to me.
July 12, 2013 at 7:56 am
If your cluster is created find your cluster name by looking in the Server Manager under Features > Failover Cluster Manager > Services and applications > [instanceName].
In the resource window in the middle content area you'll see a group called Server Name. The Name of that resource is the name of the server you should be connecting to in your connection string. That is the virtual domain object I was talking about. If you open a RDC and connect to that Name, you'll notice you've actually connected to one of the nodes (whichever one is hosting the resources at that time) but the connection first goes through the cluster name(and IP).
In your case, you should have two applications under the Services and Applications node, one for each instance. And as I type this I realize I made a mistake in my first explanation. Your second connection string should use the cluster name from the second cluster application.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply