SQL Server Multi Tenancy Setup

Question

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:

1. 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.
2. 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.
3. All of the individual Facility Databases would have the exact same schema/table structure.
4. 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?

Thanks!

Answer 1

What you are describing seems exactly like PartitionDB*. You can create your multi-tenant database design in more than one mode. Look at their configuration page.

Basically, you have a master database which populates metadata structure information between the database nodes. They support SQL Server and also a builtin replication mechanism if you like to use.

_{* I am a beta user, but not affiliated with this product otherwise.}