TryCatchFinally.net Some SQL, some .NET, and whatever else

6May/094

Accessing a clustered SQL Server instance without the instance name

When I clustered SQL Server 2005 the first time, it bothered me that I had to access each clustered instance using both the cluster DNS name and the instance name. If my SQL Cluster is called SQL-CLUSTER and the DNS alias of my first instance is SQL-INSTANCE1, I had to connect to SQL-INSTANCE1INSTANCE1. Since each instance has a dedicated name and IP address that uniquely identifies it, shouldn't that be enough? Why is the instance name required?

It turns out it's not! With a little tweaking, you can access the instance using just the SQL DNS name, without the instance name, and it works for as many SQL instances as you have on a cluster, not just one (you'll need to repeat this process for each instance). Here's how:

  1. On the active node for the instance you're adjusting, open the "SQL Server Configuration Manager" from the start menu.
  2. Expand "SQL Server Network Configuration" and select the instance you want to edit.
  3. In the right window, right-click "TCP/IP" and select "Properties."
  4. On the "Protocol" tab, ensure that "Listen All" is set to "Yes"
  5. On the "IP Addresses" tab, scroll all the way to the bottom, to the "IPAll" section.
  6. The "TCP port" box is blank by default - set it to 1433, the default port for SQL Server.
  7. Click "OK" - you'll receive a message that you need to restart the SQL Service.
  8. Restart the SQL Service on that node.

Presto - done! You can now access this SQL Server instance using either "SQL-INSTANCE1\INSTANCE1" or just "SQL-INSTANCE". It's also worth noting that this network settings follows the instance between active nodes - if you failover the SQL instance to the other node, it should still be addressable the same way, and if you check the protocol settings, you'll see that your change is active on that server now as well.

To me, it's so much easier to not have to use the instance name when addressing the server, and since the service is responding on port 1433, the default port, this change should be compatible with every application that connects, and I haven't had any problems at all.

If you try this and run into any problems, please leave a comment below.

UPDATE:

After playing with this a little more, it seems like it only works with the SQL Native Client. If you attempt to connect via the SQL Server OLEDB driver or the old SQL 2000 driver, you get the error that you're not able to connect to the specified instance. Switching your connection method to the SQL Native Client (SQLNCLI) allows you to connect, assuming your application allows that. Has anybody else experienced this problem?

Comments (4) Trackbacks (1)
  1. this is great, it solved my nightmare.

  2. Yes, I’ve experienced the same thing with just the SQL Native client working in this configuration. But if you specify the port (even though it’s the default) like “server,port” then it works again even without the Native client.

  3. Thanks very much! This solved a problem we had on our new cluster (2008 and 2014 on the same hardware).

    • Glad to have helped! I’ve been using this trick for years and still not sure why it isn’t the default behavior when you configure a clustered instance.


Leave a comment

Why ask?