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

From: Per Williamson (per.williamson_at_bredband.net)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 14:50:42 +0100

Thanks Wayne!

I'm aware of the need of replacing SA with a more anonymus user given the
sysadmin role, but I still think the users of one system may feel
uncomfortable having the "wrong" sysadmin user in "their" environment.

I agree with your view on the psychological emotions of users. Maybe I
should have written "political" instead, wich, in reality, one from time to
time has to take into consideration.

Regards
Per Williamson

"Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> skrev i meddelandet
news:unoGjpSCFHA.2600@TK2MSFTNGP09.phx.gbl...
> 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: 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: Running multiple instances in production environment. Good or bad?
    ... Wayne Snyder, MCDBA, SQL Server MVP ... > 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: 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: Are embedded views (Views within views...) evil and if so why?
    ... The majority of my problem is the maintanence of nested views in the ... not everyone is so careful in there use. ... SQL Server MVP ... posting this thread is to learn more about the technical reasons - ie. ...
    (comp.databases.ms-sqlserver)