Autonumber field: active

Showing posts with label active. Show all posts

Thursday, March 22, 2012

Backing up an active database

I have an active database running on SQL Server 2000. I use the backup
software Backup Exec 9. When I do my backups, I backup all working
directories. When I do a test restore, I notice that the database does not
backup but all the other files in the directory does. What am I not doing
correctly? Any help is appreciated.Do a SQL Backup. Backup Exec is trying to copy the sql files that are in use
by SQL Server so you won't be able to copy them.
Set up a maintenance plan to do sql backups and use backup exec to backup
the SQL backup files.
"Peter McCaul" <compcoff@.hotmail.com> wrote in message
news:e3ZNoJLHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I have an active database running on SQL Server 2000. I use the backup
> software Backup Exec 9. When I do my backups, I backup all working
> directories. When I do a test restore, I notice that the database does
not
> backup but all the other files in the directory does. What am I not doing
> correctly? Any help is appreciated.
>|||Hi,
R U trying to backup .mdf and .ldf files online?. Very few
backup softwares support Open File backup. Look into the
log of backup tool if any errors are thrown. Also it is
not a good idea to take backups of open files. you can
generate backup of the databases using maintenance plans
or scripts then use the backup tool to move them into
DATs/LTO's.
If you still insist to backup the .mdf and ldf files,which
I would not prefer for larger databases, you can schedule
as
1.Detach Database(s)
2.Backup .mdf & .ldf files using the backup software.
3.Attach Database(s)& corresponding tran. logs.
Someone may also suggest to stop SQL Server and backup
the .mdf and .ldf files, but there are chances for
failures while attaching them.
HTH
Regards
Thirumal
>--Original Message--
>I have an active database running on SQL Server 2000. I
use the backup
>software Backup Exec 9. When I do my backups, I backup
all working
>directories. When I do a test restore, I notice that the
database does not
>backup but all the other files in the directory does.
What am I not doing
>correctly? Any help is appreciated.
>
>.
>

Backing up an active database

Thursday, March 8, 2012

awe settings.

SQL2K Ent SP3a Win2k SP3
I've recently enabled awe memory on 2 of our servers.
Everything seems to be going OK, AWE memory counters
are active in performance monitor and the sql serror log
shows awe memory enabled on startup.
My question is, is the above information enough to know that
awe is working correctly? If not, what other things can be looked
at?
TIA.see
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274750#6
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Stressed" <k@.c.co.uk> wrote in message
news:%23oI0KZzVDHA.2164@.TK2MSFTNGP09.phx.gbl...
> SQL2K Ent SP3a Win2k SP3
> I've recently enabled awe memory on 2 of our servers.
> Everything seems to be going OK, AWE memory counters
> are active in performance monitor and the sql serror log
> shows awe memory enabled on startup.
> My question is, is the above information enough to know that
> awe is working correctly? If not, what other things can be looked
> at?
> TIA.
>
>|||Also, when SQL Server starts you should see a line time in
the event log that says:
"Address Windowing Extensions enabled."
Ron
>--Original Message--
>see
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q274750#6
>--
>BR,
>Mark Broadbent mcdba,mcse+i
>_________________________
>"Stressed" <k@.c.co.uk> wrote in message
>news:%23oI0KZzVDHA.2164@.TK2MSFTNGP09.phx.gbl...
>> SQL2K Ent SP3a Win2k SP3
>> I've recently enabled awe memory on 2 of our servers.
>> Everything seems to be going OK, AWE memory counters
>> are active in performance monitor and the sql serror log
>> shows awe memory enabled on startup.
>> My question is, is the above information enough to know
that
>> awe is working correctly? If not, what other things can
be looked
>> at?
>> TIA.
>>
>>
>
>.
>

AWE on 4GB multi-instance box?

