Software Architecture and Database Design: One database/web application per each company or one database/web application for all companies?

Question

Hello gurus,

I'm designing a web driven SQL database application from ground up. The application will manage information for clients that are in the same type of industry. In other words, the information (entities and relations among them) regarding each client does not vary too much from one to the next. However, the volume of the information is dictated by the size of the company. The application could be hosted on our server(s) or anywhere a client chooses.

My first question is: what are the pros and cons given the following options:

• A. Manage multiple clients information in the same database;
• B. Manage one client information per database; so each client will have its own database;

My second question is: what are the pros and cons given the following methods of deployment?

• A. Each client gets its own server (node);
• B. Use large RAID drive(s) with one powerful server with multiple websites;

As decisions to these choices affect my design, I would like to know the pros and cons from different perspectives including maintenance, cost(financially and in time) and architecture to name a few.

Technologies used:

• Database: MS SQL
• Platform: ASP.NET
• Language: C#

Any comments or suggestions are most welcome,

Thanks,

Cullen

Answer 1

The big issue is maintenance and updating of the application. If you have multiple instances running you have to maintain/upgrade all of these, this becomes even more of a hassle if they are geographically located in different areas.

The overall trend in the business software industry right now is centralizing your application to your own servers and providing software as a service to your customers.

The only real reason I could see for having to run an application at your customers site is: if it is handling sensitive information and the company has policies restricting remote storage of that data.

If you are concerned about hardware costs you could look into cloud hosting services like Amazon s2, Google App Engine, and Microsoft Azure.

Answer 2

I would not recommend giving each client their own database. I planned to do that for my current application and was talked into a single database. Good thing, since we now have 300 clients. Can you imagine managing 300 databases? Updating each one everytime you have an upgrade? Making sure each is up to date, etc.

Disclaimer: In practice we actually have several databases. A few very large clients have their own database and others share the remaining ones. I think maybe 7 databases in all.

We have the databases on one powerful SQL server. If you have multiple databases and put them on different servers, you run the risk that some servers are busier than others and small clients are underutilising their server.

Answer 3

You really want to review the following: http://msdn.microsoft.com/en-us/library/aa479086.aspx

It's a paper that goes over the different design scenarios for a multi-tenant architecture. There are a multitude of things to consider from proper data partioning, security, and skill set.

Answer 4

I'd hybrid this. Design it for the ability to use separate databases, but don't separate until there is a clear performance benefit.

Design your application so that multiple clients can exist in the same database. (i.e. build a layer of separation between client data). Then plan for it all to be in one database.

That way, you don't overarchitect your solution to start out with, but it gives you the flexibility to spin off very high-usage clients to a dedicated database. If your database exists with only one client in it, so what? This will also save on server costs so that you can be sure that you're using the hardware investment in a reasonable way already before adding more capacity.

Answer 5

I have experience running a single-tenant design with about 40 clients. Each client has separate ASP.NET application files and an SQL Server database. Application core binaries are the same for each client, but clients do have custom modules too.

I would recommend designing the whole system as multi-tenant first, but retaining the option to go single-tenant if necessary. I think that the reason we went single-tenant came from client demand. Many of our clients run the application inside their intranets.

Our business turned out to be very customer-driven, with lots of client-specific tailored features. For this, single-tenant setups fit well. But I don't think it is possible to scale this to a significantly lower price point and a larger customer base.

Pros

• Good for business software. Easy to customize, but you still get benefits from reuse
• We can use a SQL Express instances per server as long as each database is under 4GB
• Lots of separation between clients. For example, each client can have a separate IIS application pool
• Security at more system-level, not totally application level
• Single client's software version can be frozen for long periods
• Bugs do not usually surface on every client
• New features can be grown nicely based on actual client demand. First clients for a feature are basically beta-testers

Cons

• Bad for consumer software. Don't expect to scale much
• Maintenance is labor-intensive: updates, backups, debugging client-specific issues...
• Quality control is harder when versions are slightly different. Unexpected troubles surface after updates

Answer 6

Performance and etc aside, does it serve your customer's best to provide separate databases? Can this software exist or be used without your company and or a license?

For example, simple e-commerce solutions would have to provide databases so the content and database can be moved from host to host should business needs arise. Is using your software forever assuming that your company doesn't fail. I doubt my company would use such a solution, although we might use a hosted solution if we knew we could host it in house at a later date.

Security is also another concern. There is a single point of failure with one database and if one customer gets compromised and your code has a bug in it, all your customer's are exposed potentially.

Will the applications ever get customized, and if so would a single database cause issues?