SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Partitioning Databases in an Application Service Provider Model
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/19/2000 :  22:46:16  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Yesterday, Michael asked a very good question about http://www.sqlteam.com/Item.ASP?ItemID=684>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 http://www.netledger.com>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

Australia
3 Posts

Posted - 01/14/2003 :  09:54:08  Show Profile  Visit SpecApps's Homepage  Reply with Quote
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
Go to Top of Page

RocketScientist
Official SQLTeam Chef

USA
85 Posts

Posted - 07/21/2003 :  15:10:43  Show Profile  Visit RocketScientist's Homepage  Reply with Quote
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 .



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000