By David Klee (@kleegeek)

Welcome to the sixth part in our series of posts regarding the virtualization of your business critical SQL Servers. In this post, I discuss Disaster Recovery of your SQL Servers while running on VMware. I wrap up the post with a discussion of techniques used to help demonstrate the power of virtualization and discuss the benefits to those individuals that might continue to fear (or not understand) virtualization.

High Availability and Disaster Recovery

To start the discussion, let’s begin with a reminder of the two definitions of High Availability (HA) and Disaster Recovery (DR).

High Availability (HA) is a design approach where systems are architected to meet a predetermined level of operational uptime, such as a Service Level Agreement (SLA). This means systems should have appropriate levels of redundancy while still keeping the environment as simple as possible as to not introduce more failure points.

Disaster Recovery (DR) is the process of preparing for, and recovering from, a technology infrastructure failure that is critical to a business. The core metrics of DR are the Recovery Point Objective (RPO) and Recovery Time Objective (RTO). The RPO is a measurement of the maximum time window where data could be lost in the event of a disaster. The RTO is the measurement of the maximum time that a core service or system can be offline after a disaster.

High availability is not disaster recovery, and disaster recovery is not high availability. Do not confuse the two, and do not assume that a solution for one is the same solution for the other.

In my previous post I talked about the options for SQL Server HA on VMware. We continue this topic now with a greater deep-dive on DR options for SQL Server on VMware.

Disaster Recovery Discussion Topics

A proper DR configuration must be geographically separated by a reasonable amount of distance. Here in the Midwest, the extreme weather plays a key role in determining DR distances. For example, a tornado can take out a few square miles of land, but what it does strike it will probably level. On the other hand, a blizzard could come through in the winter months and knock out power for large amounts of the area for days. In this scenario, the equipment suffers no real damage, but it is equally as unavailable. Both scenarios knock you offline for an indefinite period of time.

Be smart in your placement of your resources. One of the worst examples of this was from a friend whose company had its primary datacenter in Miami. The secondary datacenter was in New Orleans. Does anyone remember Hurricane Katrina? It was about to knock out their first datacenter, so the company failed over to the New Orleans site. Four days later it destroyed their secondary datacenter, and because the primary was still down, they were left with their datacenter on the third floor of a building with 15 feet of water in the lower floors. The business was down for weeks while off-site backups were gathered, a new datacenter and equipment allocated, and the business restored to the new site. It was an awful process.

The RPO and RTO come into more importance when discussing DR strategies. The recovery point and recovery time objectives must be determined before you start the DR planning process. They drive certain technologies as part of the architecture. These numbers can quickly eliminate several options and, sometimes, can help the business rethink the numbers.

One of the major points that few people discuss is the fail-back process. How do you get your data back to the primary datacenter after you have failed over and now have new transactions? What if your downtime requirements are tight?

So, without further ado, here are the methods for achieving DR with SQL Server on VMware.

VMware Site Recovery Manager

VMware Site Recovery Manager (SRM) and its little sister, vSphere Replication, are both means to handle SAN-to-SAN replication for disaster recovery purposes. The recovery time objectives with both of these products are low and, most importantly, the human steps in the failover process are few (or sometimes nonexistent).

SRM also handles failover and failback processes and can perform automatic testing of the replication process. It’s fantastic for those environments that have to routinely demonstrate the DR process without impacting the servers. Multiple failover and failback plans can be defined and carried out as required.

Single instance SQL Server on VMware works great with SRM, as long as you can demonstrate transactional integrity during the replication. Many SAN vendors have SRM guides for properly configuring the environment to work with SRM. Test transactional integrity with your normal (and maximum) workloads before putting these solutions into production.

However, the smallest RPO per VM or LUN replication is 15 minutes, and sometimes longer, depending on the SAN vendor. Keep this in mind. Sometimes, a combination of this strategy and a more real-time replication, such as database mirroring or AlwaysOn, could be used to complement the SRM strategy and reduce the RPO accordingly.

RTO: Very low compared to other solutions. Measured in minutes. Can dictate order in which VMs are powered on.

RPO: No shorter than 15 minutes, which can be problematic for some environments.

Failover Process: Simple, repeatable, and multiple plans can be defined for various situations.

Failback Process: Simple, repeatable, and multiple plans can be defined for various situations.

Pros: Low human intervention means failover process has a lower chance of errors during failover or failback. Can be tested and audited periodically without impacting operations.

Cons: RPO could be higher than business can allow.

vSphere Replication

vSphere Replication is a new feature of vSphere 5.1 that is included for free with all versions from Essentials Plus and above. Instead of orchestrating san-to-san LUN replication, this technology handles site-to-site replication of virtual machine change blocks of individual virtual machine disks. Failover is manual, but for a free product, this technology handles replication very well.

As with VMware SRM, the smallest RPO per VM replication is 15 minutes. Keep this in mind, and sometimes a combination of this strategy and a more real-time replication, such as database mirroring or AlwaysOn, could be used to complement the SRM strategy and reduce the RPO accordingly.

RTO: Very low compared to other solutions. Measured in minutes.

RPO: No shorter than 15 minutes, which can be problematic for some environments.

Failover Process: Simple, manual, and repeatable.

Failback Process: A manual process but is simple and repeatable.

Pros: Included with the core vSphere 5.1 suite in almost all licensing levels, and is simple to setup DR with commodity hardware and a 15-minute RPO.

