SQL Server 重新启动后，C# Windows 服务 Sql 客户端无法连接到数据库

发布于 2024-11-30 22:27:05 字数 905 浏览 1 评论 0原文

我有一个 Windows 服务，它将数据更新到远程计算机中的数据库表。我想检查在 SQL Server 由于任何原因（可能是 SQL Server 软件重新启动或实际的 Windows 计算机突然重新启动）而关闭并重新启动后，服务是否正常工作。

我已经在10台服务器上安装了这个Windows服务。现在，当我重新启动开发sql server box时，所有10个box中运行的所有客户端服务程序都无法重新连接。但是，在我重新启动客户端 Windows 服务后，它工作正常。

我希望客户端程序在服务器可用后自动重新连接，并避免手动重新启动它们。

有什么建议吗？提前致谢。下面是我用来连接到 sql 服务器的代码。

private bool ConnectToSqlServer()
    {
        try
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
                return true;
            }
            return false;
        }
        catch(Exception Ex)
        {
            eventLog1.WriteEntry("ConnectToSqlServer " + Ex.Message, EventLogEntryType.Error);
            return false;
        }
    }

Windows 2008 Enterprise R2 上的 SQL Server - 2008。客户端程序为C#，在Visual Studio 2010中开发，使用.net Framework 4。

原文

I have a windows service which updates data to a database table in a remote machine. I wanted to check if the service works fine after the SQL server goes down and restarts for any reason (it may be SQL server software restart or the actual windows machine rebooting abruptly).

I have installed this windows service in 10 servers. Now, when I restarted the development sql server box, all the client service programs running in all the 10 boxes were unable to reconnect. However, it works fine after I restart the client windows service.

I want the client programs to automatically reconnect once the server is available and avoid restarting them manually.

Any advise please? Thanks in advance.
Below is the code I'm using to make a connection to the sql server.

private bool ConnectToSqlServer()
    {
        try
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
                return true;
            }
            return false;
        }
        catch(Exception Ex)
        {
            eventLog1.WriteEntry("ConnectToSqlServer " + Ex.Message, EventLogEntryType.Error);
            return false;
        }
    }

SQL Server - 2008 on Windows 2008 Enterprise R2.
Client program is C#, developed in visual studio 2010, which uses .net frame work 4.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

枫以 2024-12-07 22:27:05

我假设您只尝试连接一次并且遇到异常。如果你不这样做，那么下面的代码将不起作用...

在客户端访问数据库的任何地方添加一个 SqlException Catch 块。然后从该 SqlException 块调用逻辑，在 y 次迭代中每 x 秒重试一次连接。如下所示：

try
{ 
    //Database access code here
}
catch(SqlException sexc)
{
    if (!ReconnectToDatabase())
    {
        // log error to event log
    }
}

private bool ReconnectToDatabase()
{
   bool isConnected = false;
   for (int i = 0; i < 5; i++)  // 5 is arbitrary, use whatever retry count you want
   {
       if (ConnectToSqlServer())
       {
           break;
       }
       System.Threading.Thread.Sleep(5000);  // 5 seconds is arbitrary
   }
}

同样，重试计数和等待秒数对于本文来说是任意的。您需要考虑客户端等待时的停机时间，以及 SQL Server 重新启动通常需要多长时间。

如果您尝试重新连接，则在 SqlException 块中调用 SqlConnection.ClearAllPools()。

I'm assuming you're only trying to connect once and you're getting an exception. If you're not then the below will not work...

Add a SqlException Catch block anywhere you access the database in your client. Then from that SqlException block invoke logic that retries to connect every x seconds for y iterations. Something like below:

try
{ 
    //Database access code here
}
catch(SqlException sexc)
{
    if (!ReconnectToDatabase())
    {
        // log error to event log
    }
}

private bool ReconnectToDatabase()
{
   bool isConnected = false;
   for (int i = 0; i < 5; i++)  // 5 is arbitrary, use whatever retry count you want
   {
       if (ConnectToSqlServer())
       {
           break;
       }
       System.Threading.Thread.Sleep(5000);  // 5 seconds is arbitrary
   }
}

Again, the retry count and wait in seconds is arbitrary for this post. You will need to take into consideration the down time of your client as it waits, and how long it normally takes for SQL Server to restart.

If you are trying the reconnect then in the SqlException block, call SqlConnection.ClearAllPools().

回复收藏 0 原文