x

Planning for High Volume transaction data in a SAAS Web app

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.

more ▼

asked Oct 28 '09 at 04:28 PM in Default

emustiga gravatar image

emustiga
12 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

CREATE PARTITION FUNCTION [PF_PartitonSixes] (int)
AS RANGE RIGHT
FOR VALUES (
  1,
  2,
  3,
  4,
  5)
GO

CREATE PARTITION SCHEME [PS_SixesPartitionScheme]
AS PARTITION [PF_PartitonSixes]
TO ([PF_Fives_1],
    [PF_Fives_2],
    [PF_Fives_3],
    [PF_Fives_4],
    [PF_Fives_5],
    [PF_Fives_6])
GO

CREATE TABLE [dbo].[tblCardsPartitioned] (
  [ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
  [CardNumber] [varchar] (16) NULL,
  [PartitionElement] AS [ID]%(6) PERSISTED,
) ON [PS_SixesPartitionScheme] (PartitionElement);
GO

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.

more ▼

answered Oct 29 '09 at 08:05 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

Matt, thank you for your response, it really help me to start.

more ▼

answered Dec 01 '09 at 07:55 AM

emustiga gravatar image

emustiga
12 1 1 1

Happy to help, no problem :)
Dec 01 '09 at 08:49 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x61
x31

asked: Oct 28 '09 at 04:28 PM

Seen: 1546 times

Last Updated: Oct 28 '09 at 05:53 PM