What are the best practices procedure to store data in a high volume SAAS based Web App?
The database will be in the form of Single Instance MultiTenant. One database and one schema for all tenants.
All tenants will share the same tables and it will be a tenantID on each table to filter the information.
Is there a way to create dynamically, automatically or programmatically a new filegroup based on the tenant geographical information? for example each filegroup will contain the data for all tenants that matches the following geographical information: country/[state or province]/ [zipcode or postalcode].
Any suggestions will greatly appreciate.
First of all, this is a very open question, it's not really one that can be answered 'correctly' - but I will just not down some pointers.
With your filegroup based on geo information - I would advise that you split the filegroups based on the tenantID itself. You have that information readily available in all tables, and you could easily set up a partition scheme which split the data up based on their ID. This is also likely to give a more even spread of data than if doing it by geographical information. Here is an example of a partition function, partition scheme and partitioned table to get you going:
The next thing I would say is choose your indexing strategy carefully. Pay particular attention to the tables that will be the biggest - how will they be accessed, how often etc. Placing your clustered indexes correctly can ensure that you have a database which scales gracefully. There is nothing nicer than sitting in the break room on go-live day having a coffee because you've nursed 0 problems for half of the day.
Another thing I would say is choose your transaction isolation level very carefully. SNAPSHOT isolation can provide great benefits for high throughput application, but you need to be sure that it applies to your situation.
Anyway, this isn't an exhaustive list by any means, because to be honest, your question could be answered in a whole book. But just some bits to get you started there.
answered Oct 29 '09 at 08:05 AM
Matt Whitfield ♦♦
Matt, thank you for your response, it really help me to start.
answered Dec 01 '09 at 07:55 AM