0

We have an azure web app & a db we want to replicate all over the world.

So, we use Traffic manager to redirect the User to the closest hosted Web app , and with a location setting in the web app, It knows to which database it should go against.

Now, my question is , as the mode is One database Writeable (Primary) and the replicas being read only , how do me or azure handle that at the moment of calling the database?

For example, if from my app I am going to Add a record to database, I cant use the nearest DB connection string, I need to go against the Primary one.

Should I handle this? or I will go always against the nearest one even if its read-only an azure will handle the write transferring it to the primary db ?

In the case I am the one that should manage that, then I should handle 2 connection strings, one for the primary DB writeable, and one for the closest db readable, and I should split my services , categorized by write/read actions

and following this scenario, if I have a Store procedure which WIRTES AND READS, how would I handle that?

David Makogon
  • 69,407
  • 21
  • 141
  • 189
Alejandro R
  • 50
  • 1
  • Not sure, exactly, what you're asking. Traffic Manager does not deal with databases. Your app would deal with database connections. Not sure what you're referring to, with having the database handle redirecting to primary. But also: which database are you referring to? There's SQL Database and DocumentDB as hosted PaaS database services. Please edit your question accordingly to be more specific. – David Makogon Apr 26 '17 at 15:45

1 Answers1

0

This is a common issue when it comes to using Azure SQL in geo-replication mode. You cannot use traditional LB techniques such as Azure Traffic Manager. In this case, you should be using the retry pattern on your database connections, working from the primary down to the alternate names as required.

AFAIK, there is no easy way to tell, after connected to a database, if you are on a primary or a read-only secondary. As per this link there are some stored procs you can call to understand the topology. You can understand this using Azure PS/API, but then you would have to build that logic in to your application.

In short:

  1. You need to handle your database connections and employ retry patterns,etc
  2. You should implement CQRS to separate read/write workloads from each other if you want to take advantage of read-only secondaries

Hope that helps.

CtrlDot
  • 2,463
  • 14
  • 11