multiple databases/single instance vs multiple instances

Hello All,
Can someone please explain the con and pro of using single SQL Server
instance to store multiple databases vs using multiple SQL Server instances
that store 1 database per instance? Both choices are on a single server
machine. Any significant performance difference?
Thanks in Advance,
Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
difference.
Here are a few to consider:
If you want to have granular control over resource allocation (memory,
CPU,...) multi-instance is pretty much what you have to do. This is true for
2000 and 2005. For example, if you want to limit the amount of memory a
database can consume, you can only do it at the instance level.
Security isolation is another reason to consider multi-instance vs.
single-instance/multi-db. If your users care a lot about not having anyone
even know the names of their DB objects (meta data in general), then you're
again looking at multi-instance. If you're on 2005, you're ok because of the
security improvements (meta data security).
If you're on the 32-bit platform and your apps have high demands on virtual
memory, you may need to go multi-instance. Each 32-bit instance can address
no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
stacks, etc...) depending on which edition you're on. If you're running
32-bit SQL Server on an x64 server, you get up to 4GB but that's the
ceiling. Anything above that (AWE memory) is good for data caching not the
other stuff. So if you are facing memory pressure in the VM space, multi
instance gives each instance it's own 2, 3 or 4GB. This is true for both
2000 and 2005. If you're on 64-bit then it doesn't matter.
There's also the sharing of model, tempdb and master. In some cases, the
sharing is good (eg. only 1 master to backup) while others might not (eg.
shared tempdb could result in contention).
hth.
Good luck.
joe.
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> Hello All,
> Can someone please explain the con and pro of using single SQL Server
> instance to store multiple databases vs using multiple SQL Server
> instances
> that store 1 database per instance? Both choices are on a single server
> machine. Any significant performance difference?
> Thanks in Advance,