Cons: RPO could be higher than business can allow. Failback process is manual.

SQL Server Asynchronous Mirroring

SQL Server database mirroring has been around since 2005 Service Pack 1. Asynchronous database mirroring allows a near-real-time replication of data between the two nodes. If a failover occurs, the secondary node fires up and picks up where the other left off. However, data might be missing if failover occurs while data is still in transit.

When running in this configuration, a witness server should also be used to facilitate the automatic failover. Otherwise, the decision to promote a secondary server to principal server is not automatic.

Also, asynchronous mirroring is only available in the Enterprise editions of the product. Standard edition gets you ‘full safety’ mode, which is real-time synchronous replication only. If your bandwidth between the two sites is either slow or has a high latency, you will feel the performance impact rather quickly. You could even experience significant delay in your production operations because of this impact. Unless the bandwidth AND latency between the two sites exceeds the transactional rates required by your workload during ALL periods of the day, synchronous mirroring is not recommended as a solution for production DR.

You will also notice that if the bandwidth throughput between the two sites is lower than required, data can stack up waiting to be replicated and the recovery point objective might not be obtainable.

RTO: Very quick, as the roles are flipped and the secondary node fires right up.

RPO: Variable, depending on transactional volume and available bandwidth.

Failover Process: Quick and generally without human intervention.

Failback Process: Resynchronize and fail the nodes back to primary. It is very straightforward and simple.

Pros: Quick failover times. Simple and not error-prone failover or failback processes.

Cons: Expensive licensing. Application must support mirroring failover target. Failover is at the database level and not the instance, so applications with multiple databases might require scripting.

SQL Server 2012 AlwaysOn Asynchronous Replication

SQL Server 2012 AlwaysOn is a blend of the virtual IP address and failover of Microsoft Failover Clustering and an improved derivative of SQL Server database mirroring. Because of the options that I previously covered here in this post, AlwaysOn can serve as both a DR and an HA solution, depending on the configuration. Again, I stress asynchronous replication here because of the impact of the speed and latency logistics around WAN synchronous replication.

RTO: Failing a node over to a DR site could be measured in seconds, depending on transactional volume and replication rates.

RPO: Could be as low as a second or less, depending on transactional volume and replication rates.

Failover Process: As simple as it gets. Happens in seconds. The Availability Group moves over and the application reconnects to the same IP address as before.

Failback Process: Even simpler. It’s the same process as failover, and just works!

Pros: Easy to setup and easier to manage. Extremely simple failover and failback processes.

Cons: Potentially expensive licensing. Must go through the upgrade process to SQL Server 2012.

Transactional Replication

Transactional replication allows the replication of data and schema changes from one server to one or more other servers. Data can be replicated at various time intervals in a number of ways. One good note about transactional replication is that you can select which tables get replicated. If some tables need to be replicated and others can be ignored, you can do this!

No automatic means for failover currently exist, so failover is a manual process. Data sources must have their targets changed, or for the more technically savvy, DNS aliases would need to be updated to point to the new server. The failover process would need to be documented and heavily tested. No automated fail-back procedure exists, so this process would also need to be planned for and tested heavily as well.

RTO: Varies depending on the complexity of the environment and the applications that connect to the database(s).

RPO: Varies depending on the replication time period to the distributor plus the time to have the subscribers fetch and apply the transactions.

Failover Process: Purely manual process.

Failback Process: Purely manual process.

Pros: Can selectively replicate databases or data subsets to one or more target servers on whatever time period you require.

Cons: Potentially complex and can be cumbersome for failover and failback processes.

Log Shipping

Log shipping is a process for backing up, copying, and then restoring database transaction logs from a source database to a standby server. It has been around quite a while, and has been used successfully for DR purposes for years.

RTO: Relatively short, generally 15 minutes or less.

RPO: Varies, based on the configuration, and can be very short.

Failover Process: Purely manual process.

Failback Process: Purely manual process.

Pros: Reliable once configured and established.

Cons: Purely manual failover and failback. Downtime is guaranteed while failover occurs. Application must be reconfigured to point to the new server, or a DNS alias adjusted.

Backup and Restore

If a low RPO is not a requirement (and yes, those environments actually DO exist!), a simple replicated backup and an automated restore could suffice. If a somewhat lower RPO is needed, periodic transaction log backups could be replicated as well.

RTO: Varies. Many factors affect this figure: size of the database, speed of the servers and storage at the DR site, and the state of the replicated backup files, just to name a few.

RPO: Varies, and is probably poor. It could be as long as the time taken between backups AND / or the time to replicate the backup file(s) to the DR location.

Failover Process: Restore last known good and successfully replicated backup. If exist, restore any replicated post-backup transaction log backups.

Failback Process: Create a new backup and restore at the primary site.

Pros: Least complex and simple to manage.

Cons: Long RPO and possibly RTO. Manual process.

Why isn’t Clustering in this List?

Clustering is not a solid DR solution. Why not? Think about the following points.

• Shared storage is available at a single location, practically speaking. Extending a SAN’s high speed interconnects across a WAN is impractical.
• Your one shared-storage SAN is still a single point of failure. I don’t care what any SAN vendor says. It can still fail. It still needs maintenance. It still has a power cord that can be tripped over. Etc. etc.

Now ask that question again. Clustering is for high availability, not disaster recovery.

Now What? Demonstrating that Virtualization Helps

