May 20, 2008 at 7:15 pm
I'm not really a SQL newbie, but I wasn't sure exactly where this should go....mostly I just am curious about this question.
I've been working with SQL Server and databases for quite awhile now, for a company that offers a web app business service. We have a SQL Server 2000 backend that serves clients via a website. Our business db is a single database, with a table that holds the client accounts, and I've always considered this to be "normal".
We're now working with another company to integrate some business technology. They are in a similar business, but whereas we have everything in a single database, they have broken theirs out so there is a separate database for each of their clients. However, they have a common client-side app, so *each* of the databases is identical in structure; they just have different data.
This just seems so foreign to me, I was hoping someone could explain what the rationale would be to create a structure like this. Any structural change they make has to be propagated to all of the databases, and I image it must be a maintenance nightmare. It just strikes me that it would always be much cleaner in a modern database to put everything into one place. If it matters, I believe this company's original design started as an Access application.
I was also told that awhile back my company had a consultant look at doing a new "updated" version of our database, and part of his plan was to break ours into separate databases by client account, too. So apparently this is more common than I thought?
Any insight? Thanks!
May 20, 2008 at 8:52 pm
There are some definite advantages to the single db plan but since you already have that here are some advantages to the multiple db plan:
Another way of saying "everything in one place" is "all eggs in one basket." By splitting out clients into their own dbs you reduce the risk of losing business when a db goes bad.
As clients go away, you can simply detach their dbs and put them into long term storage instead of running "delete for client = x" statements everywhere.
You can apply special rules and settings for one client without impacting other clients.
Simpler to prevent team members from looking at data for clients that the members are not supposed to see.
Client x needs more space? Move its whole db instead of picking out all of the relevant procedures, tables, etc. (How closely does everyone follow the naming conventions?)
Another way is to have one or more dbs that are shared by all clients plus dbs that are client specific.
May 21, 2008 at 4:24 am
Agreed, but it all depends on the performance bottleneck if you were to draw a report across clients.
Querying multiple databases may kill you on volumes.
However, in terms of the flexibility, that is a small price to pay.
Also, you may considert creating a consolidated database for all client info, but then data redundancy becomes the price you pay
May 21, 2008 at 6:46 am
Thank you everyone! Interesting points, and it's good to see a different perspective. Thanks for the lesson!
May 21, 2008 at 7:04 am
The other thing is this allows for scalability. If one client grows a lot, or becomes a query resource hog, you can easily move them to their own server.
May 21, 2008 at 1:29 pm
And charge them accordingly!!:Whistling:
-- You can't be late until you show up.
May 21, 2008 at 1:48 pm
Another advantage to separate databases is, what if one client calls in and says, "hey, we FUBARed our data and need you to restore from last night's backup"? If you have one database for all clients, forget it, ain't gonna happen. If you have one database per client, this is easy. Get smart about the maintenance, and you can even ask, "Is last night's backup good enough, or would you rather have it restored to one hour ago, or noon or whatever?", assuming you run diff/log backups periodically throughout the day.
The main one, however, is the ability to add servers without having to federate the database. Maybe you don't have any one client that's big enough to need their own server, but what happens if you can really only get decent performance for 10 clients on one server, and you get 12 clients? Multiple databases, it's easy, put 6 on each server. One database with 12 clients? Not so easy.
I also have to ask about having to propagate all structure changes to all databases (on all servers). Is that really necessary in this case? If one customer wants a table called "Widgets", do all of your clients really need that table?
- 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
May 21, 2008 at 4:04 pm
GSquared (5/21/2008)
I also have to ask about having to propagate all structure changes to all databases (on all servers). Is that really necessary in this case? If one customer wants a table called "Widgets", do all of your clients really need that table?
Good question, and I don't know. The scenario I've looked at uses a single front-end to access all databases. I don't know if it's looking for the same structures no matter what or why. Frankly I think the whole thing is wonky, but I think I may have attributed that to just the fact it was breaking things out like it is. I see that was probably the wrong assumption, and these are all very good points!
May 22, 2008 at 1:17 pm
My current db's are client specific
To get around the reporting across servers
you just setup a job to import the needed information into a report server
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply