Ideal Set-Up
-
What would be the ideal set up, for smooth running of SQL, eg OS, Partitions, SQL version etc
-
Ideal? this is tough without knowing what you are trying to do. What is smooth? High Availability: try clustering. Data Integrity: Need a good design and good application programming. Good performance:Need more information.
Some rules of thumb:
1. Control changes. This will be the #1 reason things to not run smooth. This means EVERYTHING is tested before it's run on a live system.
2. Separate data and logs onto different physical devices.
3. Use RAID, 1 or 5.
4. Allow growth up to some point, not unlimited. Set this to be some value of the disk so you have additional room in an emergency
5.don't run the server under a domain admin, just a local admin.
6. If you can, I'd put the OS on one partition, swap file on another, data on 3rd and log on 4th.
7. Make regular backups and test restores.
Steve Jones
-
double poster! :). I agree with everything but one point... I still love the 0+1 setup for raid. I currently run my temp db off of a 0+1 stripe since my curent billing server kills the tempdb it has improved performance greatly handles reads and writes very well. Now that I only contract out a few hours a week I just sit at home and play with my servers 🙂
Wes
-
got some itchy fingers on the laptop today
.I tend to use RAID 1 for logs, RAID 5 for data as a general rule, but I'm budget conscious and haven't really been stressing servers. I like 0+1 if you can get the $$ for it. Oh I wish for 2GB drives for this...
Steve Jones
-
My belief on RAID 10 (0+1) is high write databases should be placed on those (they are costly yes, but they will give you the best uptime with performance). Read only of high percentage of the time with few inserts and updates are great on RAID 5 (you get a better bang for the buck).
Now to add
You should also if you have seperate drives create file groups and put tables from databases that do not have a high number of updates/inserts/deletes along with their indexes on those files (this is beacuse they will not fragment as often on the file system and keep their access times up). Also, periodically down the server for maintainence and DEFRAG the drives (this also helps with access times especially on drives with tables and indexes that grow often). Consider a growth size that causes fewer increases in the size to occurr (for example you maintainence once a week and you know that the database will grow an average of 150MB per week, have the auto grow grow 160MB so this only occurrs once per week (hopefully) as this will keep external fragmentation down. Run checkdsk utility (found by right click drive, properties, tools tab, check now) periodically with scan for bad sectors to make sure the drives are healthy. If using SQL 7 rebuild indexes periodically, 2000 do index defrag and on both do a manual update statistics to keep indexes and statistics up to par and having maximum effect. When you build the table and indexes (or rebuild indexes) consider the positions data will enter the table and plan you fill factor appropraitely to limit the number of page splits that occurr (for those that insert always at the end 100% fill factor is best, if you always double your size then a fill factor as low as 50% may be best. These are tricky though to figure out and may take playing with, but it will cause less internal server administration from occurring keeping resources freed).
As for version aim for 2000 as it has a lot of features that make it a better choice. I would use Windows 2000 Advance server minimum. But those specs depend more on what you are doing. However it is best that SQL lives on it's own server with no other competing server applications. Lost of power, 2 CPUs or better and lots of RAM will be some of your other best friends. Also periodically base line your server to get an idea of when some change is causing you issues. Then use profiler to try to find the bad guy.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply