2

We are designing a multi-schema, multi-tenant database for SQL Server 2016 that will service a basic CRUD application which will see small to medium transactional throughput and comprise of 15-20 tables. For the sake of data isolation and security, we are exploring the utilization of a composite primary key that has [TenantId] [int] and [TenantIsolationId] [uniqueidentifier]. These two columns will repeat throughout tables in a hierarchical manner, enforcing referential integrity with appropriate foreign keys, and allow us to enforce deeper row-level security.

In most scenarios that I have read, the usage of a GUID for [TenantIsolatonId] seems to be controversial due to performance implications, especially with regards to space. However, we believe that for the management of physical file partitioning, resource pool delegation, replication, portability, and general referential integrity, that a pairing of an integer and GUID will allow for better isolation and security within the multi-tenant database. We are bound by a range of security provisions, so this further leads us down the path of using this composite key type.

In thinking of children composite keys, I know the order of the columns specified matters. However, I cannot seem to come to a consensus if we should feed the the same composite key structure down the table chain or if it is better to segregate it in a more traditional manner.

For instance, given the two table structures:

CREATE TABLE [Auth].[Tenant] (
    [TenantId] [int] IDENTITY(1,1) NOT NULL
    ,[TenantIsolationId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Tenant_TenantIsolationId] DEFAULT NEWID()
    ,[TenantName] [varchar](256) NOT NULL

    ,CONSTRAINT [PK_Tenant_TenantId_TenantIsolationId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [TenantIsolationId] ASC)
    ,CONSTRAINT [AK_Tenant_TenantName] UNIQUE NONCLUSTERED ([TenantName] ASC)
);

CREATE TABLE [Auth].[User] (
    [UserId] [int] IDENTITY(1,1) NOT NULL
    ,[TenantId] [int] NOT NULL
    ,[TenantIsolationId] [uniqueidentifier] NOT NULL
    ,[FirstName] [varchar](32) NOT NULL
    ,[LastName] [varchar](32) NOT NULL
    ,[UserName] [varchar](64) NOT NULL

    ,CONSTRAINT [PK_User_TenantId_TenantIsolationId_UserId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [TenantIsolationId] ASC, [UserId] ASC)
    ,CONSTRAINT [AK_User_UserName] UNIQUE NONCLUSTERED ([UserName] ASC)
);

If we are performing row-level security and isolating the data based on [TenantId] and [TenantIsolationId], is the [UserId] primary key better served as above in a composite key, or separately?

E.g.:

CREATE TABLE [Auth].[User] (
    [UserId] [int] IDENTITY(1,1) NOT NULL
    ,[TenantId] [int] NOT NULL
    ,[TenantIsolationId] [uniqueidentifier] NOT NULL
    ,[FirstName] [varchar](32) NOT NULL
    ,[LastName] [varchar](32) NOT NULL
    ,[UserName] [varchar](64) NOT NULL

    ,CONSTRAINT [PK_User_UserId] PRIMARY KEY CLUSTERED ([UserId] ASC)
    ,INDEX [IX_User_TenantId_TenantIsolationId] NONCLUSTERED ([TenantId] ASC, [TenantIsolationId] ASC)
    ,CONSTRAINT [FK_Tenant_TenantId_TenantIsolationId] FOREIGN KEY ([TenantId],[TenantIsolationId]) REFERENCES [Tenant]([TenantId],[TenantIsolationId])
    ,CONSTRAINT [AK_User_UserName] UNIQUE NONCLUSTERED ([UserName] ASC)
);

Based on my understanding of what I have delved into, the composite key is only a good idea if we will always be looking up the data on all three columns always. Since we are wanting to isolate the data, I cannot foresee instances where we wouldn't want to look-up the [TenantId] as well as the [TenantIsolationId] before seeking to the [UserId]. Perhaps, however, I am misunderstanding the pros and cons and am better served utilizing only [UserId] for the primary key, coupled with an index against [TenantId] and [TenantIsolationId]. Is my thinking flawed?

I'm still in the infant stages of the schema's development, so I'll be running plenty of performance tests with large quantities of dummy data once I get the initial sketches completed. But as a general practice, what is recommended in this scenario?

Furthermore and generally encapsulating multi-tenant database architecture that must ensure high levels of data isolation, has there been any significant movement forward that doesn't lean itself towards utilizing a two-valued key combination? I have read and watched a good deal on the topic, primarily referencing Salesforce's Mulitenant Magic Webinar and Google's F1 white paper. More recent articles still tend to follow the concepts they've outlined even in their age, and while I am building a schema for a database that will not be anywhere close the scale of Salesforce and AdWords, I find myself leaning towards the principles that they have resonated.

  • You need to go back and fight harder, IMHO. Combining these tenants back into a single database creates a bunch of problems and doesn’t really solve any. Managing 600 databases is not going to be any different from managing 600 clients in a single database, thanks to things like automation. Managing all of that data inside a single database, though, will become quite painful as the data grows. Eventually you will outgrow your hardware, too, and splitting up your tenants or moving them all at once will introduce new challenges that having separate databases has already solved. – Aaron Bertrand Feb 21 '18 at 9:45
