Recommendations on Partitioning Multi-Tenant Data in SQL-Server

Question

I would appreciate input on design considerations for partitioning (or not) a multi-tenant business application. All data tables use a composite key that looks something like:

CREATE TABLE SomeTable(
  TenantId SMALLINT NOT NULL,
  ID INT NOT NULL,
  SomeData ...,
  CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED (TenantID, ID)
)

Almost all reads are keyed to the specific tenantid. Writes come in randomly by tenantID but any data in place for 3-5 days tends to never change after that.

Questions

1. If I don't partition, is (tenantID, id) or (id, tenantid) a better choice. Assume reads are all by tenantid, writes are random by tenantid, and a there is enough downtime nightly to run a maintenance plan.
2. What are the pros and cons of creating a separate partition for each tenantID?
3. Will partitions reduce page fragmentation if the tenantID is the first key?
4. If the partitions are in the primary file, does that alter the arguments?
5. If there are 10 or 1000 partitions, does it alter the arguments?
6. Does the answer depend on whether I use SQL 2016 or SQL Azure?

Answer 1

If I don't partition, is (tenantID, id) or (id, tenantid) a better choice. Assume reads are all by tenantid, writes are random by tenantid, and a there is enough downtime nightly to run a maintenance plan.

(tenantID,id) is the better ordering, as it creates locality for each tenant's data.

What are the pros and cons of creating a separate partition for each tenantID?

Pro: partition operations can be used to operate on single tenants. Eg SWITCH, TRUNCATE, REBUILD.

Con: Maintence, and possibly cost of partition splitting to add new tenants.

But see:

Will partitions reduce page fragmentation if the tenantID is the first key?

No. Each tenant's rows will be logically and mostly physically contiguous in the index. There will basically be one or a few fragments for each tenant. So while the partitioning will reduce fragmentation, it shouldn't be a problem without partitioning.

If the partitions are in the primary file, does that alter the arguments?

No. That is separate. All partitions should go to a single filegroup, unless there's some good reason to separate them. You could potentially have a separate filegroup for some special tenant, but I wouldn't recommend that.

If there are 10 or 1000 partitions, does it alter the arguments?

No, except that it exacerbates the maintence issue with the partitions.

Does the answer depend on whether I use SQL 2016 or SQL Azure?

No.

But stepping back, you should plan to have multiple federated databases holding your tenant data. You can still go with a multi-tenant database design, but in the end state you will have a mix of multi-tenant and single-tenant databases. There are many important advantages to isolating a tenant in a database.

1. Tenant data is isolated. This is important in selling to your customers, and it makes it very easy to make sure that tenants can never see each others' data.
2. You can upgrade and patch tenants separately.
3. You get the ability to move, backup, restore, export a tenant-at-a-time for free. Critically this gives you point-in-time restore for single tenants.
4. Databases are a real security boundary, and you can enable users to see only a single tenant's data.
5. You don't have to write tenant export, and tenant import processes to move a tenant from one database to another, which you otherwise would have to write.
6. You can scale tenants separately, giving large or premier tenants differentiated access to server resources, or even giving them their own dedicated resources. This makes your solution easier to manage, mitigates risk, and creates pricing opportunities for you.
7. Each tenant gets their own query plan cache, so you don't have query plans optimized for a small tenant used for queries for the data of a large tenant or vice versa. And you have visibility into the performance metrics for each tenant.