We current have a very simple Multi Tenant monolith, with a SQL SERVER backend (Self hosted on EC2 on AWS), and multiple application services talking to one DB behind an Classic AWS ELB. Our database has grown to a point now that we are considering splitting it by tenants per region, due to growth in different regions and latency concerns, plus downtime maintenance window considerations. We also want to keep the same DNS...www.domain.com for example for both regions due to existing links etc...
We are considering now
- Use Cloudfront to do geo based routing as well as basic cdn caching in front of the services, as an edge proxy of sorts
- Shard sql server into 2 main regions, namely Australia and North America, based on tenant's location.
- Application services exists on both regions with seperate db.
- Have a shard table maybe in dynamodb global table or s3 somewhere, and because we have less than few hundred tenants, and mostly immutable, can be cached for a long time.
Main grey area, what can I do when the route goes to the wrong Region/Db?
For example, say I have
- Tenants 1 to 10 in Australia
- Tenants 11 to 20 in USA
Tenant 1 went to USA for a holiday and tried to access www.domain.com, based on geo routing, he will be routed to the USA datacenter, which in the database in USA will not contain his data. I was thinking
- Based on the login user, I can determine the tenant (in the application service), so if he is logged in to the wrong region, based on looking up the shard table, I will reply a 304 redirect, maybe with a cookie of sorts, so cloudfront maybe, with lambda at edge that can read and do extra redirection logic? Still not clear if that is possible or a good solution.
What is the best practices around that? I reckon it would be a solved problem but I cannot seem to find anything more practical with examples with my bad googling skills, most of them are like theory of shard tables etc...
Any advice would be much appreciated.