Hi
We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
We are using an Active / Passive configuration which has 4 instances of SQL
Enterprise, and 1 Instance of IIS.
Both boxes are identical, and clustering works just fine.
However, in order to use the 4Gb in each box, should we enable the /3Gb
switch in Boot.Ini?
How then do we configure each instance of SQL Server? I believe that once
the /3Gb switch is enabled, that SQL will grab all the memory for itself,
leaving only 128Mb for the OS. We would probably need to specify a maximum
memory usage requirement, per instance, if that was to be the case.
There seems to be 2 options to me. Can anyone offer advice on whether we
should take:
1). Disable the AWE, via the removal of the /3Gb switch, and let SQL Server
manage its own memory
2). Enable AWE, via the use of the /3Gb switch, but then specify the maximum
memory each instance of SQL Server can use.
Does anyone have a view?
Thanks
Paul Aspinall
MCSD, MCDBA
Paul,
The /3GB switch does not enable or disable AWE. That is done via the AWE
option of sp_configure. If you have 4 actual instances of SQL Server plus
IIS on a single box with only 4GB of memory you certainly do not want to use
/3GB. You must share that 4GB with all 5 instances plus the OS and do not
want anything to swap to the swap file. Is there a particular reason to
have 4 separate instances vs. one with all the db's in it? It's hard to say
what the proper memory config should be given I know nothing about any of
your instances or how they are used. You might simply want to stay with
dynamic memory config on all 4 with no /3GB. AWE is only really useful with
more than 4GB anyway. It's IIS that I would be most worried about since it
doesn't play as well with others as SQL Server does.
Andrew J. Kelly SQL MVP
"Paul Aspinall" <paul@.aspy.co.uk> wrote in message
news:oG4Be.28164$O22.20831@.fe1.news.blueyonder.co. uk...
> Hi
> We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
> We are using an Active / Passive configuration which has 4 instances of
> SQL Enterprise, and 1 Instance of IIS.
> Both boxes are identical, and clustering works just fine.
> However, in order to use the 4Gb in each box, should we enable the /3Gb
> switch in Boot.Ini?
> How then do we configure each instance of SQL Server? I believe that once
> the /3Gb switch is enabled, that SQL will grab all the memory for itself,
> leaving only 128Mb for the OS. We would probably need to specify a maximum
> memory usage requirement, per instance, if that was to be the case.
> There seems to be 2 options to me. Can anyone offer advice on whether we
> should take:
> 1). Disable the AWE, via the removal of the /3Gb switch, and let SQL
> Server manage its own memory
> 2). Enable AWE, via the use of the /3Gb switch, but then specify the
> maximum memory each instance of SQL Server can use.
>
> Does anyone have a view?
> Thanks
>
> Paul Aspinall
> MCSD, MCDBA
>

Wednesday, March 7, 2012

AWE and SQL Cluster

