Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is it best to have one single instance with many database or to spread the databases across a few instances?

share|improve this question

migrated from stackoverflow.com Jun 16 '10 at 22:10

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

10  
"Best" for what? Memory? Disk space? Administration? Cost? –  Oded Jun 16 '10 at 20:35
    
Is there a reason (ie, requirement) to have multiple instances running? –  Erdrick01 Jun 16 '10 at 21:00
    
Which database server? This would be easy with some servers, near impossible for others. –  Seth Jun 16 '10 at 21:19
    
It makes no sense, all those separate instances will be sharing the same cpu/memory resources - so, how does it help your situation? There is no logic in doing that. Besides, it's going to be a maintenance nightmare! later on –  Nikolas Sakic Jun 17 '10 at 3:57

6 Answers 6

up vote 2 down vote accepted

I'd suggest one instance instead of several instances on the same box, unless you are trying to solve a very specialised problem. It makes administering the server easier and it uses less resources for overhead.

A very good rule for systems administration is the same rule that works well for programming, or did back when I used to still do that: Make things as complex as they need to be in order to solve the problem properly, no more and no less. Multiple instances are usually going to make things more complex than one instance with multiple DBs.

If you need to separate things to secure them then I'd suggest multiple server installs (you can virtualise onto one physical box here), because I'm not sure I'd consider different instances on one box to be a security boundary.

share|improve this answer

Assuming SQL Server, 'cause that's what I know...

I recommend keeping them within a single instance.

This allows for higher performance in accessing data across databases (i.e., from within db A you need to retrieve something from db B). If they are separate instances, the communication would need to be done through linked servers, using named pipes or something. Within a single instance, you can access all DBs directly (subject to security constraints).

Putting them all together gives some potential exposure to a single point of failure, but I find that SQL Server itself is pretty robust. The whole machine might fail (taking all instances down), but I haven't seen failures of the kind that would make a single-instance arrangement preferable in this way.

Since you can manage storage down to the file level, separating into multiple instances is no help there.

You might think about security implications, if somehow you get hit with a SQL injection attack (for which there's no excuse, since it's so easy to prevent!). But if you've got linked servers between the instances, you're not really getting any more protection.

share|improve this answer

Regardless of what SQL server we are talking about, as a general principle I would run all databases under one instance wherever possible, for all the reasons already mentioned by others. Moreover, if you get to the point where the one instance is not handling the load very well it's time to add another machine, not another instance. The only exceptions I can think of is applications that will only run with their own instance. Of course such software is best avoided or placed on a different machine if possible.

share|improve this answer

From a memory point of view (again, assuming MS SQL Server) multiple databases within a single instance will generally result in more efficient usage.

We run multiple instances when required to by an off the shelf application. For stuff we develop, we throw it all in a single instance.

share|improve this answer

Here are some pros and cons for having the databases on the same SQL Server instance

Pros:

  • easier administration
  • easier managing of security entities (SQL logins, roles)
  • less complexity
  • better performance when accessing one database from another (as they are both on the same SQL Server instance)
  • more efficient usage of memory and other resources

Cons:

  • Having 'all eggs in one basket' might be more dangerous in case of a SQL Server instance failure. But if the instances are on the same machine, the whole machine can fail, so it makes no difference
  • SQL Server injections and other security threats. You don;t have to worry about these if you've properly configured the system
  • Not being able to have different SQL Server versions
share|improve this answer

I feel, its totally depend on your technical and business demand, the database designs, your infrastructure, future growth of the database and the criticality level of the databases.

If you feel your infrastructure is capable to handle the pressure of number of databases, application users within a single instance then no one is going to stop you to host them on a single instance after taking into consideration of above points.

BUt its advisable to keep the most critical and playable database on a single instance. Rest all depend on the database design and capacity planning.

share|improve this answer

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.