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.

For a use-case of 9 databases spread across 3 time zones, is it advantagous to spread the databases onto multiple SQL Server instances on the same server?

What will I gain and what will I lose? Tagged sql-server-2012 because it's the most pertinent, although generally the question is across SQL Server versions.

share|improve this question
    
It would be nice if you could provide us with more details. The way your question is currently phrased, you will only receive information on the pros and cons of instances in general and not for your specific needs. –  hot2use Nov 21 '12 at 7:21
    
For what reason do you want to scale out? Ease of maintenance? –  Jon Seigel Nov 21 '12 at 15:20
add comment

4 Answers

No, no and again no. One instance, one server, Always. The reasons have to do with the SQL Server memory manager and CPU scheduler architecture, it really works best if it has the whole box to itself and nothing else runs on the box. 'Partitioning' of resources (max server memory, affinity mask) solves some problems and introduces more.

If you want to consolidate, consolidate in one instance.

If you must keep separate instances then a far far far better option is Hyper-V and partition VMs. VMs have a much tighter control of resources and offer many other advantages. You need to be able to expose IO paths directly to the VM (no DBs on VHDs).

share|improve this answer
add comment

There's only a couple of "gains" that I can think of off the top of my head. First off, security. If for some reason a login had to have elevated permissions on the instance but there are sensitive databases then that could potentially be a reason to have multiple instances on the same box.

Another one would be the requirement for different SQL Server versions to support different applications, if you didn't have other available servers.

As for losses, you are having multiple instances having to share the same resources. Sure you can easily isolate, but you're still taking away max specs if it was instead one instance.

share|improve this answer
add comment

You would want to install mulitple SQL Server instances in the same server to adjust server level settings such as:

  • Memory per instance
  • List item
  • CPU

  • affinity per instance

  • Server level security settings (often great for compliance)

For a more comprehensive list, visit: http://technet.microsoft.com/en-us/library/cc966454.aspx

So let's say you have a OLAP and OLTP environment but somehow on the same server, let's say in cases of multi-node failover. Now you are supporting both nodes on 1 server, by setting affinity masks and total amount of RAM per instance, you ensure that customers can still process data (and make $$$) while reporting takes a back seat.

share|improve this answer
add comment

I have never installed multiple instances of SQL Server on one HW server except for testing. The above mentioned reasons for multiple instances are more or less true, but if you have huge databases then you might still need multiple HW servers.

Two huge advantage of having SQL instances IMO from a production perspective

  • You can patch individual instances with Hotfixes if you encounter special situations with your database instances.
  • You can restart individual instances if they get stuck (Lock situations)

But we still don't use individual instances, because if you have to install the Service Pack then you have to do it for each instance.

share|improve this answer
add comment

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.