Should I create several small application databases or one large one?

Question

We are migrating several old mainframe applications for a customer to newer ASP.NET + SQL Server 2005 applications. Each application is generally thought of as standalone with no shared data between them, and do things like manage specialized inventory needs or vacation policy. The data is small and shouldn't outgrow a single SQL Server. Is it better to create one large database for all of these apps to share, or multiple smaller databases- one for each app?

In general, we prefer one small db per app, but a case could be made that a larger database is easier to manage backups and security. What factors should be considered in making this decision?

Answer 1

You should be using separate databases. They can all be run from the same instance of SQL Server.

Answer 2

I'd highly recommend the small db per app approach. If one application crashes and corrupts its tables, you don't want to have to piece through and do a selective restore of only certain tables. Being able to drop and load is far better for maintenance and any unforseen updates you have to do in the future.

Answer 3

It depends if those application should share the data or not. If so then it is better to have one database behind them. If not then it is better to separate them so that in the future you have the freedom to move (isolate) the applications even more.

Think about how your applications are going to be used. Because when you have one database and for example one table for customers, you might be running into locking issues when you have multiple applications accessing the same table.

Answer 4

I would use the one database per app approach if the data store hasn't got any overlap between apps. That way, if one of them outgrows the current database server, you can move it off to its own server with relative ease.

Also, from a backup/restore point of view, it's probably easier to restore the database for a single app to get it up and running instead of having to restore the whole set (because they're in a single database) and then picking up the bits you actually need to restore service.

Answer 5

I would suggest you create one DataBase solution with several Catalogs inside. Each catalog is independent and understood as a separate database, though being under the same solution offers you the benefits you mentioned before, like backup and management. After that, each catalog would contain its own data tables, etc... Catalogs do not relate data between each other like table to table.