I am wondering if anyone has any experience in converting the framework to work with a database per tenant. With Azure Elastic DB coming out, this is a great way to architect a multi-tenant solution. Having worked with a very large Multiple-Tenant, Single Database before, I just can't see using this ever again. Very limited on what you can do to scale out. Not a fan of sharding. Of course, this is my preference and doesn't have to be everyone's, so choices are good!
<a class="postlink" href="https://channel9.msdn.com/Events/Build/2016/P522">https://channel9.msdn.com/Events/Build/2016/P522</a>
So, I am setting off on the journey to modify this to work with multiple databases. I am hoping some thought has been put into this by others and hoping to have a discussion about what it takes. I hope I can give back to the community with this. So, I don't mind doing some work here, but hoping I can get a little assistance with this to get started.
Here are my thoughts and I am curious to see if anyone else has thought about this or might make sure that my logic is correct:
Design
- Host DB and TenantDB are the same root data model from a frameworks perspective. Can I leave out the new tables I create? Do I care if we can't? If I can't, maybe I need a different context and setup for the domain specific tables(Product, Customer, etc). Keep the migrations seperate.
- Create the same tenant in both the Host and Tenant. Use Same TenantId in both to tie the two together. This allows me to do some fancy reporting at some point by tying the ID's together nicely. Maybe I have some ETL that extracts certain information about each tenant into a data warehouse. I would leave each database in multi-tenant mode. This also may allow us to do a hybrid scenario. Maybe a premium addition comes with it's own Database or you could just spread out your tenants to seperate DB's (forced sharding).
- Store URL, DB Server and Database as attributes on a tenant. Tenant Settings won't work here since I need to know what store to get the settings from. This is determined based on URL.
- When a login request comes in, check Host DB for the Tenant by URL and gather DB information for all connections. For Host URL, this would be the master database. For Tenant URL's, this would be the individual tenant database.
- All remaining request use the Tenant settings to know which DB to hit.
Changes that I see:
- Will need to set the Tenant connection dynamically. Need access to the settings from within the DbContext to set the connection dynamically.
- In the Context, add some code to dynamically set the connection based on the current Tenant. Use Host by default if one isn't set.
- What settings need to be in the Tenant, what settings need to be in the Host. Email, user settings, etc. May need to add a HostSettings. How does this impact SettingManager.
- Would the Linked Accounts feature work? To me this is not a deal breaker. It's ok if this feature only works within a single tenant but would be nice if it worked accross tenants.
Any thoughts would be very helpful. I am sure you all have thought about this and any pointers would be helpful.