Mike.Sheen wrote:I wouldn't consider multi-tenanting the databases - they'd need their own. We certainly could achieve that with introducing a tenantId per table and implementing row level security - but that's a fair amount of work and a lot of interesting problems would need to be solved, so I'm not entertaining that aspect of multi-tenanting.
This is going slightly off-topic, but since we're there: I don't see that there would be much value in a multi-tenant database when it is easy enough to roll a new database for each tenant.
Microsoft AX has a multi-tenant database, which is technically interesting, but a right pain to manage, or to deconstruct if you want to remove a whole tenant.
The only slightly reasonable argument I know of for multi-tenant is for complex enterprises made up of multiple legal entities who may nonetheless require similar GL structures, and say Inventory. But with SQL Server, it's also not difficult to share data between databases - you can create views that look into another database for common data (say GL configuration).
Likewise, it's relatively easy (conceptually) to build a 'consolidated' view of multiple identical SQL databases by having views that use the 'UNION' of data from multiple databases. And given that JIWA's reporting is all driven out of SQL queries and Crystal Reports anyway, it would be easy to add a (hard-coded) 'TenantId' column for each database in a multi-database UNION query, and then report on them by tenant in a consolidated report. I know, because I've done it before (not for JIWA; for an in-house bespoke system at a former employer, but the technique is sound).