SQL Server, SANs and Virtualisation

Question

SQL Server, SANs and Virtualisation

Perry Whittle

SSC Guru

Points: 234013
More actions
January 7, 2009 at 9:30 pm

#199842

Comments posted to this topic are about the item SQL Server, SANs and Virtualisation
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 15 posts - 1 through 15 (of 68 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Good job covering an important topic when it comes to good configuration of your hardware layer for a solid ESX implementation.

Now prepare for the flame war over Virtual vs Physical and SQL Server. It is bound to happen given your topic. 😉

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

One thing that I just thought of that you didn't mention in your article is that you can also use your redundant paths to the SAN to balance your IO by setting the preferred path for specific LUN's to the secondary path in the event that you have one path become saturated. This can be done in the Virtual Infrastructure manager, and you can use putty sessions to the ESX host to monitor the true IO metrics for the HBA's to know if they are queuing commands heavily or not, so the old adage that you can't track down bottlenecks in a virtual environment no longer apply as they used to.

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 3

Perry,

Most people or consultants that I've spoken to has mentioned that it is important to have 64 kb sectors for SQL Server.

How is this athieved on SANs?

They also claim that the sectors must be "alligned".

What is this, and how does it affect speed?

How do we check the above statements?

TIA

Henrik Staun Poulsen

Stovi Software

magarity Ten Centuries Points: 1037 More actions · Answer 4

The most important thing to have when using a SAN is a good SAN administrator. The things are convoluted pieces of equipment with a lot of options and while they work with only a little setup out of the box, tweaking it properly (especially for database servers!) is NOT something one can pick up from the Quick Start installation guide. I've had to DBA a SQL Server hooked up to a SAN when there was a mediocre admin and when there was a great admin. The admin's technical expertise makes 10x the difference in your performance on the exact same SAN hardware.

jordonpilling SSCommitted Points: 1690 More actions · Answer 5

Nice Article,

One point of note however; your article seems to focus strictly on Fibre, is this because SQL Server is best suited to Fibre? what about SANS built on ISCSI (equallogic etc)? do these have the same capabilities?

Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 6

Hi mararity,

How do you tell the difference between mediocre and great Admin?

We have two people working with the SANs, but are they any good???

I would like to be able to verify that we get the best performance that is likely possible, but how?

Best regards

Henrik Staun Poulsen

kucerarj SSC Journeyman Points: 96 More actions · Answer 7

Both Fibre Channel and iSCSI use SCSI commands, the only difference is the media. There are a few more tasks that need to be done with FC vs iSCSI but the basic rules still apply. Currently FC connections have 4x more bandwidth than typical iSCSI (4Gb vs 1Gb). But 10Gb ethernet is starting to roll out. 8Gb FC is starting to come out as well. There is a little more overhead with iSCSI vs FC so iSCSI adds about 1-3ms overhead on latency. Another technology that is starting to roll out is FCOE (Fibre Channel over Ethernet). It's Fibre Channel protocol over ethernet media. It should provide the cost benefits of ethernet without the overhead of iSCSI.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

henrik staun poulsen (1/8/2009)
Hi mararity,
How do you tell the difference between mediocre and great Admin?
We have two people working with the SANs, but are they any good???
I would like to be able to verify that we get the best performance that is likely possible, but how?
Best regards
Henrik Staun Poulsen

Herik,

You could have a great SAN admin who just doesn't know enough about SQL workloads and have performance issues. Keep in mind that SAN's can be dedicated to SQL, but often are used for enterprise storage across multiple technologies, so they are often built and configured for the other things not SQL in the environment.

SQLIO is a benchmarking tool released by Microsoft that you can use to stress your IO subsystem and test various configuration changes to see the impact they have on different types of IO. This shouldn't be used on a active production SQL Server, but in the planning stages of a new implementation where you can reconstruct the LUN's in various configurations of stripe and block sizes and RAID configurations to find the best fit configuration for SQL in your environment. Other things that make a difference is having the correct offset in the Windows OS so that your drives are aligned properly and can make the most efficient use of the disk subsystem. There is a post in the Administration forum by Kendal van Dyke who has been playing with this topic recently that contains some more information.

http://www.sqlservercentral.com/Forums/Topic624961-146-1.aspx

Some other references on the subject are:

http://blogs.msdn.com/jimmymay/archive/2008/12/04/disk-partition-alignment-sector-alignment-for-sql-server-part-4-essentials-cheat-sheet.aspx

http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx

Conan Whalen-McKain-370433 Ten Centuries Points: 1154 More actions · Answer 9

I was hoping to recieve from your article a more precise breakdown of how you are making virtualization work. I have been the guy trying to use VMWare server to make it work and just found it to not handle at all well. Could you perhaps discuss more precisely how this works and what the product core is you are using?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Conan Whalen-McKain (1/8/2009)
I was hoping to recieve from your article a more precise breakdown of how you are making virtualization work. I have been the guy trying to use VMWare server to make it work and just found it to not handle at all well. Could you perhaps discuss more precisely how this works and what the product core is you are using?

I don't want to hijack Perry's discussion on this, but to help him out, can you post what version/edition of VMware you are using, and what your host hardware configuration is? Then the number and type of virtual guest servers you are trying to run on that and their sizes for vCPU, memory, and what software runs on them?

My company has a very large virtual environment, and for SQL my virtualized production servers outnumber my physical ones 4 to 1 and it works great, but the above information is all important to maybe helping provide information about what might be your issues.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 11

Good article.

I would say VWWare can be used in the testing environment, but not in a production Environment. Especially if you have very busy DB's.

-Roy

Sir Slicendice Mr or Mrs. 500 Points: 570 More actions · Answer 12

In my experience, the benefit of virtualization for SQL server is how much you are willing to trade off improved management for performance.

On the ease of management side, virtualization (VMs with SAN storage) give you ready ability to re-allocate space (disk and physmem) and relocate instances to make better use of hardware with less (or no) downtime.

But depending on the workload, the performance giveup can be huge: as an obvious case, you generally loose the ability to map SQL server partitions to spindles, which will crush performance if your partitions are properly designed. In my company, we have had great results with virtualization for sharepoint and fileserver uses, but my analytics datawarehouse system saw approximately a 10x performance improvement when we eliminated the virtualization (VM and SAN) and ran on the bare metal with the same number of spindles. As we are more than happy to give up some management flexibility for more performance for this application, the bare metal was the way to go.

Which all goes to show that you need to match the solution to the problem.....

The End.

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 13

Good article.

Here's a basic question:

How does one verify which physical array the database files and backups are located on in a SAN? I suspect that I have my backups located on the same physical array as the data files and want to separate them. My network admin seems to think this does not matter anyway because according to her, there is only one controller for all the arrays. I always thought there are separate controllers for each array (no single point of failure!). We do not have an in-house SAN admin and I only have a basic understanding of how SANs work.

Thanks!

Conan Whalen-McKain-370433 Ten Centuries Points: 1154 More actions · Answer 14

Currently, I am not engaged in using VMware. I just was trying to use it about 4 years ago. I was hoping to get a better picture of whats needed to really run virtualization and make it successful. The experience of a few years ago did not make me a great fan of virtualization for database servers. I believe this is probably the future as the possible hardware failure immunity and ease of disaster recovery makes this just too good an a possibility to ignore. Also my current company is considering this move and I am looking to give the sound technical advice that I am here for.

Thanks