So now what? You now have a solid understanding of each component in the physical and virtual stacks. You know the methods for profiling the existing physical servers and building the appropriately sized virtual equivalents. You know about HA and DR strategies.

But what is the hardest part about virtualizing business-critical servers?

It’s the environment.

It’s people.

People come in many forms – from employees who were burned from a failed virtualization attempt. It can come from skeptics (my favorite challenge). It can come from an organization that is naturally resistant to change. It can be from those with no frame of reference and are fearful of adding another black-box layer to their environments.

So what do you do?

• You educate.
• You demonstrate.
• You amplify.
• You support.

Educate the stack and application owners and stakeholders. Identify and educate the organizational advocates that exist in any group of people. Educate a team on what the other teams expect from them. Educate a team on what advantages virtualization brings to them specifically. Educate them on the technologies and how to identify the key performance metrics that matter to them.

Education will make believers of most people.

Next, setup a virtualization proof of concept on similar, if not equivalent, hardware to what you run in production. Identify your strangest, largest, or most complicated servers, and then add the servers you fear. You know, those servers that have been cranky when changes so they sit isolated and no one touches them. Clone the databases and workloads onto the new, properly sized virtual machines. Simulate production-like workloads and measure all of the key performance metrics you identified when you profiled your physical server. I know that if you followed the steps outlined in the previous posts, you will find that the performance of those virtualized workloads at least matches the physical equivalent. The technology has caught up with the business need and is now functionally transparent.

Now that you have objectively demonstrated equivalent performance in the virtualized environment, run up and down every hallway at your organization shouting ‘Virtualization works!’ No, wait. That might be counter-productive in the long run. Instead, demonstrate the performance equivalent to everyone in the organization that needs to believe in the technology. Demonstrate core vSphere functionality, such as vMotion, snapshots, or resource changes, and every response is ‘Wow!’

Make the right people believers by letting them see it for themselves. These people can become your biggest virtualization advocates.

Finally, support everyone through the production migration process. Sure, there might be hiccups along the way, but the hardest part is done. Once the virtualization of your business-critical applications has completed, the process is complete.  All new servers will be virtualized up-front, and discussions would happen only if the workload was not assumed to be suitable for virtualization (fringe cases do exist but it is rare). If performance issues arise, virtualization will not be immediately everyone’s scapegoat.

You have succeeded.

Acknowledgements

Special thanks go to my coworkers at House of Brick. We all fight the virtualization fight and enjoy the challenge. I believe we have developed the best business-critical workload virtualization team in the world, and thank you all for constantly pushing yourselves and each other to stay in front of this groundbreaking shift in IT’s core.

We all believe in the technology, and I hope it shows in our eagerness and determination to progress as the technologies continue to evolve. After reading this blog post series, I hope that you believe in the technology as much as we do. We will continue to post new and exciting technologies to this blog, all of which are furthering the quest for performance for your data. Stay tuned!

By David Klee (@kleegeek)

Welcome to the fifth part in our series of posts regarding the virtualization of your business critical SQL Servers. I discuss high availability with SQL Server on VMware, and this will include SQL Server clustering and SQL Server 2012 AlwaysOn. I had intended this post to conclude the series with Disaster Recovery, but have decided to wrap that topic into a sixth part to this series so I do not take too much of your time on this post.

High Availability and Disaster Recovery Options

To start the discussion, let’s start with two definitions.

High Availability (HA) is a design approach where systems are architected to meet a predetermined level of operational uptime, or a Service Level Agreement (SLA). This means systems should have appropriate levels of redundancy but keep the environment as simple as possible as to not introduce more failure points.

Disaster Recovery (DR) is the process of preparing for, and recovering from, a technology infrastructure failure that is critical to a business. The core metrics are the Recovery Point Objective (RPO) and Recovery Time Objective (RTO). The RPO is a measurement of the maximum time window where data could be lost in the event of a disaster. The RTO is the measurement of the maximum time that a core service or system can be offline after a disaster.

High availability is not disaster recovery, and disaster recovery is not high availability. Do not confuse the two, and do not assume that a solution for one is the same solution for the other.

To Cluster, or not to Cluster

Whenever the topic of HA for SQL Server comes up, people always gravitate towards Microsoft Failover Clustering (MFC) of SQL Server instances. In some situations in the physical word, I feel that clustering is a very viable solution. Other solutions exist – database mirroring, log shipping, or transactional replication, to name a few. Once you introduce SQL Server on top of VMware vSphere, your HA options grow, and I feel that clustering is actually NEEDED far less than people think.

Whenever I talk over the scenarios with clients, I always start with a handful of questions to get people talking.

Q: Does this SQL Server have less than a four-minute SLA for unplanned outages?

If the client answers “yes” to this question, MFC could be a good solution. However, it is not always the right solution. I will continue on after this response. However, if the client answers “no”, the remaining questions might be irrelevant.

Q: Do your administrators currently utilize a rolling maintenance strategy with existing clusters?

If a rolling maintenance strategy is not currently used, then planned outages will still drag on and possibly interrupt established SLAs.

Q: Is the application or middle-tier cluster-aware?

By asking this question, I am asking if the application has been coded to understand if the pause in SQL Server responses due to a cluster-node failover, and simply wait for the second node to begin responding. It should not return errors during the failover period. If the application cannot handle this task, you are guaranteed experience application downtime or errors during the time period the cluster is failing over. This window could be as large as three minutes, depending on the speed of the failover and SQL Server starting up on the second node.

