Hardware config requirements for data warehouse server

Posted on 2012-09-06
Medium Priority
Last Modified: 2016-02-18
Please advise abt the hardware config required for data warehousing
(SSIS,SSAS,SSRS) to be installed on Hyper V vm?
Question by:Sonali P
  • 2
  • 2
  • 2
LVL 35

Expert Comment

by:David Todd
ID: 38374374

This is a piece of string questiion.

Is this new, or an upgrade/migration/consolidation from another system? What sort of resources does the previous system(s) have?

If what are are going to be doing is of any size, then they need to be separate machines, vm or physical.

Depending on the size of the cube(s) for SSAS it can consume a fair amount of ram.

The key thought is that after all the overhead from Hyper V has been deducted, what are you presenting to the guest systems?

I've seen production web servers (which is what SSRS is) on 1U single processor servers, and SSAS sharing a 4GB physical machine with SQL.

If this is a new hyperV server, then obviously get as much as you can and its easy to balance off the competing needs  by adjusting the VMs.

Is disk going to be local to Hyper V or from a SAN?

My first thoughts

What sort of thing(s) are you doing with SSIS?

Author Comment

by:Sonali P
ID: 38374453
Thanks for the advise.

I have installed one sql server on  hyper v vm having enterprise edition, 2 vcpus, 32gb ram , disk storage is from SAN , we also have another standalone physical server which has
a SSD drive on it and users compare running queries on  both servers and find queries on
server with ssd hard drive outperform at times.
Can hardware configuration on vm be further improved to match the physical server.
Currently ETL is performed on vm server with help of stored procs without using SSIS\DTS .
We plan to configure few more SQL  servers for SSIS and SSAS implementation
Can you please advise what would be the best hardware configuration on hyperv vms and how performance be improved on Hyper V

Thank you
LVL 35

Expert Comment

by:David Todd
ID: 38374491

Its pretty difficult to beat a physical machine with SSD drives.

Short answers - get a SSD based SAN
Give the VM more CPUs
I think 32GB of ram is Windows Standard max. Any more and you will need to upgrade the OS.

What have you done to tune the disks in the SAN for SQL?

What version/edition of guest OS? Did you know that you can speed up SQL by making sure the disk partition alignment is 'correct'?

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

LVL 28

Expert Comment

by:Ryan McCauley
ID: 38374499
SQL Server performance is often limited by the speed of the underlying disk storage (not always, but I've found it's a factor more often than it's not in performance issues). With your comparison server running an SSD, even Fiber Channel drives would be hard up to compete with it. What kind of storage is underlying the SAN you're connected to? Does it have SSD as well, or is it Fiber channel (or SAS? Or SATA?) - that can make a big difference. Also, are you accessing it via iSCSI - if so, at what speed - or via an HBA? An HBA should be able to match the performance of a local drive, but iSCSI, even on a gigabit link, can be limited during burst periods.

Also, is the storage you're using with your Hyper-V VM mapped directly to the LUN, or are you using a standard virtual hard drive file? If you're using a VHD, that's an extra layer of abstraction, and I've see it slow disk access down significantly - for maximum performance, you should be attaching your LUN directly to the VM using the native disk option in Hyper-V.

Given the proper configuration and high-speed underlying storage, a VM should perform similarly to a physical server of equivalent specs - they're nothing inherently slower about using a VM, but it takes some extra care with configuration.

Author Comment

by:Sonali P
ID: 38374555
What kind of storage is underlying the SAN?
is it Fiber channel (or SAS? Or SATA?)
The drives themselves are SAS, 15K rpm.  The array is using RAID 10 in both the existing source array and the new target array.

Also, how is it being accessed?  is it via iSCSI - if so, at what speed – or is it via an HBA?
iSCSI, no HBA.  The network is all 1GB (not 10GB).  The SAN traffic is all isolated to a SAN-only VLAN.  The SAN traffic uses Jumbo Frames and Flow Control.

Also, is the storage used on Hyper-V VM mapped directly to the LUN, or is it a standard virtual hard drive file?
 They are all standard virtual hard drive files.  Having the VM directly map a drive is supposed to be slightly faster, but Microsoft’s guidance indicated that it would be on the order of a few percent.
LVL 28

Accepted Solution

Ryan McCauley earned 2000 total points
ID: 38377029
It seems that the VHD vs. Physical mapping doesn't make as large a difference as I was led to believe - throughput looks to be roughly the same:


I don't think this was always the case - at least, not on ESX, which is where we do most of our virtualization and where a pass-through disk is quite a bit quicker (in latency, if not really in throughput).

SAS 15k in a RAID-10 will be quick, and may be close on throughput, but still won't rival an SSD for access times. Also, your iSCSI connection is limited to 120MB/sec in ideal conditions - if the network is quiet, you've got flow control configured properly, and there's no other contention - and in most cases, you'll see a bit below that. It may still be enough to service your needs, but if you've got the option to go 10Gbit, I'd consider that.

At the end of the day, the only way to know how your configuration is perfoming is to actually testing. I've used SQLIO before, and there's a great walk-through here:


His script is overkill - each of those lines runs for two minutes, making the whole script something like 8 hours of stress test. However, dropping it to 15 seconds/line (or only running a few different lines) will give you a pretty clear overview of the performance you're seeing. If you run it both on your fast and your "slow" server, it should let you know pretty starkly what the issue is.

For example, running this tool showed that one of my LUNs have appalling performance (4MB/sec), even though it was on the same SAN as some other LUNs that were great (400MB/sec). It turns out that the partitions weren't aligned properly when they were created, leading to a terrible bottleneck - reformatting the partition in question with proper alignment got things running at full speed, but it took actually comparison numbers before I could engage the storage team and have them look more deeply into the issue (rather than just anecdotal "it's being slow" stories I'd been giving them).

Let me know if you have any questions!

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 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