TransactionScope in Azure SQL Database with primary and failover database server

Question

Am creating some utility tool with c# code for DBA(run Background job to get some metrics), for that am using failover database server(dbserver-fa) for read data and primary server(dbserver) for write data within same transaction scope, am getting below error in prod environment, same code working in Dev & QA without failover db(read & write in primary db)

Check inner exception for details Exception has been thrown by the target of an invocation. The operation is not valid for the state of the transaction. Transaction Timeout

using (var scope = new TransactionScope())
{
 using (var conn1 = new SqlConnection(connStrFailover))
 {
  conn1.Open();
  SqlCommand cmd1 = conn1.CreateCommand();
  cmd1.CommandText = string.Format("select * from jobsettings");
  cmd1.ExecuteNonQuery();
 }
 
 using (var conn2 = new SqlConnection(connStrPrimary))
 {
  conn2.Open();
  var cmd2 = conn2.CreateCommand();
  cmd2.CommandText = string.Format("insert into T2 values(2)");
  cmd2.ExecuteNonQuery();
 }
 
 scope.Complete();
}

do we need to enable any config or MS-DTC required on this?

Answer 1

1). Consider increasing timeout if that helps as in production data would be huge. 2). Check if there are no two connections opened to the same production database in the TransactionScop block. 3). Try using SqlConnection instead of SQLServer for opening connection part of the code. 4). See if there are nested transaction blocks, kindly restructure code.