Couple of questions concerning SQL 2005 server setup

BGI

We do a lot of sql installs, and always have some discussions on some settings when installing a sql server, so why not throw it into this group and see what the opinions are:

1) collation sequence of sql server: Needs to be Case Sensetive and accent sensetive, otherwise possible problems with tempdb? (collating sequence of Navision db needs to be always cs and as)

2) 32 bit server with 6 gb of memory:
- do you need to set de /3GB switch?
- do you need to set up sql to use AWE memory

3) are these settings the same for a 64 bit server?

kine

ad 1) We are using CI_AS combination without problems. I never heard that some problem is because CS/CI or AS/AI, just depends on company preferences because sorting etc.

ad 2) You need /3GB switch if you are using 2-16 GB of RAM. If you are using more than 16GB, the switch is not needed (is contraproductive). Do not forget that you need to use /PAE if you are using more than 4GB RAM. If you want to use more than 4GB for SQL, you need to set AWE.

ad 3) for AWE on 64bit see http://blogs.msdn.com/slavao/archive/20 ... 93019.aspx. /3GB and /PAE are not used on 64bit.

kriki

Some remarks:
-Forget 32bit for SQL2005. Best move directly to 64bit. Better performance, easier to add more memory without having to change switches.
-tempDB: best put it on its own RAID1.

David_Singleton

BGI wrote:

..
2) 32 bit server with 6 gb of memory:
- do you need to set de /3GB switch?
- do you need to set up sql to use AWE memory
...

To paraphrase kriki "Why on earth would you use 32 bit"? ](*,) ](*,) ](*,)

Waldo

32 bit can address 4Gb RAM directly.
64 bit can address 18 billion Gb RAM directly.

/3GB
Furthermore, when you have 4Gb in your server, by default, Windos is going to address 2Gb for OS (kernel mode) and 2Gb for application mode. That's why, by default, you'll only have 2Gb for your SQL Server. With /3Gb, you're going to make 3Gb available for applications (thus SQL Server) and 1Gb for the OS.

/PAE
To be able to map to more then 4Gb RAM, you're going to have to set the /PAE switch. It's somewhat like "HIGHMEM" in the old dos days. You're going to use the lower memory to address the higher memory.
Don't forget to switch the AWE on the SQL Server.

Indeed, when going above 16gb, you'll need to remove the /3Gb switch. It's some kind of counterpoint. With /3GB you only addressed 1GB for the OS. This is not enough to map all the "upper memory" through /PAE.

Hope this makes some sense...

Nobody

/3GB actually only effects virtual address space not physical memory. Even with /3GB SQL will only use 2 GB of physical memory (actual only 1.7 GB)