Multiple instances

To all:
What would be the benefit(s) of having multiple instances of Sql Server on a
Server?
Thanks,
TunjiThe multiple instancing capability of SQL Server can effectively help you
"consolidate" your servers onto one box. From an access and features point
of view, having multiple instances is the same as having many SQL Servers
across multiple machines (basically you do not get any shortcuts because of
hosting multiple instances). Ideally, you would go for multiple instances
only if you need to consolidate your environment (say testing and
production) and you have a powerful machine to handle it.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Tunji" <Tunj@.hotmail.com> wrote in message
news:u6Zn3rRBEHA.580@.TK2MSFTNGP11.phx.gbl...
> To all:
> What would be the benefit(s) of having multiple instances of Sql Server on
a
> Server?
> Thanks,
> Tunji
>|||I would generally NOT use multiple instances in a production environment,
because each instance would need to be assigned a portion of memory... Even
if no one was using one of the instances, the memory for that instance would
not be available to other instances...
however where instance ARE useful in production is when they facilitate
Clustering.
Instances are very useful in development and testing environments. You can
have a SQL instance with NO service packs, and another with SP1, and
another with SP2, etc... This would allow you to do testing (easily) against
any Service Pack without having to buy extra machines... nice nice nice
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Tunji" <Tunj@.hotmail.com> wrote in message
news:u6Zn3rRBEHA.580@.TK2MSFTNGP11.phx.gbl...
> To all:
> What would be the benefit(s) of having multiple instances of Sql Server on
a
> Server?
> Thanks,
> Tunji
>|||Tunji
Another thing to bear in mind is the difference between have multiple databa
ses on one instance (the only way you could do it before 2000) and multiple
instances with one user database each. Each SQL Server instance has it's own
resources. This means that
in the old days (Version 7 and before) if you had a serious problem on one d
atabase on a server running more than one database, it would affect all the
databases. With SQL Server 2000 instances it will generally only affect the
instance it is running on.
This makes it a more viable option for consolidation. Though obviously you n
eed a machine powerful enough to run the number of instances you require and
you need a licence for each instance.
Regards
John|||if you need multiple versions of SQL Server running for production or
testing (SQL 2000 and SQL 7.0 for example), but cannot afford multiple
physical machines then this would be a way to go.
Greg Jackson
PDX, Oregon|||I agree with Wayne. Stick with one instance until you have a *real need* to
go for several instances. When you think you have such a need, verify that
need (here, for instance) before you go the multi-instance route.
One more thing to consider, apart from resource usage, is maintenance. Each
instance comes with maintenance of system databases etc. I find it easier to
maintain one instance and several databases compared to several instances.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23Zd5r4RBEHA.1028@.TK2MSFTNGP11.phx.gbl...
> I would generally NOT use multiple instances in a production environment,
> because each instance would need to be assigned a portion of memory...
Even
> if no one was using one of the instances, the memory for that instance
would
> not be available to other instances...
> however where instance ARE useful in production is when they facilitate
> Clustering.
> Instances are very useful in development and testing environments. You can
> have a SQL instance with NO service packs, and another with SP1, and
> another with SP2, etc... This would allow you to do testing (easily)
against
> any Service Pack without having to buy extra machines... nice nice nice
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> (Please respond only to the newsgroups.)
> I support the Professional Association for SQL Server
> (www.sqlpass.org)
> "Tunji" <Tunj@.hotmail.com> wrote in message
> news:u6Zn3rRBEHA.580@.TK2MSFTNGP11.phx.gbl...
on
> a
>|||Thanks a lot for the knowledge sharing.
I have definitely learned a lot more than i thought and i really appreciate
it.
Tunji
"Tunji" <Tunj@.hotmail.com> wrote in message
news:u6Zn3rRBEHA.580@.TK2MSFTNGP11.phx.gbl...
> To all:
> What would be the benefit(s) of having multiple instances of Sql Server on
a
> Server?
> Thanks,
> Tunji
>|||Hi,
You can have different level of SP's but they all share the same MDAC as far
as i know. (Which in this case the latest one you applied.)
One advantege for multiple instance is you can spread your resources to inst
ances using SQL settings (processor affinity etc.) or using Windows Resource
Manager. (WRM usage on SQL Server is still an open question ..)