multiple instances of sql - 2 Gb memory limitation?

Hi Everyone,
I'm running a non-clustered sql environment, and I was
considering the scenario where I could install multiple
instances of sql server onto one "work-horse" server.
I'm concerned about the 2 Gb memory limit. Is this limit
per instance, or per physical machine?
E.g. one quad-processor server, with 6 Gb of memory
could easily accept 2 sql instances each with a 2Gb
memory limit.
Thanks in Advance
CraigHi,
This limitation in Memory / Cpu is instance based. So if you have 2 SQL
server instances you can allocate a maximum of 2 GB RAM for each instance.
FYI,
If your SQL Server edition is Enterprise edition it supports more than 2 GB
RAM for each instance. See books online on "Maximum Capacity specifications"
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Craig Irvine" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c43940$2592e2d0$a601280a@.phx.gbl...
> Hi Everyone,
> I'm running a non-clustered sql environment, and I was
> considering the scenario where I could install multiple
> instances of sql server onto one "work-horse" server.
> I'm concerned about the 2 Gb memory limit. Is this limit
> per instance, or per physical machine?
> E.g. one quad-processor server, with 6 Gb of memory
> could easily accept 2 sql instances each with a 2Gb
> memory limit.
> Thanks in Advance
> Craig
>|||Hi Craig,
The 2GB Memory limitation is per instance and the SQL Server can be
configured to use more memory than 2GB. Please refer to the following
article
http://support.microsoft.com/defaul...0&Product=sql2k
Excerpt from the KB Article
HOW TO: Configure memory for more than 2 GB in SQL Server
View products that this article applies to.
This article was previously published under Q274750
IN THIS TASK
SUMMARY
Support for Operating System Versions
Microsoft Windows 2000 Server
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter
or Windows Server 2003
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows NT 4.0 Enterprise Edition
REFERENCES
SUMMARY
This article describes how to configure SQL Server to use more than 2 GB of
physical memory.
Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 dynamically acquire
and free memory as needed. When you run multiple instances of SQL Server on
a computer, each instance dynamically acquires and frees memory to adjust
for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of
Microsoft Windows 2000 Address Windowing Extensions (AWE) to address
approximately 8 GB of memory for instances that run on Microsoft Windows
2000 Advanced Server, and approximately 32 GB for instances that run on
Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory
that is not in use for other applications and the operating system. Each
instance that uses this memory; however, must statically allocate the
memory it needs. SQL Server can only use this AWE allocated memory for the
data cache and not for executables, drivers, DLLs, and so forth. For
additional information, click the article number below to view the article
in the Microsoft Knowledge
Base:
283037 Large Memory Support Is Available in Windows 2000
The extended memory size option is available only for Microsoft SQL Server
7.0, Enterprise Edition on the operating system and hardware that supports
the Enterprise Memory Architecture (EMA) feature. For more information
about how to configure your system to enable the EMA feature on a
particular system configuration, refer to your Microsoft Windows NT
documentation.
Some system vendors may provide products for Microsoft Windows NT, version
4.0 or later, so that SQL Server 7.0, Enterprise Edition can use the
extended memory size option. On Intel platforms, SQL Server 7.0 can use a
feature known as PSE36. On Alpha platforms, the feature that allows use of
the extended memory option is Very Large Memory (VLM). For more information
about the availability, installation, and configuration of these products,
contact your system vendor.
Note: To use Address Windowing Extensions (AWE) memory, you must run the
SQL Server 2000 database engine under a Windows account that has been
assigned the Windows lock pages in memory administrative credentials.
back to the top
Support for Operating System Versions
Windows 2000 Server
SQL Server 2000
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of
the AWE enable option, SQL Server can use up to 4 GB of physical memory.
NOTE: You cannot allocate more than 4 GB of physical memory to an
application on Windows 2000 Server because Physical Address Extension (PAE)
is not available on Microsoft Windows 2000 Server. Also, you cannot use the
3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can
use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000
Advanced Server or Microsoft Windows Datacenter versions. For additional
information, click the article number below to view the article in the
Microsoft Knowledge Base:
291988 A Description of the 4 GB RAM Tuning Feature and the Physical
Address Extension Switch
back to the top
SQL Server 7.0
SQL Server 7.0 versions can use up to 2 GB of physical memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000.
Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server
2003
The maximum amount of physical memory addressable by a 32-bit addressing
mode is 4 GB. All processors based on the IA-32 architecture that begin
with the Intel Pentium Pro, support a new 36-bit physical addressing mode
known as Physical Address Extension (PAE). PAE allows up to 8 GB of
physical memory on Windows 2000 Advanced Server and up to 32 GB of physical
memory on Windows 2000 Datacenter Server. This is because the tested memory
limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel
requires an Intel architecture processor, Pentium Pro, or later and either
Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional
information, click the article number below to view the article in the
Microsoft
Knowledge Base:
268230 Scaling Out Versus Scaling Up with Intel Physical Addressing
Extensions (PAE)
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB
of physical RAM by using the Physical Address Extensions (PAE) feature. You
can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can
use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 4 GB of memory.
NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data
Center, make sure that the /3GB switch is not in the Boot.ini file. If the
/3GB switch is in the Boot.ini file, Windows 2000 may not be able to
address any memory above 16 GB correctly.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may
require at least 1 GB memory to manage AWE.
back to the top
Example
The following example shows how to enable AWE and configure a limit of 6 GB
for the max server memory option: sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
-or-
-or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to
use up to 3 GB of available memory.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
in order to address up to 3 GB of available memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000 Advanced Server or Windows Datacenter.
Microsoft Windows NT 4.0 Enterprise Edition
SQL Server 2000
SQL Server 2000 Enterprise and Developer Edition requires the /3GB switch
in the Boot.ini file in order to address up to 3 GB of available memory.
NOTE: Windows NT 4.0 Enterprise Edition does not support the AWE memory
architecture model so AWE support is not available.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
file in order to address up to 3 GB of available memory.
back to the top
REFERENCES
For additional information, click the article numbers below to view the
articles in the Microsoft Knowledge Base:
170756 Available Switch Options for Windows NT Boot.ini File
171793 Information on Application Use of 4GT RAM Tuning
268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
292934 Windows 2000 Datacenter Server Does Not Locate Memory Greater Than
16 GB
SQL Server 2000 Books Online; topics: "Managing AWE Memory"; "Running
Multiple Instances and Using Failover Clustering"
back to the top
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

