6.4 Configuring network Protocols

6.4 Configuring network Protocols

The database engine must have network protocols enabled before the client connects to the database engine. The SQL Server database engine can simultaneously enable multiple network protocols for the request service, and the client uses a single protocol to connect to the database engine.

You can enable or disable a network protocol directly in the right-click menu. You can also configure it in detail in the Properties window, and the modified properties take effect after restarting the instance.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/5D/E1/wKioL1UmT7-AQQLQAAGn-AgHTzw601.jpg "title=" disables. png "alt=" Wkiol1umt7-aqqlqaagn-aghtzw601.jpg "/>

6.4.1 Configuring the Shared Memory protocol

The Shared Memory protocol is configured with only one enabled option.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/5D/E0/wKioL1UmOnLzEpk-AADJOgTryGI612.jpg "title=" Shared Memory.png "alt=" Wkiol1umonlzepk-aadjogtrygi612.jpg "/>

6.4.2 Configuring Named Pipes protocol

If you need to enable the Named Pipes protocol, you can set the enabled option to Yes.

By default, the pipe name for the default instance is: \\.\pipe\sql\query; the name of the name of the named instance in pipe name, for example: \\.\pipe\mssql$sql2008\sql\query. This property box allows a maximum of 2047 characters.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/5D/E4/wKiom1UmOTfCmHMmAADGozBsjDM436.jpg "title=" Named Pipes.png "alt=" Wkiom1umotfcmhmmaadgozbsjdm436.jpg "/>

6.4.3 Configuring the TCP/IP protocol

Keep alive Specifies the interval of time, in milliseconds, for a transport-persisted packet to check if the computer at the far end is available.

Typically, the development, evaluation, and Express editions are primarily used in native environments, so these versions of the Setup program disable the TCP/IP protocol by default. If you need to enable it, set the enabled option to Yes.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5D/E4/wKiom1UmOUWCFE06AADkz43VoqE661.jpg "title=" TCP Ip.png "alt=" Wkiom1umouwcfe06aadkz43voqe661.jpg "/>

The Listen all option specifies whether the database engine listens for all IP addresses that are bound to the computer's computer card. The default is yes, which means that the setting in the IPALL property box at the bottom of the Properties window applies uniformly to all IP addresses, ignoring the respective properties of each IP address.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/5D/E1/wKioL1UmVLuT7RRXAAGqy2XTeaI282.jpg "title=" IP address. png "alt=" wkiol1umvlut7rrxaagqy2xteai282.jpg "/>

If the Listen all option is set to No, each IP address is configured with its own Properties dialog box for each IP address. When the active option is yes, it indicates that the IP address is active on the computer. The Enabled option indicates that the IP address listens for incoming connections to the instance on the computer.

Tips:

If the Listen all option is set to No, and the enabled option for an IP is set to Yes, be aware of the validity of this IP address. When SQL Server starts to discover that the IP address does not actually exist, an event with an ID of 26024 is written in the Applications node of the Windows event , prompting that the server cannot listen on an IP address. This event also causes the database engine to fail to start and logs an event with an ID of 17826, prompting that the network library cannot be started because of an internal error in the network library .

SQL Server supports two ways of listening to ports.

Static port

The default instance of SQL Server listens for incoming connections on port 1433. This port can be changed for security reasons or based on business requirements. You can listen to multiple static ports at the same time, the port number is listed in comma-delimited format, and the Static Port property box allows up to 2047 characters.

By default, named instances are configured to listen on dynamic ports. To configure a static port, leave the TCP Dynamic port box blank and provide an available port number in the TCP Port box.

When the database is enabled for a listening port, it is not actively opening the appropriate port in Windows Firewall, configure the firewall to allow inbound connections from client computers.

Dynamic ports

If an instance is configured to listen on a dynamic port, you need to set TCP dynamic port to 0. At startup, the instance checks the available ports in Windows and opens an endpoint for that port for incoming connections to the client. After the database engine has started successfully, you can view the port numbers that are currently in use in TCP dynamic ports.

A dynamic port is typically provided by the SQL Server Browser service to the client to provide parsing services, otherwise the client must know beforehand which port number the instance is currently listening on, and provide the port number in the connection string.

Because the port number can change every time the instance is started, you may need to change the configuration of the firewall after each reboot. To avoid problems connecting through a firewall, you can use a static port instead.

Tips:

If SQL Server discovers that the port is already occupied by another service or application at startup, it writes an event with an ID of 26023 in the Applications node of the Windows event, prompting that the server TCP provider cannot listen on a port , because this port is already in use. This event also causes the database engine to fail to start and logs an event with an ID of 17826 .

This article from "SQLServer2014 series" blog, declined reprint!

6.4 Configuring network Protocols