Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-09-19 : 22:46:16
|
Yesterday, Michael asked a very good question about horizontal partitioning a table to increase query performance. He also asked a followup question that I found interesting:
"Suppose I was opening a web site that provided online accounting software to businesses (such as netledger.com). So one day, I may have 3 business subscribing and the next day 2 more may subscribe. Is it best to store all of these customer's information (invoices, customers, employees, accounts payables) together in the same database and tables? Or, is it best to make a database for each of the customers and store this db name so the software would connect to the correct database for a given user?"
The company I work for is more or less an Application Service Provider like NetLedger.com, so I have a few opinions on the matter :) Read on, and if you have opinions about splitting application databases out by client, I'd like to hear them.Article Link. |
|
|
SpecApps
Starting Member
3 Posts |
Posted - 2003-01-14 : 09:54:08
|
| 1. If each company had there own database and it is there data if they decided to leave you you could supply them all the data easily. (only plus side to seperate database from my view point)2. By having all the data in one database easier to maintain, easier to upgrade, easier to backup, easier for the development team.The information you haven't supplied is who many companies how large the databases will be and so on and that will make the difference in the long run. Because if a company grow larger they may need to be moved to a different server etc.Bryan@specapps.com |
 |
|
|
RocketScientist
Official SQLTeam Chef
85 Posts |
Posted - 2003-07-21 : 15:10:43
|
I'd go for separating the databases out also. Here's why:1. 90% of the time that I've needed to do a restore-from-backup, it's not because of a hardware failure or a software failure causing corruption. It's usually because somebody did something stupid, like left the WHERE off of their DELETE statement. Having things isolated into one database per customer limits damages pretty quickly, and it makes restoring faster The smallest element of backup/restore is the database. So, you can either have one big database that takes 3 hours to back up, or 180 databases that take 10 minutes each. So, in the event of accidental data loss, being able to take one customer offline for 10 minutes is better than taking all customers offline for 3 hours.2. While having separate databases does make operations like mass-reindexes or structure changes a bit more complicated, that can easily be worked around with a quick cursor through sysdatabases. 3. Scalability gets easier with multiple databases. If you've got, say, 500 clients, and you want to spread them across multiple SQL Servers, it's easy to split them up just by detach/copy/attach (or even dump/copy/load). If it's all on one server it's a bit more complicated to move client data around.The biggest downside to having hundreds of databases on a server is that Enterprise Manager has to open every single database as it connects to a server in order to pull the status information back. Which means that when you get about 700-900 databases on one server, you're going to not use Enterprise Manager, or at least you'll never ever close it  . |
|
|
|
|
|
|
|
|
Article: Partitioning Databases in an Application Service Provider Model