multiple instances of sql - 2 Gb memory limitation?

Hi Everyone,
I'm running a non-clustered sql environment, and I was
considering the scenario where I could install multiple
instances of sql server onto one "work-horse" server.
I'm concerned about the 2 Gb memory limit. Is this limit
per instance, or per physical machine?
E.g. one quad-processor server, with 6 Gb of memory
could easily accept 2 sql instances each with a 2Gb
memory limit.
Thanks in Advance
Craig
Hi,
This limitation in Memory / Cpu is instance based. So if you have 2 SQL
server instances you can allocate a maximum of 2 GB RAM for each instance.
FYI,
If your SQL Server edition is Enterprise edition it supports more than 2 GB
RAM for each instance. See books online on "Maximum Capacity specifications"
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Craig Irvine" <anonymous@.discussions.microsoft.com> wrote in message
news:cd6601c43940$2592e2d0$a601280a@.phx.gbl...
> Hi Everyone,
> I'm running a non-clustered sql environment, and I was
> considering the scenario where I could install multiple
> instances of sql server onto one "work-horse" server.
> I'm concerned about the 2 Gb memory limit. Is this limit
> per instance, or per physical machine?
> E.g. one quad-processor server, with 6 Gb of memory
> could easily accept 2 sql instances each with a 2Gb
> memory limit.
> Thanks in Advance
> Craig
>
|||Hi Craig,
The 2GB Memory limitation is per instance and the SQL Server can be
configured to use more memory than 2GB. Please refer to the following
article
http://support.microsoft.com/default...&Product=sql2k
Excerpt from the KB Article
HOW TO: Configure memory for more than 2 GB in SQL Server
View products that this article applies to.
This article was previously published under Q274750
IN THIS TASK
SUMMARY
Support for Operating System Versions
Microsoft Windows 2000 Server
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows 2000 Advanced Server or Microsoft Windows 2000 Datacenter
or Windows Server 2003
Microsoft SQL Server 2000
Microsoft SQL Server 7.0
Microsoft Windows NT 4.0 Enterprise Edition
REFERENCES
SUMMARY
This article describes how to configure SQL Server to use more than 2 GB of
physical memory.
Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 dynamically acquire
and free memory as needed. When you run multiple instances of SQL Server on
a computer, each instance dynamically acquires and frees memory to adjust
for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of
Microsoft Windows 2000 Address Windowing Extensions (AWE) to address
approximately 8 GB of memory for instances that run on Microsoft Windows
2000 Advanced Server, and approximately 32 GB for instances that run on
Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory
that is not in use for other applications and the operating system. Each
instance that uses this memory; however, must statically allocate the
memory it needs. SQL Server can only use this AWE allocated memory for the
data cache and not for executables, drivers, DLLs, and so forth. For
additional information, click the article number below to view the article
in the Microsoft Knowledge
Base:
283037 Large Memory Support Is Available in Windows 2000
The extended memory size option is available only for Microsoft SQL Server
7.0, Enterprise Edition on the operating system and hardware that supports
the Enterprise Memory Architecture (EMA) feature. For more information
about how to configure your system to enable the EMA feature on a
particular system configuration, refer to your Microsoft Windows NT
documentation.
Some system vendors may provide products for Microsoft Windows NT, version
4.0 or later, so that SQL Server 7.0, Enterprise Edition can use the
extended memory size option. On Intel platforms, SQL Server 7.0 can use a
feature known as PSE36. On Alpha platforms, the feature that allows use of
the extended memory option is Very Large Memory (VLM). For more information
about the availability, installation, and configuration of these products,
contact your system vendor.
Note: To use Address Windowing Extensions (AWE) memory, you must run the
SQL Server 2000 database engine under a Windows account that has been
assigned the Windows lock pages in memory administrative credentials.
back to the top
Support for Operating System Versions
Windows 2000 Server
SQL Server 2000
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000
Developer Edition can use up to 2 GB of physical memory. With the use of
the AWE enable option, SQL Server can use up to 4 GB of physical memory.
NOTE: You cannot allocate more than 4 GB of physical memory to an
application on Windows 2000 Server because Physical Address Extension (PAE)
is not available on Microsoft Windows 2000 Server. Also, you cannot use the
3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can
use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000
Advanced Server or Microsoft Windows Datacenter versions. For additional
information, click the article number below to view the article in the
Microsoft Knowledge Base:
291988 A Description of the 4 GB RAM Tuning Feature and the Physical
Address Extension Switch
back to the top
SQL Server 7.0
SQL Server 7.0 versions can use up to 2 GB of physical memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000.
Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server
2003
The maximum amount of physical memory addressable by a 32-bit addressing
mode is 4 GB. All processors based on the IA-32 architecture that begin
with the Intel Pentium Pro, support a new 36-bit physical addressing mode
known as Physical Address Extension (PAE). PAE allows up to 8 GB of
physical memory on Windows 2000 Advanced Server and up to 32 GB of physical
memory on Windows 2000 Datacenter Server. This is because the tested memory
limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel
requires an Intel architecture processor, Pentium Pro, or later and either
Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional
information, click the article number below to view the article in the
Microsoft
Knowledge Base:
268230 Scaling Out Versus Scaling Up with Intel Physical Addressing
Extensions (PAE)
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB
of physical RAM by using the Physical Address Extensions (PAE) feature. You
can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can
use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 4 GB of memory.
NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data
Center, make sure that the /3GB switch is not in the Boot.ini file. If the
/3GB switch is in the Boot.ini file, Windows 2000 may not be able to
address any memory above 16 GB correctly.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may
require at least 1 GB memory to manage AWE.
back to the top
Example
The following example shows how to enable AWE and configure a limit of 6 GB
for the max server memory option: sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
-or-
-or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to
use up to 3 GB of available memory.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
in order to address up to 3 GB of available memory.
NOTE: The extended memory option in SQL Server 7.0 is not available on
Windows 2000 Advanced Server or Windows Datacenter.
Microsoft Windows NT 4.0 Enterprise Edition
SQL Server 2000
SQL Server 2000 Enterprise and Developer Edition requires the /3GB switch
in the Boot.ini file in order to address up to 3 GB of available memory.
NOTE: Windows NT 4.0 Enterprise Edition does not support the AWE memory
architecture model so AWE support is not available.
back to the top
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini
file in order to address up to 3 GB of available memory.
back to the top
REFERENCES
For additional information, click the article numbers below to view the
articles in the Microsoft Knowledge Base:
170756 Available Switch Options for Windows NT Boot.ini File
171793 Information on Application Use of 4GT RAM Tuning
268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
292934 Windows 2000 Datacenter Server Does Not Locate Memory Greater Than
16 GB
SQL Server 2000 Books Online; topics: "Managing AWE Memory"; "Running
Multiple Instances and Using Failover Clustering"
back to the top
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

multiple instances of MSDE

Hello All,
Hoping someone can tell me how the 5-user governor affects multiple
instances of MSDE.
Does each instance allow unrestricted performance for up to 5 users, or
will the 6th user experience slow-down regardless of which instance db
he/she is using?
Also, with a VS.NET license, does this provide license only one instance of
MSDE, or can you install multiple?
Rein
Hi Rein,
Workload Governor in brief :
Like all versions of SQL Server 2000, SQL Server 2000 Personal Edition and
MSDE 2000 allow 32,767 connections to an instance of the database engine.
There is no limit for the number of connections that can be executing
operations at the same time. The only effect of the workload governor is
that it starts slowing down the database engine when more than eight
operations are actively running at the same time.
The following link has more information on Workload Governor.
http://msdn.microsoft.com/library/?u.../8_ar_sa2_0ciq
.asp
You can install multiple instance of MSDE, regardless of the number of
VS.NET license you have.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks Ashish,
One thing I didn't understand from your response was whether the governor (I
thought it was applied after the fifth concurrent user, not after the 8th
operation) affected all instances of MSDE on a particular machine. If not,
crafty as I can be, I'm inclined to install multiple (up to 16) instances
where each instance will support an 5? ungoverned users (16x5=80 ungoverned
concurrent users!!!) Of course, 16 separate and full-fledged instances will
also probably bog the machine down, but I definately have a solution when my
application slows down due to the governor, add another instance and double
the capacity - I can make up for lost performance from running 2 identical
services by distributed partitioning...
Rein

> Workload Governor in brief :
> Like all versions of SQL Server 2000, SQL Server 2000 Personal Edition and
> MSDE 2000 allow 32,767 connections to an instance of the database engine.
> There is no limit for the number of connections that can be executing
> operations at the same time. The only effect of the workload governor is
> that it starts slowing down the database engine when more than eight
> operations are actively running at the same time.
...
> You can install multiple instance of MSDE, regardless of the number of
> VS.NET license you have.
> Ashish
|||I thought it was after 5 processes running is when it starts to
slow down.
francisco
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:r9ZqpFBLEHA.3396@.cpmsftngxa10.phx.gbl...
> Hi Rein,
>
> Workload Governor in brief :
> Like all versions of SQL Server 2000, SQL Server 2000 Personal Edition and
> MSDE 2000 allow 32,767 connections to an instance of the database engine.
> There is no limit for the number of connections that can be executing
> operations at the same time. The only effect of the workload governor is
> that it starts slowing down the database engine when more than eight
> operations are actively running at the same time.
>
> The following link has more information on Workload Governor.
>
http://msdn.microsoft.com/library/?u.../8_ar_sa2_0ciq
> asp
>
> You can install multiple instance of MSDE, regardless of the number of
> VS.NET license you have.
>
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
|||hi Francisco,
"Francisco" <nomail@.baseball.net> ha scritto nel messaggio
news:uxN27hGLEHA.4052@.TK2MSFTNGP11.phx.gbl...
> I thought it was after 5 processes running is when it starts to
> slow down.
>
you are quite right, but the reported limit (8) includes some system related
workloads in order to grant a minimum of 5 concurrent users workloads..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Rein,
The Workload governor is affects individual instance of MSDE.
Extract from the link that I had sent in the earlier reply.
================================================== ==========================
==================================
The Microsoft SQL Server 2000 workload governor is designed to limit the
performance of an instance of the database engine any time more than eight
operations are active at the same time. An instance of the SQL Server 2000
database engine is one copy of the database software that operates as an
operating system service.
================================================== ==========================
==================================
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||You could also try the method detailed here:
http://groups.google.com/groups?selm... output=gplain
By changing a single bit in one of the MSDE files you can tell the SQL Server
to allow any number of concurrent operations without a penalty. This is
effectively a hidden switch to turn the workload governor off.

