What is the best way to use Multi Tenant Database Architecture when all of the "Tenants" will be from the same company?
We are going to be using AWS and SQL Server for our backend system. We currently have 1000+ facilities that will end up using this new system and are steadily increasing by roughly 10-20% a year on acquiring new facilities that will have to be brought onto the system.
The debate we are currently having is if we should have a single massive database that handles/stores all of the transaction's for all of our facilities, or if we should scale out to a 1 database per facility architecture.
The biggest requirement we have is the ability for Global Accounts. A customer needs to be able to have a rental item with us in a Facility in Florida as well as a Facility in Washington.
The idea of how to set this up we have is this:
- Have 1 Master Database that holds all of "global" data for the company. Meaning things like Accounts or config settings that are the same at every facility.
- Have 1 SQL Server Instance per time zone we have facilities in (for less latency across the geo locations) and then have 1 database per facility in that time zone on the instance.
- All of the individual Facility Databases would have the exact same schema/table structure.
- We would also want to set up some sort of Streaming replication with ETL from all of the Databases into a Data Wharehouse that would ideally be located on AWS as well.
Has this type of setup been done before? Is this even considered a good approach?