SQL Server, SANs and Virtualisation

Question

SQL Server, SANs and Virtualisation

Viewing 15 posts - 16 through 30 (of 68 total)

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

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 1

We use VM's in production quite a bit.

It's a great (cheap) solution for those vendor applications that require SA.

Licensing is an issue for those not on enterprise licensing agreements, but I believe a processor license will allow you to run as many vm's with SQL as you want (on that processor). This probably doesn't apply to the OS license... but I'm not a licensing expert.

Anyone have some insight on this?

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

Not to be a forum hog (oink oink),

but something else to consider with SANs and VM's, is that lots of your hardware performance measurements go flying out the window.

for example, say you're on shared disk on the SAN and you're showing 70% Disk utilization. Behind the scenes the apps that are all sharing the SAN disks could be filling the /actual/ available throughput. I've not run into it yet, but it's something I try to keep in mind. Depending on your organization, you might try to get some access to the SAN tools to see the actual disk use on shared disk... or demand dedicated disk.

The same principles apply to virtualization and Memory/CPU measurements.

I'd be interested to know if anyone has the answers.

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

BuckWoody Ten Centuries Points: 1077 More actions · Answer 3

This is a good treatment of a complex subject in a short space. Thanks for putting this together!

It might be useful to compare VMware and Hyper-V from Microsoft, rather than the older 2K5 technology you mentioned. With Hyper-V from Microsoft, SQL Server is now supported, and Hyper-V is far superior to 2K5 in that respect.

Something that is important to keep in mind as well is that it is a bit more of a challenge to cluster SQL Server on any Hyper-V, and without that it is difficult to do rolling upgrades.

- Buck

Buck Woody
MCDBA, MCSE, Novell and Sun Certified

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

Sir Slicendice (1/8/2009)
In my experience, the benefit of virtualization for SQL server is how much you are willing to trade off improved management for performance.
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.

This boils down to implementation and how you have structured your infrastructure early on. The SAN LUNS I have are dedicated to SQL, and at the console level of ESX we can see what the IO being done by that LUN. I have a 300GB data warehouse on VMware that runs just fine, in fact I have less problems from it because it is designed properly than I do from some of our < 2GB databases on physical hardware that have bad designs. One of the < 2GB databases was moved off of VMware because the vendor refused to support it on a virtual machine because that was the problem. Now on a dedicated 2 processor 4GB RAM x64 Physical Server and it still won't perform, and the database is small enough to exist completely in the Buffer Cache.

You can certainly track performance in ESX if you plan your infrastructure properly.

BuckWoody Ten Centuries Points: 1077 More actions · Answer 5

Licensing for a VM is the same as a physcial machine. If you have 5 VM's installed, you have to license SQL Server 5 times.

Most often the problems people are trying to solve with VM's are far better corrected by using multiple Instances on the same physical system - in this case, the license is as you mentioned.

- Buck

Buck Woody
MCDBA, MCSE, Novell and Sun Certified

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

BuckWoody (1/8/2009)
Something that is important to keep in mind as well is that it is a bit more of a challenge to cluster SQL Server on any Hyper-V, and without that it is difficult to do rolling upgrades.
- Buck

That is one of the big issues with any of the hypervisors currently. You lose the ability to do a vMotion, or the HyperV equivalent if you want to do clustering because the Guest VM's vmdk/vhd has to be on the local storage of the host, and not on SAN space. We fortunately don't have requirements that would make it necessary to do clustering in our VM's at this point as we can suffer the 10 minute down times to do a reboot for monthly patches. Hardware issues would be handled by ESX and a vMotion to a different host in the group.

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

BuckWoody (1/8/2009)
Licensing for a VM is the same as a physcial machine. If you have 5 VM's installed, you have to license SQL Server 5 times.
Most often the problems people are trying to solve with VM's are far better corrected by using multiple Instances on the same physical system - in this case, the license is as you mentioned.
- Buck

Enterprise Edition isn't licensed this way, and with the new 6 and 8 core processors that Intel and AMD are promising, licensing a 2 CPU 16 way server with 2 EE licenses for $50K and being able to run 8 different SQL Servers with 2 processors each isn't that bad of a deal.

Truth be told, if I could have it my way, we wouldn't be using SQL Server on VMware, and we would use multiple instances as you suggest, but when we implemented our DR strategy on VMware the options available today like boot from SAN were in their infancy, and VMware was moving along good. The whole purpose of having SQL virtualized here is to have it bootable immediately in our remote data center in the event of a hurricane that destroy's Tampa.