Multiple Instances - Properties tuning

Hope you don't mind another few questions about tuning this multiple
instances server.
When installing 2 separate instances on MS SQL server 2000 Sp3a, would
I best leave the configurationof server Memory to SQL server
(dynamically), or should I allocate manually say 300 MB for each
instance (Total MB RAM = 1 GB). The applications will not be heavily
used.
If I were to audit, would I benefit from only auditing failure, or
would you yourself choose to audit All or None ? Do I audit only when
necessary or all the time ?
The BOL say that this file will grow max to 200 MB and that I should
specify immediately during setup a new directory for these audit
files. I can't see where I should enter this new directory.
Is this the SQL server error log ? BOL suggests making a directory
like MSSQL$instance\audit. That would be in the data directory right ?
Where do I register this directory in SQL server as the audit
directory? Do I need to use profiler to set auditing on and off ?
In the Remote Server connections, would you deny remote RPC
connections to your SQL server or not? Why ?
For your first question, checkout the page titled "Multiple Instance
Recommendations" in SQL Server 2000 Books Online.
Regarding auting, I generally look for failures, in the SQL Errorlog.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:ogaie0tdj18cb194ubpp7vm971obqsva1j@.4ax.com...
Hope you don't mind another few questions about tuning this multiple
instances server.
When installing 2 separate instances on MS SQL server 2000 Sp3a, would
I best leave the configurationof server Memory to SQL server
(dynamically), or should I allocate manually say 300 MB for each
instance (Total MB RAM = 1 GB). The applications will not be heavily
used.
If I were to audit, would I benefit from only auditing failure, or
would you yourself choose to audit All or None ? Do I audit only when
necessary or all the time ?
The BOL say that this file will grow max to 200 MB and that I should
specify immediately during setup a new directory for these audit
files. I can't see where I should enter this new directory.
Is this the SQL server error log ? BOL suggests making a directory
like MSSQL$instance\audit. That would be in the data directory right ?
Where do I register this directory in SQL server as the audit
directory? Do I need to use profiler to set auditing on and off ?
In the Remote Server connections, would you deny remote RPC
connections to your SQL server or not? Why ?
|||Hi,
Why do you want to install mutiple instances of SQL Server since you have
only 1 GB of physical
memory in machine.
Better option will be create 2 databases and allocate 1 db for each
application.
If you need to install multiple instances of SQL Server always it is
recommended to fix the
memory usage for each instance of SQL Server. If you do not have any other
application other than
SQL Server in your machine then you can go for even 400 MB for each
instamce. THis memory parameter
should be changed based on usage of each instance. You can use Performance
monitor to identify the usage.
If I were to audit, would I benefit from only auditing failure, or
would you yourself choose to audit All or None ? Do I audit only when
necessary or all the time ?
I recommend you to audit only the failure, Otherwise you need to enable the
c2 Audit mode
which will audit all the activity. SO it uses more resources. During the
requirement stages
you can use Profiler to trace the activity.
Do I need to use profiler to set auditing on and off ?
No need, From Query Analyser execute
sp_configure 'c2 audit mode',1
To diable to audit
sp_configure 'c2 audit mode',0
(restart SQL Server service)
I can't see where I should enter this new directory.Is this the SQL server
error log ?
As far as i know,You have to change the default data directory
Thanks
Hari
MCDBA
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:ogaie0tdj18cb194ubpp7vm971obqsva1j@.4ax.com...
> Hope you don't mind another few questions about tuning this multiple
> instances server.
> When installing 2 separate instances on MS SQL server 2000 Sp3a, would
> I best leave the configurationof server Memory to SQL server
> (dynamically), or should I allocate manually say 300 MB for each
> instance (Total MB RAM = 1 GB). The applications will not be heavily
> used.
> If I were to audit, would I benefit from only auditing failure, or
> would you yourself choose to audit All or None ? Do I audit only when
> necessary or all the time ?
> The BOL say that this file will grow max to 200 MB and that I should
> specify immediately during setup a new directory for these audit
> files. I can't see where I should enter this new directory.
> Is this the SQL server error log ? BOL suggests making a directory
> like MSSQL$instance\audit. That would be in the data directory right ?
> Where do I register this directory in SQL server as the audit
> directory? Do I need to use profiler to set auditing on and off ?
> In the Remote Server connections, would you deny remote RPC
> connections to your SQL server or not? Why ?
>
|||Hi,
Why do you want to install mutiple instances of SQL Server since you have only 1 GB of physical
memory in machine.
Better option will be create 2 databases and allocate 1 db for each application.
If you need to install multiple instances of SQL Server always it is recommended to fix the
memory usage for each instance of SQL Server. If you do not have any other application other than
SQL Server in your machine then you can go for even 400 MB for each instamce. THis memory parameter
should be changed based on usage of each instance. You can use Performance monitor to identify the usage.
If I were to audit, would I benefit from only auditing failure, or
would you yourself choose to audit All or None ? Do I audit only when
necessary or all the time ?
I recommend you to audit only the failure, Otherwise you need to enable the c2 Audit mode
which will audit all the activity. SO it uses more resources. During the requirement stages
you can use Profiler to trace the activity.
Do I need to use profiler to set auditing on and off ?
No need, From Query Analyser execute
sp_configure 'c2 audit mode',1
To diable to audit
sp_configure 'c2 audit mode',0
(restart SQL Server service)
I can't see where I should enter this new directory.Is this the SQL server error log ?
As far as i know,You have to change the default data directory
Thanks
Hari
MCDBA
"Citizen" wrote:

