How many Applications should I put in a database?

[pclfw]

I was asked a question a few days ago that I cannot easily answer.

"Why don't we put all of our small application databases into a single database?"

Well, There's nothing really stopping us. All of our small apps were developed in house so we don't have any problems with conflicting table names etc.

My initial reaction was to recoil in horror, "No way!" I don't think that I shouted, but it wasn't far off.

But the questioner stuck to his guns, "Why not, can you come up with a REASON why we shouldn't do this?"

Well, how about high availability? Well we can set up a cluster, or mirrored pair of servers, so that's no limitation.

And, no, I couldn't.

So I'm throwing this open - How many Applications should be using a single database?

Over to you.
:)

[adam_k]

There are pros and cons to both ways. I can't answer without any details and the small apps is pretty generic.
What does applications mean? Do they share data (not that you can't share cross-db), share users, share DBAs, share naming standards?

[pclfw]

Thanks Adam_k,

An app is a business application, in this case it would have been developed in house and thus SHOULD have conformed to (what little) naming conventions we have.

Each application currently has its associated data tables hosted in a single SQL Server database. One of the DBs holds data that is destined to be accessed by all of the other apps, but no other data sharing is envisaged.

The suggestion has been made that ALL of the data table be hosted in a single database. And the person who made that suggestion is influential enough to have this imposed unless a real REASON can be put forward against this proposal.

[debasisdas]

You can go ahead and implement the approach if you have proper back up and recovery mechanism. Remember you are putting all the eggs in a single basket.

[Netcode]

I was asked a question a few days ago that I cannot easily answer.

"Why don't we put all of our small application databases into a single database?"

Well, There's nothing really stopping us. All of our small apps were developed in house so we don't have any problems with conflicting table names etc.

My initial reaction was to recoil in horror, "No way!" I don't think that I shouted, but it wasn't far off.

But the questioner stuck to his guns, "Why not, can you come up with a REASON why we shouldn't do this?"

Well, how about high availability? Well we can set up a cluster, or mirrored pair of servers, so that's no limitation.

And, no, I couldn't.

So I'm throwing this open - How many Applications should be using a single database?

Over to you.
:)

Well, i really cant say yes or no, it depends on the user and environment. Putting all in one database is possible if they are related like a Human Resource and a Payroll but also remember like you've been told before that you have all in one basket and a good backup and recovery mechanism is paramount.

Think if it is really necessary and make best conclusions

[BitBlt]

As far as your initial reaction, I'm with you. I too would be horrified at the thought.

However, strictly speaking, there is NO reason why you can't put them all in one database. We do that ourselves for certain groups of apps. These apps are tiny, with limited user base, limited volatility and no sensitive or confidential data.

The only GOOD reason has nothing to do with the applications themselves, it has to do with maintainability and administration.

If you mix all your apps into one database, what happens when some poor programmer has to figure out how to modify/enhance one of the apps? Which tables does he concentrate on? Of course, if you are using schemas instead of good old "dbo.myTable" it becomes easier. But what about shared tables?

Then again, what about security permissions? Depending on the sensitivity of the data, it becomes either an administrative nightmare or a confidentiality hole.

I'm not sure if this response helps your situation at all, but these are just some things you might want to consider.

[adam_k]

So, reasons for not putting all data in a single db:
1) Different backup strategies. Production dbs with data that change every second has totally different backup strategies than dbs with reporting data only. Size of dbs and of transaction logs are critical factors
2) Ownership. If each application has it's owner (or belongs to a different department) for security reasons it's best to keep the dbs separated and restrict the db owners to their db. Also merging all dbs in 1 db under this scheme means that somebody has to assume the role of db administrator / owner.
3) Compatibility. If the apps you are using require a different compatibility level then the separate dbs is a must.
4) Lack of resources. To change a lot apps means a lot of testing (depending on the importance of those apps, but still) and probably some troubleshooting. Does your company have the resources for this?
5) Data availability. When you restore a db for example, do you want everybody to surf the net, or just a portion of the company?

[Netcode]

So, reasons for not putting all data in a single db:
1) Different backup strategies. Production dbs with data that change every second has totally different backup strategies than dbs with reporting data only. Size of dbs and of transaction logs are critical factors
2) Ownership. If each application has it's owner (or belongs to a different department) for security reasons it's best to keep the dbs separated and restrict the db owners to their db. Also merging all dbs in 1 db under this scheme means that somebody has to assume the role of db administrator / owner.
3) Compatibility. If the apps you are using require a different compatibility level then the separate dbs is a must.
4) Lack of resources. To change a lot apps means a lot of testing (depending on the importance of those apps, but still) and probably some troubleshooting. Does your company have the resources for this?
5) Data availability. When you restore a db for example, do you want everybody to surf the net, or just a portion of the company?

Well written