Solved

Configuration of servers for sql server

Posted on 2010-11-27
4
353 Views
Last Modified: 2012-05-10
We currently have a SAN with a combination of SATA drives and fast fibre channel disks.  We have 4 host 2950 servers as hosts for virtual servers using vmware.

Our SQL servers are having performance issues and i wondered how people would configure their SQL servers.  We have a combination of heavy read and heavy write databases.

I have read you should not virtualize your SQL servers and other documents that you should.

What configuration should I use and should they be virtual or physical (my boss has decided that RAID 10 is not needed and RAID 5 is adequate because we are only a college)
0
Comment
Question by:WNottsC
4 Comments
 
LVL 47

Expert Comment

by:dlethe
Comment Utility
Well, since your knucklehead boss thinks  you are only a college so you don't need performance, then you should start by minimizing the amount of I/O you perform.

Since SQL Server does 64KB I/Os, you better make sure your RAID is configured for optimum IOPs using 64KB I/O size. Chances are that it is not.

You should really be using RAID1 for I/O intensive, and write intensive things like journals, temporary/scratch data space and log files.  Perhaps your boss will let you do that.  If not, maybe buy a small SSD?  For a few hundred bucks you can get a SSD that can maintain 20-30,000 RANDOM I/Os per second.   Use that for scratch table space and your performance problems may be a thing of the past.
0
 
LVL 7

Expert Comment

by:OxygenITSolutions
Comment Utility
I would install Windows on two of the servers and cluster them. I have always found SQL performs better when installed Natively than when installed as virtual servers. You can use VMWare for your test/DR environment on the other servers.
0
 

Author Comment

by:WNottsC
Comment Utility
at the moment I have no control over the way the SAN is configured.  It has two Raid 5 volumes one I am using for data and one I am using for logs with the temp and OS going on a raid 1 drive.

I have been looking at some of the dell MD3200 storage devices with 2 or 3 servers in a cluster at the front end.  Can anyone give any suggestions as to configuration or alternatives.

My company do not want to spend alot, see RAID 5 being ok,  but we are getting speed issues with some of our database applications and time outs
0
 
LVL 55

Accepted Solution

by:
andyalder earned 500 total points
Comment Utility
RAID 5 is cheaper?

Let's assume you get 200 IOPS per disk (that's a little high but sums are easier):-

10 disks in RAID gives 1800 IOPS read or 500 IOPS write, average 1150 assuming 50:50
10 disks in RAID 10 gives 2000 IOPS read or 1000 IOPS write, average 1500 assuming 50:50

So for the same price you can get 1150 IOPS with RAID 5 or 1500 IOPS with RAID 10, in other words as far as speed is concerned RAID 10 is cheaper. dmittedly these are classroom sums but real world experience is similar.

As far as SAN Vs DAS you get more bang per buck with DAS, you can't cluster DAS but if you want high availability then you can use log shipping which is built into SQL anyway.

 Bear in mind that you can get away with slow SATA disks for transaction logs as long as they are dedicated, they're pretty good at sequential access, just pants for random access.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Moving your enterprise fax infrastructure from in-house fax machines and servers to the cloud makes sense — from both an efficiency and productivity standpoint. But does migrating to a cloud fax solution mean you will no longer be able to send or re…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now