2

Possible Duplicate:
Should I use one database per application or share a single database amongst multiple applications

We have a bunch of small to medium sized apps, each of which has its own database (MSSQL Server). There was a suggestion that we consoldate the 'related' databases into a smaller set amount of larger databases.

They don't particularly share a lot of data, they would just be under a similar business group. For example, using a 'Finance' database to hold the tables and procedures for finance apps.

Would it be appropriate to use a different schema for each app, e.g.

App1.SomeTable
App1.SomeOtherTable
AppTwo.SomeTable

What are the pros and cons of this approach? What should I watch out for?

2
  • 1
    Yeah I saw that one. That question seemed to be dependent on the fact that the applications/databases would share a lot of the same data. Since there is not a lot of sharing between ours, I wasn't sure if the same issues applied. I am wondering if there is an upside or downside to consolidating, or if this is a case of 'If it ain't broke, don't fix it'
    – King
    Jun 20, 2012 at 17:56
  • There have been similar questions before, that was the first one I could find (also probably on dba.stackexchange.com/questions) The issue of rolling out structure changes to many instances will still be a problem, even if you don't have to worry about shared reference data. Jun 20, 2012 at 17:57

3 Answers 3

5

At the risk of stating the obvious, in general, if you share the DBs, you're coupling the applications and making DB changes more "global." In most situations, that's probably a bad idea, for the same reasons that broadly-scoped variables are generally a bad idea. However, if there is significant effort in maintaining or optimizing the DB, then you might have some gains (e.g., if your DBA tunes up the DB to perform better, everyone gets the boost, or if you spend a significant amount of effort auditing and cleaning the data).

2

Integrating the source of information is a good thing to seek. In fact, organizations spend millions to integrate their disparate decision support systems and operational systems. One needs to determine:

1-What are the shared data?

2-How often does this data change?

3-How large is the volume of this data?

4-Can the data be re-entered manually or is that too time consuming and/or error-prone?

5-How sensitive is the shared data?

6-What is the cost of automation?

In some cases, specially with small business, the number of transactions is not that large and it would probably take some one less than 1 hour to enter the information manually. The cost of this effort can be measured in dollars against the proposed automation cost.

You may want to use a matrix similar to the crude example below (I know it is neither accurate nor complete!) to show the data shared and how this data is shared. The left hand side represents the table and horizontal column headers represent the different systems. There is an analysis technique called Clustering Analysis that could be used to show good system boundaries, but I don't have an electronic reference for it (it is old!).

enter image description here

1

This drastic of a change should be done to solve a problem. There are pros and cons of multiple/more vs single/fewer databases. It depends on the problems you're trying to solve.

Do your developers struggle trying to find where the data are? It may be easier to work in the Finance app knowing there is a single Finance db.

Is referencing two tables in a query from different databases really that hard?

Backups - restoring at the database level is pretty easy. The fewer tables and other parts the easier it gets. Yes, you can break up large databases into multiple files. It's only going to make it harder to maintain a development database if you have to transfer massive files especially when you only need a specific section.

More Servers - it's easier to move a database to another server. It's much harder to put one database on several pieces of hardware.

I don't know what your situation is, but there just doesn't seem to be any justification for one database. If problems come up, reconsider.

Not the answer you're looking for? Browse other questions tagged or ask your own question.