CSLA .NET

I have an application written in CSLA that potentially thousands of companies could be using.  I am using SQL Server and my question is am I better off creating a separate database for each company or storing all the data in one database?

I currently have 18 companies running off one database without any problem but want to make sure this is the way I should be going before I get too far into this.

Thanks for any comments.

From a security standpoint, I would have a seperate database for each company.

 

It sounds like you're running some sort of online service provider. I remember a podcast discussing this sort of thing. The question then was about using a virtualized system for each company rather than one system with effectively a CompanyId column in each table. The one system, one database is much more efficient on resources compared to virtualization. Multiple databases are not so bad I guess but the suggestion for security was that they have an unbreakable rule that everything is accessed via views and stored procedures. The CompanyId variable is set once when someone logs in and the views and sprocs always use that variable.

Ross

eslater:

I have an application written in CSLA that potentially thousands of companies could be using.  I am using SQL Server and my question is am I better off creating a separate database for each company or storing all the data in one database?

I currently have 18 companies running off one database without any problem but want to make sure this is the way I should be going before I get too far into this.

Thanks for any comments.

That is currently how I am doing it.  CompanyID is set on login and then all stored procs use it.  I dont currently use any views.  Everything is working great right now but I am concerned about security and scalability.

I wonder what scenario would work best in the long run.  If you have separate databases per company and lets say a single server can accomodate 50 companies.  What happens when you add the 51st?  I am using a DataPortal which points to the SQL box.  If there is more than one box how would I distribute the app such that they point to the correct box (it is a winforms app using remoting)?

From a performance perspective would many databases be better than 1 really big one?

Where is the CompanyID being set/stored? In the app? What happens if Company A guesses/discovers Company B's CompanyID?

I also don't like storing CompanyID with every record in every table, and also having to have it in every WHERE clause in every sproc or view. It seems like a lot of storage/work to try to keep info seperate that shouldn't be together in the first place.

Unless there is a compelling reason to have ALL the data available in one place or share a lot of data between companies. I would seperate them. It will also make migrating one or more companies to a different box a lot easier.

 

I agree with all of these points in theory.  I offer one counterpoint: what happens when you have to perform some type of DB upgrade (changing a stored proc, adding a column to a table, changing a varchar's maximum width, etc).  Do you want to perform this upgrade "potentially thousands" of times?

matt tag

Hi

I am also interested in this.  I am interested to hear from those who use the companyid approach as I have similiar scenario .... rather that companies I have a web app that covers different regions/offices and need some confidence to use the officeid (rather than separate databases for each office) approach.

Any contributions welcome

Griff

 

 

 

The counter-counterpoint is, do you want to make sure that ALL customers/clients are updated prior to upgrading the DB? Multiple DB's allow you to roll out upgrades one customer at a time. It also allows you to offer customer specific customization if necessary.

 

Actually this isnt a problem for me.  When the app starts I have it check a server status which I can use to indicate an update is underway.  I can freely update the database and the flick the switch to turn it back on.  When the user starts the app it sees an update available and automatically downloads and installs it.

As of right now I am in favour of the one database approach (for ease of update and distributuion) although the security is somewhat of an issue.  I dont think anyone is going to guess a guid and even if someone did they dont have direct access to the database since it runs through the portal and doesnt accept remote connections. I am not a security expert by any stretch but I would love to hear some comments on the topic.  For instance how would someone infirtrate the db if they did have knowledge of one of the other CompanyID's?

I'm the cheif architect for a company that offers SaaS.

Our hosted small company application is having a couple of thousand customers in the same database. No client will ever know it's id or being able to input it anywhere to trick the system to bring back data from some other client. If you are planning to grow big, it's best to have as few instances as possibly from a maintanence perspective. Database Indexes and webserver caching is probably the two toughest problems for us. Caching since no data is shared between clients, and idexes since we have to index to the mean, not how a specific clients data neads are. WE rarely have any performance problems though!

We also have single database for each hosted large client.

Large clients gets there own url and virtual directory, so its always easy to figure out what database they should connect to.

Sceptical

We are also developing a SaaS app and have similar issues to deal with. We went with an isolated archtiecture (single-db/single-tenant) because it is the easiest to implement during the development phase. However, I do have concerns about operational support and costs after implementation, which tends toward a shared model (single-instance/multi-tenant or multi-instance/multi-tenant for very large implementations).

The shared model would use horizontal partioning and a hybrid security model consisting of both impersonation and trusted subsystem. Key wrapping is also a consideration in order to simplify all of this.

Wondering if anyone here has had any experience with the latter?

It really depends on the application you're creating and the data you store.  What would be the consequences be of a malicious user retrieving data for another company?  At a guess you have a Company table, so what would it take for an amateur hacker to find a hole in your app and run a select on that table?  How likely is that to happen?  How many developers are working on the product and what's the likely hood they bypass the stored procedures and run a sql statement direct?  How much data is being stored by each company? Is it vast amounts? Maybe you'll have issues when you have x amount of companies using it?  What happens if you lose data? One unhappy client is better than all of them!  What are the consequences of a server going down?  Is the application exposed as an application or a website? etc.

You need to weigh all that stuff up against the overhead of having seperate databases/servers.  Chances are it will be fine to have a single database, it just depends on what your priorities are I guess.

---

UK Daaarn Saaarf

We are in the process of looking at possible ways of addressing this exact scenario. We provide a service to +- 50 companies all of which are hosted on the same database. From an operational/end user point of view this does seeem to be the best way as we have teams that could work on multiple companies at the same time, switching between companies within the same instance of the application provides a huge productivity gain for a user.

In my system I put an organization_id column on all root tables even though I'm giving each client their own database. It would be extremely difficult to go the other way later verses just using an extra column now.

Also, updating many client database schemas shouldn't be much different than updating one. It's just sql scripts after all, right?

Do a search on "multi tenancy". It's discussed in a lot of blogs / forums and I think I remember seeing more arguments for separate databases than not. The single point of failure is a bit scary for me. I guess if the amount of data per client was very small and the scema was very simple I would lean towards the single database.