TransactionAbortedException and sql server connection does not restore

[luyangdev]

Hello, I got TransactionAbortedException after restarting sql server. SqlServer connection does not restore.

Orleans.Transactions.OrleansTransactionAbortedException: Transaction 4357009a-e08b-4866-bce4-11f8a95ed22c Aborted because of an unhandled exception in a grain method call. See InnerException for details.
 ---> Orleans.Runtime.OrleansException: Error from storage provider AdoNetGrainStorage.TestGrains.TaskGrain,TestGrains-TaskGrain during ReadState for grain Type=TestGrains.TaskGrain,TestGrains-TaskGrain Pk=*grn/TestGrains.TaskGrain/0+key-0xA0398413 Id=GrainReference:*grn/TestGrains.TaskGrain/0+key Error=

Exc level 0: System.Data.SqlClient.SqlException: 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)

Reproduce: github_repo In the Step 3 below, I expect sql server connection to be restored and no exception to be thrown, however, the above exception still happened.

using var client = BuildClientSqlServer();

await client.Connect();

Console.WriteLine("\n== Step 1. Should OK ==");
Console.WriteLine("Make sure sql server is running now, and press any key to continue");
Console.ReadKey();
var result = await client.GetGrain<ITaskGrain>("key").Execute();
Console.WriteLine(result.Count);

Console.WriteLine("\n== Step 2. Should Exception ==");
Console.WriteLine("Stop sql server now, and press any key to continue");
Console.ReadKey();

try
{
    result = await client.GetGrain<ITaskGrain>("key").Execute();
    Console.WriteLine(result.Count);
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}

Console.WriteLine("\n== Step 3. Should OK, try several times ==");
Console.WriteLine("Restart sql server now, and press any key to continue");
Console.ReadKey();

for (var i = 0; i < 5; i++)
{
    Console.WriteLine($"-- Step 3 >> Attempt {i}");
    try
    {
        result = await client.GetGrain<ITaskGrain>("key").Execute();
        Console.WriteLine(result.Count);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
    }
    await Task.Delay(TimeSpan.FromSeconds(5));
}

Could you please help on this? Thank you so much.

[ReubenBond]

Hi @luyangdev, apologies for the slow response. How long do you wait after restarting SQL Server before retrying?

[luyangdev]

Hello @ReubenBond, thank you for reply. I used windows services.msc to restart sql server. I just waited for the starting progress bar going to the end and the SqlServer service "Status" shows "running" again, and then I retried.

[ReubenBond]

@luyangdev have you tried to configure the ADO.NET connection retry settings via the connection string? See this article which has an example of how to do so using the connection string builder: https://docs.microsoft.com/en-us/sql/connect/ado-net/step-4-connect-resiliently-sql-ado-net?view=sql-server-ver15

[luyangdev]

@ReubenBond I just added connection retry settings to connection string, but still got the same error.

"Data Source=localhost;Initial Catalog=TestOrleansDb;uid=testorleans;pwd=testorleans;ConnectRetryCount=10;ConnectRetryInterval=3;";

[ReubenBond]

I'm not sure what should happen in this scenario. I would expect ADO.NET to be responsible for connection management. It sounds like an issue there. If not, what should Orleans do in this situation?

[ghost]

We've moved this issue to the Backlog. This means that it is not going to be worked on for the coming release. We review items in the backlog at the end of each milestone/release and depending on the team's priority we may reconsider this issue for the following milestone.