question

emustiga avatar image
emustiga asked

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.

best-practicefilegroup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

emustiga avatar image
emustiga answered

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

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Happy to help, no problem :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.