Q: Does the appropriate level of technical expertise exist to support clustering?

This question bothers some folks, but it needs to be asked. The truth of the matter is that in a large percentage of the clustered environments that I encounter, the SQL Servers experience greater amounts of instability and unplanned downtime than if the SQL Server were just a standalone instance without being clustered. It sounds counter-intuitive, but if clustering is not installed, configured, and managed appropriately, the complexity of the environment will usually lead to problems that manifest in outages. A single-instance SQL Server is nowhere near as complex as a clustered SQL Server, and therefore the simplicity of the environment contributes to its stability.

Weigh the responses to these questions carefully.

You have to decide if the extra complexity of MFC will truly help the organization, do nothing at all, or worst of all, hurt it. By hurting it, I do mean substantial impacts to the day-to-day operations of your business.

What are my alternatives to MFC?

I start this deep dive with alternatives to MFC. I start with alternatives before tackling MFC itself for a simple reason: I think that a large number of organizations that I encounter are unnecessarily using clustering, and that it over complicates their environment without merit. A number of solutions exist that can offer equal or– in some situations – greater levels of HA than clustering. These solutions include using just a single instance of SQL Server on VMware VM with VMware High Availability (HA) enabled, SQL Server mirroring, SQL Server 2012 AlwaysOn, and old-school transactional replication or log shipping.

Single Instance SQL Server on VMware

A large number of the environments that I work with are fairly simple and have established adequately sized maintenance windows for routine items such as patching. An unplanned outage that lasts only a few minutes causes only a mild inconvenience for the business. In these environments, installing a single instance of SQL Server on a VM and enabling VMware HA on the VMware cluster will accommodate the business requirements for HA without inducing ANY unnecessary complexity. This option is as simple as it gets. Simply enable High Availability within the VMware vSphere cluster that the virtual machine resides in. If a physical host fails, any VMs that were running on that host get remapped onto running hosts and are automatically restarted. The average start-to-finish time of this operation averages around 2m 45s, depending on the amount of data that must go through crash recovery.

Enable the VM monitoring option and if the operating system goes unresponsive, VMware can detect it through a VMware Tools heartbeat setting, and will restart a VM after two minutes (configurable) if no CPU or I/O activity is detected for that time window.

Of course, maintenance will result in an outage, but when a maintenance window is already established, is this really a negative attribute?

Pros:

• Least complex.
• Simple to manage.
• Single copy of data.

Cons:

• Outage from host failures or maintenance operations.

SQL Server Mirroring

SQL Server mirroring, both synchronous and asynchronous, present a relatively simple configuration for better HA. Mirroring requires two VMs (three if a witness server is utilized), and each is configured in a nearly identical manner. Databases are configured for either synchronous or asynchronous mirroring individually. VMware HA can assist if a host fails by restarting the now secondary node on a different host. The secondary node fires up and then re-syncs to the new primary. The average failover time in our experience is around 45s.

Some cons to mirroring. Asynchronous mirroring requires SQL Server Enterprise edition. Mirroring is performed on a database level, so what happens if I am mirroring multiple databases for a single application (ex: Sharepoint) and only one of the databases fails over for some reason? You’ve now got a problem. Also, the application must support mirroring. Newer technologies like .NET (if proper database connectivity standards are used) or ODBC with a modern SQL Server driver can handle the failover without problem. Older technologies, however, could have a problem. The issue lies in how the client is responsible for being aware of both the primary and secondary node IP addresses, and performing the appropriate failover in the application. Some legacy applications simply cannot do this. The solution is to migrate to SQL Server 2012 AlwaysOn.

Pros:

• Relatively simple to install, configure, and manage.
• Availability Groups handle multiple database failovers.
• Very little downtime during failover.
• Easy fail-back.

Cons:

• Double the space on a SAN required.
• Limited to two nodes.
• Reading data from secondary node is a painful process.
• Database failover is per individual database.
• Might not be supported by some legacy applications.
• Deprecated in SQL Server 2012 in favor of AlwaysOn.

SQL Server 2012 AlwaysOn

I have talked here about the benefits of SQL Server 2012 AlwaysOn before. The combination of the virtual IP from the no-shared-disk MFC configuration and the improved mirroring configuration lends itself beautifully for placement on VMware. You can have two synchronously mirrored nodes or four asynchronously mirrored nodes, and each can be opened up to read-only activity (but check your licensing first).  Databases can be bundled into Availability Groups so that the group fails over together.
This technology works beautifully for HA operations. My experience is that an unplanned failover still only takes 45s on average.
Pros:

• Relatively easy to setup and manage.
• Solves most cons of traditional mirroring.
• Allow read-only activity on secondary nodes.
• Quick failover times.
• Simple fail-back.

Cons:

• Adjusted licensing model.
• Need at least twice the space for data and replicas.

Transactional Replication or Log Shipping

Traditional replication and log shipping both are both traditional ways to replicate data and changes from one server to another. However, both are not great means for HA with a low RPO. They are somewhat complicated to implement and manage. They do not guarantee full database data replication. No automatic failover method exists, and fail-back is even more difficult.

However, in some environments, these forms of HA are suitable. It does only replicate the data you are actually interested in, which can reduce your bandwidth consumption. There is no wait time at the secondary node during a failover.

Weigh your options wisely and select the technology appropriate for your environment.
Pros:

