You are designing a Windows Azure application that will store data in two SQL Azure databases. The
application will insert data in both databases as part of a single logical operation. You need to recommend an
approach for maintaining data consistency across the databases.
What should you recommend?
A.
Execute database calls on parallel threads.
B.
Wrap the database calls in a single transaction scope.
C.
Use Microsoft Distributed Transaction Coordinator (MSDTC).
D.
Handle errors resulting from the database calls by using compensatory logic.
Answer D?
Yes. Should be D, though C appears logical answer. DTC doesn’t work in Azure or any SaaS cloud infra. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-transactions-overview
Answer is B
using (var scope = new TransactionScope())
{
using (var conn1 = new SqlConnection(connStrDb1))
{
conn1.Open();
SqlCommand cmd1 = conn1.CreateCommand();
cmd1.CommandText = string.Format(“insert into T1 values(1)”);
cmd1.ExecuteNonQuery();
}
using (var conn2 = new SqlConnection(connStrDb2))
{
conn2.Open();
var cmd2 = conn2.CreateCommand();
cmd2.CommandText = string.Format(“insert into T2 values(2)”);
cmd2.ExecuteNonQuery();
}
scope.Complete();
}
Hi,
I think they are mainly asking “You need to recommend an approach for maintaining data consistency across the databases.” So I believe it is D which can perform a rollback if there is any error.