House of Brick Principal Architect

A couple of weeks ago Microsoft released SQL Server 2012 to the wild. Even though this release is in its infancy, organizations and their database administrators should begin to explore it 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 have just been revolutionized.

Prior to SQL Server 2012, SQL Server had multiple solutions for high availability and disaster recovery built in. However, each solution 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 in order to utilize the failover features.

Additional features are included in SQL Server 2012 that should make database administrators even happier. These new features include: 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 a much more granular insight into processes and events inside the engine itself.

Our best practices for installing and configuring SQL Server on a VMware vSphere environment have not changed much with this latest release. 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 our opinion, SQL Server 2012 AlwaysOn is the single best feature to come out of the SQL Server team in this release, revolutionizing database recovery and availability.

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, originally added for mirroring of databases between two servers, that 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 also 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. This now includes legacy applications that could not be configured for a failover node in previous releases. SQL Server’s disaster recovery options have been 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.

This feature alone is enough to make us re-tool existing environments and go back to former clients and advocate for the immediate investigation of SQL Server 2012.

Note: Database mirroring is still enabled in SQL Server 2012. However, 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 future versions, AlwaysOn will be available in Standard Edition.

Extended Events GUI
Extended Events allows a DBA to extract event-driven information about the engine. DBAs must learn this tool, as the legacy Profiler tool is deprecated and will soon be gone. Extended Events were first introduced in SQL Server 2008. They were very difficult to manage and maintain because 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 manage the Extended Event sessions. This new version also 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. These headaches are due to the structural nature of the engine itself. Namely, in how it maps system logins to database users, how scheduled jobs are managed, 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 also 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 might sound bad, but an existing architecture could be tweaked with partitioning so that enabling this feature does not break an application.

Microsoft released a great whitepaper on this subject entitled: “Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0”.

Licensing

SQL Server 2012 has simplified to 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 that the organization 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 should 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.

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 CPU reduction and/or I/O performance improvements.
• Evaluate the VMXNET3 virtual network adapter driver for 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.

As customer adoption of this new version increases, these best practices will be explored, baselined and benchmarked, reviewed, and any new best practices that we develop will be shared on this blog.

Stay tuned – coming soon we plan on developing a “SQL Server 2012 AlwaysOn on VMware” series of posts to help you get the most out of your SQL Server 2012 installations.