Feedback on use of multiple instances

I am currently looking into the high-availability and
consolidations features in SQL Server 2000. MS implies
that running multiple instances of sql server on the same
server will result in a degregation of performance. I am
looking to install a solution that uses Quad 2.5 GB
processor, 4 GB ram server and possibly a cluster. I was
wonder if performance would be an issue for running 10 -
16 instances of sql on that.
Does anyone have a similar setup? If so, how many
instances do you use?
Is this something is widely used among corporations?
Thanks in advance,
SarahSarah,
It's not that simple. If you are talking about consolidating 10 to 16
individual servers into 1 quad processor server then that may be a bit much.
Not because SQL Server can't handle 16 instances but it may simply be a
matter or resources. Of coarse this all depends on what your doing with the
16 instances. Why that many instances vs just a few? You don't need a
separate instance for each db or application. What kind of apps / db's are
you planning on having in this server. You can't take 10 servers that had
anywhere from 10 - say 20 processors, each with say 1GB of ram and simply
through them all onto 1 quad proc server with 4GB and expect to have similar
performance. Unless of coarse they were all well under utilized to begin
with. The bottom line is that there is nothing inherently wrong with
upsizing and consolidating but you need to make sure you have all your ducks
in a row before deciding what type os server to use and how to allocate the
resources.
--
Andrew J. Kelly
SQL Server MVP
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:0c3401c3a476$d0af6e40$a401280a@.phx.gbl...
> I am currently looking into the high-availability and
> consolidations features in SQL Server 2000. MS implies
> that running multiple instances of sql server on the same
> server will result in a degregation of performance. I am
> looking to install a solution that uses Quad 2.5 GB
> processor, 4 GB ram server and possibly a cluster. I was
> wonder if performance would be an issue for running 10 -
> 16 instances of sql on that.
> Does anyone have a similar setup? If so, how many
> instances do you use?
> Is this something is widely used among corporations?
> Thanks in advance,
> Sarah|||Thank you for your response.
So then, what are the peformance differences if say you
had 10 databases on one instances of sql on a single
machine verses 10 instances of sql w/ one database each.
What I am looking to do is basically keep the
databases/application isolated from one another from a
performance and security standpoint. All of these are for
the most part low usage.
The current directive is to place all or most db's on the
same server & same instance. I'm trying to decide if
going w/ separate instances would be a better option.
Thanks again,
Sarah
>--Original Message--
>Sarah,
>It's not that simple. If you are talking about
consolidating 10 to 16
>individual servers into 1 quad processor server then that
may be a bit much.
>Not because SQL Server can't handle 16 instances but it
may simply be a
>matter or resources. Of coarse this all depends on what
your doing with the
>16 instances. Why that many instances vs just a few?
You don't need a
>separate instance for each db or application. What kind
of apps / db's are
>you planning on having in this server. You can't take 10
servers that had
>anywhere from 10 - say 20 processors, each with say 1GB
of ram and simply
>through them all onto 1 quad proc server with 4GB and
expect to have similar
>performance. Unless of coarse they were all well under
utilized to begin
>with. The bottom line is that there is nothing
inherently wrong with
>upsizing and consolidating but you need to make sure you
have all your ducks
>in a row before deciding what type os server to use and
how to allocate the
>resources.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c3401c3a476$d0af6e40$a401280a@.phx.gbl...
>> I am currently looking into the high-availability and
>> consolidations features in SQL Server 2000. MS implies
>> that running multiple instances of sql server on the
same
>> server will result in a degregation of performance. I
am
>> looking to install a solution that uses Quad 2.5 GB
>> processor, 4 GB ram server and possibly a cluster. I
was
>> wonder if performance would be an issue for running 10 -
>> 16 instances of sql on that.
>> Does anyone have a similar setup? If so, how many
>> instances do you use?
>> Is this something is widely used among corporations?
>> Thanks in advance,
>> Sarah
>
>.
>|||"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:01e301c3a488$72b6b570$a101280a@.phx.gbl...
> Thank you for your response.
> So then, what are the peformance differences if say you
> had 10 databases on one instances of sql on a single
> machine verses 10 instances of sql w/ one database each.
> What I am looking to do is basically keep the
> databases/application isolated from one another from a
> performance and security standpoint. All of these are for
> the most part low usage.
Performance will most likely be worse. Each instance has its own overhead
in processor time and memory usage. So if each database is using only 1% of
the available processor time and memory in a single instance, you would
probably see zero change in performance if you ran 10 instances each with
its own database. But if each database were using 10% of the available
resources in one instance, splitting these into 10 instances your available
resources will no longer be the same and each database's chunk of available
memory and/or processor time will be lower, thereby resulting in worse
performance.
Plus there's the management issue. Managing 10 instances is going to be
quite a bit more work than managing a single instance. From a security
standpoint, databases can be isolated very easily by simply managing logins
to the server. What benefit are you hoping to get, security-wise, from
multiple instances?|||if all the db's are single company internal users, i would
try to run as many db apps within a single instance as
practical
i would recommend multi-instance for ISP environments
where each instance belongs to a completely different
company/customer and security/isolation is required.
>--Original Message--
>Sarah,
>It's not that simple. If you are talking about
consolidating 10 to 16
>individual servers into 1 quad processor server then that
may be a bit much.
>Not because SQL Server can't handle 16 instances but it
may simply be a
>matter or resources. Of coarse this all depends on what
your doing with the
>16 instances. Why that many instances vs just a few?
You don't need a
>separate instance for each db or application. What kind
of apps / db's are
>you planning on having in this server. You can't take 10
servers that had
>anywhere from 10 - say 20 processors, each with say 1GB
of ram and simply
>through them all onto 1 quad proc server with 4GB and
expect to have similar
>performance. Unless of coarse they were all well under
utilized to begin
>with. The bottom line is that there is nothing
inherently wrong with
>upsizing and consolidating but you need to make sure you
have all your ducks
>in a row before deciding what type os server to use and
how to allocate the
>resources.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c3401c3a476$d0af6e40$a401280a@.phx.gbl...
>> I am currently looking into the high-availability and
>> consolidations features in SQL Server 2000. MS implies
>> that running multiple instances of sql server on the
same
>> server will result in a degregation of performance. I
am
>> looking to install a solution that uses Quad 2.5 GB
>> processor, 4 GB ram server and possibly a cluster. I
was
>> wonder if performance would be an issue for running 10 -
>> 16 instances of sql on that.
>> Does anyone have a similar setup? If so, how many
>> instances do you use?
>> Is this something is widely used among corporations?
>> Thanks in advance,
>> Sarah
>
>.
>|||Sarah,
I pretty much agree with Adam. With proper security you can still isolate
the db's from each others users without going to the trouble of putting them
in their own instance. Again as Adam points out performance would most
likely be better for those reasons and sql server will not have to
dynamically adjust memory between the 10 instances. From the little info we
know of your situation it does not sound like it is worth the trouble of
having a separate instance for each db.
--
Andrew J. Kelly
SQL Server MVP
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:u2HcEnIpDHA.2776@.tk2msftngp13.phx.gbl...
> "Sarah" <anonymous@.discussions.microsoft.com> wrote in message
> news:01e301c3a488$72b6b570$a101280a@.phx.gbl...
> > Thank you for your response.
> > So then, what are the peformance differences if say you
> > had 10 databases on one instances of sql on a single
> > machine verses 10 instances of sql w/ one database each.
> > What I am looking to do is basically keep the
> > databases/application isolated from one another from a
> > performance and security standpoint. All of these are for
> > the most part low usage.
> Performance will most likely be worse. Each instance has its own
overhead
> in processor time and memory usage. So if each database is using only 1%
of
> the available processor time and memory in a single instance, you would
> probably see zero change in performance if you ran 10 instances each with
> its own database. But if each database were using 10% of the available
> resources in one instance, splitting these into 10 instances your
available
> resources will no longer be the same and each database's chunk of
available
> memory and/or processor time will be lower, thereby resulting in worse
> performance.
> Plus there's the management issue. Managing 10 instances is going to be
> quite a bit more work than managing a single instance. From a security
> standpoint, databases can be isolated very easily by simply managing
logins
> to the server. What benefit are you hoping to get, security-wise, from
> multiple instances?
>|||Here's where I'm coming from...
I am used to envrinoment where every application got it's
own sql db on it's own sql server. Now I am in an
environment where they all have to be placed on the same
server. How can I guarantee up time to my users when
mutliple applications have access to the same sql server.
If I have to stop one, they all get stopped... serivce
packs/ upgrades, errors, runaway transactions... etc.
I was thinking that by using a resource manager and
separate instances, I could limit a lot of the interaction.
But then I was unsure if other people actually did that
and if it would work properly.
As you can tell... I'm not really a dba, just a windows
admin that has to work w/ sql server...
>--Original Message--
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01e301c3a488$72b6b570$a101280a@.phx.gbl...
>> Thank you for your response.
>> So then, what are the peformance differences if say you
>> had 10 databases on one instances of sql on a single
>> machine verses 10 instances of sql w/ one database each.
>> What I am looking to do is basically keep the
>> databases/application isolated from one another from a
>> performance and security standpoint. All of these are
for
>> the most part low usage.
> Performance will most likely be worse. Each instance
has its own overhead
>in processor time and memory usage. So if each database
is using only 1% of
>the available processor time and memory in a single
instance, you would
>probably see zero change in performance if you ran 10
instances each with
>its own database. But if each database were using 10% of
the available
>resources in one instance, splitting these into 10
instances your available
>resources will no longer be the same and each database's
chunk of available
>memory and/or processor time will be lower, thereby
resulting in worse
>performance.
> Plus there's the management issue. Managing 10
instances is going to be
>quite a bit more work than managing a single instance.
From a security
>standpoint, databases can be isolated very easily by
simply managing logins
>to the server. What benefit are you hoping to get,
security-wise, from
>multiple instances?
>
>.
>|||"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:02fc01c3a494$656e5330$a101280a@.phx.gbl...
> Here's where I'm coming from...
> I am used to envrinoment where every application got it's
> own sql db on it's own sql server. Now I am in an
> environment where they all have to be placed on the same
> server. How can I guarantee up time to my users when
> mutliple applications have access to the same sql server.
> If I have to stop one, they all get stopped... serivce
> packs/ upgrades, errors, runaway transactions... etc.
> I was thinking that by using a resource manager and
> separate instances, I could limit a lot of the interaction.
> But then I was unsure if other people actually did that
> and if it would work properly.
> As you can tell... I'm not really a dba, just a windows
> admin that has to work w/ sql server...
You shouldn't have to stop and restart the server very often (almost
never, unless something is very wrong). Just like normal Windows
administration, you'll install service packs and upgrades during planned
downtime or off hours... And now that you're administrating SQL Server I'd
like to welcome you to the world of being a DBA :)sql