SQL Database - replication, transactions, & both

Question

Hi all;

A couple of questions around Azure SQL Database replication & transactions. First, while I understand replication and transactions pretty well, I find I'm doing a bad job explaining them big picture to those new to them.

First, is there a good web page that explains replication? How it works - one instance to write to, multiple instances to read from, fail over if the primary one goes down, and all the lovely issues with this such as a read may not immediately show a write.

Second, is there a good web page that explains transactions? How they work - including the various types of transactions and guidance on what type to use for different situations.

Third, years ago when SQL Database was first getting started, it was recommended to have transactions retry a couple of times if they failed, to handle transient issues writing to the DB. Is this still needed and if so is there a web page laying out how to do this? Or is this now handled for us in the DBConnection/DBTransaction objects (or some special version of them)?

Fourth, and I need this info as well as my forwarding it to others, a transaction works great if there's a single DB. Transaction completes, DB server blows up, and it's all gone. Ok, that's all there was. But with replication a transaction can succeed, the server blows up, and if it didn't replicate, that transaction is lost even though it completed. While if the transaction completes and replicates, and then the server blows up, that transaction is complete in the fail over. So is there the concept of transaction complete and replicated? And if so, how does that all work?

Fifth, is there anything else I am missing?

thanks - dave

Answer 1

@David Thielen :
You can follow the refence links provided with your queries to get a better understanding of the topics.

First, is there a good web page that explains replication?

Reference Link:
https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database?view=azuresql#types-of-replication

Second, is there a good web page that explains transactions?

Reference Link:
https://social.technet.microsoft.com/wiki/contents/articles/1639.azure-sql-database-handling-transactions.aspx

Third, years ago when SQL Database was first getting started, it was recommended to have transactions retry a couple of times if they failed, to handle transient issues writing to the DB. Is this still needed and if so is there a web page laying out how to do this? Or is this now handled for us in the DBConnection/DBTransaction objects (or some special version of them)?

Reference Link:
https://social.technet.microsoft.com/wiki/contents/articles/4235.retry-logic-for-transient-failures-in-windows-azure-sql-database.aspx

Fourth, Is there the concept of transaction complete and replicated? And if so, how does that all work?

Reference Link:
https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver16