Re: Running multiple instances in production environment. Good or bad?

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 08:23:07 -0500

See Inline.... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Per Williamson" <per.williamsson@bredband.net> wrote in message
news:22459782.0502020433.3898081c@posting.google.com...
> Hi there,
>
> Hoping to get a answers from people with "real life" experience of
> running multiple Sql server instances!
>
> I have two different application using two different sets of
> application databases, and for licensing reasons I´m going to let them
> use the same physical database server. Both of them, I should mention,
> require fairly low performance (5-10 concurrent users, resp. 1-2
> concurrent users). My server has two 2.6 Ghz processors and 2 Gb
> memory.
>
> As I have a Sql Server Standard Edition, "Per Processor" license, I am
> now considering to have two different Sql Server instances, serving
> one application each. Some of advantages, as I see them are:
>
> 1) The "owners" of the different applications feel they have their
> "own" server (may be of psychological importance)
They can see a phsychologist for those problems. I don't need to be snippy,
but we should deal with technical problems, and myabe leave their emotional
issues to themselves ( although I understand the kind of thing you are
dealing with..) They should be concerned that they will get good service,
how you provide that serivice - who cares..
> 2) Separates the SA roles, and improves security.
You, and they should NOT, absolutely NOT be using SA for anything... If you
need admin access create a login in SQL for integrated security ,and give
that login sysadmin role membership... That way you can have granular
control, while still being able to differentiate the various users...
> 3) One of the application is new to the organization and will need a
> "running-in" period. Makes it possible to avoid disturbance in case of
> needing to restart Sql Server.
It is very very unlikely there will be a need to re-boot SQL server, that is
mostly a thing of the past...
>
> I have read some of the postings in this forum, and most of them seem
> to be negative to running multiple instances in a production
> environment, mainly due to the fact they will compete for system
> resources.
>
> I now wonder if there are people out there with real experience of
> running multiple instances. In theory, performance can suffer hard,
> but in practice? Is it stable?
>
There are several reasons why most of us would TRY very hard NOT to have
multiple instances on the same server.
    Wasteful of memory, multiple instances competing for the same memory
    Increased maintenance, having to upgrade (Service pack) mulitiple
instances - what a pain...
    you'll have to duplicate many things you do , like add new users - you
may have to add a new user to multiple servers... also an avoidable pain.
There are several reasons why you might WANT to use multiple instances,
     I guess biggest reason is that (for some reason) you expect one group
to need to upgrade to a new version of SQL, while the other group can NOT do
the upgrade ( maybe due to software incompatibility - who knows..)
    You might have multiple instances running as the result of a failover in
an active/active configuration.
    you wish to limit resources, or allocate resources by instance... One
instance gets tons of memory, the other gets little...
But mostly, you won't find much support for he idea of having multiple
production instances on the same server...
Hope this helps...
> Thanks in advance
>
> Per Williamson

Relevant Pages

  • Re: Running multiple instances in production environment. Good or bad?
    ... > Wayne Snyder, MCDBA, SQL Server MVP ... > that login sysadmin role membership... ... >> to be negative to running multiple instances in a production ... > There are several reasons why most of us would TRY very hard NOT to have ...
    (microsoft.public.sqlserver.server)
  • Re: Additional instances and licensing...
    ... "A Microsoft SQL Server 2005 Enterprise Edition license entitles you to install multiple instances of SQL Server 2005 Analysis Services on a single computer. ...
    (microsoft.public.sqlserver.setup)
  • Re: 3 queries about active/active and multi-instance
    ... To run multiple instances of SQL Server in the same machine. ... machines are servicing the same database. ... Multiple instance cluster is more ...
    (microsoft.public.sqlserver.clustering)
  • Re: Multiple instances
    ... I would generally NOT use multiple instances in a production environment, ... have a SQL instance with NO service packs, and another with SP1, and ... I support the Professional Association for SQL Server ...
    (microsoft.public.sqlserver.server)
  • RE: Running multiple instances in production environment. Good or bad?
    ... We run multiple instances on single servers. ... dominate another one (Max Server Memory setting). ... > now considering to have two different Sql Server instances, ... > to be negative to running multiple instances in a production ...
    (microsoft.public.sqlserver.server)
Loading