有关SQL server connection Keep Alive 的FAQ(3)

  • Article

 

这个是SQL Server Keep Alive FAQ文章的最后一篇。

 

问题1：为什么我在客户端设置了KeepAlive值,但是我使用SQL server management studio 测试的时候，该值总是不起作用？

 

[答]目前市场上主流的数据库访问技术有ODBC，OLEDB，SQL native client 和 SQLclient。其中SQLclient是.NET里面使用的托管provider。目前SQL Server configuration manager里面提供的keepalive设置是针对SQL native client 的：

 

 

也就是说， 该值只对使用SQL Native Client 来访问数据库的程序有效。SQLcmd.exe是使用SQL native client来访问SQL server的一个命令行工具，所以您可以使用SQLCmd来测试上面的keepalive值。注意修改keepalive值后，你需要退出SQLcmd重新运行它才能使用新值。我在几组机器测试了几组不同的keepalive值，SQLcmd程序的TCP连接都很好按照设置的值发出keepalive网络包。注意你需要使用TCP连接服务器来测试。 我使用下面的连接串以保证使用TCP 连接：

 

SQLcmd -E -STCP:mycomputer\SQL2012,55890

(机器名：mycomputer，实例名：SQL2012，端口号 55890)

 

我使用network monitor 3.4捕获keepalive包如下图：

 

 

 

上图中深蓝高亮的是我挑出的由服务器TCP连接发给SQLcmd的keepalive包，每个包间隔30秒（时间间隔可以看time offset前后相减即可）。而上图间隔7秒的是SQLcmd的TCP连接发出的keepalive包。我测试中使用SQL server configuration manager设置了native client 的keepalive为7秒。从上面图也印证了服务器端的TCP连接和客户端的TCP连接各自发送自己的keepalive包，相互不影响。

 

注意， SQL Server native client的keepalive值存放在下面注册表里面：

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNIx.0\tcp\Property2

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNIx.0\tcp\Property3

 

那么SQL server management studio （ssms.exe）是使用.NET managed的SQLclient,所以上面针对native client的设置对SQLclient无效。SQL server management studio  使用了 hard-coded的30秒值，无法修改。

 

 

问题2. ODBC，SQL OLEDB，SQL native client和SQLClient的TCP keepalive缺省时间都是多少？在哪里可以设置?

 

[答]缺省值都是30秒。

 

可以修改如下注册表值来设置ODBC 和SQL OLEDB的keepalive值：

 

HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp ==> KeepAliveTime : DWORD : <dwValue>

HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp ==> KeepAliveInterval : DWORD : <dwValue>

 

注意如果是x64的机器，在上面运行32位的ODBC/SQL OLEDB程序，则需要修改如下注册表：

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SuperSocketNetLib\TCP

 

对于SQLserver native client 和SQL server 的keep alive设置，可以在SQL server configuration manager 修改，具体请参考请参考FAQ （1）和FAQ （2）或问题1。

 

对于SQL client，需要在使用sqlclient的应用程序里面使用代码设置。

 

问题3：服务器端如果网线被拔掉，这对于那些已经存在的TCP连接会有什么影响？SQL server会马上关闭连接么？如果拔掉网线然后马上又插回去呢？

 

[答]这样问题复杂一些。当网线拔掉的时候，网卡首先获知该事件。Windows 2003以及后面的操作系统都实现了media sense 媒体感知功能。操作系统收到网卡发来的media sense 的断线（disconnect）事件后，如果在DisconnectDampInterval（一般是5-10秒）的时间内，网线连接还没有恢复，那么就会发送信息给侦听该网卡IP的连接。那么所有监听该IP的上层应用程序如SQL server 就会得到disconnect的事件，然后就会进行相应的动作如kill connection， rollback事务等。

 

具体点来说，SQL server 的网络IO是通过IOCP （IOCompletionPort）实现的。 SQL server在后台有几个专门的线程侦听该端口。这些线程调用GetQueuedCompletionStatus（）函数得到网络数据状态，然后调用相应的callback函数（如ReadHandler函数）来做具体的数据处理。当网线断的时候，操作系统过了DisconnectDampInterval时间后，GetQueuedCompletionStatus函数就会得到disconnect信息，而callback函数readhandler就被调用来读取数据但实际上是读到disconnect的网络断事件。SQL server 得知该disconnect事件后，就生成一个task，该task就是做kill session的任务，把事务会滚，把连接(connection)关掉。

 

所以说，如果你在很快的时间内（几秒），拔掉网线马上插回来，那么SQL server的TCP连接(connection)会保持不会被关闭。

 

你或许会问，media sense功能如何disable呢？具体可以参考如下命令：

 

netsh interface ipv4 set global dhcpmediasense=disabled

netsh interface ipv6 set global dhcpmediasense=disabled

 

如何察看这些设置的命令如下：

netsh interface ipv4 show global

netsh interface ipv6 show global

 

回到keepalive。 如果SQL Server服务器的TCP的keepalive设置时间很短，小于DisconnectDampInterval -5时间值，那么SQL server会先于操作系统得知连接断线的状态而采取相应的动作。实际中不建议过短的keepalive值。

 

问题4：DisconnectDampInterval在哪里可以修改？有没有media sense的更多的信息？

[答]可以参考如下WinCE的文档：在

https://msdn.microsoft.com/en-US/library/ee486075(v=WinEmbedded.60).aspx

 

ConnectDampingInterval specifies the amount of time, in seconds, by which TCP/IP will delay any action in response to a Media Sense connect event.

DisconnectDampInterval specifies the amount of time, in seconds, by which TCP/IP will delay any action in response to a Media Sense disconnect event.

 

在非CE平台上该定义依然有效，不过，缺省值是5~10秒。可以修改如下注册表修改该值：

 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Parameters

DisconnectDampInterval

ConnectDampInterval

 

这两个参数有最大最小值限制, Min = 5 seconds,Max = 10 seconds。

 

问题5：如下图SQL server的网络协议TCP的属性页为何没有keep alive interval选项？

 

 

[答]SQL server的网络协议TCP的keep alive interval是不能修改的,注册表也无法修改. SQL server源代码里面写定了1秒的时间间隔.

 

问题6：我的应用程序如何才能捕获keepalive机制侦测到的网络问题？

[答] 对于用户程序,你需要调用ExecuteSQL或类似的方法才能获知connection的真实状态. 如下面例子所示:

 

using (SqlConnection connection = new SqlConnection())

{

connection.ConnectionString ="Data Source=TCP:cn-simon-w7\\sql2012,55890;Initial Catalog=test;Integrated Security=true;pooling=false";

 

  SqlCommand cmd = new SqlCommand();

  cmd.CommandText = "select * from t";

  cmd.Connection = connection;

  cmd.CommandTimeout = 0;

 

 try

 {

  connection.Open();

   cmd.ExecuteNonQuery();

  }

 

 catch (Exception ex)

   {

      //do your work here for this exception

       MessageBox.Show(ex.Message);

    };

                                

}

 

我做了小测试步骤如下:

 

1) 想办法阻塞 “select * from t”使得它不返回结果而处于等待状态。

2) 然后拔掉服务器网络线。

3) 然后过一会，我的catch语句捕获了错误：

 

你其实可以对ex对象做更多的处理，请参看有关编程文档，此处不多描述。