Virtualizing SQL Servers Leads to Maintenance Savings

House of Brick Principal Architect

This blog post analyses the raw numbers behind routine maintenance and demonstrates how virtualization reduces the number of servers that need to be periodically patched. Virtualization also reduces the effort needed to patch the environment. The numbers in this case study are approximations and should be adjusted when the time comes to architect the full production environment. These numbers are based on a one-to-one migration from physical to virtual machines, and does not take into consideration any database consolidation or major adjustments to the architecture.

Case Study

This case study is based on a larger sample environment. Currently, the sample environment consists of 500 clustered instances in a SQL Server environment. All database servers in the environment are clustered for configuration uniformity. The current cluster architecture has groups of three active nodes. Each cluster has an additional failover hot-spare, so a total of 667 physical servers are used.

casestudy1

Based on logical divisions, 1/3 of the environment is focused on production purposes, another 1/3 on QA, and the last 1/3 on development. This count leaves roughly 165 instances per environment.

casestudy2

A rough estimate, based on the production analysis from the beginning of the project, has 25% of the current production servers as bound by an SLA lower than four minutes. (This is the approximate time it takes VMware HA to detect a failure and complete a VM restart on a new server.) These servers are prime candidates for the highest availability classification, where some form of high availability outside of VMware HA or clustering is used. In this example, SQL Server mirroring is used. This assumption brings the required number of mirrored instances to 42, and an additional seven mirroring witness servers should be used for faster failover. If SQL Server 2012 AlwaysOn is used, these witness servers are not used.

casestudy3

In a mixed environment like this, a very conservative consolidation ratio is five to one. For the production nodes, this number could be less. For development nodes, this number could be dramatically higher. With the assumptions listed above, the current physical environment requires 667 physical servers. The same assumptions in virtual environment would require only 132 VMware servers. That’s an astounding 20% of the physical environment footprint. The maintenance savings alone from this decrease in physical equipment is tremendous – the time spent maintaining physical device failure, power, cooling, etc. all benefit from this change.

The physical environment requires 667 Windows-based servers to be routinely patched. These patches include operating system patches, SQL Server instance patches, and hardware BIOS and driver updates. In a perfect world, this level of maintenance is performed at least quarterly. In the virtual environment, this number drops to 544 virtual machines. That’s 123 fewer servers to be routinely patched than in the physical environment. The time spent updating these 544 servers is also reduced, as the hardware BIOS and driver update requirement does not exist. Since redundancy and reserve resources will be built into the virtual infrastructure design, most, if not all, of the hardware maintenance that would normally take place at night and on weekends can now be performed during the business day. By simply vMotioning virtual machines away from hosts targeted for maintenance, there is no impact to the normal operation of the business.

casestudy4

Routine maintenance and patching by the system administrators and DBAs become simpler. While the number of virtual servers required to patch is 82% of the physical machine count, VMware snapshots can be used to simplify the rollback plans. For every patch and update performed, a rollback plan should be required, and developing these plans requires time. This time could be better spent on more proactive tasks. VMware snapshots can be incorporated into this process. Based on the maintenance scenario, the rollback plan becomes as simple as two clicks to revert a snapshot. This saves a substantial amount of time over a given work-year.

The DBA will treat the virtual servers as they always have in terms of maintenance. No additional effort is required by the DBA to routinely patch these virtual systems versus their physical equivalent.

Table of Contents

Related Posts