Partitioning Approach for multi-tenant db

[John Bartkus]

Looking for advice on best practice/approach for partitioning a database based on tenant.

I have a db of 54 tables, which (other than global reference tables) has a SiteID of type SmallInt on each table to differentiate clients/tenants.
While this is only supporting two clients today, I expect 50 within a year (and target maxing out at less than 500).

Background: I want to partition the db by SiteID in order to:
* Speed interactions with Client data since almost all operations are by SiteID.
* Allow IDs (such as UserID, EventID, RegistrationID) to autoincrement for each individual client (more intuitive).
* Be able to rightfully declare that each client's data is separated from other clients.

Key Questions:
* Which partition method is best for this SiteID situation?
Assume the SiteID is an integer between 1 and 500 (max).
Is it "LINEAR KEY" on SiteID ?
* Will this (or another partitioning) method be able to dynamically create a partition for the next SiteID that gets created?
(SiteID is an autoincrement PK for each new client).
Or would I have to go into every table to increase the partition count and associate the new partition with the new SiteID? (Ugh).

Thanks for any insights!!
John