Mar 232017
 

I am proud to announce that we contributed to the latest revision of the Microsoft SQL Server on VMware best practices guide, freely available at this address. This document outlines some of the common VM-level tweaks and adjustments that are made when running enterprise SQL Server VMs on VMware platforms. This guide is considered a must-read if you manage these sorts of SQL Servers, which cannot be treated as general purpose virtual machines.

This guide was recently updated for vSphere 6.5, and we consider it an absolute must for your enterprise management library!

Special thanks go to Niran Even-chen for his efforts in making this guide continuously current, plus Allan Hirt and Deji Akomolafe for their incredible contributions as well.

Mar 252012
 

Microsoft recently released SQL Server 2012 to the wild just a couple of weeks ago. Even though this release is in its infancy, organizations and their database administrators should immediately begin to explore and plan for the adoption of this release into their corporate technology roadmap. Multiple features in this release are individually tremendous, and as a whole, can change the way a business thinks about database management. Database recovery and high availability were just revolutionized.

Prior to SQL Server 2012, SQL Server had multiple solutions for high availability and disaster recovery built in, but all had at least one major limitation. For example, failover clustering configuration and management is nontrivial in its complexity as well as its single point of failure shared disk requirement.  The shared disk configuration on VMware forced administrators to use Raw Device Mappings (RDMs) or in-guest disk presentation. Database mirroring creates fantastic failover and high availability features, but lacks a simple IP address that legacy applications can connect to and utilize the failover features.

Additional features are included in SQL Server 2012 that should make database administrators even happier. These new features range from offloading backups to different servers to reduce their performance impact, index improvements that dramatically simplify their management, self-contained databases that make migrations and transportability simpler, and tools and features to allow much more granular insight into processes and events inside the engine itself.

From our perspective, our best practices for installing and configuring SQL Server on a VMware vSphere environment have not changed much with this latest release, but as always we are actively evaluating the new features and working to refine our recommendations to make this version perform at top speed.

New features that Database Administrators Should Care About

AlwaysOn
In my opinion, SQL Server 2012 AlwaysOn is the single best feature to come out of the SQL Server team in this release. Database recovery and availability were just revolutionized.

AlwaysOn takes the automatic failover process and virtual IP features of Microsoft Failover Clustering (MFC) but removes the dependency on shared disks. On VMware, this removes the complexity of RDM or in-guest disk management. It also adds functionality that was originally added for mirroring of databases between two servers, but allows up to four independent systems to be part of this mirroring replication group. Up to four instances can be members of the AlwaysOn cluster, and the replication can be split between synchronous and asynchronous to different members at the same time. Secondary replicas can even be opened up for read-only activity without having to deal with painful database snapshots. These same replicas can even be used to move the impact of a database backup off of the primary system and onto the replicas.

Failover can be configured to be automatic and seamless to all applications, including legacy applications that could not be configured for a failover node in previous releases. SQL Server’s disaster recovery options just blended with its high availability tooling to provide a seamless, simple to manage package.

Database availability groups can be configured, which lets multiple databases fail from one system to another. This eliminates the scripted failover processes if a single application requires more than one database on an instance, and all of the databases are mirrored. You can even fail over across subnets now.

Note: Database mirroring is still enabled in SQL Server 2012, but it is now deprecated in favor of AlwaysOn. AlwaysOn currently requires Enterprise Edition. Our collective prediction is that when database mirroring is finally removed from a future version, AlwaysOn will available in Standard Edition.

Extended Events GUI

Extended Events allow a DBA to extract event-driven information about the engine. DBAs must learn this tool, as the legacy Profiler tool is deprecated and going away soon. Extended Events were first introduced in SQL Server 2008, but were very difficult to manage and maintain as no GUI existed, the T-SQL required to create them was nontrivial, and the output was XML and took time to parse. Industry adoption was negligible.

SQL Server 2012 adds a GUI to not only manage the Extended Event sessions, but adds a Data Viewer to let you view the real-time output of your Sessions. These tasks are much lighter on the system than a Profiler trace, and can be stored on the server for re-use at any time.

