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,|||Hi,
I am in no way knowledgeable of SQL Server accept at the basic level,
however the question I need to ask may be relevant to the one posed and
answered here.
I have SQL Server 2005 with two large application databases, HP Openview
Service Desk and Centennial Discovery 2005. My question is, does anyone see a
problem with running both these databases on one server? I am being told that
it could be by the support people who provided us with Discovery 2005 as it
has been having "issues", although they cannot define the cause.
Cheers
Matt
"Joe Yong" wrote:
> 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,
>
>|||SQL Server 2000 even not 2005.
"Intridium" wrote:
> Hi,
> I am in no way knowledgeable of SQL Server accept at the basic level,
> however the question I need to ask may be relevant to the one posed and
> answered here.
> I have SQL Server 2005 with two large application databases, HP Openview
> Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> problem with running both these databases on one server? I am being told that
> it could be by the support people who provided us with Discovery 2005 as it
> has been having "issues", although they cannot define the cause.
> Cheers
> Matt
> "Joe Yong" wrote:
> > 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,
> >
> >
> >|||As Matt,
It should not be a problem running multiple databases in a single instance.
What kind of issues do you meet? If it is performance-related, running the
perf monitor on SQL Server counters would reveal the possible bottleneck.
Good luck,
Franky
"Intridium" wrote:
> Hi,
> I am in no way knowledgeable of SQL Server accept at the basic level,
> however the question I need to ask may be relevant to the one posed and
> answered here.
> I have SQL Server 2005 with two large application databases, HP Openview
> Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> problem with running both these databases on one server? I am being told that
> it could be by the support people who provided us with Discovery 2005 as it
> has been having "issues", although they cannot define the cause.
> Cheers
> Matt
> "Joe Yong" wrote:
> > 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,
> >
> >
> >|||Hi Franky,
Well, prior to the problems occuring, performance was fine. When the problem
occured, the agent exectuable for the discovery service was maxing one of the
CPU threads to 100% and was doing little in the way of processing any
information (it was but very very very very slowly), also items in the
database were not showing in the front end application and device counts were
a mess, this did not affect HP OV SD however. We tried to use SQL Profiler to
isolate the problem, but had no idea what to look for, after a database
restore the problem seemed to go away, but then came back, i have since
reinstalled completely the discovery software and created a new database, but
i dont want this to happen again, our support for the product in question
suggested it was because both Discovery 2005 and HP OV SD are heavy users
(though our performance indicators show this not to be an issue, as since the
problem has gone we dont have Sql_server.exe using a constant 25%-56% cpu
time, its now around 1-12% depending on usage of service desk.
I am trying to basically answer their question can sql handle them both?
"Franky" wrote:
> As Matt,
> It should not be a problem running multiple databases in a single instance.
> What kind of issues do you meet? If it is performance-related, running the
> perf monitor on SQL Server counters would reveal the possible bottleneck.
> Good luck,
> Franky
> "Intridium" wrote:
> > Hi,
> >
> > I am in no way knowledgeable of SQL Server accept at the basic level,
> > however the question I need to ask may be relevant to the one posed and
> > answered here.
> >
> > I have SQL Server 2005 with two large application databases, HP Openview
> > Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> > problem with running both these databases on one server? I am being told that
> > it could be by the support people who provided us with Discovery 2005 as it
> > has been having "issues", although they cannot define the cause.
> >
> > Cheers
> >
> > Matt
> >
> > "Joe Yong" wrote:
> >
> > > 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,
> > >
> > >
> > >|||Hi, Intridium,
Basically, sql server can host mutiple databases without problem, I have
managed server is heavily used and get 50 databases in one instance. it
works fine.the problem you met is in the application,
there are several way to cause the probem.
query large set of data, scan the table.
query large set of in a transaction. that would block the other process to
get resource.
may be run a query in a loop.
It is just 2c, I hope it is helpful.
Yifei
"Intridium" <Intridium@.discussions.microsoft.com> wrote in message
news:80FB8054-3EDE-4D4E-832A-C5AB5BA60108@.microsoft.com...
> Hi Franky,
> Well, prior to the problems occuring, performance was fine. When the
problem
> occured, the agent exectuable for the discovery service was maxing one of
the
> CPU threads to 100% and was doing little in the way of processing any
> information (it was but very very very very slowly), also items in the
> database were not showing in the front end application and device counts
were
> a mess, this did not affect HP OV SD however. We tried to use SQL Profiler
to
> isolate the problem, but had no idea what to look for, after a database
> restore the problem seemed to go away, but then came back, i have since
> reinstalled completely the discovery software and created a new database,
but
> i dont want this to happen again, our support for the product in question
> suggested it was because both Discovery 2005 and HP OV SD are heavy users
> (though our performance indicators show this not to be an issue, as since
the
> problem has gone we dont have Sql_server.exe using a constant 25%-56% cpu
> time, its now around 1-12% depending on usage of service desk.
> I am trying to basically answer their question can sql handle them both?
>
> "Franky" wrote:
> > As Matt,
> >
> > It should not be a problem running multiple databases in a single
instance.
> > What kind of issues do you meet? If it is performance-related, running
the
> > perf monitor on SQL Server counters would reveal the possible
bottleneck.
> >
> > Good luck,
> >
> > Franky
> >
> > "Intridium" wrote:
> >
> > > Hi,
> > >
> > > I am in no way knowledgeable of SQL Server accept at the basic level,
> > > however the question I need to ask may be relevant to the one posed
and
> > > answered here.
> > >
> > > I have SQL Server 2005 with two large application databases, HP
Openview
> > > Service Desk and Centennial Discovery 2005. My question is, does
anyone see a
> > > problem with running both these databases on one server? I am being
told that
> > > it could be by the support people who provided us with Discovery 2005
as it
> > > has been having "issues", although they cannot define the cause.
> > >
> > > Cheers
> > >
> > > Matt
> > >
> > > "Joe Yong" wrote:
> > >
> > > > 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,
> > > >
> > > >
> > > >|||Hi there,
in theory (and often in practice too!) you can have multiple databases
on a single SQL Server.
The problem you are seeing here is down to the fact that the Centennial
server (the box with the Centennial Server Agent) formulates it's
requests and then passes them into the Discovery database via stored
procedures.
The Centennial stored procedures do almost all of the work and this can
cause 100% CPU utilisation.
As it happens, Centennial isn't doing anything wrong. 5 years ago we
were in the client-server world where a client would retrieve data from
the database (SQL, Oracle, DB2, etc) do lots of processing and then
write the results back.
In this brave new world, though, we expect to pass simple transactions
"add this software to this asset" to the database and have it perform
all of the calculations and updates required to write this to the
database.
The problem you are experiencing is that you presumably have a large
number of "traditional" client/server type apps writing to databases on
your server and this isn't compatible with the heavy stored procedure /
CPU utilisation that Centennial exhibits.
Centennials recommendation (and one that I support) is to have a
dedicated database server for their application.
As a workaround, ensure that your Centennial audits are scheduled
during a quiet period (e.g. 1900-0500 overnight, or over the weekend)
as this is where the bulk of the stored procedure calls are made.
Cheers,
Skot|||Hi,
My question is I'm trying to install multiple instances but I can't uncheck
the default selection because it's grayed out. I've selected the upgrade
selection in the previous window but it will not allow me to change the name.
Does anyone know how I can get pass this issue to install another instance
of SQL? My goal is to install Project Server 2003 which uses SQL.
I'm running SQL Server 2000 SP4 on a Windows 2003 server.
Any suggestions would be helpful.
Thanks,
Lewis
"scott.leckie@.btclick.com" wrote:
> Hi there,
> in theory (and often in practice too!) you can have multiple databases
> on a single SQL Server.
> The problem you are seeing here is down to the fact that the Centennial
> server (the box with the Centennial Server Agent) formulates it's
> requests and then passes them into the Discovery database via stored
> procedures.
> The Centennial stored procedures do almost all of the work and this can
> cause 100% CPU utilisation.
> As it happens, Centennial isn't doing anything wrong. 5 years ago we
> were in the client-server world where a client would retrieve data from
> the database (SQL, Oracle, DB2, etc) do lots of processing and then
> write the results back.
> In this brave new world, though, we expect to pass simple transactions
> "add this software to this asset" to the database and have it perform
> all of the calculations and updates required to write this to the
> database.
> The problem you are experiencing is that you presumably have a large
> number of "traditional" client/server type apps writing to databases on
> your server and this isn't compatible with the heavy stored procedure /
> CPU utilisation that Centennial exhibits.
> Centennials recommendation (and one that I support) is to have a
> dedicated database server for their application.
> As a workaround, ensure that your Centennial audits are scheduled
> during a quiet period (e.g. 1900-0500 overnight, or over the weekend)
> as this is where the bulk of the stored procedure calls are made.
> Cheers,
> Skot
>