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?
2
1
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
2
0
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();
}
0
0
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.
2
0
C.
MSDTC is used by SQL Server and other applications when they want to make a distributed transaction between more than one machine. A distributed transaction is simple a transactions which spans between two or more machines. The basic concept is that machine 1 starts a transaction, and does some work. It then connects to machine 2 and does some work. The work on machine 2 fails, and is cancled. The work on machine 1 needs to then be rolled back.
0
2
Since MSDTC is not available for Platform-as-a-Service application in Azure, the ability to coordinate distributed transactions has now been directly integrated into SQL DB. Applications can connect to any SQL Database to launch distributed transactions, and one of the databases will transparently coordinate the distributed transaction
0
0