> Hope you don't mind another few questions about tuning this multiple
> instances server.
> When installing 2 separate instances on MS SQL server 2000 Sp3a, would
> I best leave the configurationof server Memory to SQL server
> (dynamically), or should I allocate manually say 300 MB for each
> instance (Total MB RAM = 1 GB). The applications will not be heavily
> used.
> If I were to audit, would I benefit from only auditing failure, or
> would you yourself choose to audit All or None ? Do I audit only when
> necessary or all the time ?
> The BOL say that this file will grow max to 200 MB and that I should
> specify immediately during setup a new directory for these audit
> files. I can't see where I should enter this new directory.
> Is this the SQL server error log ? BOL suggests making a directory
> like MSSQL$instance\audit. That would be in the data directory right ?
> Where do I register this directory in SQL server as the audit
> directory? Do I need to use profiler to set auditing on and off ?
> In the Remote Server connections, would you deny remote RPC
> connections to your SQL server or not? Why ?
>
>
|||On Mon, 5 Jul 2004 12:03:07 +0100, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:

>For your first question, checkout the page titled "Multiple Instance
>Recommendations" in SQL Server 2000 Books Online.
>Regarding auting, I generally look for failures, in the SQL Errorlog.
Thanks,
Just one little question : In the database settings tab from the SQL
Server Properties, you can see New databases location.
Do they mean transaction log or error log or audit log when they ask
the default log directory ? I think transaction log (LDF), is that
correct ?
|||You see two text boxes there, one for database and the other for transaction
log files.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:emlie0dba1fo913c3d1m0rrhifj12frvlv@.4ax.com...
On Mon, 5 Jul 2004 12:03:07 +0100, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:

