application service provider architecture

Hi Gang,
Not sure if this is the "right" sql server group to post this question in, but thought I'd give it the "ol'college try". If you feel that I may benefit by posting same question in a different group, please don't hesitate to let me know. Anyhow, below is my question:
Working with a client on developing an Application Service Provider system where users of the system will be affiliated with a parent company, the company will have the contract with my client and each company's data must be separated from all other companies. salesforce.com is a great example of such a structure, but has nothing to do with our application (meaning, we're not building a sales app).
What it comes down to is that there are 2 choices (unless someone knows of a others...)
1. Having the system create a separate database for each company or
2. Managing individual user access rights either through database roles or programatically.
We're looking at other DB providers to see what options we have, and for example, have found that Oracle 9i (which salesforce.com runs on) has a "Virtual Private Database Option". This feature enables developers to build 1 DB to support the ASP model and have Oracle logically (behind the scenes) manage access to the data. There are some disadvantages to doing this, but it is interesting.
I'm looking for information about / examples of SQL Server being used in an ASP environment and what methodology was used and why. Any assistance is greatly appreciated.
Thanks!I prefer separated databases... Each database can be modified, or
recovered, or backed up as the user sees fit... If they are all in a single
database and one user does something nasty and needs to rollback, it would
be a much more difficult task.. Security would be cleaner also. But you
would have lots of backup jobs to deal with...or maybe more complicated
jobs to backup everything.
However if there are going to be lots of customers, which would mean
hundreds or perhaps more databases, then the SQL tools do not work well...
It would take lots of time to open SQL Enterprise Manager for instance, so
you'd have to go command line probably...
The benefit of using a single database, is easier maintenance... But mixing
customer data might be a problem... would you have a separate set of tables
for each customer or simply add a companyid field to each table... IF you
have a companyID field added, you'd need to make sure performance wouldn't
suffer... If I have a small number of rows for my company in one of the
tables, where other companies have millions of rows, if I needed to do a
table scan, I'd have to look through everyone elses rows as well...
Tough choice,
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"appsprov" <anonymous@.discussions.microsoft.com> wrote in message
news:B973EF65-7175-4208-9657-3B8AAAADB2D5@.microsoft.com...
> Hi Gang,
> Not sure if this is the "right" sql server group to post this question in,
but thought I'd give it the "ol'college try". If you feel that I may
benefit by posting same question in a different group, please don't hesitate
to let me know. Anyhow, below is my question:
> Working with a client on developing an Application Service Provider system
where users of the system will be affiliated with a parent company, the
company will have the contract with my client and each company's data must
be separated from all other companies. salesforce.com is a great example of
such a structure, but has nothing to do with our application (meaning, we're
not building a sales app).
> What it comes down to is that there are 2 choices (unless someone knows of
a others...)
> 1. Having the system create a separate database for each company or
> 2. Managing individual user access rights either through database roles
or programatically.
> We're looking at other DB providers to see what options we have, and for
example, have found that Oracle 9i (which salesforce.com runs on) has a
"Virtual Private Database Option". This feature enables developers to build
1 DB to support the ASP model and have Oracle logically (behind the scenes)
manage access to the data. There are some disadvantages to doing this, but
it is interesting.
> I'm looking for information about / examples of SQL Server being used in
an ASP environment and what methodology was used and why. Any assistance is
greatly appreciated.
> Thanks!