1

I am a new DBA working for a charity and I have project to consolidate the multiple databases scattered across the organization.

I have identified the main databases that are mission critical to the organisation. We have a virtualised infrastructure. We currently run SQL Server 2008R2.

  • Fundraising Database (off the shelf package)
  • Case Load Services Database (Developed in house)
  • Finance Database (off the shelf package)

Now the question I have is (bearing in mind limited budget and resources) is whether I should put all 3 of these databases on 1 single instance on 1 single Windows Server 2008 VM. Or would it be better to have them on a single box with 3 named instances? Or possibly even 3 separate VM Servers with 1 databases each?

So even though each database is mission/business critical it is ok to have all of them on 1 single instance. So we would create a virtualised windows 2008 server called SQL01 for example. Install 1 instance of SQL 2008r2 and restore all 3 databases on that single instance? Or would it be better to create 3 names instances on that single server and have each database within its own named instance?

4

The only benefit to using three separate instances would be if you plan to take one instance offline for some reason, e.g. to patch SQL Server one instance at a time, without affecting the other two databases. Of course this doesn't buy you isolation from other types of events, such as patching Windows, never mind that all your eggs are in one VM's basket, too - so all three databases will be vulnerable to underlying hardware issues, resource limitations, etc. whether they are on one instance or three. And of course all three instances will be sharing the same resources within the VM; there is significant overhead involved with running each instance regardless of the databases or volume.

Also, using separate instances - and especially separate VMs - seems to fly in the face of your overall mission (consolidation).

So, I strongly recommend you create a single instance, and put all three databases on one instance.

2

If you put three instances on the VM, you will have 3 copies of SQL Server running.

If you put the 3 databases into a single instance, then you have only 1 copy of SQL Server running on your VM which makes more memory, processor, etc available to your processes.

Assuming no serious processing issues, I would recommend the single instance. Why add overhead unless there is some other compelling reason to do so?

  • 2
    I don't believe three instances on the same machine requiring independent licensing (most editions support up to 50). But I will confess I am not overly familiar with physical vs. VM licensing peculiarities. – Aaron Bertrand Mar 27 '14 at 22:37
  • @AaronBertrand - Perhaps you are right about the licenses. Of course, having 3 instances on one machine still requires more resources on the machine. – RLF Mar 28 '14 at 12:13
  • Yes, I absolutely agree about the resources, just not about the licenses. :-) – Aaron Bertrand Mar 28 '14 at 13:27

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.