Using the Azure database replication is it possible the application to read data from the database that is closest to its location. My main database is in the USA while the secondary database is in Brazil. My system runs in these two countries, I wanted it when I was in Brazil the system read the database of Brazil and the same in the USA. I read in this article: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-overview that it is possible to read the data from the primary and secondary databases, But manually. I would like of the do this automatically according to a location. Is it possible?
2 Answers
1
votes
You can route all read requests to secondary,but you can't use secondary as read Write because in Geo Replication,Secondaries are only readable ..
when in Brazil the system read the database of Brazil and the same in the USA
Regarding reading(only reading) the data,you can route all read requests to secondaries...but these won't be closest,you will have to detect the client ip and based on that ,you will have to use appropriate connection string to connect..
You can use Trafficmanager to direct users to nearest webapps/cloud services..but you can't do the same with databases
0
votes
The approach is to use 2 connectiostrings one for write one for read, and then use local to read and primary to write. In Azure sql when using Geo-replication and Fail-over group you can use dns name to primary sql instance. Azure SQL Database now supports transparent geographic failover of database groups
So you can use:
- failovergroup.database.windows.net - for write
- failovergroup.secondary.database.windows.net - for read
