Home > Blogs > Virtualize Business Critical Applications > Tag Archives: SQL Server

Tag Archives: SQL Server

Updated – Microsoft SQL Server on VMware vSphere Availability and Recovery Options

Choosing the right availability configuration for your SQL Server on vSphere can be a bit confusing as there are more than a few options to choose from, questions such as: Should I use vSphere HA if i’m using AlwaysOn? What are the implications of running different availability configurations on vSphere, and what are the best practices? 

This very popular guide called “Microsoft SQL Server on VMware vSphere Availability and Recovery Options” which outlines the availability options and best practices for SQL Server on vSphere and tries to answer these question, is now updated with the latest information.

Download the guide here

As always, any feedback is welcome,


Demo – Dynamically Enforcing Security on a Hot Cloned SQL Server with VMware NSX

VMware NSX is a software defined solution that brings the power of virtualization to network and security.VMware NSX

There are many great papers about NSX in general: for example here & here and many others, the purpose of this demo is not to dive into everything that NSX does, Instead I have focused on one capability in particular and that is the intelligent grouping of NSX Service Composer with the Distributed Firewall (DFW) and how to utilize it to make life easier for SQL DBAs and security admins, its doesn’t have to be only SQL Server, it can be any other database or application for that matter but for this demo I am focusing on SQL Server.

First, a bit of background: The NSX Service Composer allows us to create groups called “Security groups”. These Security groups can have a dynamic membership criteria that can be based on multiple factors: It can be part of the computer name of a VM, its guest OS name, the VM name, AD membership or a tag (tags are especially cool as they can be set automatically by 3rd party tools like antivirus and IPSs, but that is for a different demo)

These Security groups are than placed inside the Distributed Firewall (DFW) rules which allows us to manage thousands of entities with just a few rules and without the need to add these entities to the Security Group manually.

In the demo I have created an environment that is set with 0 trust policy, that means that everything is secured and every packet between the VMs is inspected, the inspection is done on the VMs vNIC level in an east-west micro segmentation way. That means that if a certain traffic is not defined in the DFW it is not allowed to go through.

This is something that wasn’t really possible to do before NSX

Our production app database is an SQL database and in the demo the DBA wants to hot-clone it aside for testing purposes, but obviously the cloned SQL Server needs to have some network traffic allowed to pass to it, yet it needs to be secured from everything else.

Instead of having the traditional testing FW zone with its own physical servers, I created the rules that apply to a test DBs in the DFW, created a dynamic membership Security Group, and nested that group in the rules. Now, any database server that I will clone which corresponds to the criteria will be automatically placed in the rules.  What’s really nice about this is that no traffic is going northbound to the perimeter FW because the packet inspection is done on the vNIC of the VMs (and only relevant rules to it are set on it) , no additional calls to security admins to configure the FW are needed after the first configuration has been made. This is a huge time saver , much more efficient in terms of resources (physical servers are now shared between zones) and a much more secure environment than having only a perimeter FW.

As usual any comment or feedback is welcome




Updated: Microsoft SQL Server on vSphere Best Practices Guide

Microsoft SQL server is the most virtualized enterprise mission critical application today. In recent years it has become a mainstream effort among VMware customers to virtualize critical databases to allow better agility and scale while increasing availability and operational efficiency.

This guide, now named “Architecting Microsoft SQL Server on VMware vSphere – Best Practices Guide” to reflect its focus on architecture and configurations of vSphere as well as SQL server for maximizing the benefits of virtualizing SQL server, is aimed at providing VMware customers and partners guidance on how to achieve best performance and efficiency with the latest versions of Microsoft SQL server and VMware vSphere.

In this guide there are also references to other VMware and third-party documents which we encourage the reader to consult for better understanding of the topics discussed.

Click here to download the guide.

Critical Factors to consider when virtualizing Business Critical Applications: (Part 1 of 2)

Over the past few years, there has been significant acceleration in adoption of the VMware platform for virtualization of business critical applications. When vSphere 5 was introduced with its initial support for up to 32 vCPU many of the vertical scalability concerns that existed earlier were addressed. This has been increased to 64 processors with the later vSphere 5.x releases ensuring that more than 99% of all workloads will fit vertically.

Having personally worked in IT infrastructure for more than 20 years with a strong focus on implementing and managing business critical applications, I see a general reluctance from application owners to virtualize business critical applications. When virtualizing business applications there are many critical factors one should consider.  I seek to address the typical concerns of application owners about Virtualization with this multipart series on Virtualizing BCA. Continue reading

Demo: SQL Server Rolling Patch Upgrade using Standby VM

For those who read my earlier post on SQL Server Rolling Patch Upgrade using Standby VM, and are interested in trying it out, below is a step-by-step demo video for your reference.


DBA Guide to Databases on VMware

DBAs are being challenged to provide 24×7 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



Monitor SQL Server Performance on VMware

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


  • 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


Figure 1. Performance Chart Views with vSphere Client

Figure 2. resxtop CPU Metrics


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.


Metric (esxtop/resxtop)

Metric (vSphere Client)

Host/Virtual Machine






CPU used over the collection interval (%)



Virtual Machine

CPU time spent in ready state




Percentage of time spent in the ESX Server VMKernel


Swapin, Swapout

Swapinrate, Swapoutrate


Memory ESX host swaps in/out from/to disk (per virtual machine, or cumulative over host)




Amount of memory reclaimed from resource pool by way of ballooning



NumberRead, NumberWrite


Reads and Writes issued in the collection interval




Average latency (ms) of the device (LUN)




Average latency (ms) in the VMkernel, also known as "queuing time"




Average latency (ms) in the guest. GAVG = DAVG + KAVG


MbRX/s, MbTX/s

Received, Transimitted


Amount of data transmitted per second


PacketsRx, PacketsTx


Packets transmitted per second


DroppedRx, DroppedTx


Dropped packets per second

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 He, Technical Solutions Architect