Contained Databases

One of the trickiest features that constantly plague database administrators is how to migrate one or more databases to a different instance. Part of these headaches are due to the structural nature of the engine itself, namely in how it maps system logins to database users, or scheduled jobs are managed, or servers linked together, or even more obtuse items such as default and database collations (SAP anyone?).  Contained databases now bundle and contain these entries into more manageable items to save DBAs hours during planning and deployments.

ColumnStore Indexes

ColumnStore indexes are the engine’s first column-based indexing feature. It allows for a simple creation of a covering index on one or more columns, but splits each column into its own group of pages. Compression ratios benefit from having similar values on the same group of pages, and aggregate queries not only become sequential but must read fewer pages. The performance boosts are reported to be substantial.

The best use for this feature is for reporting database tables, as these indexes are (currently) read only. This sounds bad, but an existing architecture could be tweaked with partitioning so that enabling this feature does not break an application.

A great whitepaper from Microsoft is entitled Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0 (http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf).

Licensing

SQL Server 2012 has simplified to just three main editions – Standard, Business Intelligence (BI), and Enterprise editions. Datacenter Edition is no longer present. It has also shifted from socket-based licensing to core-based. Server and Client Access Licenses (CALs) are still available, but only for Standard and BI editions. The virtualization of SQL Server 2012 now also recommends the organization to purchase Software Assurance (SA). If SA is not purchased, vMotion activities are artificially greatly hindered.

What do all of these changes mean for you?

You must now purchase at least four licenses, as it requires a minimum of four core licenses per socket. They are sold in two core packs.

If you virtualize, SA must be purchased if you wish to allow the hypervisor to freely move your virtual machines to different cluster nodes; otherwise you are subjected to a 90-day gap between vMotion operations rule. Purchase licenses for all of your cores in the cluster (or sub-cluster if using means to confine your VMs to certain machines) and you can run “unlimited number of instances [up to] the number of core licenses assigned to the server” (SQL Server 2012 Licensing Quick Reference Guide, page 10).

The total price for your organization to run this technology has probably just gone up.

If you are interested in more details around the new licensing model, review the SQL Server 2012 Licensing Quick Reference Guide (http://download.microsoft.com/download/7/3/C/73CAD4E0-D0B5-4BE5-AB49-D5B886A5AE00/SQL_Server_2012_Licensing_Quick_Reference_Guide_Mar2012.pdf).

SQL Server 2012 on VMware vSphere Best Practices

  • First and foremost, go through the exercise of verifying and validating the amount of licensing you need to successfully maintain compliance with Microsoft licensing regulations.
  • Tweak your BIOS settings for optimal performance: set CPUs to high performance mode, enable turbo boost
  • Never overcommit resources! Use VM resource reservations. Never disable the balloon driver.
  • Use eagerzeroedthick disks for all of your SQL Server data, log, and tempdb object drives
  • Use multiple virtual SCSI adapters to optimize I/O distribution
  • Evaluate the Paravirtual SCSI driver (PVSCSI) to check for possible CPU reduction and/or I/O performance improvements
  • Evaluate the VMXNET3 virtual network adapter driver for possible CPU reduction and network performance improvements
  • Schedule processor affinity for specific NUMA nodes (VM settings – Resources – Advanced CPU – Scheduling Affinity and NUMA Memory Affinity)
  • Ensure that your virtual machine CPU NUMA configuration best matches your physical machine’s configuration
  • Disable physical network adapter interrupt moderation, virtual interrupt coalescing, and Large Receive Offload (LRO)
  • Configure and use Windows Large Pages and ‘Lock Pages in Memory’ privileges for service account
  • Configure and use Instant File Initialization
  • Set Min and Max Server Memory in SQL Server
  • Ensure absolute redundancy and eliminate single points of failure in your AlwaysOn design. Use anti-affinity rules to ensure that AlwaysOn members are never placed on the same ESXi hosts

Stay tuned for more details on AlwaysOn in the near future! This post will be cross-posted on houseofbrick.com soon.