Pros/Cons of using multiple databases vs using a single database

Question

I was working on a new project which has the requirement to use 7 databases, arguing that performance, stability, optimization are more easily implemented.

While I don't agree, I'm having trouble collecting good arguments to use a single database (splitting the tables into logical domains).

One argument I have so far is data integrity (I can't use foreign keys between databases).

What are good pros/cons for using a single or multiple databases?

[summary so far]

Arguments against multiple databases:

• Losing data integrity (can't use foreign keys over databases)

• Losing restore integrity

• Gaining complexity (db users/roles)

• Small odds server/database will go down

Solutions:

• Use schemas to separate domains.

• POC: Use dummy data to prove the point in 7/1 db's execution plans

Answer 1

None of performance, stability, optimization are true. Does anyone have a solid argument or reference article why these would be true?

Resources are not allocated to a database: the SQL Server Instance balances resources so it makes no difference

You lose:

• data integrity
• restore integrity (data in DB7 will be later then DB1)

You gain complexity:

• security (users, roles etc) have to be in all databases
• you'll have some data that doesn't fit into 1 database nicely

Options:

• splitting a database onto separate disks can be done with filegroups
• use schemas to logically separate data (based on other answer)

Answer 2

Good reasons to create separate databases would be to support different availability requirements or simplify administration. For example if your databases require very different backup schedules or different recovery models. Another reason would be if you may want to run them on different instances.

There are no performance optimisations available with multiple databases that you can't also achieve with one database. Can you give more detail on what you mean by "performance, stability, optimization"?

Answer 3

If you're after splitting the data by logical domain you could always look at using schemas within SQL2008 (going away from the default of dbo.) but even that is painful and can cause issues with OR/Ms that aren't expecting a non-standard schema.

I've been in the position of collating data from more than one database and it is painful and far from high-performance. You end up cacheing data or at least using tricks to maintain speed.

As a test, build 7 dummy databases. Build a query that requires data simultaneously from all 7, or at least a good number of them.

Then compare the execution plans! I think you'll win your case right there.

Answer 4

Thought experiment: Instead of dividing your database into seven pieces, split it instead into 7,000 pieces. What is the likelihood that a hardware failure is going to impact your application? If there is a 0.1% chance that any particular server may die on a particular day, are your chances better or worse that you're going to be impacted by a hardware failure when increasing the number of machines you're dependent upon?

I think it's important to split the notion of "the database" into two pieces: the schema and data vs. the hardware which is used to serve the data.

Breaking the database across multiple machines does you no good for the many reasons explained by the other answers in this topic.

If you're going to use multiple machine for reliability and enhanced performance, perhaps you can structure them so that you have a master server with multiple warm/hot standby machines which could also be used to distribute queries across. This way if any one machine experiences a failure, you lose no data, and at worst you'll have to restart a query. Of course, it's more complex than this, but the basics do apply.

Answer 5

Like any other approach in Software, each one comes with its own Pros and Cons. One could argue that an approach is chosen to serve a need/requirement.

Take an ERP system for example. Imagine a system comprised of Inventory, Accounting, Engineering processes (E.g. Maintenance Systems), Payroll, CRM etc. and all these packages belong to one complex System... Would you really want to use a single database for this? You probably could, If you want.

But the best case scenario is multiple databases. Firstly, Databases are designed(via Schemas) before they are implemented. So data integrity is not a problem at all as long as the designer knows what they're doing in terms of how the databases communicate and how they are related.

1. Performance

From performance point of view, these systems are often used by Big companies and putting such in one database bring some performance risks because that database will have to serve all of those systems at once. When each sub system in split into a distinct database, the performance will be much better because each database serves a specific sub-system(Of course since they're part of the bigger system, they do provide cross data).

2. Scalability

When it comes to scaling, It almost often doesn't matter if you're using Single or Multiple Databases. But, in our case, By splitting databases based on each subsystem, it's much easier to scale each database without affecting all the other databases. You could literally encounter a problem on the Payroll system database (down time), and not have other systems affected at the same time. Would this be the case in terms of single database? No ways!

3. Stabilization and Optimization

These really don't matter much on approach. They are mainly resource based.

4. Maintenance

Why would this be an issue if you're a big company and you can hire a lot of database admins to manage the databases specific to each sub-system? Hah aha... this really isn't a problem at all. And by knowing the schemas, there should be no difficulty in managing these databases.

Conclusion

Both approaches are better depending on when they are used. Unless you find the need to have multiple database, I would suggest that you strictly stick to a single database.

Answer 6

I concur with one DB and using file and schema options instead.

There are edge cases where splitting into multiple pieces makes sense.

Application environment (dev, test, prod) configuration, such as FTP servers, export file paths, etc..., Stuff you want to store per server, and not get overwritten on a restore.

Also as a way to isolate client specific procedure changes.

But these are support and not performance issues.

Answer 7

I don't agree with those who say that there is no difference in performance between a single database and several for the same application. Indeed, there are at least three negative impacts (and undoubtedly others...).

Firstly, each database has metadata tables (more than a hundred) which are necessary for using the data (users, privileges, objects, indexes, etc.). By multiplying the databases we multiply the number of cached metadata tables, which takes up memory...

The absence of the possibility of setting up referential integrity constraints prevents certain semantic or heuristic type optimizations, such as the optimizer carries out...

In terms of maintenance, this adds processing time. Each maintenance process must inevitably address each of the bases. This is longer than a single treatment on one base. In addition, treatments take resources to the detriment of production.