SQL 2005 SERVER SET UP

I have set up a new server this week to host SQL server 2005 to manage my count engine.

Spec:

 – Antec Sonata 3 Super Case with 500W aPFC PSU
 – Abit AB9 WiFi iP965, S775, PCI-E (x16),
    DDR2 533/667/800, SATA II, SATA RAID, ATX
 – 4GB (2x2GB) Corsair TwinX XMS2, DDR2 PC2-6400 (800),
    240 Pins, Non-ECC Unbuffered, CAS 5-5-5-18
 – Intel Core 2 Quad Q6700, Kentsfield Core, Socket 775,
    2.66 GHz 1066MHz 8MB Cache, Retail
 – Pioneer DVR-215DBK 20x DVD±R, 10x DVD±DL,
    DVD + RW x8/-RW x6, SATA, Black, OEM
 – 1000 GB Hitachi HDS721010KLA330 Deskstar 7K1000,
    SATA II, 7200 rpm, 32MB Cache, 8.5 ms, NCQ
 – 500 GB Hitachi 0A33437 Deskstar 7K500, SATA II,
    7200 rpm, 16MB Cache, 8.5 ms, NCQ
 – Edimax 9270TX 64Bit 10/1000 Gigabit (GbE) PCI
    Network Card

I’ve done a lot of research into getting the most out of my hardware. After some time I concluded the following set up to be the best;

Windows Server 2003 OS
SQL Server 2005 32Bit.

To get the most out of the RAM using the 32Bit installation of SQL Server 2005 I needed to to tweak the boot sequence using the /3GB switch – to allow 3 og the 4 GIG of RAM available for processing.

Start –> Right-click My Computer –> Properies.
Advanced Tab –> Startup and Recovery –> Settings –> Click the Edit button to edit BOOT.ini

Boot.ini;

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS=”Windows Server 2003, Enterprise” /noexecute=optout /fastdetect /3GB

the /3GB switch applies to all 32-bit applications running on the box (not just SQL Server).
It reduce the kernel mode memory to just 1GB, thereby allowing the user mode portion 3 of the 4GB in the virtual address space.

The next key setup process was to set up the HDD to spread the work load. The following is how Microsoft suggest setting up HDD in SQL server;

C:  (RAID1 mirror for redundancy)
O/S
SQL Installation

D:  (RAID1 mirror for improved read and redundancy, or RAID10 for improved read/write and redundancy)
MDF Files

E: (RAID1 for redundancy if you are not backing up database or log files to a NAS, otherwise just a standard drive is ok)
Log Files
 
F: (RAID0  for fast read/write, no redundancy required as it’s just temp working, or a standard drive)
TempDB

I only had 2 HDD in my server so I did the following;

c:
Winders Server 2003
SQL Server 2005

F:
Data Files (MDF)
LOG Files
TEMPDB

which works fine for my purposes. If I need to increase processing speed I’ll add further HDD in the future.