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


  • Use schemas to separate domains.

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

  • This is a complex area and there are pros and cons - take a look here and links within. – Vérace Jul 30 '19 at 16:21

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


  • splitting a database onto separate disks can be done with filegroups
  • use schemas to logically separate data (based on other answer)
  • 2
    The same way the OP doesn't have the sources for the performance, stability and optimization points, you also provide none that proves otherwise. – MMalke Mar 11 '20 at 4:11

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.

  • My idea is to (indeed) use schema's for logical domains. Also will be using Entity Data Models. As an alternative I will try the 8 dummy db's :) – rdkleine Jul 12 '11 at 9:47

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"?

  • The client haven't explained the details on 'performance, stability and optimization yet'. I'm also curious to this answer. Will be speaking him later this week. – rdkleine Jul 12 '11 at 12:27

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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