SQL 2005-64 bit

Question

Post reply

SQL 2005-64 bit

California

SSCommitted

Points: 1888
More actions
July 7, 2008 at 12:39 pm

#194823

I am running SQL-64bit on a Windows 2003 box. I believe we dont have to worry about enabling AWE or PAE switches any more on 64-bit. But what about lock pages in memory. do i have to grant the SQL account to lock pages in memory or this too is not required on SQL 2005-64 bit?
Appreciated if someone please suggest.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

we've posted this to death and to death - however -

you can only lock pages in memory for enterprise sql server:: if you have enterprise then lock pages.

IMPORTANT always set max memory no matter what edition in x64

there is no PAE or awe in x64 ( the awe box does nothing )

depending on your server you should allow 4 - 6 GB for the o/s

you may set min memory but it's not essential in my experience.

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/06/configuring-windows-2003-x64-for-sql-server.aspx

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

California SSCommitted Points: 1888 More actions · Answer 2

Thanks for the contribution. I am indeed running SQL2005-enterprise edition. i am going to enable the lock memory pages. but in-case i dont enable this option - what are the issues i am looking at?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The OS can swap SQL's memory allocations out to disk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sgmunson SSC Guru Points: 110640 More actions · Answer 4

I know from a prior post that this has been posted to death, but for whatever reason, I can't seem to find any of those posts in a reasonably short time-frame. I'm finally installing my SQL Server 2005 Developer Edition (64-bit) on Vista Ultimate 64-bit with 8GB of RAM. I was trying to find the posts that talk about what priveleges are required for the various services that require an account, so that I can properly set those priveleges prior to installation. I remember seeing something about granting the SQL Server service's service account the "Lock Pages in Memory" privelege, but as I can't seem to find the post, I'm hoping someone can either direct me to the post, or provide the details. As this is a brand new install, and I plan to only access the server from client PCs on the home network, and have hardware firewall within my router, I don't think I need to worry quite as much about external intrusion, but still want to follow best practices on these things. I have a Win2K3 EE Server domain controller, and the Vista machine is a domain member, as are all the client PCs.

I plan to do some Blu-Ray video stuff on this machine at times, but when I do, I'll be turning off SQL Server, as it's not going to have any kind of uptime requirement. Any/all assistance will be much appreciated.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Don't bother with lock pages on a desktop. Since it prevents SQL from been swapped out, it could give you perf problems with other apps.

Lock pages is important on servers where consistent performance is required.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Thanks Gail. If you could, though, can you at least point me to the other posts about what priveleges one should be granting the accounts to be used for the various services? I am going to experiment with setting that lock pages parameter, as this machine may be a desktop, but it's usage will be far more like a server than a traditional desktop.

Once I have my tape backup device installed, and can be sure I can recover any data I need, I'll migrate my e-mail, Quicken, and Sudoku programs. Those items don't require a whole lot of performance. My objective is to treat this machine as much like a server as I can, in order to best simulate that environment, and have at least some real-world impact to myself if I mess it up. That will force me to learn the things I need to know to avoid that kind of problem.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

smunson (12/3/2008)
Thanks Gail. If you could, though, can you at least point me to the other posts about what priveleges one should be granting the accounts to be used for the various services? I am going to experiment with setting that lock pages parameter, as this machine may be a desktop, but it's usage will be far more like a server than a traditional desktop.
Once I have my tape backup device installed, and can be sure I can recover any data I need, I'll migrate my e-mail, Quicken, and Sudoku programs. Those items don't require a whole lot of performance. My objective is to treat this machine as much like a server as I can, in order to best simulate that environment, and have at least some real-world impact to myself if I mess it up. That will force me to learn the things I need to know to avoid that kind of problem.
Steve
(aka smunson)
:):):)

do you mean the service account for the sql server services, if so change the account through sql configuration manager and it will asign all necessary permissions

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sgmunson SSC Guru Points: 110640 More actions · Answer 8

I'm talking about ALL the service accounts that can be assigned during the installation process. I'd like to follow best practices for a server environment, despite the machine being a desktop OS. It's 64-bit, and I want to treat it just as if it were a server. I want to grant the minimum priveleges necessary, and still assign newly created user accounts (with appropriate levels of privelege) for each service where appropriate, or the existing system accounts where appropriate. My grief is that I just don't know what each of these service accounts is expected to be able to do. Kind of like trying to discipline the child of an alien you just met (and has suddenly wandered off) when you have no idea what constitutes appropriate child behaviors for that species, and that species is something other than bipedal or mammalian in nature. You just know you probably don't want to let it eat anything nor do you want to lose a finger or a limb trying to touch it. (if that's an excessively colorful analogy, then guilty as charged, and I'll just have to plead ignorance due to lack of exposure).

Steve

(aka smunson)

:):):)

Perry Whittle (12/3/2008)
do you mean the service account for the sql server services, if so change the account through sql configuration manager and it will asign all necessary permissions

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

smunson (12/3/2008)
I'm talking about ALL the service accounts that can be assigned during the installation process. I'd like to follow best practices for a server environment, despite the machine being a desktop OS. It's 64-bit, and I want to treat it just as if it were a server. I want to grant the minimum priveleges necessary, and still assign newly created user accounts (with appropriate levels of privelege) for each service where appropriate, or the existing system accounts where appropriate.

If you change the service account via the Service config tool, or during installation, the SQL config manager/installer will automatically grant the minimum required permissions.

They should be documented in BoL, somewhere.

The only ones not granted are the lock pages in memory and perform volume maintenance tasks. They're not automatically assigned, because they are not relevant/required/recommended in all cases.

Perform volume maintenance allows for instant initialisation and hence, since the files are not zeroes out, may compromise security. Lock pages may not be a good idea in all cases due to memory contention.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sgmunson SSC Guru Points: 110640 More actions · Answer 10

I finally found what I was looking for, now that I've had a few minutes with BOL... I was in the initial install process at home when I 1st posted, and thus didn't have access to BOL. And guess what: a search of BOL actually found a link to SSC !!! Sure enough, it was a Steve Jones article on the whole shootin' match, and couldn't have been more useful.

Here's the link for anyone else that might need this:

http://www.sqlservercentral.com/articles/Administration/sqlserver2005serviceaccounts/2341/

Thanks again everyone for all your assistance!

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 11

EDIT: OOPS!! double post - pls ignore.

============================================================

I finally found what I was looking for, now that I've had a few minutes with BOL... I was in the initial install process at home when I 1st posted, and thus didn't have access to BOL. And guess what: a search of BOL actually found a link to SSC !!! Sure enough, it was a Steve Jones article on the whole shootin' match, and couldn't have been more useful.

Here's the link for anyone else that might need this:

http://www.sqlservercentral.com/articles/Administration/sqlserver2005serviceaccounts/2341/

Thanks again everyone for all your assistance!

Steve

(aka smunson)

:):):)

============================================================

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)