SQL Server Performance

new server setup

Discussion in 'Performance Tuning for Hardware Configurations' started by speed, Nov 18, 2002.

  1. speed New Member

    Hi all,

    I am a new computer geek who knows enough to get himself in a whole lot of trouble. Thanks to satya, he has helped me find this site, as well as a few others.

    I have read many of the FAQs and postings on performance and clustering. But my boss is cheap and does not want to buy a real server till fall of next year. Just a quick note, using software raid.

    I get to setup a new server with SQL 2000. here is my hardware

    a desktop p3 computer with 1gb of RAM and 2 40gb ide hdds. This machine will also have a tape device.

    what I was thinking was a new install of win2k server
    (4gb partition for the O/S) mirror this 4gb patition.

    create another partition with the remaining 36gb (approx) for the SQL Server install partition (this is where I would keep the database file).

    On the other disk (that houses the mirror of the O/S) I would create two partitions. One partition would house log file and any manual backups to disk and the other partition would house free space for backups to archive. Does this sould like a good plan or should I do something differently.

    Any suggestions would be appreciated
  2. bradmcgehee New Member

    So if I understand this correctly, this server only has two physical disk drives (each one being 40GB), and no other hard disk drives at all. I also assume that you want to mirror because of fault tolerant reasons, and that you have no choice but to use software mirroring, not hardware mirroring because of hardware limitations.

    Assuming I understand the above correctly, here is what I would do (give your limitations). First, I would only create a single partition on each drive, and mirror them appropriately using software mirroring. Then I would go ahead and install the OS, SQL Server, the databases, and logs all in the same single (now mirrowed) partition. This is not ideal from a performance perspective, but you don't have any other good choices. At least your server has 1GB of RAM, which will help performance. Also mirroring will provide you the best available fault tolerance for your data, and mirroring will help boost (slightly) I/O performance over not mirroring. Read I/O should be very close to mirroring or not mirroring.

    Please let me know if I have understood your question and if you have any questions about my comments.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. speed New Member

    Hi Brad, yes you are right in your understanding of the scenario. Now as for assuming the mirror for fault tolerance, I assume I should use some sort of fault tolerance after everything I have read about Win2k and SQL Server. I don't need to I just think I should (you know that thought you get when you think you should do a back up just in case and you dont, then you are up the river with no paddle) that is the feeling I am getting. The problem is that no one here (not including myself) will ever know what to do if the server fails (until they read my documentation, also if they bother to read it).

    I dont have to do the mirror, but I was reading somewhere that the two files that are created by SQL Server (the dbf file and the transaction log file) should be on different physical hard disks to avoid the read/write head from continuous movement and better performance.

    Plus my boss is under the assumption that if the server crashes it will not be up and running until the next business day. And that is part of the disaster recovery plan if I need to rebuild the server it is a complete rebuild with SQL install and then restore the full database from tape. So is there a real need for the mirror? And if I did not have the mirror would that give me different options?
  4. bradmcgehee New Member

    Assuming if one of the drives of the mirror has a failure, than user's would not notice (much) of a failure occurs, and rebuilding a failed mirror is not to hard to learn.

    But if you don't mind rebuilding the server (assuming you have very good, and tested backups), then no mirror of any kind is needed. To take advantage of the performance boost due to having the log files on a separate drive, then only log files, no other files, must be on this drive, otherwise the performance benefit is minimal. This means that you would put the OS, SQL Server, and data on one drive, and the logs on the others.

    On the other hand, the benefit of putting logs on a separate drive is only really beneficial if your database a large amount of activity, at least over 1,000 transactions an hour, and up. If your SQL Server won't be that busy, then you won't see much real benefit from putting the logs on their own disk. If it was my choice, I would go with the mirroring, unless the database will be very, very busy.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page