Back to the SQL Server Basics with Wayne

Connecting to a SQL Server instance is one of the first things that you will do after installing SQL Server. There are different ways of connecting to the instance. Certain High Availability features offer other ways to connect. In this Back to Basics article, I’ll cover the various methods of connecting to a SQL Server instance.

Protocols

The first configuration setting that impacts how you can connect to an instance is which protocols that are enabled. You can examine (and change) the enabled protocols from the SQL Server Configuration Manager (SSCM) tool:

Available Protocols for connecting to a SQL Server instance

In the left pane, expand the “SQL Server Network Configuration” node, and you will see a node for each instance of SQL Server installed on the computer. Click on one of the nodes, and you will see the available protocols for that instance. In the above image, you can see that I have three available protocols for the selected instance, and that they are all enabled. In a default installation, the “Named Pipes” protocol is not enabled. Additionally, the “TCP/IP” protocol is disabled for the Development edition of SQL Server. The “TCP/IP” protocol and remote Named Pipes are disabled in SQL Server Express editions. You can read all about these different providers at this Microsoft link.

Remote Connections

The next configuration item that has an impact on how you can connect to the SQL Server instance is whether the instance allows remote connections:

Configuring remote connections

Alternatively, you can use T-SQL to see check this configuration:

EXECUTE sp_configure 'remote access';
SELECT *
FROM sys.configurations
WHERE name = 'remote access';

See Jason Brimhall’s post on other ways to access the configuration options.

To configure remote access with T-SQL:

EXECUTE sp_configure 'remote access', 1;
RECONFIGURE WITH OVERRIDE;

This is not an advanced option, so you don’t need to turn on “Show Advanced Options” first.

Connecting with Shared Memory

Shared memory can only connect to a SQL Server instance installed on the same computer. The normal method of connecting with Shared Memory is by the computer name (or computer name\instance name for a named instance). However, there are some alternatives. All of the methods for connecting by Shared Memory are:

• computer name[\instance name]
• .[\instance name]
• localhost[\instance name]
• (local)[\instance name]

Connecting with Named Pipes

A named pipes connection can be used to connect to SQL Server instances on the local area network. It has the format of \\.\pipe\sql\query, where the “.” has the format of “.[\service name of named instance]”. For my SQL 2012 instance, it would be \\.\pipe\MSSQL$SQL2012\sql\query. As above, the “.” indicates the local computer name. However, the following can be used in place of the “.”:

• . (*)
• Computer name
• Computer’s IP address
• localhost (*)
• loopback (*)
• 127.0.0.1 (*) (loopback / localhost IP address)

(*) – instances on the local computer only

Connecting with TCP/IP

The method that you are most likely to use to connect to a remote SQL Server instance is TCP/IP. With this protocol, it is possible to connect to any SQL Server instance in the world. You can use the following for connecting to a SQL Server instance with the TCP/IP protocol:

• Computer Name
• Fully Qualified Name for the computer (domain only)
• Computer’s IP Address
• 127.0.0.1 (*) loopback / localhost IP address)
• loopback (*)

(*) – instances on the local computer only

Connecting to High Availability SQL Servers

If the SQL Server instance is installed on a cluster, then you can connect to the instance on the active node of the cluster by:

• Cluster Name.
• Fully Qualified Name for the cluster.
• Cluster’s IP address.

If the SQL Server is a clustered instance, or an Availability Group (AG), then you can connect to it by:

• Clustered instance name / AG name.
• Fully Qualified Name for the clustered instance / AG.
• IP address for the clustered instance / AG.

Note that the active node of the cluster is not necessarily the active node of the AG.

All of these options are only available through the TCP/IP protocol. Remember that they connect to the instance on the active node. The active node for an AG may be different than the active node for the cluster that the AG is on.

Testing out all of these connections

On my local machine, I have configured a connection using all of the non-domain ways available to me. I used a Registered Server group (see my recent post about Registered Servers), and it looks like this:

Local Connection Methods

As seen by this image, the connections not using the Named Pipes protocol show as connected. This is already a great sign! Now, if I open up a Multi-Server Query and run the following statement, I can see each connection, what @@SERVERNAME returns for that connection, and the protocol used for connecting:

SELECT @@SERVERNAME [@@SERVERNAME], net_transport
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID;

On my system, this returns:

We can see that these connections did in fact connect to the proper instance, and which protocol each connection uses.

Testing on an Availability Group

On a client’s system, I created all the TCP/IP and Shared memory connections. This screen shot shows all the possible connections:

AG Connection Methods

When running the same query, all the new connections for the cluster / AG are under the TCP protocol. The cluster connections connect to Node 2 of the cluster; all the remaining connections connect to Node 1.

How can these help me?

By now, you’re probably saying to yourself “Great – but why should I care about any of this?”. By knowing these various methods, you can do various troubleshooting. Let’s say that you can connect on the local server using any of the Shared Memory protocols, but not the other protocols. You should check that the other protocols are enabled. If you can connect on the local computer only, but not remotely, check out the “Allow Remote Access” option. If you can connect by IP address, but not by names, you need to check out your DNS server.

The Wrap Up

In my first Back to Basics post, we have covered:

1. How to enable a protocol for a SQL Server instance to communicate on.
2. Enabling a SQL Server instance for remote communication.
3. Identified a multitude of ways to establish a connection to a SQL Server instance.
4. Various methods of connecting to a High Availability SQL Server instance.
5. And finally, a demonstration showing all of the various ways of connecting to a SQL Server instance, and the protocol used.

The post Connecting to a SQL Server instance – Back to Basics appeared first on Wayne Sheffield.