SQL SERVER 2000 -Best Practice measure in setting a server up? 1

[gman10]

Hello all-

This is my first time in this particular forum and am curious if anyone can point me towards a URL or a white paper on this topic.. I can generally build a Windows 2000/SQL 2000 Standard edition server BUT the devil is always in the detail. The main things I'm looking for is SQL performance settings and where to set them up once SQL Server is installed? Things like RAID levels, Indexing, throttling, load balancing,

thanks all!

gman

 

[mrdenny]

Start by searching this forum. There are several topics on this subject.

Here are some basic answers to your questions.
RAID (In a perfect world each section listed would have it's own array. Don't put anything on the OS array.)
OS - Raid 1
Log files - RAID 1
Data files - RAID 5
Indexes - RAID 5 (I love to seperate these out of huge database servers)
TempDB - RAID 1

Load Balancing - sort of possible using Replication, but shouldn't really be used. If you need to scale out like that it's time to look at federated databases on high end servers.

Indexing - Indexes placement should be done based on where it's needed. If you don't need an index, and it won't give you a performance increase then don't create it. More indexes slow down updateing, inserting, and deleting.

RAM - The more the better. Be sure to configure SQL Server to leave ~512 MB for the OS. If you can afford to leave 1 GB for the OS that's great.

Throttling - Why on earth would you want to throttle your SQL Server?

Anything else, let me know.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[gman10]

Hi Denny,

I appreciate all the info.. truly! Where can I set a certain SQL parameter that contains something to the degree of SIMPLE or FULL.. It's a detailed setting in SQL involving data thru put I believe? Not sure but I do remember seeing it but now I can't find it in SQL Standard 2000 Edition.


thx again so much!
gman

 

[mrdenny]

The only thing that comes to mind with SIMPLE or FULL would be the recovery mode of the database. Right click a database, properties, Options tab. The recovery mode setting is what tells the database what level of logging is made to the transaction log.

I know of no setting for setting the data thru put from SQL.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[gman10]

Hi Denny,

Just a few more questions to ease my SQL-challenged brain.. When you generally build a WIndows 2000/SQL server, do you generally created a separate DATA drive?? When I built the server for the customer it was built on a Compaq DL380 model.. First you need to insert the SmartStart disk to setup your intial RAID configuration.. I chose 1+0 for the OS and RAID 5 for the remaining 3 drives. (5 drives total).. I guess my question is.. Is this correct? Plus, the RAID configuration setup doesn't really give you the choice of qualifying those remaining 3 drives as a drive letter (I'd like to make/name it Data).. Could I just do this later in Windows Disk Management and format that stripe of disks there and qualify it as DATA?? Also from your knowledge, what should the pagefile be for a general SQL server.. Hoping you'll get back to me before the weekend is up, I'll be at the customer on Monday again and if you could help me with these scenarios I'd be most appreciative..

Thanks - Have a great weekend!
gman

 

[mrdenny]

Generally I use a RAID 1 for the OS, and a RAID 5 for the data. I always put the database files on a seperate drive. There are a few reasons for this thinking.

1. Database files and page files on the same drive will be competing for drive time. Major slow down can be caused by this.
2. Windows doesn't like it when the OS drive is filled up. What happenes if your databases grow out of controll for an unknown reason? You fill your OS drive, and you are now screwed.

Never stripe the drives in windows for a SQL Server. If you need to do this, stop, bust out the corporate credit card, and purchase a hardware raid controller. A stripe or RAID 5 stripe done through windows is a software setup. This means that the CPU needs to figure out the parity as the data is written across the drives. This takes CPU time away from the SQL Server and the OS, making the machine slower. Why would you ever want to do this especially when you have the nice fast Compaq raid card in the DL380.

When you setup your drives in smartstart they will be two different sizes. This is how you can tell which is which when you install. The smaller one is the OS drive. Or, only create the OS drive, install windows, reboot off the smart start CD again, and then create the second array. That compaq smartstart cd also comes with software to create the array from within windows. That way you don't have to reboot of the smartstart cd to create the array.

