by Barnaby Jeans

In a previous article, we talked about some best practices for virtualizing Microsoft SQL Server on vSphere and to follow it up I want to take some time to answer a common question - what is the licensing impact when virtualizing SQL server.

I have just completed a 3 week, 5 city, tour across Canada talking to companies about virtualizing Business Critical Applications (SQL Server, Exchange, SharePoint, Oracle, SAP) on vSphere.  One of the most frequent questions has to do with how to license SQL Server and understanding the Microsoft licensing cost implications as they consolidate their physical environments to virtual.  During those conversation they quickly come to appreciate that in many cases their existing licenses cover them for more virtual machines than they had planned to deploy.

As Microsoft offers a number of different licensing options for its software, you should consult with your Microsoft representative to obtain the most accurate licensing information for your situation.

Below I've provided a summary based on the available documentation to help you understand your options when it comes to running SQL Server on vSphere.

SQL Server 2008 R2

From the SQL Server 2008R2 Licensing Quick Reference Guide

Licensing for Virtualization Under the Per Processor Model
The number of operating system environments (OSEs) in which you may run instances of SQL Server 2008 R2 under the Per Processor model depends upon the edition you license and whether or not you license all of the physical processors with a Per Processor License.

Licensing All Physical Processors If you license all of the physical processors on the server (one license per physical processor), you may run unlimited instances of the SQL Server software in the following number of OSEs (either physical or virtual):

Edition	# of OSEs in Which You May Run SQL Server
SQL Server 2008 R2 Datacenter	Unlimited
SQL Server 2008 R2 Enterprise	Up to 4 per license

 

SQL Server 2012

With the release of SQL 2012 Microsoft has changed the licensing and has retired the Datacenter Edition as well as moved to Core-Based licensing.

From the SQL Server 2012 Licensing Quick Reference Guide (March 2012)

SQL Server 2012 offers expanded virtualization rights, options and benefits to provide greater flexibility for customers deploying in virtual environments. When deploying SQL Server 2012 software in virtualized environments, customers have the choice to license either individual virtual machines (VMs) as needed, or to license for maximum virtualization in highly virtualized, private cloud, or dynamic environments. Maximum virtualization can be achieved by licensing the entire physical server with Enterprise Edition core licenses and covering those licenses with Software Assurance.

In looking at the information above, you’ll note that SQL Server 2008 R2 Enterprise Edition already provides you with the ability to run 4 virtual machines with an existing license, and in most cases, customers are able to consolidate multiple physical SQL Server environments down to a much smaller number of physical hosts running SQL Server in vSphere.

Additionally, both SQL Server 2008 R2 Datacenter Edition and SQL Server 2012 Enterprise Edition with SA provide you the ability to run as many VMs on a physical host as the hardware allows.  This makes it very easy to include SQL Server in your Private Cloud initiatives.

The last documents that you’ll want to be familiar with are the Microsoft Application Server License Mobility brief and Licensing Microsoft Server Products in Virtual Environments.  Together the documents mentioned above provide a good summary of your options.  Once I walk customers through some of the information in these documents, they realize that in addition to making sense technically and operationally, it can also lead to reduced SQL Server license costs as they virtualize SQL Sever on vSphere.

As always, we suggest you consult with your Microsoft representative to obtain the most accurate licensing information for your situation.

By now, a lot of tier-2, tier-3 SQL Servers has been virtualized. Customers are seeing more availability, better agility with those SQL Servers running in a virtualization environment. That's leading customers to consider moving tier-1 mission critical SQL Servers onto the virtualization environment. A properly configured ESX is crucial to the successful deployment of a tier-1 SQL Server. In this post, I would like to talk about some storage configuration guidelines that would help maximize SQL Server performance on VMware.

Use eagerzeroedthick disk

Virtual machine disk files can be deployed in three different formats: thin, zeroedthick, and eagerzeroedthick. Thin provisioned disk enables 100% storage on-demand, disk space is allocated and zeroed out at the time disk is written. Zeroedthick disk is pre-allocated, but blocks are zeroed out by the hypervisor during the first time the disk is written. Eagerzeroedthick disk is pre-allocated and zeroed initialized during provision time. There is no additional cost for zeroing out the disk during run time.

For optimal disk access performance, use eagerzeroedthick disks for SQL Server data, transaction log, and tempdb files.

Ensure alignment of VM virtual disks

Partition alignment is a well discussed topic for SQL Server. An unaligned partition results in a track crossing and additional IOs, incurring penalty on latency and throughput. See figures below.

On VMFS, partition alignment should be configured at the VMFS level and Windows level. Beginning with ESX v3.x, VMware automatically aligns new partitions to a 64KB boundary when you create a VMFS from vCenter. In ESX v5.0, new partition is automatically align to the 1MB boundary. You may also manually align your VMFS partition. See http://www.vmware.com/pdf/esx3_partition_align.pdf for instructions on manual alignment.

For Windows 2008 and above, partition alignment is usually performed by default. The default for disks larger than 4 GB is 1 MB; the setting is configurable through Windows registry setting HKLM\SYSTEM\CurrentControlSet\Services\VDS\Alignment. However the default partition alignment may be altered if system is created with OEM setups. It's a good practice to always check to confirm partition alignment. For more information, see Disk Partition Alignment Best Practices for SQL Serverhttp://msdn.microsoft.com/en-us/library/dd758814.aspx.

Optimize with device separation

SQL Server files have very different disk access patterns. Placing SQL Server binary, data, transaction log, and tempdb files on separate storage devices provides maximum flexibility, and improves performance. Consider the following best practices for deploying a tier 1 mission critical SQL Server.

• Place SQL Server binary, log, and data files into separate VMDKs. In additional to the performance advantage, separating SQL Server binary from data and log also provides better flexibility for backup. The OS/SQL Server Binary VMDK can be backed up with snapshot-based backups, such as VMware Data Recovery. The SQL Server data and log files can be backed up through traditional database backup solutions.
• Maintain 1:1 mapping between VMDKs and LUNs. When this is not possible, group VMDKs/SQL Server files with similar I/O characteristics on common LUNs.
• Use multiple vSCSI adapters. Place SQL Server binary, data, log onto separate vSCSI adapter optimizes I/O by distributing load across multiple target devices.
• Test prior to deploying SQL Server. Storage sub-system should be able to achieve IO requirements with an acceptable latency in additional to capacity requirements. IOMeter can be used to simulate SQL Server IO patterns. Below table are example SQL IO patterns that can be used to test storage sub-system performance without invoking an actual SQL Server workload.

-Wanda

Wanda He, Technical Solutions Architect

This blog is part of a series on Virtualizing Your Business Critical Applications with VMware. To learn more, including how VMware customers have successfully virtualized SAP, Oracle, Exchange, SQL and more, visit www.vmware.com/go/virtualizeyourapps.

SQL Server patching is a common use case for high availability deployments. When people think about minimizing down time for patch upgrades, most people will think of SQL Server failover clustering or SQL Server database mirroring. Those are the two SQL Server native availability features that support rolling patch upgrade. Did you know that you could also perform rolling patch upgrades with just a standby virtual machine (VM) if you are running SQL Server on VMware?

With VMware vSphere, a virtual disk can be hot removed or hot added onto a VM without impacting services running in the VM. Given that, you could put together a rolling patch upgrade solution that's similar to the SQL Server failover cluster share everything architecture by using a standby VM. The SQL Server data and log disks are shared between the primary and standby VMs, although they are only assigned to one VM at a time. The primary and the standby VM each runs an identical copy of the SQL Server binary. When you need to apply SQL Server patches to the primary VM, you can switch ownership of the SQL Server data and log disks from the primary VM to the standby VM. The standby SQL Server VM can continue servicing application requests. The followings describe the steps and process flows for the solution.

Step 1: Configure standby VM

• Create a standby SQL Server VM, if one does not yet exist, using VMware templates or cloning technologies.
• Confirm SQL Server logins, job, and other instance level configurations are configured identically between the standby and primary VM.

Step 2: Patch standby VM

• Apply service patches to the standby SQL Server VM.

Step 3: Hot remove SQL Server resources from primary.

• On the primary VM, stop client connections to the database(s). One way this could be accomplished is to disable the virtual machine network interface. Connection to the virtual machine can be made using a management interface for Remote Desktop Service connections or vSphere client console connection.
• Detach database(s) from primary SQL Server by issuing the sp_detach_db T-SQL command.
• From Windows Disk Management right click the data and log volumes and select Offline to prepare them for hot-remove.
• From the vCenter client, remove SQL Server data and log virtual disk(s) from the running primary SQL Server VM.

Step 4: Hot add resources to the SQL Server standby VM.

• From the vCenter client, add the virtual disk(s) containing the SQL Server data and log files to the standby VM.
• From Windows Disk Management, bring the disks online if needed and confirm that the disk(s) are mounted with the correct drive letter(s) assigned.
• Attach SQL Server database(s) by issuing the sp_attach_db T-SQL command(s).

   

  

Step 5: Switch role

• On the standby VM, enable application network traffic to the VM.
• The old standby VM is the new primary, SQL Server service is resumed for the application(s).
• The old primary VM is ready for service patching and may be left in the standby role if desired until the next patching cycle.

During steps 3 to 5, application(s) would experience temporary connection issues to the SQL Server. Similar to the failover clustering or database mirroring requirements, reconnection is expected to be handled by the application layer, with zero data loss, and any in flight transactions would need to be resubmitted. All operations in steps 3 through 5 are metadata only operations, and are expected to execute instantaneously.

If you would like the ability to do rolling patch upgrades, but don't want to put up with the cost and complexity of maintaining a failover cluster or a mirrored database, this solution provides a viable alternative for you. For those of you that are into scripting, the process flow can be automated using PowerShell and PowerCLI.

-Wanda

Wanda He, Technical Solutions Architect

DBAs are being challenged to provide 24x7 database services to application owners with the flexibility and autonomy they expect while keeping the infrastructure as simple and economical as possible. Traditional databases running on fixed physical hardware are often over-sized, under-utilized, protected by complex, expensive clustering solutions, and require rigorous processes for version control and continued application compatibility. Meanwhile, organizations are increasingly virtualizing their enterprise applications in production and with a robust platform like VMware, mission-critical databases are no exception.

If knowledge is power, then knowing the capabilities of the underlying intelligent virtual infrastructure is certainly powerful for the DBA. Experienced Database Administrators (DBAs) recognize that virtualization unlocks capabilities that were impossible in physical environments.

Most importantly, the value of virtualization goes far beyond basic consolidation. Virtualizing database applications on vSphere can improve application Quality of Services (QoS), and accelerate application lifecycles while significantly reducing application costs. In this brand new DBA Guide to Databases on VMware, we discuss database performance on VMware, examine the general tasks for DBAs, and introduce VMware technologies and tools that assist DBAs in design, implement, test, operate, and maintain databases in a virtual environment.

The following topics are covered in detail in this guide:

• DBA's roles and responsibilities
• Understanding VMware performance
• Design, Develop and Test databases on VMware
• Migrating existing databases to VMware
• Securing and running databases on VMware
• Monitor and Troubleshoot database performance
• Frequently asked questions from SQL and Oracle DBA's

Get your free copy of this hot-off-the-press DBA Guide to Databases here. And if you are a vSphere veteran you might want to share it with your DBAs. Don't forget to share your feedback and questions in the comments section of this blog.

Kannan Mani, Oracle on VMware, Solutions Architect

Wanda He, Microsoft on VMware, Solutions Architect

Virtualization adds new software layers and new types of interactions between SQL Server and the hardware components. While the general methodology for monitoring and troubleshooting SQL Server performance does not change, VMware provides additional tools for monitoring and troubleshooting at the physical host level. This is the first of a series of Monitoring and Troubleshooting SQL Server performance on VMware blogs. In this blog, I'd like to discuss how to effectively monitor SQL Server performance on VMware.

SQL Server and Windows provide a number of tools for monitoring performance. Those are still the primary tools to use for identifying problem areas, and resource bottlenecks with SQL Server running on VMware. The methodologies stay the same as performance monitoring SQL Server in a physical environment.

• Performance Monitor: monitors overall resource usages. SQL Server specific counters: \SQLServer:*\*
• SQL Server Profiler: monitors performance at T-SQL statement level. Common events of interests are: SQL:BatchStarting/SQL:BatchCompleted, RPC:Starting/RPC:Completed, Errors and Warnings (All), P:StmtStarting/Completed, SQL:StmtStarting/Completed
• DMVs: monitors the internal health of SQL Server, sys.dm_*

For more information on using these tools for monitoring and troubleshooting SQL Server, see Troubleshooting Performance Problem in SQL Server 2008.

VMware provides two main tools for observing and collecting performance data at the host-level: the vSphere Client and the esxtop/resxtop utilities.

vSphere Client:

• GUI interface, primary tool for observing performance and configuration data for one or more ESX/ESXi hosts
• Does not require high levels of privilege to access the data

esxtop/resxtop

• Command line utility that runs in interactive, batch, or replay mode
• Gives access to detailed performance data of a single ESX/ESXi host
• Provides fast access to a large number of performance metrics
• Requires root-level access

   

Among the performance data exposed by host-level monitoring tools, the following is a list of key metrics that can help you quickly isolate issues to a specific resource area such as CPU, memory, storage, or network. See VMware Communities: Interpreting esxtop Statistics and vCenter Performance Counters for a full list of counters.

Note** The measurement units reported in esxtop/resxtop and vSphere Client may be different. See the above VMware community links for details.

Of the CPU counters, the total used time indicates system load. Ready time indicates overloaded CPU resources. A significant swap rate in the memory counters is a clear indication of a shortage of memory, and high device latencies in the storage section point to an overloaded or misconfigured array. Network traffic is not frequently the cause of most database performance problems, but it is an area to monitor to make sure the network bandwidth is there.

When SQL Server is running in a virtual environment, any time-based measurements reported with SQL Server monitoring tools or Windows Perfmon may be inaccurate if the host machine resources are over-committed. The accuracy of the in-guest tools would depend on the guest OS and kernel version being used, and the total load of the VMware ESX host. The vSphere tools provide more accurate reporting on those measurements. See vSphere Performance Troubleshooting for more information. For effective monitoring and troubleshooting of SQL Server performance problem on VMware use the SQL Server tools and Perfmon for monitoring SQL Server resource usage, server state, and internal health. Focus on identifying performance bottlenecks instead of time-base measurements and then further correlate performance data collected from the host using vSphere tools. By focusing on key performance metrics you can quickly isolate issues to a particular resource area.

For example, for identifying a CPU bottleneck, instead of looking at the % Processor Time Perfmon counter, you can focus on the Process Queue Length counter. If there are a high number of processor queues and SQL Server Wait Statistics is showing a high number of waiters for worker, it's obvious a processor bottleneck exists. You can then use vSphere Client to verify:

1. If the number of virtual processors allocated to the SQL Server virtual machine is sufficient.
2. If the Shares, reservation, and limits for the virtual machine CPU Resource Allocation are configure properly.
3. If there is a host overload with sustained high %used, and high %rdy.

Similarly, you can effectively identify a disk bottleneck if the Disk Queue Length is high and a high number of SQL Server users are waiting on PAGEIOLATCH_EX, PAGEIOLATCH_SH. After a resource bottleneck is identified on the SQL Server virtual machine you can correlate statistics collected from the vSphere Client and esxtop/resxtop to identify any host level configuration issue that's affecting the SQL Server virtual machine performance.

Thanks for reading. On my next blog, I will discuss identifying and solving some common SQL Server on VMware performance problems. Stay tuned…

-Wanda

Wanda He, Technical Solutions Architect

Hello and welcome to the new VMware Business Critical Applications blog, a source for information, insight, and updates to help you virtualize your business critical applications on the VMware platform.

The blog will cover the following applications/functional areas and we will continue to add more over time:

• Microsoft Applications
  • Exchange
  • SQL Server
  • SharePoint
• SAP
• Oracle
• Java Applications 

We intend to use this blog to:

• Communicate best practices for deploying on the vSphere platform.
• Provide Updates on current solution projects and activities including solution rollouts, new collaterals etc.
• Provide Update on various product and solution enablement activities including events, webinars, partner engagements, trainings etc.
• Publish results of lab testing in the aforementioned functional areas.  Testing and results will include functional and technical use cases, workload characterization study and deployment  "how-to".  For official performance test results, please refer to the VROOM! Blog at http://blogs.vmware.com/performance/.
• Communicate general application design principles that we've discovered through research and work with our customers.
• Communicate step-by-step procedures for common application and infrastructure management tasks.
• Highlight VMware and partner products or features than can enhance the overall solution.
• Provide links to relevant online documentation.
• Provide insight on optimizing software licensing costs for virtualization.

We plan on posting regularly so grab the RSS feed or sign up for an e-mail alert to receive notification of new entries as they are posted.

For published whitepapers, including technical whitepapers and customer success stories, please visit our Business Critical Applications website at http://www.vmware.com/solutions/business-critical-apps/.  On the right-hand side, you'll find links to the individual application pages.

Thanks for reading!

Scott Salyer

Solutions Manager, VMware