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 :)