The page file should be about one and a half times the memory in the server. That's a fairly good rule for all servers. SQL will do it's best to not use the page file. Once SQL runs out of physical memory it will start removing things from memory to make room for more things in memory. SQL Server is smart enough to know that using the page file will be just as slow or slower than removing things from memory, and then cacheing more things. Espically when removing things from memory it doesn't have to write them anywhere, they just go away. But with the page file blank space has to be written to the file, so it's slower.

I hope that I've been able to answer your questions.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[gman10]

Wow, intense and detailed info! Thank you so much Denny.. truly!! I will put this to use immediately. I will need a separate data files drive.. I'll rebuild this box and create a D: (data) drive using SmartStart from within Windows from the remaining three drives.. Hey, which drive should I create the pagefile on then? the OS drive c:? certainly not the d: data drive correct?

Thanks again for the amazing information, clears up a ton of inquiries! enjoy your weekend!

gman

 

[mrdenny]

Correct, the page file should be on the C drive.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[gman10]

Hi Denny,

Just one more question.. I read your commentary on the RAID type used for SQL DB DATA partitions.. I had a slight debate with someone earlier about whether it's better to use raid 1+0 instead of raid 5 on a data partition. I have my second array (3 drives) set up on raid 5 (this is my DATA drive Eon my SQL server.. Why would I need to think about making this RAID 1+0.. Does this even make sense?? I'm hearing issues pertaining to raid1+0 producing better read /write values.. but then I read even more articles that state raid 5 is truly the way to go when it concerns SQL DATA drive partitions... A biased opinion here? anyone?

thanks again Denny.

gman

 

[mrdenny]

Based on what I have read:

RAID 0+1 is better for drives that have a high write IO, as they do not need to calculate parity while writting the data to the drives. Within a database the Transaction Logs are mostly write, with very few reads if any. (Basically your transaction log backups and system startup is when the logs are read.)

Taking the same number of drives (in this case we will use 4 drives) a RAID 5 will give us better read speed. In a 0+1 array you only have 2 drives to read from. Assuming SCSI3 UltraWide at ~80MB/sec that's a total max throughput of 160 MB/sec. Now with our RAID 5 array we have 4 drives, reading from 3 of them now gives us a through put of approx 240 MB/sec. Keep in mind that you only have to calculate parity when writting to the disk, not when reading.

The same numbers work with seek times. With a 0+1 you only have 2 drive heads seeking, with a RAID 5 array you have 3 heads seeking. Since I don't know the forumla to figure this out, I won't put any stats.

The RAID 5 makes more sence on a cost basis. With the RAID 5 array you get more storage for your money. With the RAID 0+1 you buy 4 drives, but only get the space of 2 of them. With the RAID 5 array you buy 4 drives, and get the space of 3 of them.

You have basically the same reduncency between the two with 4 drive arrays. With a raid 0+1 you can technically loose 2 drvies, but they have to be in the same raid 0 array. And once you loose one drive from a raid 0 array, you only have a RAID 0 array for your data. With a RAID 5 array you can only loose one drive. If a second drive is lost the array is lost.

This is where hot spairs come into play. I recommend always having a hot spare if your RAID controller supports it. If you aren't farmiler with hot spairs, the idea is this. You have 4 drives plus a hot spare. Drives 0, 1, 2 and 3 are in the array (doesn't matter RAID 0+1, 4, 5, etc). Drive 4 is the hot spare. Drive 2 dies. The raid controller will rebuild the array putting drive 4 into drive 2's logical place in the array. Meaning that you can now replace drive 2, and make it the hot spare. This gives you the change to blow 2 drives before you have a problem. While this is rare it can happen, so replace that hot spare often.

You know sometimes I think I should write a book on all this stuff.

Let me know if you've got any more questions.

Denny

--Anything is possible. All it takes is a little research. (Me)