SQL Server 2008 R2 TCP/IP Networking Explained

 

 

Beforehand we explore into the features on the client side in SQL Server, it's vital to mark note of a few server-side features. This info will help you increase an understanding of which networking features are initially configured on the server as well as how inward connections are dealt with. Such information can be invaluable in identifying connectivity problems.To get your clients up and running fast, you need to be sure the Database Engine is snooping for them. The subsequent section define how to set up the server’s basic network config, with configuring it to receive remote connections, learning which protocols it supports, and understanding in what way it listens for and replies to client demands.

The main and most simple step after a SQL Server installation or upgrade is to ensure the correct network protocols are constructed on the server. First, you must ensure that the protocols your clients when used to connect to SQL Server 7, 2000 or 2005 (or that your clients would like to use) are still supported by SQL Server 200. You may be amazed to learn that the subsequent protocols that were supported in SQLServer 2000 are no longer supported by SQL Server 2005 or SQL Server 2008:

Subsequent are the only protocols that SQL Server 2008 supports:

If you were using any of these protocols and you recently upgraded, Setup copies your pre-upgrade configuration over to SQL Server 2008, including the enabled state, IP addresses, TCP ports, pipe names,.. Clients can basically test their connections to confirm the upgrade was effective, and in most cases, no changes need to be made.

In SQL Server 2008, remote connections need to be allowed for each network protocol on which you want the server to connect. This is easily accomplished using the SQL Server Configuration Manager.  You open SSCM from the SQL Server 2008 Config Tools menu group. In SSCM, you expand SQL Server Network Configuration and and select the Protocols entry for the SQL Server instance that you want to configure. In the Details screen, rightclick on one of the presented protocols (for example, Named Pipes) and select allow to allow connections for this protocol.

When the protocol is permitted, SQL Server is configured to snoop for connections from clients using the same protocol. You must restart the SQL Server for the modifications to take effect and for SQL Server to truly start listening for connections. You can confirm that SQL Server is listening on the protocol that you have enabled by using netstat command and or looking at the logs. Every time the SQL Server is restarted,log messagesare written to the log specifying which protocols it is listening on. The subsequent sample error log messages display what SQL Server is listening for:

Server i s listenin g on [ 'any' <ipv4> 1719] ,
Server named pipe provider i s ready to accept connection on
[ \ \ . \pipe\MSSQL$INST2008\sql\query ] .

The server listens on all designed protocols simultaneously, giving no preference or priority to any. This is different to the unambiguously prioritized manner in which clients effort to connect via all configured protocols. This networking feature in SQL Server 2008 enhances an additional layer to the client/server network structure with Tabular Data Stream (TDS) endpoints. When you deploy SQL Server 2008, a default system endpoint is generated on the server for each accessible protocol on the server. These endpoints can’t be dropped, and they are made regardless of whether the protocol is disabled or else unavailable. Below are the default system endpoints:

 You may view these endpoints and check their status by running the following T-SQL commands:

Use Master
Go
SELECT * FROM sys.endpoint s WHERE principal_i d = 1

Out of the box, all users are allowed access to these endpoints (except the DAC, which is only for associates of the sysadmin role). Administrators may create additional endpoints on the server to increase connection security by stopping the default system endpoints and then making new user-defined endpoints that precise clients access. Each endpoint interconnects with clients via TDS packets, which are arranged on the server side by SNAC and on the client side by SNAC or another of the net-libraries. Administrators have the preference of stopping and starting endpoints while sessions are still active, preventing new connections from being made while still supporting current ones. An administrator can allow or deny revoke endpoint access to precise users or groups . It is so vital for clients to know that this configuration exists and to learn how they obtain permission to connect to endpoints via a server-side procedure.