I was hoping that someone could give me some advice on what setup/configuration would provide the best performance for our new SQL Server installation.
We are moving from SQL2000 to SQL2008. We are running Nav 4.01 and our database is currently 50GB however we are planning on doing a compression of some historical data once the move is complete.
The new server has the following configuration:
Windows 2008 Enterprise Edition.
SQL Server 2008
2 x 2.5Ghz Quad Core CPU
1 x 146GB mirror (C:)
1 x 292GB 4 drive RAID10 (F:)
The plan so far was to load the operating system and the SQL Server software on the c: drive. The log file and database files would be on f: drive.
My first question would be, is it ok to load SQL Server on the c: drive along with the operating system?
Next, I know you can create addition database files, however I’m not sure how you go about spreading the data evenly amongst the files created. Also would it make any sense to create a new partition off of f: drive to store the log files.
I’ve read a lot of information about server configuration but obviously I don’t have the expertise with the hardware or SQL software. Any help at all would be appreciated.
There are many many documents on the ideal configuration for a SQL server. Most of the documents I’ve read recommend at least 4 seperate drives (and not one large volume partitioned). For example:
C:\ OS & SQL
E:\ Transaction Log
I’m sure there will be other posts suggesting differing ideas.
Creating extra database files will do you no real benifit if they are on the same physical disks. Some good things about your setup is at least you do not have raid 5.
I’d be interesting to see if others in this forum would recommend breaking the 4 drive 10 into 2 mirrors, so you have 3 drives. That would give you some more options.
In terms of creating 2 logical partitions on a drive, it will be easier to manage but could impact performance, depending on whats on the other parition.
For hosting SQL on the same drive as the OS, no real issue there if you’re talking about the program files. You may want to consider moving the system SQL databases to the other volume, although having tempdb on the same disk as the DB could cause you some performance hickups, depending on your processing load.
I should also add there are people in this forum much more experienced than me when it comes to tuning Nav on SQL Server.