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.

  • 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. – John aka 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

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).

| improve this answer | |

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.

| improve this answer | |

Late post, but not a lot of good info available around multiple instances..

I have been advised by MS to install multiple instances of SQL server to work around spinlock problems due to Always On. It has not helped with push locks in Clustering Services though, as they sit below SQL Server instances.

| improve this answer | |

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.

| improve this answer | |

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.

| improve this answer | |

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.