We recently moved Exchange off of VMware and onto Physical hardware with boot from SAN but the cost is two fold for that, because we have a server in our local data center and then a duplicate at the remote datacenter waiting to be powered up, which is not a cheap proposition. We could in theory, reclaim 1.6TB of SAN space (OS and SQL binaries from the reduced servers) and reduce our SQL Licenses from 48 down to 16 by consolidating over to a couple of high performance physical servers, so I don't discredit that as a more viable option in cases like ours.

However, for the small shop where you have an old workstation sitting under the desk acting as your SQL Server, virtualizing that into a VM on a high power host makes a lot more sense.

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 8

BuckWoody (1/8/2009)
Licensing for a VM is the same as a physcial machine. If you have 5 VM's installed, you have to license SQL Server 5 times.
Most often the problems people are trying to solve with VM's are far better corrected by using multiple Instances on the same physical system - in this case, the license is as you mentioned.
- Buck

not according to figure 4 on this microsoft web page:

http://www.microsoft.com/sqlserver/2005/en/us/Special-Considerations.aspx

Figure 4. This example shows the extra option available with the enterprise edition only.Number of processor licenses required for Enterprise Edition : 4 SQL Processor LicensesNumber of processor licenses required for Standard, Workgroup: 5 SQL Processor licenses

But the caveat is Enterprise Ed.

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

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

SQLBOT (1/8/2009)

Using this same image and configuration, Buck has discussed this before, that you would be better off going physical for this implementation using 5 instances on the physical server, and I would have to agree.

The reason being, that you can use processor affinity to lock each instance to a processor so no one instance over runs the others for computing. You also don't have the overhead of the virtual host running in the background to contend with, or the added overhead of 4 additional operating systems on the server. This equates to more available memory for each instance since 1GB is not being used by the OS for each VM being run, and therefore better caching of data by the SQL Instance.

Planning and implementing the multi-instance server definately takes a bit of forethought, as you will want to map out your instance to processor mapping, and then how you fix your memory for each instance is equally as important. Ideally, you would be doing that on new hardware and be x64 so that you can scale memory better, but also so that you can lock pages in memory for each instance and prevent working set trims of the processes or excess paging of the sql processes.

Just playing devils advocate here.......

DBA_Rob Default port Points: 1494 More actions · Answer 10

Thanks for a good overview. We have already implemented virtualization and this would have handy to have during the planning stage. One area where virtualization falls short is on servers that use OPENQUERY to access Oracle data. In our case the throughput on these queries was 4 times longer than the same query on a non-virtual server. VMWARE was not able give us any type of workaround. The virtual servers work fine other then that. Accessing data on other SQL Servers from a virtual based SQL Server caused no problems, I can't talk about any other systems since our environment is SQL Server and Oracle.

Perry Whittle SSC Guru Points: 233853 More actions · Answer 11

Wow, some great responses here. Did I open a can of worms? I appreciate all the feedback both negative and positive. This was only ever meant to be a "quick and dirty" for the n00b. It’s then up to them, now they have a better understanding to go and read further.

The 64KB alignment option has been doing the rounds for some time now and although a MS best practice I have also seen documents from MS warning not to implement on a whim. One MS document I read actually showed that in their test lab they didn’t actually see any performance benefit and advised you to performance monitor your system to ensure you actually required this. I personally have never implemented a SQL server with aligned disks and never had a problem, then the servers I have used all have fairly small databases (40-50GB at the most) with average usage. Also, the point has been raised about the RAID block level on the SAN. Throw into that the block level of the actual VMFS file system too and for a virtual SQL server at what point does the alignment option become usable. I think it would be more important to ensure the SAN is configured efficiently to provide robust high-speed disk access. Our EMC SAN has more disks than you could shake a stick at and we have LUN’s dedicated to SQL server alone. A good SAN admin can make the best of any configuration they just need to understand the I\O loads that they need to service.

With regard to licensing our hosts are licenced at datacentre level which pretty much covers everything. Its not the cheapest but covers you for everything you'll need

As with any technology you should always "shop around" and fully realise what's on offer. Yes iSCSI is becoming a more viable option than FC but for the main most people are using FC SAN's, myself included. As pointed out previously both technologies use SCSI protocol, it’s just the underlying transport that is different. And let's face it EMC (one of the biggest players in this arena) at the moment are to SAN's what VMWare are to Hypervisors. It would indeed be an interesting benchmark to get VMWare and Hyper-V on a test bed. What you have to remember is that VMWare have a huge head start with this technology, that’s not to say that MS can’t compete, as we all know that is MS’s strength. It will be interesting to see how things develop. One thing I think is for sure and I’m sure you’ll agree;

Virtualisation is fast becoming the preferred option. It allows data centres to become compact and streamlines DR processes, not too mention the power saving benefits in this “Go green” era.

