FAQs about SQLserverconnectionKeepAlive

Reprinted: FAQs about SQLserverconnectionKeepAlive: 1. What is the keepAlive of the SQLserverTCP connection? To put it simply, keepalive is the keepaliveinterval and keepalivetime parameters of the TCP protocol specified by SQLserver when establishing each TCP connection. In this way, for each TCP connection, if the connection

Reprinted: FAQs about SQL server connection KeepAlive: 1. What is the keep Alive of the SQL server TCP connection? To put it simply, keep alive is the keepaliveinterval and keepalivetime parameters of the TCP protocol specified by SQL server when establishing each TCP connection. In this way, for each TCP connection, if the connection

Reprinted: FAQs about SQL server connection KeepAlive

To:

1. What is the keep Alive of the SQL server TCP connection?

To put it simply, keep alive is the keepaliveinterval and keepalivetime parameters of the TCP protocol specified by SQL server when establishing each TCP connection. In this way, if the idle time of each TCP connection (without any data interaction) exceeds keepalivetime, the TCP protocol will automatically send a keepalive packet to check whether the connection is alive or not. If the number of keepalive detection times exceeds the value defined by the Registry TcpMaxDataRetransmissions and the other party does not respond, TCP considers the connection to be faulty and closes it. Through this mechanism, SQL server can detect problems such as orphaned connection.

For each TCP connection, SQL server sets the default value of keep alive to 30 seconds and keepaliveinterval to 1 second. TcpMaxDataRetransmissions configured for Windows TCP is 5 times by default. That is to say, if the TCP connection idle takes 30 seconds, TCP will send the first keepalive check. If the packet fails, TCP resends the keepalive packet every one second, and the Hong Kong virtual host continues to resend the packet five times. If the fifth check still fails, the connection is closed. Therefore, if a TCP connection encounters an exception, it will be closed in about 35 seconds.

2. Where can I configure the keep alive configuration of SQL server?

The code in SQL server 2000 also specifies the keep alive attribute for TCP connections, but does not provide user interfaces for custom modifications. SQL server2005 uses configuration manager to modify the keep alive value, but does not modify keepalive interval. Keepaliveinterval is the 1 second of hardcoded.

The Configuration manager interface is as follows:

This value is saved in the following registry location:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft SQL Server \ MSSQL .? \ MSSQLServer \ SuperSocketNetLib \ Tcp

Note that the Native client of SQL server has a similar configuration. Do not mix it with the TCP configuration of server side:

The Native client's keep alive configuration is saved in the following location:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ Client \ SNI9.0 \ tcp

3. Is the keepalive in SQL server the same as that in Windows TCP?

The principle is the same, but it does not interfere with each other.

The TCP protocol for Windows also has the keep alive configuration. The Hong Kong server is located as follows:

HKLM \ SYSTEM \ CurrentControlSet \ Services \ Tcpip \ Parameters

The operating principle of the operating system's TCP keep alive and SQL server's keep alive is the same, the Hong Kong virtual host, the keepalive attribute of the TCP connection is specified when a TCP connection is established (see the description below ). However, SQL server reads the keep alive of its own registry to set the TCP connection attribute, regardless of the keepalivetime and keepaliveinterval values in the registry of windows OS.

If an application does not explicitly call a function to set the keepalive attribute of a TCP connection, its TCP connection uses the OS TCP configuration by default. The OSkeep alive configuration is disabled by default.

For more information about the TCP configuration of the operating system, see the following documents:

KeepAliveInterval

Key: Tcpip \ Parameters

Value Type: REG_DWORD-Time in milliseconds

Valid Range: 1-0 xFFFFFFFF

Default: 1000 (one second)

Description: This parameter determines the interval that separates keepalive retransmissions until a response is already ed. after a response is already ed, KeepAliveTime again controls the delay until the next keepalive transmission. the connection is aborted after the number of retransmissions that are specified by TcpMaxDataRetransmissions are unanswered.

KeepAliveTime

Key: Tcpip \ Parameters

Value Type: REG_DWORD-Time in milliseconds

Valid Range: 1-0 xFFFFFFFF

Default: 7,200,000 (two hours)

Description: The parameter controls how frequently TCP tries to verify that an idle connection is still intact by sending a keepalive packet. if the remote computer is still reachable and functioning, the remote computer acknowledges the keepalive transmission. by default, keepalive packets are not sent. A program can turn on this feature on a connection

4. How does TCP keep alive in SQL server and OS be implemented?

For details, see the following documents:

SQL server also calls the following API to pass the keepalive parameter (lpvInBuffer) to this API:

Int WSAIoctl (

_ In SOCKET s,

_ In DWORD dwIoControlCode,

_ In LPVOID lpvInBuffer,

_ In DWORD cbInBuffer,

_ Out LPVOID lpvOutBuffer,

_ In DWORD cbOutBuffer,

_ Out LPDWORD lpcbBytesReturned,

_ In LPWSAOVERLAPPED lpOverlapped,

_ In LPWSAOVERLAPPED_COMPLETION_ROUTINE lpCompletionRoutine

);

This article provides the following description:

SIO_KEEPALIVE_VALS (opcode setting: I, T = 3)