>For your first question, checkout the page titled "Multiple Instance
>Recommendations" in SQL Server 2000 Books Online.
>Regarding auting, I generally look for failures, in the SQL Errorlog.
Thanks,
Just one little question : In the database settings tab from the SQL
Server Properties, you can see New databases location.
Do they mean transaction log or error log or audit log when they ask
the default log directory ? I think transaction log (LDF), is that
correct ?
|||On Mon, 5 Jul 2004 14:37:13 +0100, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:

>You see two text boxes there, one for database and the other for transaction
>log files.
thanks a million
sql

Multiple Instances - Properties tuning

Hope you don't mind another few questions about tuning this multiple
instances server.
When installing 2 separate instances on MS SQL server 2000 Sp3a, would
I best leave the configurationof server Memory to SQL server
(dynamically), or should I allocate manually say 300 MB for each
instance (Total MB RAM = 1 GB). The applications will not be heavily
used.
If I were to audit, would I benefit from only auditing failure, or
would you yourself choose to audit All or None ? Do I audit only when
necessary or all the time ?
The BOL say that this file will grow max to 200 MB and that I should
specify immediately during setup a new directory for these audit
files. I can't see where I should enter this new directory.
Is this the SQL server error log ? BOL suggests making a directory
like MSSQL$instance\audit. That would be in the data directory right ?
Where do I register this directory in SQL server as the audit
directory? Do I need to use profiler to set auditing on and off ?
In the Remote Server connections, would you deny remote RPC
connections to your SQL server or not? Why ?For your first question, checkout the page titled "Multiple Instance
Recommendations" in SQL Server 2000 Books Online.
Regarding auting, I generally look for failures, in the SQL Errorlog.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:ogaie0tdj18cb194ubpp7vm971obqsva1j@.
4ax.com...
Hope you don't mind another few questions about tuning this multiple
instances server.
When installing 2 separate instances on MS SQL server 2000 Sp3a, would
I best leave the configurationof server Memory to SQL server
(dynamically), or should I allocate manually say 300 MB for each
instance (Total MB RAM = 1 GB). The applications will not be heavily
used.
If I were to audit, would I benefit from only auditing failure, or
would you yourself choose to audit All or None ? Do I audit only when
necessary or all the time ?
The BOL say that this file will grow max to 200 MB and that I should
specify immediately during setup a new directory for these audit
files. I can't see where I should enter this new directory.
Is this the SQL server error log ? BOL suggests making a directory
like MSSQL$instance\audit. That would be in the data directory right ?
Where do I register this directory in SQL server as the audit
directory? Do I need to use profiler to set auditing on and off ?
In the Remote Server connections, would you deny remote RPC
connections to your SQL server or not? Why ?|||Hi,
Why do you want to install mutiple instances of SQL Server since you have
only 1 GB of physical
memory in machine.
Better option will be create 2 databases and allocate 1 db for each
application.
If you need to install multiple instances of SQL Server always it is
recommended to fix the
memory usage for each instance of SQL Server. If you do not have any other
application other than
SQL Server in your machine then you can go for even 400 MB for each
instamce. THis memory parameter
should be changed based on usage of each instance. You can use Performance
monitor to identify the usage.
If I were to audit, would I benefit from only auditing failure, or
would you yourself choose to audit All or None ? Do I audit only when
necessary or all the time ?
I recommend you to audit only the failure, Otherwise you need to enable the
c2 Audit mode
which will audit all the activity. SO it uses more resources. During the
requirement stages
you can use Profiler to trace the activity.
Do I need to use profiler to set auditing on and off ?
No need, From Query Analyser execute
sp_configure 'c2 audit mode',1
To diable to audit
sp_configure 'c2 audit mode',0
(restart SQL Server service)
I can't see where I should enter this new directory.Is this the SQL server
error log ?
As far as i know,You have to change the default data directory
Thanks
Hari
MCDBA
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:ogaie0tdj18cb194ubpp7vm971obqsva1j@.
4ax.com...
> Hope you don't mind another few questions about tuning this multiple
> instances server.
> When installing 2 separate instances on MS SQL server 2000 Sp3a, would
> I best leave the configurationof server Memory to SQL server
> (dynamically), or should I allocate manually say 300 MB for each
> instance (Total MB RAM = 1 GB). The applications will not be heavily
> used.
> If I were to audit, would I benefit from only auditing failure, or
> would you yourself choose to audit All or None ? Do I audit only when
> necessary or all the time ?
> The BOL say that this file will grow max to 200 MB and that I should
> specify immediately during setup a new directory for these audit
> files. I can't see where I should enter this new directory.
> Is this the SQL server error log ? BOL suggests making a directory
> like MSSQL$instance\audit. That would be in the data directory right ?
> Where do I register this directory in SQL server as the audit
> directory? Do I need to use profiler to set auditing on and off ?
> In the Remote Server connections, would you deny remote RPC
> connections to your SQL server or not? Why ?
>|||Hi,
Why do you want to install mutiple instances of SQL Server since you have on
ly 1 GB of physical
memory in machine.
Better option will be create 2 databases and allocate 1 db for each applicat
ion.
If you need to install multiple instances of SQL Server always it is recomme
nded to fix the
memory usage for each instance of SQL Server. If you do not have any other a
pplication other than
SQL Server in your machine then you can go for even 400 MB for each instamce
. THis memory parameter
should be changed based on usage of each instance. You can use Performance m
onitor to identify the usage.
If I were to audit, would I benefit from only auditing failure, or
would you yourself choose to audit All or None ? Do I audit only when
necessary or all the time ?
I recommend you to audit only the failure, Otherwise you need to enable the
c2 Audit mode
which will audit all the activity. SO it uses more resources. During the req
uirement stages
you can use Profiler to trace the activity.
Do I need to use profiler to set auditing on and off ?
No need, From Query Analyser execute
sp_configure 'c2 audit mode',1
To diable to audit
sp_configure 'c2 audit mode',0
(restart SQL Server service)
I can't see where I should enter this new directory.Is this the SQL server e
rror log ?
As far as i know,You have to change the default data directory
Thanks
Hari
MCDBA
"Citizen" wrote:

> Hope you don't mind another few questions about tuning this multiple
> instances server.
> When installing 2 separate instances on MS SQL server 2000 Sp3a, would
> I best leave the configurationof server Memory to SQL server
> (dynamically), or should I allocate manually say 300 MB for each
> instance (Total MB RAM = 1 GB). The applications will not be heavily
> used.
> If I were to audit, would I benefit from only auditing failure, or
> would you yourself choose to audit All or None ? Do I audit only when
> necessary or all the time ?
> The BOL say that this file will grow max to 200 MB and that I should
> specify immediately during setup a new directory for these audit
> files. I can't see where I should enter this new directory.
> Is this the SQL server error log ? BOL suggests making a directory
> like MSSQL$instance\audit. That would be in the data directory right ?
> Where do I register this directory in SQL server as the audit
> directory? Do I need to use profiler to set auditing on and off ?
> In the Remote Server connections, would you deny remote RPC
> connections to your SQL server or not? Why ?
>
>|||On Mon, 5 Jul 2004 12:03:07 +0100, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:

>For your first question, checkout the page titled "Multiple Instance
>Recommendations" in SQL Server 2000 Books Online.
>Regarding auting, I generally look for failures, in the SQL Errorlog.
Thanks,
Just one little question : In the database settings tab from the SQL
Server Properties, you can see New databases location.
Do they mean transaction log or error log or audit log when they ask
the default log directory ? I think transaction log (LDF), is that
correct ?|||You see two text boxes there, one for database and the other for transaction
log files.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:emlie0dba1fo913c3d1m0rrhifj12frvlv@.
4ax.com...
On Mon, 5 Jul 2004 12:03:07 +0100, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:

>For your first question, checkout the page titled "Multiple Instance
>Recommendations" in SQL Server 2000 Books Online.
>Regarding auting, I generally look for failures, in the SQL Errorlog.
Thanks,
Just one little question : In the database settings tab from the SQL
Server Properties, you can see New databases location.
Do they mean transaction log or error log or audit log when they ask
the default log directory ? I think transaction log (LDF), is that
correct ?|||On Mon, 5 Jul 2004 14:37:13 +0100, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:

>You see two text boxes there, one for database and the other for transactio
n
>log files.
thanks a million