My boss tells me to install 2 SQL instances on each node. This means 8 instances on 4 nodes.
His reasons: a) Rather than 1 instance with databases having both high and low transactions, better to split into 2 instances, 1 with high usage databases, and the other with lower usage databases. CPU and memory utilisation is better this way.
b) Each instance will have less databases, therefore if a server is overloaded. Moving just 1 instance out may solve the problem.

I have read in the SQL Server 2000 Failover Clustering White Paper that "it is not recommended to have more than 1 instance on a node" (pg 30).

Can someone tell me the pros and cons of having >1 instance on a node? Where in the microsoft website can I find such resources?

Hi

In a cluster environment, if it is Active/Active, you will always have more
than 1 instance per node, but only 1 would be active and the other instances
are the failover instances from the other nodes.

If you have more than 1 *active* node on a cluster, all the instances need
to contend for the same resources, during failover, you need more than 1
instances to fail over and start up. If one instance does start up and hogs
all the resources, the other instance might not get serviced and it might be
down for a long period of time, waiting for the other instance.

Don't forget, each instance needs it's own disk resources, so you have to
double the number of drive letters you have on your SAN (and you have only
about 18 available in most scenarios)

If you have 2 instances per server, each node needs to have the resources
setup to handle it's and the failed node's instances (on a 2 node server,
that is 4 instances, so you can really only allocate 1/4 of the memory to
each to the active 2 instances on the server). On a 4 node cluster that
goes out to 1/16 if they all can take over from each other.

On a 4 node cluster, you are sharing one SAN, in most cases, the SAN in the
bottleneck, so adding more instances may not improve performance at all, it
actually might reduce it as you are wasting more memory on each node, that
could rather be used for caching data.

Regards
--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike (AT) NOSPAMepprecht (DOT) net

Specialist SQL Server Solutions and Consulting

"Benny Koh" <anonymous (AT) discussions (DOT) microsoft.com> wrote

databases, and the other with lower usage databases. CPU and memory
utilisation is better this way.

Hi
you mentioned that "on a 2 node server, that is 4 instances, so you can really only allocate 1/4 of the memory t
each to the active 2 instances on the server)...."
I do not understand why the 2 active instances will get only 1/4 of the node's memory. These active instances are OS process, therefore they should be allowed to grab whatever memory they needed until the OS decides enough is enough. So I don't see why for a server with 8GB RAM, each of these 2 active instances cannot get at least, say 3GB each? TIA for your explanation 8-

Hi

When running multiple instances, it is best to fix the memory size per SQL
instance. Otherwise the instances will be continuously borrowing from each
other, and starving each other. If you use the /3GB switch, must use a
fixed memory size. (see further information KB Article #274750 "HOW TO:
Configure Memory for More Than 2 GB in SQL Server").

If you have 2 node Active/Active, with 2 instances, you need to make sure
that if one node has to take the whole load, there is enough memory to go
around. So for all the instances to run, each one must be limited to a 1/4
of the memory on the server.
It does not help to have 2 using 90% of the memory and the other 2 failed
instances sharing 10%. Those 2 instances then might as well not be running.

1/4 is for illustration purposes only, but is you have a one instance that
needs 70% and the other one, 30% and not only 1/4 each, you would adjust the
memory accordingly Cluster memory allocation is subject to fine tuning over
time.

--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike (AT) NOSPAMepprecht (DOT) net

Specialist SQL Server Solutions and Consulting

"Benny Koh" <anonymous (AT) discussions (DOT) microsoft.com> wrote

be allowed to grab whatever memory they needed until the OS decides enough
is enough. So I don't see why for a server with 8GB RAM, each of these 2
active instances cannot get at least, say 3GB each? TIA for your explanation
8-)

Oh I see..
I guess the most important thing to note is to limit the memory usage of each instance to an acceptable level and does not have to be exactly 1/4 of the server resources.
Thanks for your explanation.

----- Mike Epprecht [SQL Server MVP] wrote: -----

Hi

When running multiple instances, it is best to fix the memory size per SQL
instance. Otherwise the instances will be continuously borrowing from each
other, and starving each other. If you use the /3GB switch, must use a
fixed memory size. (see further information KB Article #274750 "HOW TO:
Configure Memory for More Than 2 GB in SQL Server").

If you have 2 node Active/Active, with 2 instances, you need to make sure
that if one node has to take the whole load, there is enough memory to go
around. So for all the instances to run, each one must be limited to a 1/4
of the memory on the server.
It does not help to have 2 using 90% of the memory and the other 2 failed
instances sharing 10%. Those 2 instances then might as well not be running.

1/4 is for illustration purposes only, but is you have a one instance that
needs 70% and the other one, 30% and not only 1/4 each, you would adjust the
memory accordingly Cluster memory allocation is subject to fine tuning over
time.

--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike (AT) NOSPAMepprecht (DOT) net

Specialist SQL Server Solutions and Consulting