October 31, 2003 at 8:03 am
I have two questions here:
1). I don't know if SQL enterprise 64-bit is compatible with Win2k Advanced Server.
2). If yes, should I enable AWE? If not, I will install SQL enterprise 32-bit on it, should I enable AWE?
October 31, 2003 at 8:14 am
quote:
1). I don't know if SQL enterprise 64-bit is compatible with Win2k Advanced Server.
No. You need 64-bit Windows server like windows 2003.
quote:
2). If yes, should I enable AWE? If not, I will install SQL enterprise 32-bit on it, should I enable AWE?
It depends on how much physical memory you have in server. If you have more than 4GB, you need to enable it at Windows level by adding /PAE to boot.ini file and enabling AWE in SQL Server.
October 31, 2003 at 12:26 pm
Where to add the switch /PAE in boot.ini?
eg.
[boot loader]
timeout=10
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect
October 31, 2003 at 12:30 pm
[boot loader]
timeout=10
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /PAE /fastdetect
October 31, 2003 at 12:44 pm
Using AWE Memory
To use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege.
SQL Server Setup will automatically grant the MSSQLServer service account permission to use the Lock Page in Memory option. If you are starting an instance of SQL Server 2000 from the command prompt using sqlservr.exe, you must manually assign this permission to the interactive user's account using the Windows 2000 Group Policy utility (gpedit.msc), or SQL Server will be unable to use AWE memory when not running as a service.
To enable the Lock Page in Memory option
Windows
How to enable the Lock Page in Memory option (Windows)
Note This functionality is available only if you are running the Microsoft® Windows® 2000 operating system.
To enable the Lock Page in Memory option
On the Start menu, click Run, and then in the Open box, type gpedit.msc.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the Users Rights Assignment check box.
The policies will be displayed in the details pane.
In the details pane, double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add.
In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
October 31, 2003 at 1:08 pm
Allen, I never knew it is complicated, I thought is as simple as the following statement to enable AWE on SQL server.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 8192
RECONFIGURE
GO
I don't understand "assigned the Windows 2000 lock pages in memory privilege". The lock page confused me.
Does it mean that the SQL server service must be running under domain or local admin account in order to make AWE work.
October 31, 2003 at 1:18 pm
No, It can be local system or domain user account but no necessary to be admin account.
From BOL.
"Using the Local System Account
The local system account does not require a password, does not have network access rights in Windows NT 4.0 and Windows 2000, and restricts your SQL Server installation from interacting with other servers.
Using a Domain User Account
A domain user account uses Windows Authentication, that is, the same user name and password used to connect to the operating system is also used to connect to SQL Server. A domain user account is typically used because many server-to-server activities can be performed only with a domain user account, for example:
Remote procedure calls.
Replication.
Backing up to network drives.
Heterogeneous joins that involve remote data sources.
SQL Server Agent mail features and SQL Mail. This restriction applies if using Microsoft Exchange. Most other mail systems also require clients (the SQL Server and SQL Server Agent services) to be run on accounts with network access.
Note Several servers running SQL Server can share the same user account. When setting up replication, it is recommended that a Publisher and all its Subscribers share the same service account for the SQL Server service.
Requirements for Domain User Account
All domain user accounts must have permission to:
Access and change the SQL Server directory (\Program Files\Microsoft SQL Server\Mssql).
Access and change the .mdf, .ndf, and .ldf database files.
Log on as a service.
Read and write registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
-or- for any named instance: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server.
HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQLServer.
-or- for any named instance: HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQL$Instancename.
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib.
In addition, a domain user account must be able to read and write corresponding registry keys for these services: SQLAgent$InstanceName, MSSearch, and MSDTC.
This table shows additional permissions required for certain functionality.
Service Permission Functionality
SQL Server Network write privileges Write to a mail slot using xp_sendmail.
SQL Server Act as part of operating system and replace a process level token Run xp_cmdshell for a user other than a SQL Server administrator.
SQL Server Agent Member of the Administrators local group Create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator.
Use the autorestart feature.
Use run-when-idle jobs.
SQL Server Member of local Power Users or local Administrators group Add and delete SQL Server objects in the Windows 2000 Active Directory "
October 31, 2003 at 1:40 pm
Finally, where to check if the 8GB memory is being used by SQL server? if 8GB memory is assigned to SQL server, I think another 2 or 3GB is needed for OS, right?
October 31, 2003 at 1:47 pm
Performance monitor counter, SQL Server:Memory Manager Total Server Memory (KB) tells you how much memory SQL Server consumes.
By default, System will reserve 2GB for itself and the rest for applications within 4GB memory boundary. You can allocate more for application by adding /3GB to boot.ini and let OS uses 1GB itself.
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /3GB /PAE /fastdetect
October 31, 2003 at 1:53 pm
If SQL was assigned 8GB, apps 3G, OS 1G, total I need for the server is 12GB, is 1GB enough for Win2k Advanced server?
October 31, 2003 at 2:12 pm
For example, if you have 8GB memory in total physically in your server. By adding /3GB and /PAE, system will allocate 1GB for itself, SQL Server and the rest applications use 3GB within the 4GB memory because they are 32bit application and can't address more than 4GB in memory. After enabling AWE in SQL Server, SQL Server will be able to use the memory above 4GB. In this case, SQL Server will use 7GB memory.
October 31, 2003 at 4:19 pm
quote:
Using AWE MemoryTo use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege.
Please clarify: If Win2k 32bit Application can only use 4GB of RAM, why do you need to set the 'lock pages' settings in Win2k as mentioned above to use AWE memory?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply