Hyper-V Equivalent to VMware CPU Ready Time

I’ve blogged about CPU Ready Time in VMware in the past, and one of the questions I am often asked is whether there is an equivalent counter in Hyper-V? The definition of CPU Ready time by VMware is:

“Percentage of time that the virtual machine was ready, but could not get scheduled to run on the physical CPU.”

Looking through the Hyper-V performance counters and their description on Windows Server 2012/2012 R2, the Hyper-V Hypervisor Virtual Processor\CPU Wait Time Per Dispatch counter provides the following description:

“The average time (in nanoseconds) spent waiting for a virtual processor to be dispatched onto a logical processor.”

image

Unfortunately this counter is new in Windows Server 2012 Hyper-V and doesn’t exist in Windows Server 2008 or 2008 R2.  However, moving forward this counter should provide similar information about VMs waiting to execute on physical processor resources similar to what we’ve had with VMware CPU Ready.

The Accidental DBA (Day 24 of 30): Virtualization High Availability

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

Virtualization has been popular for many years, and more and more businesses are moving low-latency line-of-business applications like SQL Server into virtual machines every day.  One of the common reasons that I’ve heard over the years for moving SQL Server to a virtual machine is that high availability is built-in.  Usually what this translates into is, “We don’t need to use SQL Server availability options because the VM already has HA.”  This may be the case for some scenarios but as the saying goes “there’s no such thing as a free lunch.”  In this post we’ll look at the high availability provided to virtual machines and the considerations that need to be taken into account when determining whether or not to implement SQL Server high availability while using virtual machines.

Basic Virtual Machine HA

The high availability provided through virtualization depends on the configuration of the host environment on which the VMs are running.  Typically for a high-availability configuration for virtualization, multiple host servers are clustered together using a shared-storage solution on a SAN, NFS, or NAS for the virtual machine hard disks.  This provides resilience against failure of one of the host servers by allowing the virtual machines to restart on one of the other hosts.  Both Hyper-V and VMware provide automated detection of guest failures in the event of a problem and will restart the VMs automatically on another host, provided that sufficient resources exist to meet any reservations configured for the individual VMs.

VMs also gain better availability over physical servers through features like Live Migration/vMotion and the ability to perform online storage migrations to move the virtual hard disks from one storage array to another one available to the host(s).  This can be very useful for planned maintenance windows, SAN upgrades, or for balancing load across the host servers to maximize performance in response to performance problems. The VM tools that are installed in the guest, to improve performance and integration with the host server, can also monitor availability of the guest through regular ‘heart-beats’ allowing the host to determine that a VM has crashed, for example a blue screen of death (BSOD), and automatically restart the guest VM in response.

VM Specific HA Features

Addition to the basic high availability provided by virtualization, there are VM-specific HA features that are offered by both VMware and Hyper-V for improving availability of individual VMs.  VMware introduced a feature for VM guests called Fault Tolerance in vSphere 4 that creates a synchronized secondary virtual machine on another host in the high-availability cluster that is lock stepped with the primary.  In the event of a host failure, guests that have Fault Tolerance enabled immediately failover to their secondary in a manner that is similar to a vMotion operation, preventing application downtime from occurring.  At the same time, a new secondary VM is created on another host inside of the cluster and synchronized with the new primary maintaining the fault tolerance of the guest inside of the environment. Unfortunately this is limited to a single virtual CPU, even in ESX 5.1 so it’s not likely to be used with SQL Server VMs.

Hyper-V does not currently provide an equivalent feature to VMware Fault Tolerance, even in Server 2012.  Hyper-V 2012 introduced Replica’s which are provide disaster recovery through replication to a remote data center with manual failover, but it doesn’t provide automated failover in a similar manner to Fault Tolerance.

SQL Server Considerations

The primary consideration I ask about when it comes to SQL Server high availability on virtualization is whether or not it is acceptable to incur planned down times associated with routine maintenance tasks like Windows Server OS patching, and SQL Server patching with Service Packs or Cumulative Updates. If a planned down time is possible to allow for patching then the high availability provided by virtualization may meet your business requirements.  However, I would always recommend testing a host failure to determine the amount of time required to detect the failure, and then restart the VM on another host, including the time required for Windows to boot, and SQL Server to perform crash recovery to make the databases available again.  This may take 3-5 minutes, or even longer depending on the environment, which may not fit within your downtime SLAs.

If planned down time for applying server patches is not possible, you will need to pick a SQL Server availability option using the same considerations as you would for a physical server implementation.  Support for Failover Clustering of SQL Server on SVVP-certified platforms was introduced in 2008, and Database Mirroring and Availability Groups are also supported under server virtualization.  However, none of the SQL Server high availability options are supported in conjunction with Hyper-V Replicas, so there are additional limitations that need to be considered whenever you combine features on top of server virtualization.  One of the limitations that should always be factored into the decision to virtualize SQL Server and use SQL native high availability options is the added complexity that exists by adding the virtualization layer to the configuration.

The Accidental DBA (Day 12 of 30): Backups: VM Snapshots

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

In the final post in the Backup section of our Accidental DBA series, I’m going to a look at backups using virtual machine (VM) snapshots, which are popular among VM administrators but may not be the right solution for your SQL Server recovery needs, depending on your RPO and RTO requirements.

VM Snapshot Backup Support

The “Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment” Knowledge Base article includes information about the support of virtualization-aware backups for SQL Server. As long as the backup solution uses the volume shadow-copy service (VSS) to perform volume-based snapshots of the virtual machine hard disks, those backups are supported for recovery with SQL Server in a virtualized environment. Some examples of these tools include:

  • Hyper-V backup
  • Veeam
  • vRanger

There are also many other VM backup tools available; this is just a brief list of the ones that come immediately to my mind. Additionally, many of the traditional backup products like Symantec NetBackup also support VSS-based backups of virtual machine images as well. VSS integration is required to provide an application-consistent backup of the databases contained within the SQL Server instance. This ensures that disk I/O activity is quiesced (frozen) properly prior to the snapshot being created. Any snapshot functionality that does not use VSS may leave SQL Server in an inconsistent state, and this may include standard VM snapshots that can save the VM memory and device state to disk without quiescing through VSS, unless specifically configured to do so.

Limitations of VM Backups

VM snapshot-backup solutions are popular with VM and server administrators because they standardize the backup implementation across the enterprise and remove the need for application-specific backup configurations. While this is generally a benefit, there are other considerations that should be evaluated when it comes to relational database management systems (RDBMS) like SQL Server. I specifically say RDBMS here because these same considerations need to be applied to any RDBMS and not just SQL Server for backups. Below are a few of the considerations that you should keep in mind while evaluating whether to use VM snapshot backups for SQL Server.

Point-in-time Recovery?

One of the features provided by VM backup solutions is the ability to perform a point-in-time restore of the virtual machine image, or even the files contained within the VM. While it is true that you can restore to a point in time, that point is simply the last snapshot backup point for the VM being backed up. Depending on the frequency of the backups that are occurring, this might meet your business recovery requirements, but it doesn’t provide the same capabilities as native SQL Server backups, which provide the ability to restore to any point in time using a combination of the latest full backup of the database, and all of the transaction log backups since that full backup (using the full recovery model, or the bulk_logged recovery model with some restrictions). If you are not currently using the full recovery model for databases and also taking transaction log backups at regular intervals, the point-in-time recovery provided by VM snapshot backups can reduce the risk of data loss over only performing daily full or differential SQL Server backups of the database, depending on the snapshot backup interval configured for the VM.

Single Database Restore?

Depending on the tool being used for VM backups, it may or may not be possible to restore a single database from the VM backup without first having to restore the entire VM image from the backups to obtain access to the database files contained in the image. Some tools do allow guest-OS file indexing of the VM backups which allows for individual files to be restored from the VM backup without having to restore the entire VM image. Other tools also offer the ability to mount a VM backup as a VM that is boot-able to allow for object-level recovery of individual tables through data transfers back to the production VM SQL Server.

Transaction Log Clearing?

For databases that use the full or bulk_logged recovery models, transaction log clearing only occurs when the log is backed up using SQL Server native backup commands. The VSS backup will not cause log clearing to occur inside of SQL Server on it’s own. Some of the VM backup tools offer options to perform transaction log clearing, but you need to be very careful with these options, especially if you have concurrent SQL Server backups being performed for the VM. The way that certain tools clear the transaction log during a VM snapshot backup is to issue a subsequent command to the SQL Server, BACKUP LOG <database_name> TO DISK =’NUL’, which dumps the transaction log records to nowhere, essentially throwing them away completely and breaking the log backup chain for any native SQL Server backups being performed. If you are still using native SQL Server backups, it is recommended that the VM snapshot backups be configured to not truncate the transaction logs for the databases.

Summary

While VM snapshot backups can provide a simplified method of backing up SQL Server VMs, there are some trade-offs that exist when using them that should be considered to ensure that you are going to be able to meet your business RPO and RTO requirements. The ability to restore a VM, database files, or even an individual object inside of a database to the point-in-time of the last snapshot backup can help minimize the data loss associated with a crash or accidental deletion of data inside of a database.

However, contrary to popular belief, it is not possible to restore to an intermediate point-in-time that exists between the VM snapshot backups, so if that level of recovery is needed or expected, you will still need to perform SQL Server native backups of the databases. Paul explained how to get near zero data loss using SQL Server backups in his post The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy.

This level of recovery currently can only be accomplished through the use of SQL Server native backups.