• Scale-out technology that can be used for HA.
• Replicate the data that you care about, not the entire database.

Cons:

• Complex to implement and manage.
• No built-in automatic failover.
• Difficult fail-back.

Clustering on VMware

Do any of the aforementioned alternatives mean I cannot execute Microsoft Failover Clustering on VMware? Absolutely not. We have done this in a number of clients, and every time, it was the most appropriate means for executing High Availability for their situation. Many scenarios demand MFC, and clustering on VMware is fully supported. In fact, just last week VMware released an updated MFC on VMware guide called ‘Setup for Failover Clustering and Microsoft Cluster Service’ for vSphere 5.1. Up to five nodes are now supported. VMware HA can still be used to ensure that VMs on a failed node are restarted on healthy nodes, which solves the single-point-of-failure issues that accompany a lengthy cluster node outage.

The tricky part comes with the architecture around the shared-disk configuration required by the clustering setup. The use of vMotion and DRS are prohibited. For shared disk configurations spanning hosts (I can’t image why anyone would build a cluster-in-a-box except to play with), either fiber channel RDMs or in-guest iSCSI presentation of the shared cluster disks is required. Failover times can span between a minute and up to five, depending on the infrastructure. Pros:

• Fully supported.
• Only space for one copy of data required.
• Single IP address for applications to be aware of.

Cons:

• Complex to manage without inducing greater downtime than standalone instance.
• Still have potentially lengthy downtime during failover.

Conclusion

This portion of the VBCA series is to inform you of all of the HA options available to you when you place your business-critical SQL Servers on the VMware platform. Many options are available, and at least one should work well for your organization and workloads.

In the final part of this series, I will discuss Disaster Recovery options with SQL Server on VMware. The remainder of the series presents the challenge behind Business Critical virtualization as it becomes organizational, where people require the most focused effort to help change their minds. I close with a discussion of techniques used to help demonstrate the power of virtualization and discuss the benefits to those individuals that might continue to fear or not understand virtualization.

By David Klee (@kleegeek)

Welcome to a series of posts regarding the virtualization of your business critical SQL Servers. Throughout this series we will be dispelling the various myths and misconceptions around this topic. We will also present specific details around our best practices for a business critical SQL Server virtual machine, operating system, and instance. We will also talk through the process of how to prove that in an apples-to-apples comparison of a physical and virtual SQL Server, the performance is at least equivalent.

Look for us at a SQL Saturday (www.sqlsaturday.com) near you! This topic is one that is near and dear to my heart, and I present on this topic frequently.

What is a Business Critical SQL Server, and Why Virtualize It?

A business critical SQL Server is just that – it is a SQL Server that your business absolutely depends on. If this server crashes, or data is lost, your business could fail. At a minimum, your employees could be left with nothing to do while it is down. These are the systems that, as management, you place the most resources and most care for high availability and disaster recovery. They absolutely must be running and recoverable.

The benefits of virtualization on these systems are tremendous. Individually, each one of the following benefits should be enough to get an organization excited about virtualization. Together, these benefits revolutionize the way datacenters are architected and managed.

Added Flexibility, Efficiency, and Agility

When virtualized, the application is effectively freed from the underlying infrastructure. VMs can move from resource node to resource node transparently, allowing the resources underneath to automatically handle growth and spikes without a disruption to business. An administrator can provision new servers in minutes instead of days with pre-configured VM templates.

Improved Disaster Recovery

Due to the decoupled nature of virtual machines from the underlying hardware, disaster recovery of virtual machines is much simpler than that of their physical counterparts. Through multiple means, VMs can be continually replicated to a DR location, audited and tested, and failed over and failed back quickly.

Increased Uptime

Increase your application uptime with built-in features such as VMware High Availability (HA) and Fault Tolerance (FT). VMware HA can minimize application outages in the event of a hardware failure. VMware FT can eliminate the application outages altogether. Avoid the downtime normally associated with hardware maintenance with vMotion and Storage vMotion.

Easier Development / Test / QA Environments

Ordinarily, constructing development, test, and QA environments that match production requires the same sorts of hardware as the production environment. Keeping these environments in technology and configuration sync with production can be cost prohibitive. With VMware, entire production stacks can be cloned and placed in a development, test, or QA role. These systems can be routinely refreshed with just a few clicks. This will accelerate the application development lifecycle because developers receive a development environment that is seemingly identical to production.

Server Consolidation

One of the obvious benefits of virtualization is server consolidation. The hardware server count is reduced, which lowers the server support and warranty costs, reduces the hardware footprint in the datacenter, and lowers power and cooling costs. Licensing can also be optimized to save even more capital. I normally speak less on consolidation when virtualizing business critical systems than with other environments or tiers of servers.

Why Virtualize Business-Critical Systems?

First of all, ask yourself - why not? The technology has evolved to the point where it is functionally transparent to the stack.

As of 2010, there are more virtual machines on this planet than there are physical servers. Even though the remaining physical servers are in the minority, these are the vast majority of business-critical systems. Take a look at the bell curve below.

The vast majority of lower-tier servers have already been virtualized. Businesses are sitting, waiting to cross the chasm to the business-critical system. This area is where the vast majority of capital is spent to maintain. This is the area where the lion's share of the productivity and revenue lies. This is the area that is most vital to the business. This is the area where businesses are most cautious when addressing virtualization.

This is the area where virtualization can benefit the organization the most.

Myths and Misconceptions

A number of myths and misconceptions exist around virtualizing business-critical systems. With proper education, planning, and understanding, these can be eliminated and virtualization can do what it does best – helping your organization’s bottom line.

Performance

People seem to harbor a tremendous number of misconceptions around performance. I am constantly shocked when I talk to people who insist that virtualization continues to inflict a serious penalty in performance because of virtualization overhead. Some hypervisors have more overhead than others, and older versions of VMware vSphere did have a noticeable overhead, but VMware vSphere 5 has become transparent.  As of a pre-release version of vSphere 5, storage has a 100-microsecond latency per I/O, and this latency linearly scaled all the way to one million IOPs. The only reason this benchmark was ended was because they ran out of storage to attach to the testbed. When it takes a benchmarking team to measure your system’s minute virtualization overhead, I declare it functionally transparent.

More often than not, these performance concerns come from some sort of virtualization trial (or even worse - a failed production go-live) that was performed in the past. Poor results (rightfully so) put a bad taste in people’s mouths. However, the investigation of their virtualization trial normally demonstrates a bad and unfair test. For example, the following diagram demonstrates a typical virtualization proof-of-concept system stack.

On the left is an average production system stack. On the right is the virtualization POC system stack.

What is wrong with this picture? Five dramatic items are different between the two stacks.

• The workloads are not the same. The POC has a much heavier workload placed on it.
• Only one storage path exists.
• The disk configuration is different – RAID-5 versus RAID-10.
• SATA disks are used instead of Fiber Channel drives.
• The amount of service processor read/write cache in the SAN is much lower.

In my experience, most people butcher a business-critical virtualization POC because the host hardware is dangerously overcommitted and the storage is completely overwhelmed. In this scenario, the CPU utilization is guaranteed to cause CPU Ready times to shoot through the roof, which will negatively impact VM performance. Storage performance is already at a disadvantage due to the disk configuration. RAID-5 suffers a write performance penalty when compared to RAID-10, and the lack of cache only magnifies the difference. SATA disks have a lower number of rated IOps than fiber channel disks.

This poorly constructed virtualization POC of business-critical systems is doomed to fail. As a result, the organization will now declare that virtualization cannot handle their top-tier systems.

It does not have to be this way.

With an apples-to-apples virtualization POC, or proper architecture if using equipment that is not at the same performance level of the production stack, the business-critical POC can succeed, paving the way to full production virtualization.

Support

We seem to always to field a lot of questions regarding support stances from different vendors. In truth, some vendors are nicer than others when answering this question. Some vendors have support statements in writing that differ from what their salespeople say. Some vendors have not certified their software for use on a virtual platform and refuse to support it (which tells me they are so woefully ignorant on the topic and/or so lazy that they cannot perform simple engineering validation tests, which are guaranteed to pass).

Microsoft has supported and embraced virtualization for years, and has a published support policy for their applications on VMware. It is officially supported via the Server Virtualization Validation Program. In a nutshell, if the server hardware and hypervisor platform has been validated (and if it is on the VMware HCL, chances are it is), Microsoft supports it.

You can read VMware’s official customer support statement at http://vmware.com/support/policies/ms_support_statement.html.

You can read Microsoft’s support statement, located in KB897615 at http://support.microsoft.com/kb/897615.

Database Size

Another misconception is that virtualization performance will be progressively negatively affected as the database size increases.

Database size has no impact on performance. Period.

If it works in the physical world, it will work in the virtual world. If you have serious storage performance degradation due to a workload size, you have a misconfiguration somewhere in the stack outside of the virtual infrastructure.

The only factors that matter for database performance are execution counts, concurrent connections, and SQL I/O access paths. Space has nothing to do with it. As the database size grows, concerns emerge such as backup and recovery throughput, disaster recovery operations, and migrations as needed. However, these concerns have nothing to do with virtualization. These are concerns that exist no matter the platform. No distinction between the physical and virtual environment exist!

Licensing

Licensing is one of those fun topics that, when mentioned, everyone cringes. It should not be. When done right, virtualization can potentially lower your licensing costs. A dedicated SQL Server cluster could be constructed where the physical cores are licensed, or a sub-cluster of an existing vSphere cluster could be licensed. It all depends on your environment, your agreements with Microsoft, and your server architecture. But, never fear licensing. Evaluate your environment and determine how much licensing money virtualization could save you.

Coming Soon

In the upcoming parts of this series, I discuss a number of the best practices that we follow that you should be aware of when building your SQL Server virtual machines. These are specific details around the virtual machine, operating system, and SQL Server instance configuration tweaks. I will also discuss how to prove that a virtual SQL Server performs as well as the physical counterpart in an apples-to-apples comparison.

Stay tuned, and check back in a couple of weeks for the next part of this series where I discuss the perfect build of a SQL Server virtual machine!

{jcomments on}

David Klee - @kleegeek

High Availability is one of those topics that many people either confuse or intermingle with Disaster Recovery, or just plain misunderstand. This post is an attempt to help differentiate between High Availability (HA), Fault Tolerance (FT), and Disaster Recovery (DR). I will also discuss some of the High Availability options present in Microsoft SQL Server that your organization should take into consideration when planning your business continuity strategy.

First, a few definitions are needed:

