Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

One database per customer or One DB for all customers Expand / Collapse
Author
Message
Posted Saturday, January 10, 2009 9:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 2, 2009 8:51 PM
Points: 4, Visits: 23
I am designing a web service for hom e inspectors and using SQL to store text and images from each inspection. The data is later used when the inspection report is viewed online or when inspection company analyzes the business data over time - reporting.

I think that I should generate a new database copy for each new inspection company that joins the service. The inspection data is private and there could be multiple inspectors per company.

I'm looking for guidance on best practice. Is there any reason why I should want one huge database for all inspection companies or is it best to separate companies across multiple databases?

First major project and need direction on how to think about this.


Mort Strom
Post #634281
Posted Saturday, January 10, 2009 10:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:04 PM
Points: 2,107, Visits: 3,581
The main reasons that I could think about keeping them together is so that you only have to maintain one schema and for manageability. However there are tools now that are available for blasting changes to more than one location so schema maintenance is less of an issue.

It seems that you could keep the data together but you would have to be real sure that your application was only going to display the pertinent company information based on the company that was logged in. The risk should be minor with testing.

The other thing to consider is how much data would be in common and what amount of duplicate data you would be storing if you split this information up to company specific databases. That could get costly if there was a bunch of common data. My guess is that there wouldn't be based on your description.

No answers, but some things to consider anyway!


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #634293
Posted Saturday, January 10, 2009 11:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
Yogi Berra had a saying... "When you come to a fork in the road, take it!" In other words, given a choice, take all choices.

A partitioned table would work very nicely here. It combines the benefits of have individual tables for each "customer" along with the benefits of have one huge table for all customers. It makes future archival processes simple, it makes adding new "customers" simple, it makes table maintenance simple, and it makes index maintenance both simple, relatively unobtrusive, and fast.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #634297
Posted Monday, January 12, 2009 8:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 11, 2009 3:59 PM
Points: 1, Visits: 20
You are asking about "single tenant" (one db per customer) vs "multi-tenant" (one db for all customers) for SaaS (software as a service). You may want to Google those terms for a lot of opinions either way. MSDN has a write up on the various options.

I find single tenant (separate databases) quite tedious to maintain. If you have an explosion of customers, it quickly becomes unmanageable to maintain so many separate databases, and your scripts need to be extended to loop across all tenant databases to run any DDL or bulk operations. Expanding the number of databases has other ramifications such as affecting backup (such as if you are creating a new subdir per db name) and restoration (need to script restore across all dbs in case of failure).

Also, if you are not careful about your development strategy, schemas can get out of whack across the databases. I do not recommend doing different schemas per tenant (having different functionality for each customer's application, for instance), as it becomes a complete nightmare for ongoing maintenance and support at that point. You don't mention it, but you can also consider multi-tenant vs single-tenant in your application code deployment as well as database.

If your application provides the same schema set to each tenant, I greatly prefer the multi-tenant approach (which they label "Shared Database, Shared Schema"). As the article above notes, it can be extended to provide some limited customization per tenant if need be. Proper application coding should assure that one customer cannot access or view another customer's data. Application side, you should only have one deployment of code to a single location versus having a deployment per customer.

As someone noted, if the quantity of data is huge per customer, partitioning each customer's data to a separate file may be advantageous. I would not go this route on a whim, however -- research and testing would be required to see if customer access times lower in this scenario. I think I'd personally require the multi-tenant db to be 20G or more (and core tables be 250M+ rows) before considering partitioning.

HTH,

SQLServer 2005 DBA



Post #634716
Posted Monday, January 12, 2009 9:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
On the other hand, Backups and Restores work very well for individual databases and also allows for a multi-server environment which has both it's own pains and advantages.

The bottom line is to do like muji_mu suggested above. Google for all the types of options and the related pro's and con's... then, decide what is better for the data and the server(s).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #634815
Posted Monday, January 12, 2009 10:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
This is the same question asked here: http://www.sqlservercentral.com/Forums/Topic634092-361-1.aspx and there are answers in both posts. Please don't post in 2 forums as that just fragments responses.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #634824
Posted Monday, January 12, 2009 2:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 2, 2009 8:51 PM
Points: 4, Visits: 23
My proof-of-concept service has been in production for 2 years now and the db size is 125MB (1 company, 2 inspectors). As I design the 'real' online service for multiple inspection companies, my big concern is for performance, scalability and manageability. Security and privacy are a concern but based on posts, this appears doable regardless the path.

Regardless the levels of services i plan to offer, there's only one database schema in mind at this time.

I expect high frequency concurrent transactions
+ up to 8 inspections a day will be created per company (initial inserts and updates)
+ inspections are conducted online/real-time via wireless device. client is web browser. (inserts)
+ average of 25 digital images are stored per inspection. (inserts)
+ related parties view a completed inspection + images average of 5 times per day. (reads)
+ after 30 days, an inspection may never be viewed again
+ inspection companies will have a variety of canned reports that lend insight into their business over time. (read)
+ inspection companies maintain a list of real estate agent (contact info) which is not shared across companies

The responses are most helpful and thanks to muji_mu for helping me label the question as "single" vs. "multi" tenant. I'm learning how to think about this and can read the MSDN article and Google.

If I go with a multi-tenant model, is it a difficult task to move to the single-tenant model if future success requires it?

Does SQL 2005 or 2008 allow for partitioning across servers? my ignorance complete here.


Mort Strom
Post #635055
Posted Monday, January 12, 2009 2:19 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Moving from either to the other, once you have data in them, is a bit of a pain. Depends on how much data you have and how you've structured it, and how the front-end application has been built.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #635062
Posted Tuesday, January 13, 2009 11:48 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
mort.strom (1/12/2009)
If I go with a multi-tenant model, is it a difficult task to move to the single-tenant model if future success requires it?

If you implement it correctly, then it is a large task, but not a difficult one.

I have implemented large shared schema, multi-tenant applications & databases and it is all rather straight-forward as long as you follow certain rules:

1) All application users must be easily distinguishable by tenant to SQL. Practically speaking this means that either each tenant has a separate Login or each user has a sepearate Login to SQL Server and a Tenant_Users table to map the user back to their tenant association.

2) Every table that the application can write to must have a tenant_id column that identifies the tenant-owner of the data.

3) Every such table must have a corresponding "security" view that insure that any user can only access rows in that table from the same tenant., like so:
CREATE VIEW Secure_TABLE as
Select *
From Physical_TABLE T
Inner Join Tenant_Users U
ON U.tenant_id = T.tenant_id
And U.UserName = sUser_sName()

4) No application code, including client code, application stored procedures, views, etc., is permitted to directly access the physical tables. All such data access is only permitted through the Security views. (use database roles, schemas and permissions to implement this, do not rely on code).

If you notice, this approach insures that, except for the user Logon's, the application has no knowledge of the multi-tenancy, and no application changes should normally be necessary. This means that you can move a tenants data into or out of this approach without any changes to most applications, other than changing the Server.Database address. (note that to be truly transparent you would have to remove the tenant_id from the output of the security views).

The tenant_id should be made the first field of the primary key and probably also the Clustered Index (if different).

Here are some links to other discussions about shared schemas and an MSDN article on it:

http://www.sqlservercentral.com/Forums/FindPost610335.aspx
http://www.sqlservercentral.com/Forums/Topic529170-361-1.aspx
http://www.sqlservercentral.com/Forums/Topic569567-149-1.aspx

http://msdn.microsoft.com/en-us/library/aa479086.aspx


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #635710
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse