6

Could anyone point me or tell me how to write connection string to a SQL Server cluster instance?

I want to establish trusted connection to a database (initial catalog) of the specific instance of a SQL Server cluster. I find for quite some time but can not find official answer from Google.

I am asking for the connection string for C# ADO.Net code.

I know how to write connection string to a normal one-machine non-cluster SQL Server instance.

Improve this question

4 Answers 4

Reset to default
4

The server name is the virtual server name.

Example:

  • You have Physical pserver1, pserver2
  • These together make cluster cserver -This hosts virtual servers vserv1 (and perhaps, see below) vserv2

So, it's vserv1\instancename or vserv1. you don't use physical server names

Edit, based on number of possible names for virtual servers:

The cluster may be active/passive /A/P) or active/active (A/A)

  • In A/P, only one virtual server is hosted and the standby node is not actively used.
  • In A/A, there are 2 virtual servers and normally each node hosts one. Each node is standby for the other.
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Confused -- "hosts virtual servers vserv1 (and perhaps) vserv2", do you mean the cluster has two virtual server name, vserv1 and vserv2? Or it is a typo?
what is the connection string format of SQL cluster non-default instance?
vserv1\instancename (or vserv2\instancenamein A/A scenarios
3

The connection string is written in the usual way; but in clustering you have 3 (or more) addresses: one for each node which constitues the cluster and one for the cluster. E.g.: we have two node with addresses 192.168.0.10 and 192.168.0.20: if you write down one of this numbers as the DataSource in the connection string, you'll gain access to that physical instance. But if you want to access the cluster (virtual) Sql Server instance, you'll have to use the cluster's address (e.g.: 192.168.0.230) as the DataSource. Of course, you can also use the machines' names instead of the IP addresses, provided you are in the same domain as the cluster.

Improve this answer

7 Comments

Using machine names does not depend on being in the same domain, only on the names being exposed in DNS. And having a rational DNS configuration: this might be a problem in some organisation :-(.
You're right; i was focusing on the connection string and missed this point, sorry.
@Turro, 1. what is the connection string format of SQL cluster non-default instance? 2. For a SQL cluster, do we allow to connect by machine name to access individual machine instead of virtual server name? Does such access corrupt data mirroring between two virtual nodes?
@Richard, what is the problem do you mean?
@George2: 1) the connection string is always the same, you just have to specify in the DataSource=xxx the name of the instance you want to access; e.g.: Password=pwd;Persist Security Info=True;User ID=userid;Initial Catalog=dbname;Data Source=THENAMEORTHEIPADDRESSOFTHEINSTANCE;
|
1

This is not documented anywhere that I could find, but I had to use the name of the availability group listener on the cluster. For example:

data-source=tcp:AG1-Listener

Literally, this is documented nowhere. I got lucky and found a connection string to a cluster on a blog somewhere and figured it out from there.

Improve this answer

1 Comment

It is now documented here: learn.microsoft.com/en-us/sql/database-engine/…
0

I know this is an old thread but:

https://support.microsoft.com/en-us/kb/273673

explains why a timeout should be specified on the connection. An extract is below.

If a SQL Server server is failed over by the cluster admin, TCP reset packets are not sent. If the SQL Server process is terminated by the operating system (by Kill.exe), the reset packets are sent.

This may affect the client application if the application does not specify a query timeout parameter or a query timeout of zero (0).

If the application does not have a query timeout value then open connections will be left in the ESTABLISHED state after a failover occurs. The fact that the open connections are not closed and that no further TCP packets are sent from those connections indicates that those connections are completely idle. Because the failover did not send any TCP reset packets to the client application, those open connections wait for the query results indefinitely (assuming an infinite query timeout), and potentially cause the connection to stop responding (hang).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.