Virtualizing the Data Warehouse : VMware, SQL Server 2005, and EMC Clariion

VMware Inc.

At my current employer we use a custom built ETL process for building business reporting and analysis data. Originally this started as a medium-sized Dell server with a full rack of local storage. As the criticality and scale of this resource grew, it outgrew the hardware it was on.  The key to this server was that the build process ran overnight and the server was accessed by multiple departments throughout the day.  This left very little time for hardware maintenance. I had helped move all development environment servers to a VMware cluster months before. Using this momentum I pitched the idea of solving the criticality and scalability with a VMware-based solution. The argument was four-fold:

  1. The company wanted to avoid the licensing and hardware expense of moving to a Microsoft clustering solution.
  2. VMware HA provided resumption of services in the case of a hardware failure and hardware maintenance would not require downtime.
  3. The RTO was satisfied by an automatic HA fail over.
  4. The additional cost of VMware licenses and new hosts would be spread over future planned provisioning and actually reduce costs by introducing consolidation.

After playing the part of VMware sales rep. I was able to get endorsements from the CTO, Data Services, and Executive groups. This would be the first time we would attempt to put a business critical service on a VMware platform. I was the only individual in IT at the time that had any exposure to VMware and needless to say my reputation and job was likely on the line.  The success (and to a greater extent stability) of the development environments was the key to get my foot in the door but now it was time to change our culture.

We opted to purchase additional Dell 2950 servers and EMC Clariion enclosures and dedicate a single DAE of fibre-channel 146GB x 15 for database drives. We did a few weeks of testing SQL loads that matched the ETL process (at the time) and determined that the optimal configuration was a single RAID10 of 14 disks with 3 separate VMDKs for data, log, and temp db.
The critical decision was “how do we move it?”.  At the time, P2V of SQL, Active Directory, and Exchange was not very common. I pitched the idea of a cold P2V using VMware converter. My justification was that the virtual could be brought up in parallel and everything tested completely. It took a few meetings to explain the process and give everyone a level of comfort
The P2V was successful and the virtualization has proved to be extremely valuable to the management of this server since. Here are just a few examples of VMware features that we have leveraged:

  • Storage VMotion – Moving the OS drive
  • Snapshots – Before a critical change
  • VMotion – Ability to add to, repair, or restructure hardware without having to involve a outage
  • Scale Up & Scale Out – Again without impacting service availability
  • DRS – Resource balancing servers of mixed workloads

Our company IT culture has changed in many ways over the last few years. VMware has become a central part in our Infrastructure foundation and has brought both stability and agility. We have virtualized Exchange, OCS, web servers, application databases, and many other services sucessfully since. Now we have ambitious goals of leveraging advanced SOA-based platforms coupled with business continuity and scalability using a VMware infrastructure. Looking back, this initial server was our first step.

I will be adding more posts soon on lessons we learned post-virtualization. If you have comments or even disagreements please leave comments below.

Commentary

2 Comments Leave a comment

  1. Hi,

    Very interesting article. I am looking at a virtualised SQL Data Warehouse/BI solution and would be interested to know your VM sizing and database sizing.

    Thanks

    IK

%d bloggers like this: