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.
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).
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.
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.
You would want to install mulitple SQL Server instances in the same server to adjust server level settings such as:
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.
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
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.