3

But as a general practice, what is recommended in this scenario?

Database-per-tenant is the best-practice here. There are scenarios where it is impractical, but should be your strong preference in designing any multi-tenant system on SQL Server.

Database-per-tenant gives you:

  • Excellent security and data isolation that's verifiable and easy to sell.

  • Optimal Performance, with no shared query plans.

  • Horizontal Scalability across as many Instances/Elastic Pools as you need.
  • Performance Isolation, with the option of isolated, dedicated resources for a tenant.
  • Per-Tenant servicing, including the ability to upgrade or hotfix a single tenant.
  • Per-Tenant backup and recovery, and optionally differentiated HA/DR.
  • Per-Tenant Ad-Hoc reporting.

Other than that:

What is the point of the TenantIsolationID?

You should include TenantID in every clustered index that contains tenant data.

It should be the leading column, unless you're using it for partitioning, in which case it can be a trailing column.

You must plan for splitting your single database into smaller databases as a scale plan. But split can be a one-way operation.

Whether TenantID is an INT or UNIQUEIDENTIFIER only matters for index size. All your secondary indexes will be bigger if you use UNIQUEIDENTIFIER. But that's not a huge cost. Fragmentation and page splitting won't be a big deal here. See Good Page Splits and Sequential GUID Key Generation for details on the performance implications of having multiple insert points in a table.

  • We are currently utilizing database-per-tenant in our current model and have found management and deployments as we've scaled to 600+ tenants to be cumbersome. These databases heavily rely on poorly constructed business logic throughout nested stored procedures which sits on an overly-normalized database schema. The architect wants to move this to the front-end and rely on a single DB. While database-per-tenant has been my preferred method, especially with regards to isolation, we are limited by internal resources, skill sets, and general change in product direction and overall architecture. – PicoDeGallo Feb 20 '18 at 21:16
  • "The architect wants to ... rely on a single DB". Architects are biased to solve problems with architecture changes. That's a mistake here. The grass is definitely not greener on the multi-tenant-schema side of the fence. The work to properly automate your database-per-tenant solution is going to be less than the initial and ongoing work to implement and manage the multi-tenant database. – David Browne - Microsoft Feb 20 '18 at 21:21
  • This is a long-winded battle I have fought tooth and nail over with the architect and business executives alke, providing ample and detailed comparisons (database-per-tenant, suggesting a move towards Azure SQL to take advantage of Elastic Databases, PostgreSQL with Citus Data, etc.), but this is driven by forces above me. While I am in agreement that this is not the best design decision, I have to do my part and ensure that the pieces I do have control over are implemented as well as possible. I have confines in which I work and I want to do the best that I can within them. – PicoDeGallo Feb 20 '18 at 21:33
  • Yep. This is a Product Management decision. Anyway see the back-half of my answer. Basically use leading TenantID (int) in every clustered PK, remove TenantIsolationId, and consider partitioning (but without using multiple filegroups). Also you may want to hard-code TenantId in all your queries, instead of using a parameter to minimize plan reuse across big/small tenants. – David Browne - Microsoft Feb 20 '18 at 21:38
  • I'll take a good look at the article you linked, thank you! Regarding hard-coding [TenantId], we are using Entity Framework, thus (to my dismay) ORM to begin. Once we've decided where the inefficiencies lie, we'll move those pieces to SPs that will rely on a `[TransactionalTierId] to mitigate sniffing by differing small tenants and big tenants. This would be an additional column in the 'Account' table, and the value assigned would be determined primarily by their throughput, or if stagnant but large, number of rows they have. – PicoDeGallo Feb 20 '18 at 22:09
2

I'm not sure of the purpose of having the TenantIsolationID UniqueIdentifier in there. I would suggest removing it or explaining in the question the express purpose of it. If it is to have an external ID that can be given to the client such that you don't expose the IDENTITY value, then the TenantIsolationID would only be in the Tenant table and not in any of the others. And in that case it would be looked up once when someone logs in, and the looked-up TenantID should be cached to be used from that point forward.

You initial intuition was correct that TenantID should be placed first in the Clustered Index (whether that is the PK or not).

For additional details on dealing with this, including a potential performance pitfall, please see my answer, also here on DBA.SE, to a very similar question:

Composite Primary Key in multi-tenant SQL Server database

Regarding always querying on the TenantID, or even TenantID, TenantIsolationID composite key: since you don't want customers to see each other's data, yes, this will be the case most of the time. However, there will still be maintenance operations, such as garbage collection of records over X days / months / years old, that are not per-Tenant. For those you can create a non-clustered index with a leading column of the creation date or whatever is relevant for the particular table.

  • Within the database, TenantIsolationId would be an additional layer of tenant identification for row-level security, wherein a combination of TenantId and TenantIsolationId would serve as a "key pair". This does seem redundant as I type it out, but I intuitively fear that relying strictly on a single integer value could be problematic. Admittedly this is illogical. It would also serve as the root level of storage pool identification so that we aren't relying on an integer value to determine the physical partitioning of documents being stored. I'll look over your previous post, thank you! – PicoDeGallo Feb 20 '18 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.