4

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?
  • Are you dead set on this architecture? – Erik Darling Sep 2 '17 at 20:09
  • How many customers do you expect to have? SQL Server 2017 supports a maximum of 15,000 partitions. – Max Vernon Sep 3 '17 at 16:29
  • I am not dead set on the architecture. If someone has arguments for a better approach I'm open to it. Perhaps I should have added that the tenants that will combined in a single database are generally from the same franchise/association. There are less than 1,000 in any association, so the 15K cap would not a problem. The design also incorporates multi-databases, as well as individual databases per tenant in some cases. – cyriously Sep 3 '17 at 23:34
6

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.
  • You are quite prescient when you say "in the end state you will have a mix of multi-tenant and single-tenant databases". This is exactly the design going into it. I probably should have clarified the use case - where tenants are all franchisees or members of a single association. We have other use cases where each tenant is their own database. The system is designed for multiple databases; I was just looking for advice on partitioning. Thanks for the detailed and clear feedback. – cyriously Sep 3 '17 at 23:40

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.