T-SQL Tuesday #86 - Can haz native multi-tenant support in SQL Server?
Go to Connect.Microsoft.com, the place where we file bug reports and enhancement requests.
Find the most interesting bug or enhancement request (and it can be your own), and write a blog post about it (including a link to the Connect item so that folks who agree with you can upvote the item)
I’m going to blog about multi-tenant database support in SQL Server. If this does not ring any bells at all, then go to the bottom of this post, and visit my post on backup/restore of multi-tenant databases. Here I explain the basics of the topic.
But first of all, I think Brent Ozars topic is great. Personally, I have created two connect tickets in my career. One on support for DBCC CLONEDATABASE in SQL Server 2016, which has been fixed (see link to blog post below). One on a bug in the query processor, where certain T-SQL MERGE statements will make SQL Server throw an error about not being able to create an execution plan (see link to blog post below). This one has not been fixed (yet?). So, with my sample the SQL Server team fixes 50% of all connect items… J
The connect item I would like the SQL team to reconsider is this one
filed by Mark Bower.
The description is not very detailed:
We are building a multi-tenant SaaS solution and would like better support for the common multi-tenant patterns. For instance in the single-shared schema pattern, we have to manage security within the application layer, or by creating views for each tenant, restricted by tenantID.
There is actually a lot of administration around building a “single database, shared schema” model, where tenants as a first-class citizen in SQL Server would be awesome. Examples are
Backup a tenant
Restore a tenant
Export a tenant to a bacpac file
Import a tenant from a bacpac file
Move a tenant to another (multi-tenant) database
Move a tenant to another (non multi-tenant) database (for tenants, who want to live in their private database)
Default settings of Row Level Security
Per-tenant resource governance
Possible default setup of table partitioning
to mention a few.
For anyone who needs to build the database backend for a multi-tenant SaaS solution (using on prem SQL Server or Azure SQL Database), native support for tenants like the examples I mention above would make life sooo much easier.
That’s it for this months T-SQL Tuesday. I will be hosting the March 2017 edition of this cool tradition. Stay tuned for more info later. Thanks for reading along. KTHXBYE
Read more about T-SQL Tuesday #086 here:
Read my connect blog post on DBCC CLONEDATABASE here:
Read my blog post on the MERGE bug here:
Read more about the “single database, shared schema” model in my blog post here:
Read more about LOLCODE here: