SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


One database per customer or One DB for all customers


One database per customer or One DB for all customers

Author
Message
mort.strom
mort.strom
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
David Benoit
David Benoit
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14244 Visits: 3650
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! Hehe

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)

Group: General Forum Members
Points: 383589 Visits: 43058
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
muji_mu
muji_mu
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)SSC Guru (383K reputation)

Group: General Forum Members
Points: 383589 Visits: 43058
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79602 Visits: 14975
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
mort.strom
mort.strom
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
GSquared
GSquared
SSC Guru
SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)

Group: General Forum Members
Points: 106483 Visits: 9730
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
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61770 Visits: 9519
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search