Hi, I have the following questions regarding AWE on SQL 2000 with Windows
2000 Advanced Server and on a cluster (both nodes have an active SQL Server
instance running). The SA has upgraded the memory of both nodes to 8G each.
And I am tempted to use AWE to boost my SQL Server performance. The server
is dedicated to running SQL Server.
After AWE is enabled, can I configure my SQL Server max memory to something
like 7G?
Concern is, while it may run OK during normal operating mode, what will
happen in case both SQL Servers are moved to one node?
Thanks very much in advance.
SeanI would definately suggest using the AWE enabled option set to true.
especially if each node is dedicated to sql server. you will have to
restart the sql services to have the changes take place. check the
BOL: awe enabled option and the section managing awe memory.
Sean T Shen wrote:
> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> 2000 Advanced Server and on a cluster (both nodes have an active SQL Server
> instance running). The SA has upgraded the memory of both nodes to 8G each.
> And I am tempted to use AWE to boost my SQL Server performance. The server
> is dedicated to running SQL Server.
> After AWE is enabled, can I configure my SQL Server max memory to something
> like 7G?
> Concern is, while it may run OK during normal operating mode, what will
> happen in case both SQL Servers are moved to one node?
> Thanks very much in advance.
> Sean|||As I understand your situation, by use AWE and setting max memory to 7 GB
for the active virtual server, there will be no memory available in case of
a 'fail-over' (With AWE, the OS requires 1 GB).
I believe that you need to set max memory to an amount that leaves adequate
memory for the other virtual server in case of fail-over.
From:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
Allocating Memory
If you are running multiple instances of SQL Server on separate nodes in the
cluster, you must determine the total amount of physical memory required for
each server and determine the appropriate allocation of memory for each SQL
Server instance. Performance of SQL Server is directly related to the amount
of physical memory allocated to it by the operating system. SQL Server
provides two modes for memory management: dynamic and fixed. Dynamic memory
allocation lets SQL acquire and release memory from the operating system as
needed. Dynamic memory allows you to set a maximum amount of physical memory
that the operating system can allocate to SQL Server. This allows the
remaining memory to be reserved for other purposes, such as the operating
system and other SQL Server instances. Fixed memory allows you to set a
specific amount of physical memory that SQL Server can use. This amount
neither grows nor shrinks.
Configure each server with the same amount of physical memory, and then
allocate memory to each SQL Server instance to ensure that sufficient memory
is available to support the failover of a SQL Server instance from another
node. If a server does not use a lot of memory compared to the available
memory on the server, specify dynamic memory and set the maximum amount of
memory that the operating system can allocate. By doing so, you leave
sufficient memory for the failover to occur quickly and successfully. If an
instance uses all of the available physical memory, failover will occur more
slowly and can fail if memory is not released quickly enough.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>I would definately suggest using the AWE enabled option set to true.
> especially if each node is dedicated to sql server. you will have to
> restart the sql services to have the changes take place. check the
> BOL: awe enabled option and the section managing awe memory.
> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||Let me add to that, as I understand it, that if you use 4 GB or less for a
SQL Server instance, by enabling AWE, you will experience a net loss.
See:
"AWE is a set of memory management extensions to the Microsoft Win32? API
that allows applications to address memory beyond 4 GB."
Memory -AWE, Not usable < 4GB
http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case
> of a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves
> adequate memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in
> the cluster, you must determine the total amount of physical memory
> required for each server and determine the appropriate allocation of
> memory for each SQL Server instance. Performance of SQL Server is directly
> related to the amount of physical memory allocated to it by the operating
> system. SQL Server provides two modes for memory management: dynamic and
> fixed. Dynamic memory allocation lets SQL acquire and release memory from
> the operating system as needed. Dynamic memory allows you to set a maximum
> amount of physical memory that the operating system can allocate to SQL
> Server. This allows the remaining memory to be reserved for other
> purposes, such as the operating system and other SQL Server instances.
> Fixed memory allows you to set a specific amount of physical memory that
> SQL Server can use. This amount neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient
> memory is available to support the failover of a SQL Server instance from
> another node. If a server does not use a lot of memory compared to the
> available memory on the server, specify dynamic memory and set the maximum
> amount of memory that the operating system can allocate. By doing so, you
> leave sufficient memory for the failover to occur quickly and
> successfully. If an instance uses all of the available physical memory,
> failover will occur more slowly and can fail if memory is not released
> quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||I think your right about the 1GB for the OS on clustered services...he
definately needs to factor in outside things such as the clustered
services, other applications, etc.
Arnie Rowland wrote:
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case of
> a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves adequate
> memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in the
> cluster, you must determine the total amount of physical memory required for
> each server and determine the appropriate allocation of memory for each SQL
> Server instance. Performance of SQL Server is directly related to the amount
> of physical memory allocated to it by the operating system. SQL Server
> provides two modes for memory management: dynamic and fixed. Dynamic memory
> allocation lets SQL acquire and release memory from the operating system as
> needed. Dynamic memory allows you to set a maximum amount of physical memory
> that the operating system can allocate to SQL Server. This allows the
> remaining memory to be reserved for other purposes, such as the operating
> system and other SQL Server instances. Fixed memory allows you to set a
> specific amount of physical memory that SQL Server can use. This amount
> neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient memory
> is available to support the failover of a SQL Server instance from another
> node. If a server does not use a lot of memory compared to the available
> memory on the server, specify dynamic memory and set the maximum amount of
> memory that the operating system can allocate. By doing so, you leave
> sufficient memory for the failover to occur quickly and successfully. If an
> instance uses all of the available physical memory, failover will occur more
> slowly and can fail if memory is not released quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
> >I would definately suggest using the AWE enabled option set to true.
> > especially if each node is dedicated to sql server. you will have to
> > restart the sql services to have the changes take place. check the
> > BOL: awe enabled option and the section managing awe memory.
> >
> > Sean T Shen wrote:
> >> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
> >> Server
> >> instance running). The SA has upgraded the memory of both nodes to 8G
> >> each.
> >> And I am tempted to use AWE to boost my SQL Server performance. The
> >> server
> >> is dedicated to running SQL Server.
> >>
> >> After AWE is enabled, can I configure my SQL Server max memory to
> >> something
> >> like 7G?
> >> Concern is, while it may run OK during normal operating mode, what
> >> will
> >> happen in case both SQL Servers are moved to one node?
> >>
> >> Thanks very much in advance.
> >> Sean
> >|||I am facing a dilemma:
For each node, the total memory is 8G.
If I configure each SQL instance as having 3.5G, then when both of the SQL
instances running on the same node (the failover situation), I would have
3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
But, most of the time, I am running each SQL on each node, thus I am wasting
all this memory (8-3.5=4.5G for each node).
In this case, looks like I am better off not upgrading the physical memory.
If I just configure each node with 4G of memory each, and have SQL Server
dynamically eat up its uage (with a max server memory set to 3.5), then I
still will get 3.5G for each SQL Server. And when both instances fails over
to one side, SQL and OS will dynamically negotiate their memory needs.
Plus, I have one other cluster with AWE set. they each have 8G. So here is
what I did
Boot.ini --> /3GB /PAE --> reboot
SQL
sp_configure 'awe', 1
reconfigure
sp_configure 'max server memory', 3584 /* 1024x3.5 */
reconfigure
[I was worry about this failover situation, that is why I only set this to
3.5G to see what happens]
You would expect that after this and everything rebooted, I should see my
SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
Contrary to what I have read in articles.
Now my question is
1. In AWE, does or does not SQL Server use static memory allocation?
2. Will SQL Server ever take advantage of AWE, even if I counter it to use 6
or 7G?
3. Do I need to set anything else in sp_configure other than what I have
already done?
Thanks for the answer already provided, and additional thanks in advance for
any further info.
Sean
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
> Let me add to that, as I understand it, that if you use 4 GB or less for a
> SQL Server instance, by enabling AWE, you will experience a net loss.
> See:
> "AWE is a set of memory management extensions to the Microsoft Win32? API
> that allows applications to address memory beyond 4 GB."
> Memory -AWE, Not usable < 4GB
> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and release
>> memory from the operating system as needed. Dynamic memory allows you to
>> set a maximum amount of physical memory that the operating system can
>> allocate to SQL Server. This allows the remaining memory to be reserved
>> for other purposes, such as the operating system and other SQL Server
>> instances. Fixed memory allows you to set a specific amount of physical
>> memory that SQL Server can use. This amount neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance from
>> another node. If a server does not use a lot of memory compared to the
>> available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By doing
>> so, you leave sufficient memory for the failover to occur quickly and
>> successfully. If an instance uses all of the available physical memory,
>> failover will occur more slowly and can fail if memory is not released
>> quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>|||The bigger question is, can I even set anything up beyond 3.5G per each SQL
instance? If I do, will I not be able to move both SQL node resources to
one node (thus defeats the purpose of cluster HA protection)?
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158948261.902182.108570@.d34g2000cwd.googlegroups.com...
>I think your right about the 1GB for the OS on clustered services...he
> definately needs to factor in outside things such as the clustered
> services, other applications, etc.
> Arnie Rowland wrote:
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of
>> a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate
>> memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the
>> cluster, you must determine the total amount of physical memory required
>> for
>> each server and determine the appropriate allocation of memory for each
>> SQL
>> Server instance. Performance of SQL Server is directly related to the
>> amount
>> of physical memory allocated to it by the operating system. SQL Server
>> provides two modes for memory management: dynamic and fixed. Dynamic
>> memory
>> allocation lets SQL acquire and release memory from the operating system
>> as
>> needed. Dynamic memory allows you to set a maximum amount of physical
>> memory
>> that the operating system can allocate to SQL Server. This allows the
>> remaining memory to be reserved for other purposes, such as the operating
>> system and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory
>> is available to support the failover of a SQL Server instance from
>> another
>> node. If a server does not use a lot of memory compared to the available
>> memory on the server, specify dynamic memory and set the maximum amount
>> of
>> memory that the operating system can allocate. By doing so, you leave
>> sufficient memory for the failover to occur quickly and successfully. If
>> an
>> instance uses all of the available physical memory, failover will occur
>> more
>> slowly and can fail if memory is not released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>> >I would definately suggest using the AWE enabled option set to true.
>> > especially if each node is dedicated to sql server. you will have to
>> > restart the sql services to have the changes take place. check the
>> > BOL: awe enabled option and the section managing awe memory.
>> >
>> > Sean T Shen wrote:
>> >> Hi, I have the following questions regarding AWE on SQL 2000 with
>> >> Windows
>> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> >> Server
>> >> instance running). The SA has upgraded the memory of both nodes to 8G
>> >> each.
>> >> And I am tempted to use AWE to boost my SQL Server performance. The
>> >> server
>> >> is dedicated to running SQL Server.
>> >>
>> >> After AWE is enabled, can I configure my SQL Server max memory to
>> >> something
>> >> like 7G?
>> >> Concern is, while it may run OK during normal operating mode, what
>> >> will
>> >> happen in case both SQL Servers are moved to one node?
>> >>
>> >> Thanks very much in advance.
>> >> Sean
>> >
>|||Actually, you are not 'wasting' any memory -you are 'buying' high
availability (fail-over) insurance -and the cost that insurance is reflected
in the price of licenses, increased hardware costs, memory, etc.
You have a couple of options. (these comments are NOT germane for 64 bit
Windows and 64 bit SQL Server.)
1. Set memory statically at 3.5 GB per node.
2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB and
a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
virtual server to grab a starting point and then to negotiate with the OS
for more memory from the active virtual server.
Your other questions:
Don't use AWE unless a virtual server will have more than 4 GB -so option 1
above does not use AWE.
SQL Server will use memory about 2 GB for data cache only. Procedure cache,
and all other SQL memory needs come out of 2 GB
SQL Server does not immediately 'acquire' the max memory, it will increase
its demand from the OS as need requires.
SQL Server will use AWE memory above 4 GB for data cache only.
I hope this, and the articles I posted earlier, help.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sean T Shen" <seantshen@.optonline.net> wrote in message
news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>I am facing a dilemma:
> For each node, the total memory is 8G.
> If I configure each SQL instance as having 3.5G, then when both of the SQL
> instances running on the same node (the failover situation), I would have
> 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
> But, most of the time, I am running each SQL on each node, thus I am
> wasting all this memory (8-3.5=4.5G for each node).
> In this case, looks like I am better off not upgrading the physical
> memory. If I just configure each node with 4G of memory each, and have SQL
> Server dynamically eat up its uage (with a max server memory set to 3.5),
> then I still will get 3.5G for each SQL Server. And when both instances
> fails over to one side, SQL and OS will dynamically negotiate their memory
> needs.
> Plus, I have one other cluster with AWE set. they each have 8G. So here
> is what I did
> Boot.ini --> /3GB /PAE --> reboot
> SQL
> sp_configure 'awe', 1
> reconfigure
> sp_configure 'max server memory', 3584 /* 1024x3.5 */
> reconfigure
> [I was worry about this failover situation, that is why I only set this to
> 3.5G to see what happens]
> You would expect that after this and everything rebooted, I should see my
> SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
> Contrary to what I have read in articles.
> Now my question is
> 1. In AWE, does or does not SQL Server use static memory allocation?
> 2. Will SQL Server ever take advantage of AWE, even if I counter it to use
> 6 or 7G?
> 3. Do I need to set anything else in sp_configure other than what I have
> already done?
> Thanks for the answer already provided, and additional thanks in advance
> for any further info.
> Sean
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32? API
>> that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and
>> release memory from the operating system as needed. Dynamic memory
>> allows you to set a maximum amount of physical memory that the operating
>> system can allocate to SQL Server. This allows the remaining memory to
>> be reserved for other purposes, such as the operating system and other
>> SQL Server instances. Fixed memory allows you to set a specific amount
>> of physical memory that SQL Server can use. This amount neither grows
>> nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>|||That should have read 'above'.
> SQL Server will use memory above 2 GB for data cache only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>|||Not sure if I have successfully posted my reply.
Trying it again
Well, this is what I quote from a Microsoft article...do not have it handy
right now...but this is what it says
--
One caution about using the "awe enabled" setting is that after turning it
on, SQL Server no longer dynamically manages memory. Instead, it takes all
of the available RAM (except about 128MB which is left for the operating
system). If you want to prevent SQL Server from taking all of the RAM, you
must set the "max server memory" option (described in more detail later in
this article) to a figure that limits SQL Server to the amount or RAM you
specify.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>

Thursday, March 22, 2012

Backing up an active database

Backing up an active database

Backing up an active database

Thursday, March 8, 2012

awe settings.

AWE on 4GB multi-instance box?

Wednesday, March 7, 2012

AWE and SQL Cluster

AWE and SQL Cluster

AWE and SQL Cluster

Autonumber field

Blog Archive

About Me