Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented.

While I don't agree I'm having trouble collecting good arguments to use a single database. (While the 7 databases splits the tables in logical domains)

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

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

[summary so far]

Arguments:

  • Losing data itegrity (can't use foreign key over databases)

  • Losing restore integrity.

  • Gaining complexity (db user/roles)

  • Small odds server/database will go down.

Solutions:

  • Use schemas to seperate domains.

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

share|improve this question

migrated from stackoverflow.com Jan 26 '12 at 14:32

This question came from our site for professional and enthusiast programmers.

5 Answers 5

up vote 9 down vote accepted

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)
share|improve this answer

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.

share|improve this answer
    
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

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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