asp.net - 有时无法从 Azure 网站访问 Azure SQL 数据库

Question

我有一个 asp.net 应用程序部署到连接到 Azure SQL 数据库的 Azure 网站。去年这一直工作得很好，但上周末我开始在连接数据库时出现错误，给出以下堆栈跟踪。

[Win32Exception (0x80004005): Access is denied]

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]

此错误出现并持续几个小时，然后消失几个小时。数据库始终可以从我的机器访问。

我尝试过的一些事情是:

• 添加“允许全部”防火墙规则 (0.0.0.0-255.255.255.255) 无效
• 更改连接字符串以使用数据库所有者作为凭据无效。
• 真正起作用的是将 azure 托管级别更改为其他级别。这暂时解决了问题，网站可以再访问数据库几个小时。

可能发生了什么导致此错误开始出现？该应用程序自 8 月以来未曾更改。

编辑:Azure 支持发现实例上存在套接字和端口耗尽问题。其根本原因是什么，目前尚不清楚。

Answer 1

Craig 是正确的，您需要实现 SQLAzure transient 故障处理。您可以在这里找到说明:https://msdn.microsoft.com/en-us/library/hh680899(v=pandp.50).aspx

摘自文章

You can instantiate a PolicyRetry object and wrap the calls that you make to SQL Azure using the ExecuteAction method using the methods show in the previous topics. However, the block also includes direct support for working with SQL Azure through the ReliableSqlConnection class.

The following code snippet shows an example of how to open a reliable connection to SQL Azure.

using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.AzureStorage;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;

...

// Get an instance of the RetryManager class.
var retryManager = EnterpriseLibraryContainer.Current.GetInstance<RetryManager>();

// Create a retry policy that uses a default retry strategy from the 
// configuration.
var retryPolicy = retryManager.GetDefaultSqlConnectionRetryPolicy();


using (ReliableSqlConnection conn = 
  new ReliableSqlConnection(connString, retryPolicy))
{
    // Attempt to open a connection using the retry policy specified
    // when the constructor is invoked.    
    conn.Open();
    // ... execute SQL queries against this connection ...
}

以下代码片段显示了如何通过重试执行 SQL 命令的示例。

using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.AzureStorage;
using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;
using System.Data;

...

using (ReliableSqlConnection conn = new ReliableSqlConnection(connString, retryPolicy))
{
    conn.Open();

    IDbCommand selectCommand = conn.CreateCommand();
    selectCommand.CommandText = 
      "UPDATE Application SET [DateUpdated] = getdate()";

    // Execute the above query using a retry-aware ExecuteCommand method which 
    // will automatically retry if the query has failed (or connection was 
    // dropped).
    int recordsAffected = conn.ExecuteCommand(selectCommand, retryPolicy);

}