如何检查 SQL Server 是否还活着？
我有一种方法来检查并确保我的 SQL 服务器在线，我在代码的某些连接敏感部分中使用了该方法。
虽然它工作正常，但我注意到它需要长达 20 毫秒才能运行，我想知道是否有人知道检查 SQL Server 以确保其正常运行的更好方法。
这是我现有的代码。
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
public static bool IsSqlServerOnline(string connectionString)
{
#if DEBUG || DEBUG_SINGLE_CORE
Stopwatch sw = new Stopwatch();
sw.Start();
#endif
#if DEBUG || DEBUG_SINGLE_CORE
// This sould only occur of we are in the VSTS designer
if (string.IsNullOrEmpty(connectionString))
{
return false;
}
#endif
if ( !NetworkInterface.GetIsNetworkAvailable() )
{
return false;
}
if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException("connectionString");
bool isSqlServerOnline = false;
SqlConnectionStringBuilder conString = new SqlConnectionStringBuilder(connectionString);
try
{
using (Ping p = new Ping())
{
string sqlSvr = conString.DataSource;
if (sqlSvr != null)
isSqlServerOnline = p.Send(sqlSvr).Status == IPStatus.Success;
}
}
catch (PingException)
{
isSqlServerOnline = false;
}
if ( isSqlServerOnline )
{
try
{
conString.ConnectTimeout = 3;
using (SqlConnection conn = new SqlConnection(conString.ToString()))
{
conn.Open();
isSqlServerOnline = true;
}
}
catch ( Exception )
{
isSqlServerOnline = false;
}
}
#if DEBUG || DEBUG_SINGLE_CORE
sw.Stop();
Trace.WriteLine(string.Format("IsSqlServerOnline: {0}", sw.ElapsedMilliseconds));
#endif
return isSqlServerOnline;
}
I have a method to check and make sure my SQL server is online, which I use in some connection sensitive parts of my code.
Although it works fine, I notice it takes upto 20ms to run, and I was wondering if anyone knows of a better way of checking SQL server to ensure its up and kicking.
Here is my existing code.
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
public static bool IsSqlServerOnline(string connectionString)
{
#if DEBUG || DEBUG_SINGLE_CORE
Stopwatch sw = new Stopwatch();
sw.Start();
#endif
#if DEBUG || DEBUG_SINGLE_CORE
// This sould only occur of we are in the VSTS designer
if (string.IsNullOrEmpty(connectionString))
{
return false;
}
#endif
if ( !NetworkInterface.GetIsNetworkAvailable() )
{
return false;
}
if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException("connectionString");
bool isSqlServerOnline = false;
SqlConnectionStringBuilder conString = new SqlConnectionStringBuilder(connectionString);
try
{
using (Ping p = new Ping())
{
string sqlSvr = conString.DataSource;
if (sqlSvr != null)
isSqlServerOnline = p.Send(sqlSvr).Status == IPStatus.Success;
}
}
catch (PingException)
{
isSqlServerOnline = false;
}
if ( isSqlServerOnline )
{
try
{
conString.ConnectTimeout = 3;
using (SqlConnection conn = new SqlConnection(conString.ToString()))
{
conn.Open();
isSqlServerOnline = true;
}
}
catch ( Exception )
{
isSqlServerOnline = false;
}
}
#if DEBUG || DEBUG_SINGLE_CORE
sw.Stop();
Trace.WriteLine(string.Format("IsSqlServerOnline: {0}", sw.ElapsedMilliseconds));
#endif
return isSqlServerOnline;
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的代码检查您是否能够建立与 SQL Server 的连接。
您可能还有兴趣更进一步并通过运行类似的命令来验证它是否正在响应查询
Your code checks if you are able to establish a connection to the SQL Server.
You may also be interested in taking it one step further and verifying that it is responding to queries by running something like
正如其他人提到的，20 毫秒的障碍可能不需要担心。但是，我可能会问代码被调用的频率是多少？将调用结果缓存 3-5 秒（甚至只是 2-3 秒）是否足够？而不是每次都访问网络？ 2-3 秒是一个相当小的缓存窗口，而且没有什么可说的，“服务器活动”检查不能返回 OK，只会导致服务器在代码执行过程中崩溃。如果您浪费的只是每隔几秒（最多） 20 毫秒，那几乎没有什么可担心的。
As others have mentioned, the 20ms barrier is probably not anything to worry about. However, I might ask how often the code gets called? Would it be sufficient to cache the result of the call for 3-5 seconds (or even just 2-3)? instead of accessing the network each time? 2-3 seconds is a pretty small cache window, and there's nothing to say that a "server alive" check couldn't return OK only to have the server crash in the middle of the code executing anyway. If all you're wasting is 20 ms every few seconds (at most), that's hardly anything to worry about.
您可以使用 Windows 服务控制管理器 API：
如果状态为 SERVICE_RUNNING，则 ，服务就起来了。
如果您不熟悉 P/Invoke，请查看 System.ServiceProcess.ServiceController。该函数
看起来可能用于检索特定服务的状态。
You could use the Windows service control manager API:
If the status is SERVICE_RUNNING, the service is up.
If you're not into P/Invoke, have a look at
System.ServiceProcess.ServiceController. The functionlooks like it might be used to retrieve the status of a specific service.