I have pointed out that although it is possible to host under VMWare server or VS2005 Enterprise they should not be used in anger. Also IIRC VMWare ESX 3.5 now supports full 8 node clustering, I’m not sure about Hyper-V.

I can’t stress enough how important it is to fully understand the Hypervisor you are using to host your virtual machine(s). Virtual networking alone can be a pitfall if you're new to it.

I have seen many posts on the forums from users who are coming into contact with these new technologies and wondering what they consist of. This article is meant to be a taster to wet the appetite and entice the user to want to find out more, a gentle introduction so that further reading would be more comprehendible

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

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

One thing I would add: people who are contemplating virtualization/SANs for data warehouses should track down the performamce analysis work done by the late Jim Gray (Microsoft); his concept of storage bricks is interesting to look at....

Data warehouse I/O patterns are inherently sequential, and thus place heavy demands on I/O bandwidth, demands that are very difficult to satisfy with economical SAN solutions.

The End.

John Beggs SSCommitted Points: 1921 More actions · Answer 13

Perry, can you link the article from MS where they hesitate on the offset recommendation?

Also, in saying that you haven't used offset but haven't had a problem, doesn't indicate that there wouldn't be a gain by moving to offset partitions. There is no way to make a proper recommendation there w/o having data from both sides. The exception to this is if you already have a setup that is acceptable, should you change? The process of changing this is very tedious, making doing it while things are "good" not very attractive.

Having worked with SQL Server and SANs for about 10 years, here are some things to consider...

If you are using a SAN and do not have an offset set up, either through your MS OS or the SAN itself, you are taking a performance hit at the SAN level that you don't need to take. Unless you are pushing your array very hard, the performance may not be seen at your server. SANs are notorious/famous for hiding IO inefficiencies behind your plethora of spindles. Why not eliminate this if you can?

64 sectors is not the magic number. It is the MINIMUM number. Because 63 sectors are used by MS, that leaves a 1 sector overlap for proper base 2 conversion. On an array that has a stripe size of 64 sectors (typically 32k) setting the offset to 64 sectors helps. However, most current SANs (EMC Clariion, Compaq, 3Par and others), default to 128k stripes for MS OSes. Therefore, in that situation your offset should be 128k. Keep this in mind when setting things up.

So, when it comes to setting up new arrays, I always use an offset. I have never read or discussed any type of performance hit in doing so, nor have I seen any through extensive load testing. I may not always see the gain I would like, depending on the shape of the load, but I have never seen a loss in performance. (Hence the desire to see this MS article referenced above.) I also always use the OS to set the offset, rather than the SAN. This is largely due to older systems, where changing this, and other LUN changes took a good bit of time, making the MS offset quicker to change if needed. However, with the new disk virtualizations from 3Par and now available on the Clariion line from EMC, it's much simpler to do at the SAN level.

As for reconfiguring old arrays, I usually save it for a spindle reconfiguration and kill two birds with one stone. But again, using the new virtualized storage solutions, this is also much less of an issue. The arrival of the virtualization of storage in the mid-size SAN arena (where most MSSQL resides) is quite a blessing to administrators.

Shameless plug: If you have need of a SAN, but don't have a SAN administrator, take a look at 3Par. About as close as you can get to the "set it and forget it" SAN...

Perry Whittle SSC Guru Points: 233853 More actions · Answer 14

John

cant find the link at present, it detailed the NTFS cluster size and disk alignment within the windows OS. MS indicated that changing default cluster sizes,etc didnt necessarily boost performance.

I've often wondered how would the alignment at the RAID level affect the VMFS file system upon which the virtual disks sit

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 15

this isn't a plug for my blog, but I've been actually testing 3 sans for performance as part of a data centre move, becuase of a nda I can't name one san manufacturer. You might want to glance over my blog http://sqlblogcasts.com/blogs/grumpyolddba/ as I'm putting up a series of posts concerning performance.

Note that windows 2008 does not require partition alignment and some san manufacturers claim it's irrelevant. There are a lot of factors concerning a storage area network and the article gave a good insight. My personal experience tends to indicate real performance on a san is very hard to achieve and there is far more contention than vendors care to admit. Probably the most difficult factor is performance monitoring which seems to be sadly lacking on most sans.

Beware of using "tools" to measure disk subsystem storage; I've watched tools used to prove san performance, when i figure out how to blog about it accurately I will, the upshot was that I had claimed our application performance was degraded on a new san, the vendor produced a series of non sql tests which proved otherwise - in the end I was able to find a number of application code samples and run them in loops along with backups, restores, index rebuilds which showed the new san was around 50% slower than the existing san .. it's a long story but do be careful of claims about san technology.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/