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
(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
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.
Will partitions reduce page fragmentation if the tenantID is the first
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
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?
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.
- 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.
- You can upgrade and patch tenants separately.
- 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.
- Databases are a real security boundary, and you can enable users to see only a single tenant's data.
- 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.
- 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.
- 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.