• High availability is a system design approach and associated service implementation that ensures a pre-arranged level of operational performance will be met during a contractual measurement period. (Wikipedia - http://en.wikipedia.org/wiki/High_availability)
• Fault-tolerance is a design that enables a system to continue operation, possibly at a reduced level (also known as graceful degradation), rather than failing completely, when some part of the system fails. (Wikipedia - http://en.wikipedia.org/wiki/Fault_tolerance)
• Disaster recovery involves the processes, policies, and procedures related to preparing for recovery or continuation of technology infrastructure critical to an organization after a natural or human-induced disaster. (Wikipedia - http://en.wikipedia.org/wiki/Disaster_recovery)

When looking at High Availability options for Microsoft SQL Server (with an emphasis on VMware), there are several options available (in no particular order):

• SQL Server 2008R2 Cluster on Windows Server 2008R2 Cluster
• SQL Server 2008R2 Database Mirroring
• VMware High Availability (HA)
• VMware Fault Tolerance (FT)
• SQL Server 2012 AlwaysOn Cluster

These options are complex and are not interchangeable.

• Microsoft Failover Clustering (MFC) runs at the operating system level and requires two or more host servers with shared storage.  An MFC can host cluster-aware applications, such as SQL Server, in cluster service groups that have their own name and IP address. In the event of a host failure - or a service failure - MFC can fail the cluster service group (including the name and IP) over to another host node.  Throughout this document, I will refer to this as “traditional clustering” to differentiate it from SQL Server 2012 AlwaysOn.
• Database Mirroring requires two or more host servers but does not require shared storage. It runs a primary database copy that is used by the application and a “mirror” copy that is constantly updated (synchronously or asynchronously) via transaction copies.  With the use of a witness server, mirroring can be set up to automatically failover in the event of a host or service failure on the primary.  The catch is that the secondary server has its own name and IP address so the application has to be able to handle the change. Some applications cannot be modified to point to a second SQL Server and accommodate the failover. The additional storage for the second copy of the data is sometimes cost-prohibitive.
• VMware HA monitors for the failure of the ESXi host and, in the event of a failure, evacuates all guest VM’s on that host to other ESXi hosts.  This is not an online operation where the VM stays running if a host fails, and will cause brief downtime. Normally, this downtime is less than three minutes, but does result in a VM being restarted on a new host.
• VMware FT is similar to VMware HA in that it protects against the failure of the ESXi host, but it does it in a different way.  VMware FT maintains a “shadow copy” of the VM’s CPU and memory footprints on another ESXi host, which is kept in “virtual lockstep” with the primary VM. In the event of a host failure, the “shadow copy” instantaneously takes over without a visible loss of service to the outside world. VMware FT is limited to a single vCPU at this time, so it not broadly applicable to production environments. VMworld 2011 brought a demo of vSMP FT, so we know VMware is actively working to add more vCPUs into the feature.
• SQL Server 2012 AlwaysOn is a new technology that has taken the benefits of operating system clustering and database mirroring, engineered away the features that cause administrators so many headaches, and combines them into a new package.  AlwaysOn clusters do not require shared storage (and is similar to mirroring). They also have a shared name and IP (like O/S clustering) so that the applications do not need to be aware of multiple database server names.

Before I start the discussion on any one of the above listed technologies, I first ask the following question:

Do you have a less than four minute Service Level Agreement on your application?

This four-minute mark is, in the event of a sudden VMware host failure, a conservative estimate of the time taken to restart a virtual machine on a surviving host and have the SQL Server service up and responding to requests. If you have an SLA greater than four minutes (or no SLA at all), then VMware HA alone could be speedy enough to meet your availability needs.  If the answer to the four-minute SLA question is yes, then consider the following questions:

Do you need to utilize rolling maintenance to minimize downtime?

Is your application middle tier cluster-aware?

Does your company have the technical expertise to manage Microsoft clustering?

The answer to all of these questions needs to be in the affirmative to successfully implement clustering.  Without rolling maintenance, cluster-aware applications, and technical expertise, traditional clustering can introduce more downtime than it will prevent.

Many organizations think they are forced into using an operating system-level solution (clustering or mirroring) over the VMware solutions (HA and FT) because the operating system solutions protect you from a failure of the application service itself inside the operating system. However, programs like Symantec ApplicationHA and Neverfail vAppHA can handle this task through the vSphere 5 Application Awareness API (download the GuestAppMonitor SDK if you wish to investigate the API behind this feature).

SQL Server 2012 comes with a new spin on HA clustering called AlwaysOn (which requires Enterprise Edition).  AlwaysOn has several key differences from traditional clustering and database mirroring:

• AlwaysOn uses separate storage for each SQL Server instance whereas traditional clustering relies on shared storage.
• AlwaysOn uses “Availability Groups” to group one or more databases as a logical unit that moves as one unit, whereas traditional mirroring is done database-by-database either synchronously or asynchronously.
• AlwaysOn Availability Groups can have an individual IP and DNS name that moves with the group, whereas traditional mirroring has a different IP and name for each server in the cluster.
• AlwaysOn can also open up the secondary databases in a read-only manner for reporting and backups.

I’ve blogged about my high opinion of AlwaysOn before on this blog, and I’ll continue to endorse this technology.

If you need to cluster, whether traditional clustering or SQL Server 2012 Always On, then the next question often is: can you cluster on VMware, getting the best of both worlds?  Yes, it is fully supported, but there are additional complexities and architectural limitations incurred by this decision.

You lose many of the traditional benefits of VMware (such as vMotion and Storage vMotion), and you gain new, more stringent requirements on storage, such as the requirement for either raw disk mapping (RDM) or in-Guest iSCSI rather than VMFS. (VMFS is only supported for “cluster in a box” where a single ESXi host hosts all cluster node VMs, which is not a High Availability solution due to the single point of failure of the single ESXi host.)  What you gain is VMware HA (once the appropriate host affinity rules are configured to keep the guest cluster nodes on separate ESXi hosts by default).

HA recommendations are always specific to a particular environment, but due to the many advantages of AlwaysOn, as described above House of Brick’s general recommendation, if clustering is needed is to create a SQL Server 2012 AlwaysOn solution on VMware if possible.  If clustering is not necessary based on the criteria contained in this document, VMware HA is our general recommendation.

But… what about DR?

HA is not DR. HA is not your Business Continuity Plan (BCP). HA is not your backup.

This is extremely important, so I’ll repeat this with more emphasis.

HA is not DR. HA is not your Business Continuity Plan (BCP). HA is not your backup.

HA is your first strategy to provide a buffer for a set of minor scenarios. It can help keep unexpected downtime to either minutes or seconds rather than hours or days. Disaster recovery ensures that your critical data and their respective systems are not lost in the event of a serious catastrophe.

So, how do the aforementioned technologies play in DR? MFC is an HA technology, not a DR technology for more than one datacenter. So are VMware HA and FT. So what do you do? Well, the SQL Server toolkit provides the following options for DR:

• SQL Server Database Mirroring
• SQL Server Transactional Replication
• SQL Server Log Shipping
• SQL Server 2012 AlwaysOn Cluster

The differences between these technologies are dramatic.

• Database mirroring to a DR location is an asynchronous solution that is configured on a per-database basis. Transactions are queued up and replicated to the DR location. This solution works very well for DR if the following two conditions are met: First, the mirrored database needs to be identical to the principal database. Secondly, the WAN connection between the two sites must be able to transmit the volume of data in a reasonable time window.
• Transactional replication and log shipping are similar, in that transactional data is copied and applied from the primary datacenter to the server in the DR site. No automatic failover detection exists. Fail-back from the DR site is most likely complicated. However, it can replicate only the specific data you are interested in, if that is one of your goals for DR. This can cut down on the bandwidth required for DR. It’s an interesting topic to discuss if you think either of these solutions are an option for your DR initiative.
• SQL Server 2012 AlwaysOn can handle your DR replication and failover in a simple GUI. The full discussion on using SQL Server 2012 AlwaysOn for HA and DR together was previously presented in this blog post of mine: How SQL Server 2012 AlwaysOn Blurs the Lines Between High Availabilty and Disaster Recovery.

So, what do you use?

That’s simple. You use the best tool for the job. Every environment is different. Every organization treats these topics in a unique manner. Use the feature that makes the most sense for your environment, test – test – test (and test again for good measure), and you are on your way!

Special thanks goes to Andy Galbraith (@DBA_Andy) for contributing much of this content. Thanks Andy!

David Klee  (@kleegeek)

Over the last month, I have been exploring the new features within SQL Server 2012. I have paid special attention to AlwaysOn, a new feature that effectively blurs the lines between high availability (HA) and disaster recovery (DR).

In previous versions of SQL Server, these two items were mutually exclusive. Both were managed separately and, in some cases, even managed by different people.

Historical Options for High Availability include:

• Microsoft Failover Clustering - A SAN must be used to present storage to all of the cluster nodes. Dealing with the quorum and the shared drive failover was cumbersome, and even routine maintenance like rolling patches could cause headaches. Most people continue to cringe at the mention of MSFC.
• Database mirroring - Mirroring in synchronous mode worked well, but since the mirrored pair presents two IP addresses for the application to connect to, the application needed to be mirror-aware. This was easier said than done; mirroring only worked in pairs, but was configured to fail over at a single database level. Consideration was not given to applications that had two or more databases, such as Microsoft SharePoint. Scripting was needed to fail over more than one database.

Historical Options for Disaster Recovery include:

• Database mirroring - Mirroring in asynchronous mode also worked well but the application still needed to be mirror-aware. Scripting was still needed to fail over more than one database.
• Transaction log shipping - This option was complex and failover was not automatic. However, you could have multiple nodes in the background for reporting or other purposes.

SQL Server 2012 AlwaysOn takes the best of these technologies, adds features to fill in some gaps, and presents it in an extremely simple to manage package.

Microsoft Failover Clustering, running without shared storage, handles the virtual IP address for all cluster nodes. Availability Groups, or groups of databases common to an application or purpose, can even have their own virtual IP address.

AlwaysOn allows you to set up four nodes in the Availability Group. Each secondary node can be configured for synchronous or asynchronous replication. Each can be configured to allow read-only access and database backups and reporting can now be processed from these nodes. Asynchronous nodes can be set up at a DR location for branch-level reporting and failover and failback is automatic.

Each Availability Group can contain multiple databases, which eliminates the need to manually script and keep things up to date as the environment changes.

In the terms of raw manageability, the amount of time saved by using these features is easily demonstrated through reduced complexity and simplification of maintenance procedures.

In the event of an HA or DR event, you want the simplest, most robust solution possible. Your business demands the highest uptime possible. Can your business afford NOT to evaluate SQL Server 2012 AlwaysOn?