Today we will introduce you to the features of using SQL Server 2019 with Unity XT storage, as well as give recommendations for virtualizing SQL Server using VMware technology, configuring and managing the basic components of a Dell EMC infrastructure.
In 2017, Dell EMC and VMware published the results of the SQL Server Trends and Evolution Survey - "SQL Server Transformation: Towards Agility and Resiliency" () that draws on the experience of the Professional Association of SQL Server (PASS) member community. The results show that SQL Server database environments are growing in both size and complexity driven by increasing data volumes and new business requirements. SQL Server databases are now deployed in many companies, powering mission-critical applications and often underpinning digital transformation.
In the time since this survey, Microsoft has released the next generation of the database, SQL Server 2019. In addition to improving the core functions of the relational engine and data storage, new services and features have appeared. For example, SQL Server 2019 includes support for big data workloads using Apache Spark and the Hadoop Distributed File System (HDFS).
Alliance of Dell EMC and Microsoft
Dell EMC and Microsoft have a long history of collaboration on SQL Server solutions. Successful implementation of an end-to-end database platform such as Microsoft SQL Server requires the coordination of software functions with the underlying IT infrastructure. This infrastructure includes processor processing power, memory resources, storage, and network services. Dell EMC offers infrastructure for the SQL Server platform for any type of workload and application.
The Dell EMC PowerEdge server lineup offers a variety of processor and memory configurations. These configurations are suitable for a wide range of workloads, from small enterprise applications to the largest mission-critical systems such as enterprise resource planning (ERP), data warehouses, advanced analytics, e-commerce, and more. The storage line is designed to store unstructured and structured data.
Customers deploying SQL Server 2019 with Dell EMC infrastructure can work with structured and unstructured data using SQL Server and Apache Spark. SQL Server also supports combinations of client access, server-to-server, and server-to-storage communications technologies. The Dell EMC vision is based on a disaggregated model offering an open ecosystem. Organizations can choose from a wide range of industry standard network applications, operating systems and hardware platforms. This approach gives you maximum control over technologies and architectures, resulting in measurable cost savings and flexibility.
VMware provides virtualization of all critical infrastructure components that SQL Server needs to achieve high performance and consistency of operations. In addition to the private cloud, VMware also currently offers hybrid models for workloads spanning private and public cloud architectures.
Many organizations are turning to virtualization to reduce infrastructure costs, provide high availability, and simplify disaster recovery. 94% of SQL Server professionals surveyed report some level of virtualization in their environment. 70% of those who use virtualization have chosen VMware. 60% have a SQL Server virtualization level of 75% or more. In addition, the survey results strongly suggest that the high availability and disaster recovery implemented at the virtualization layer were important factors in the decision to virtualize SQL Server databases.
New Features in SQL Server 2019
The SQL Server 2019 database platform includes a wide range of technologies, features, and services that support mission-critical applications such as analytics, enterprise databases, business intelligence (BI), and scalable transaction processing (OLTP). The SQL Server platform has gained capabilities for managing data integration, data warehousing, reporting and advanced analytics, replication features, and managing semi-structured data types. Of course, not all clients or applications require all of these features. In addition, in many cases it is preferable to separate SQL Server services using virtualization.
Businesses today often have to rely on large amounts of data from a wide range of ever-increasing datasets. With SQL Server 2019, you can get valuable insights in near real time from all your data. SQL Server 2019 clusters provide a complete environment for working with large datasets, including using machine learning and artificial intelligence capabilities. Key new features and updates in SQL Server 2019 are listed in .
Dell EMC Unity XT Midrange Storage
The Dell EMC Unity storage series was launched almost three years ago and has since sold over 40 systems. Customers value this mid-range array for its simplicity, performance and cost effectiveness. Dell EMC Unity XT midrange platforms are shared storage solutions that provide low latency, high throughput, and low management overhead for SQL Server workloads. All Unity XT systems use an architecture with two processors (storage processors, SP) for I/O and active/active data operations. Unity XT dual SP uses full internal 000 Gb/s SAS connectivity and a patented multi-core architecture to deliver high performance and efficiency. Disk arrays allow you to expand storage capacity with additional shelves.
Dell EMC Unity XT, a new generation of arrays (hybrid and all-flash), delivers significant performance gains, improved efficiency, new features and services for multi-cloud environments.
The Unity XT architecture allows you to concurrently process data, reduce data volume, and support services such as replication without compromising application performance. Compared to the previous generation, Dell EMC Unity XT storage has doubled its performance and 75% faster response time. And of course, Dell EMC Unity supports the NVMe standard.
Storage with NVMe drives excels in latency-sensitive applications. For example, in applications such as massive databases, NVMe provides low latency and high peak data transfer rates. Reducing latency and increasing concurrency greatly improves read/write performance. It is no coincidence that by 2021, NVMe and NVMe-oF (NVMe over Fabric) connected all-flash arrays will account for roughly half of all external storage sales worldwide by XNUMX, according to IDC.
Storage efficiency is improved by data compression algorithms. Dell EMC Unity XT can reduce the amount of data up to five times. Another important indicator is the overall efficiency of the system. Dell EMC Unity XT uses 85% of system capacity. Compression and deduplication are performed inline - at the controller level. The data is stored in a compressed form. The system also automates work with data snapshots.
Easy-to-use Unity all-flash arrays with unified (block and file) access provide stable response times, integrate with cloud storage services, and support upgrades without data migration. In a basic configuration, this versatile storage system installs in 30 minutes.
A storage technology called "dynamic pools" allows you to move from static to dynamic storage expansion, provides high operational flexibility and ease of increasing system capacity. Dynamic pools save capacity and budget, and take less time to rebuild. Expanding the capacity and performance of Dell EMC Unity does not require data migration.
Many companies today use multiple public cloud services in conjunction with their on-premises infrastructure. Dell EMC Unity XT can function as a component of the Dell Technologies Cloud. This storage can be used in the public cloud and transfer data to the private cloud. In addition, Dell EMC Unity XT storage is available as a service. This is one of the Dell EMC Cloud Storage Services.
Cloud storage is growing in popularity as it allows you to increase your ROI by reducing infrastructure costs. Cloud Storage Services extends customer data centers to the cloud by providing Dell EMC storage (directly connected to public cloud resources) as a service. Third party providers can provide high speed (low latency) public cloud connectivity directly to Dell EMC Unity, PowerMax and Isilon systems in the customer's data center.
The Unity XT family includes Unity XT All-Flash, Unity XT Hybrid, UnityVSA, and Unity Cloud Edition systems.
Unified hybrid and flash arrays
Intel-based Unity XT Hybrid and Unity XT All-Flash storage systems provide an integrated architecture for block, file, and VMware VVols with support for network storage (NAS), iSCSI, and Fiber Channel (FC) protocols. The Unity XT Hybrid and Unity XT All-Flash platforms are NVMe ready.
Unity XT hybrid systems support multi-cloud environments. Multi-cloud support means extending storage to the cloud or deploying to the cloud with flexible resource usage options. Multi-cloud storage is designed to provide mobility, portability of data between several cloud platforms - private and public. This affects not only data movement processes, but also the organization of application access to data in several public clouds.
These hybrid arrays provide the following features:
- Scalable to 16PB raw capacity.
- Built-in data reduction features for all flash pools.
- Fast installation and configuration (on average it takes 25 minutes).
Solid state drive technology is advancing rapidly, and new revolutionary products will hit the market in the coming years. In the meantime, organizations will continue to replace traditional HDDs with SSDs to improve performance, ease of management, and energy savings. New generations of all-flash arrays will feature better storage automation, public cloud integration, and integrated data protection.
Unity XT All-Flash systems deliver speed, efficiency, and multi-cloud support. Their features:
- Double performance.
- Data reduction to 7:1.
- Fast installation and configuration (the process takes less than 30 minutes).
UnityVSA
UnityVSA is software-defined storage for VMware ESXi virtual environments using server, shared or cloud storage capacity. UnityVSA HA, a dual storage configuration of UnityVSA, provides additional fault tolerance. UnityVSA storage offers:
- Up to 50TB of full featured unified storage.
- Compatible with Unity XT systems and features.
- Support for high availability systems (UnityVSA HA).
- Connectivity as NAS and iSCSI.
- Data replication from other Unity XT platforms.
Unity Cloud Edition
For file synchronization and disaster recovery operations with the cloud, the Unity XT family includes the Unity Cloud Edition, which provides:
- Full-featured storage capabilities using software-defined storage (SDS) deployed in the cloud.
- Easily deploy block and file storage with VMware Cloud on AWS.
- Disaster recovery support, including testing and data analysis.
Unity XT All Flash for SQL Server
In the 2017 Unisphere Research report "SQL Server Transformation: Towards Agility and Resiliency" () 22% of respondents reported that they use flash storage technology in production (16%) or plan to do so (6%). 30% use hybrid arrays that include flash. 13% use direct-attached flash arrays. 13% back up SQL Server databases to flash storage.
This rapid adoption of all-flash storage for use with SQL Server means that Unity XT All-Flash arrays are particularly well suited for SQL Server developers and administrators. Unity XT All-Flash systems provide SQL Server developers and administrators with capabilities and performance that go beyond what typical Storage Area Networks (SANs) offer.
Unity XT All-Flash systems are NVMe-ready (for even higher performance and lower latency) in a 2U form factor, support dual-core processors, dual active/active controllers.
Models Unity XT All-Flash
UnityXT
Processors
Memory (per processor)
Max. number of drives
Max. raw capacity (PB)
380F
1 Intel E5-2603v4
6c/1.7GHz
64
500
2.4
480F
2 Intel Xeon Silver
4108 8c/1.8GHz
96
750
4.0
680F
2 Intel Xeon Silver
4116 12c/2.1GHz
192
1,000
8.0
880F
2 Intel Xeon Gold 6130
16c/2.1GHz
384
1,500
16.0
Details can be found in the array specifications ().
Storage Pools
Many SQL Server professionals know that all modern storage arrays provide the ability to group drives into larger storage units with a fixed level of RAID protection. Individual disk groups with RAID protection are traditional storage pools. While Unity XT hybrid systems only support traditional pooling, Unity XT All-Flash arrays also offer dynamic storage pooling. For dynamic storage pools, RAID protection applies to disk extents, which are units of storage smaller than a full disk. Dynamic pools provide more flexibility in managing and expanding disk pools.
Dell EMC provides storage pool management recommendations for maximum performance with minimum complexity. For example, it is recommended to minimize the number of Unity XT storage pools to reduce complexity and increase flexibility. However, setting up additional storage pools can be very useful in some cases, including when you need to:
- Support separate workloads with different I/O profiles.
- Allocate resources to achieve certain performance parameters.
- Allocate separate resources for multi-tenancy.
- Create smaller domains for failover
Storage volumes (LUNs)
How to trade-off between management and flexibility when choosing the number of volumes in an array? For maximum flexibility in Unity with SQL Server, it is recommended to create volumes for each database file. In practice, most organizations adopt a tiered approach, where critical databases are given maximum flexibility and less important database files are grouped into fewer, larger volumes. We recommend that you review all requirements for databases and any related applications, as data protection and monitoring technologies rely on file isolation and placement.
Multiple volumes are often difficult to manage, especially in virtualized environments. Virtualized SQL Server environments are a good example of where it can make sense to have multiple file types on the same volume. The database administrator or storage administrator (or both) must strike the right balance between flexibility and maintainability when determining how many volumes to create.
File storage
NAS servers host file systems on the Unity XT storage system. File systems can be accessed using the SMB or NFS protocols, and thanks to the multiprotocol file system, both protocols can be used at the same time. NAS servers use virtual interfaces to connect a host to SMB, NFS, and multiprotocol file systems, as well as VMware NFS storage and VMware virtual volumes. File systems and virtual interfaces are isolated within a single NAS server, allowing multiple NAS servers to be used for multi-tenancy. NAS servers automatically failover if the storage processor fails. Their associated file systems are also failover.
SQL Server 2012 (11.x) and later support the Server Message Block (SMB) 3.0 protocol, which allows you to share a network file for storage. For both standalone and failover cluster installations, you can install system databases (master, model, msdb, and tempdb) and Database Engine user databases with the SMB storage option. Using SMB storage is a good option when using Always On Availability Groups because the file share requires access to a highly available network resource.
Creating SMB file shares for deploying SQL Server with Unity XT storage is a simple three-step process: you need to create a NAS server, a file system, and an SMB share. The Dell EMC Unisphere Storage Management software includes a setup wizard to guide you through this process. However, there are some important considerations to keep in mind when hosting SQL Server workloads on SMB file shares that do not necessarily apply to the use of SMB file shares. Microsoft has compiled a list of installation and security issues along with currently known issues; see "Installing SQL Server with SMB File Storage" in .
Data Snapshots
Data has become a critical resource for companies, and today mission-critical environments require more than just redundancy. Applications need to be always online, provided with uninterrupted operations and updates. They also require high performance and data availability through options such as local snapshot replication and remote replication.
The Unity XT storage array offers block and file snapshot capabilities that share a common workflow, operation, and architecture. The Unity snapshot methodology provides a simple and effective way to protect data. Snapshots make it easy to restore data - roll back to an earlier snapshot, or you can copy selected data from a previous snapshot. The following table lists snapshot retention times for Unity XT systems.
Local and remote storage of data snapshots
Snapshot type
CLI
UI
REST
Manually
Scheduled
Manually
Scheduled
Manually
Scheduled
Local
1 year
1 year
5 years
4 week
100 years
Without restrictions
Remote
5 years
255 weeks
5 years
255 weeks
5 years
255 weeks
Snapshots are not a direct replacement for other data protection methods such as backups. They can only supplement traditional backup as a first line of defense for low RTO scenarios.
The Dell EMC Unity snapshot feature includes data reduction and advanced deduplication. Snapshots also benefit from the space savings that are achieved on the original storage resource. When you take a snapshot of a data reduction-enabled storage resource, the data at the source may be compressed or deduplicated.
Here are some notes regarding database recovery when using snapshots with SQL Server databases:
- All SQL Server database components must be protected as a data set. When the data and log files are on different LUNs, those LUNs must be part of a consistent group. A consistent group ensures that a snapshot is taken simultaneously on all LUNs in the group. When data and log files are on multiple SMB file shares, the shares must be on the same file system.
- When restoring a SQL Server database from a block-based snapshot, if the SQL Server instance must remain connected, use a Unisphere host attach. For file-based recovery, an additional SMB share is created using a snapshot as the source. Once the volumes are mounted, the database can be attached under a different name, or the existing database can be replaced with the restored one.
- When performing a restore using the Unisphere Snapshot Restore method, take the SQL Server instance offline. SQL Server is not aware of restore operations. Taking an instance offline ensures that volumes are not corrupted by database writes prior to recovery. Once the instance is restarted, SQL Server Disaster Recovery will bring the databases back into a consistent state.
- Allow snapshots for multiple storage objects at the same time, and then ensure that the system is in the recommended operating modes when monitoring the system before enabling additional snapshots.
Automation and Scheduling Snapshots
Snapshots in Unity XT can be automated. The following default snapshot options are available in Unisphere Storage Management: Default protection, Shorter retention protection, and Longer retention protection. Each option creates daily snapshots and saves them for different periods of time.
You can choose one (or both) of the scheduling options - every x hours (from 1 to 24) and daily / weekly. Daily/weekly snapshot scheduling allows you to specify specific times and days for snapshots. For each option selected, a retention policy must be set, which can be configured to automatically delete the pool or temporarily hold.
For more information on Unity snapshots, see .
Thin clones
A thin clone is a read/write copy of a thin block storage resource, such as a volume, a VMware VMFS datastore, or a VMware datastore, that shares blocks with the parent resource. Thin clones are a great way to quickly and compactly represent copies of a SQL Server database, which is not possible with traditional SQL Server tools. Once the thin clone has been presented to the host, the volumes can be brought online and the database attached using the DB attach method in SQL Server.
When using the upgrade feature with thin clones, disable (offline) all databases that are on the thin clone. This must be done before the update operation. Failure to take the databases offline before performing the upgrade can result in data mismatch errors or incorrect data results in SQL Server.
Data replication
Replication is a software feature that synchronizes data with a remote system at the same site or at a different location. Unity's replication and configuration options let you choose an efficient way to meet RTO/RPO requirements for SQL Server databases while balancing performance and throughput.
When using Dell EMC Unity replication to protect SQL Server databases on multiple volumes, restrict all database data and log volumes to a single consistent group or file system. Replication is then configured on a group or file system, and can include volumes or shares from multiple databases. Databases that require different replication options must be on separate LUNs, consistent groups, or file systems.
Thin clones are compatible with both synchronous and asynchronous replication. When a thin clone replicates to a destination, it becomes a full copy of a volume, a consistent group, or a VMFS storage. After replication, a thin clone is a completely independent volume with its own settings.
The process of thin clone replication between source and target systems.
Replication of the tempdb database is not required because the file is rebuilt when SQL Server is restarted and therefore the metadata does not match the method of other SQL Server instances. Careful selection of volumes to replicate and the contents of those volumes eliminates unnecessary replication traffic.
Microsoft SQL Server integrated copy management
Most modern storage products (including all Dell EMC products) can create "operating system consistent" copies of any type of file by:
- A consistent write order by the operating system at all levels - from the host to the drive.
- Grouping volumes so that multiple files on different volumes maintain the write order.
With the widespread adoption of scalable storage devices, Microsoft has developed an API for storage providers. This API allows storage providers to coordinate with SQL Server database software to create "application-consistent copies" using the Volume Shadow Copy Service (VSS). These copies simulate the interaction between SQL Server and the operating system during scheduled and shutdown of SQL Server. All write buffers are flushed and transactions are suspended until all disks are updated and consistent at a specific point in time, which is captured in the SQL log.
Integrated with Unity XT snapshots, Dell EMC AppSync software simplifies and automates the process of creating, using, and managing application-consistent copies of production data. This software is intended to be used in copy management scenarios for database recovery and reuse.
The AppSync software automatically discovers the application's databases, learns the database structure, and maps the file structure through the hardware or virtualization layers to the underlying Unity XT storage. It organizes all the necessary actions, from creating and verifying a copy to mounting snapshots on the target host and starting or restoring the database. AppSync supports and simplifies SQL Server workflows that involve updating and restoring a production database.
Data reduction and advanced deduplication
The Dell EMC Unity storage family offers rich, easy-to-use data reduction services. Savings are achieved not only on configured primary storage resources, but also on snapshots and thin clones of these resources. Snapshots and thin clones inherit the data reduction setting of the original storage, which increases capacity savings.
The data reduction feature includes deduplication, compression, and null block detection activities, potentially increasing usable storage space for user objects and internal use. Unity XT's data reduction feature replaces the compression feature in Unity OE 4.3 and later. Compression is a data reduction algorithm that can reduce the physical allocation of the capacity required to store a set of data.
Unity XT systems also provide an advanced deduplication feature that can be enabled if data reduction is enabled. Advanced deduplication reduces the capacity needed for user data by keeping only a small number of copies (often only one copy) of Unity data blocks. A deduplication area is one LUN. Keep this in mind when choosing a storage scheme. Fewer LUNs result in better deduplication, but more LUNs provide better performance.
The capacity savings from advanced deduplication can provide the most value in most environments, but also consumes the CPU resources of the Unity array. In OE 5.0, advanced deduplication, if enabled, will dedupe any block (compressed or uncompressed). For more information see .
The following table lists the supported configurations for data reduction and advanced deduplication:
Data reduction in Unity (all models) and enhanced support for deduplication
Unity OE version
Technology
Supported pool type
Supported models
4.3 / 4.4
Data reduction
Flash Pool - Traditional or Dynamic
300, 400, 500, 600, 300F, 400F, 500F, 600F, 350F, 450F, 550F, 650F
4.5
Data reduction
300, 400, 500, 600, 300F, 400F, 500F, 600F, 350F, 450F, 550F, 650F
Data reduction and advanced deduplication*
450F, 550F, 650F
5
Data reduction
300, 400, 500, 600, 300F, 400F, 500F, 600F, 350F, 450F, 550F, 650F, 380, 480, 680, 880, 380F, 480F, 680F, 880F
Data reduction and advanced deduplication
450F, 550F, 650F, 380, 480, 680, 880, 380F, 480F, 680F, 880F
* Data reduction is disabled by default and must be enabled before advanced deduplication becomes an available option. After data reduction is enabled, advanced deduplication is available, but it is disabled by default.
Data reduction in Unity and data compression in SQL Server
SQL Server 2008 Enterprise Edition was the first release with native data compression capabilities. SQL Server 2008 row-level and page-level compression uses knowledge of the SQL Server back-end database table format to reduce the space occupied by database objects. Reducing space allows more lines to be stored per page and more pages to be stored in the buffer pool. Because data not stored in the 8k data page format, such as non-row data such as NVARCHAR(MAX), will not use row or page compression methods, Microsoft introduced the Transact-SQL COMPRESS and DECOMPRESS functions.
These functions use the traditional data compression approach (GZIP algorithm), which must be called for each section of data to be compressed or decompressed.
Unity XT compression, which is not exclusive to SQL Server, uses a software algorithm to parse and compress storage data. Since the release of Unity OE 4.1, Unity data compression has been available for block storage volumes and VMFS datastores in a flash pool. Starting with Unity OE 4.2, compression is also available for file systems and NFS datastores in flash storage pools.
The choice of data compression method for SQL Server depends on several factors. These factors include the content type of the database, available CPU resources on both the storage and database servers, and the I/O resources required to maintain the SLA. In general, you can expect additional space savings for data that is compressed using SQL Server, but data compressed using the TSQL compression function using the GZIP algorithm is unlikely to receive a significant additional reduction in volume from the Unity XT compression functions, since most of the benefits come from the first applied universal algorithm.
Unity compression provides space savings if the data on the storage object is compressed by at least 25%. Before you enable compression on a storage object, determine if it contains data that can be compressed. Do not enable compression on a storage object unless it saves capacity.
Consider the following when deciding whether to use Unity data reduction, SQL Server database-level compression, or both:
- Data that is written to the Unity system is validated by the host after it is stored in the system cache. However, the compression process does not start until the cache is cleared.
- Compression savings are achieved not only for Unity XT storage resources, but also for snapshots and thin clones of the resource.
- During the compression process, multiple blocks are aggregated using a sampling algorithm to determine if the data is to be compressed. If the sampling algorithm determines that only minimal savings can be achieved, then compression is skipped and data is written to the IS pool.
- When data is compressed before being written to the media, the volume of data operations is greatly reduced. Therefore, compression helps reduce wear and tear on flash memory by reducing the physical amount of data written to the drive.
For more information about row and page compression in SQL Server for tables and indexes, see .
Keep in mind that any compression requires CPU resources. With high bandwidth requirements, compression can have a significant impact on performance. High write ratios for OLAP workloads can also reduce the benefits of compression for a SQL Server database.
Dell EMC has studied the potential savings by using actual data reduction ratios in the Unity array. The team collected data on VMware virtual machines, file sharing, SQL Server databases, Microsoft Hyper-V virtual machines, and more.
The results of the study showed that the reduction in the size of the SQL Server log file is almost 10 times less than the data file:
- Database size = 1,49:1 (32,96%)
- Log volume = 12,9:1 (92,25%)
The SQL Server database was provided with two volumes. The database files are stored on one volume and the transaction logs are stored on another. Using data reduction technology with database volumes can provide storage savings; however, the performance impact should be considered when deciding whether to enable deduplication on database volumes. While the actual reduction in database size may vary depending on the data stored, the results of the study showed that the storage space for SQL Server transaction logs can .
Best practices for data reduction
Before you enable data reduction on a storage object, consider the following guidelines:
- Use storage system monitoring to make sure it has available resources to support data reduction.
- Enable data reduction for multiple storage objects at the same time. Monitor the system to ensure it is in the recommended operating modes before enabling it on additional storage sites.
- On Unity XT x80F models, data reduction will provide capacity savings if the data in the storage unit is compressed by at least 1%.
Data reduction on previous Unity x80F models running OE 5.0 provided savings if the data was at least 25% compressible.
- Before you enable data reduction on a storage object, determine if the object contains compressible data. Certain types of data, such as video, audio, images, and binary data, usually offer little benefit from compression. Do not enable data reduction on a storage object unless there is space savings.
- Consider selectively compressing a volume of file data, which usually compresses well.
VMware virtualization
VMware vSphere is an efficient and secure virtualization and cloud platform. The core components of vSphere are VMware vCenter Server and the VMware ESXi hypervisor.
vCenter Server is a unified platform for managing vSphere environments. It is easy to deploy and proactively optimizes resources. ESXi is an open source hypervisor that installs directly on physical servers. ESXi has direct access to core resources, and a small size of 150 MB, which minimizes memory requirements. It provides reliable performance for a variety of application workloads and supports powerful virtual machine configurations of up to 128 vCPUs, 6 TB of RAM, and 120 devices.
For SQL Server to run efficiently on modern hardware, the SQL Server Operating System (SQLOS) must "understand" the structure of the hardware. With the advent of multi-core and multi-node non-uniform memory access (NUMA) systems, it has become especially important to understand the relationships between cores, logical processors, and physical processors.
Processors
A virtual processor (vCPU) is a virtual central processing unit that is assigned to a virtual machine. The total number of vCPUs assigned is calculated as:
Total vCPU = (количество виртуальных сокетов) * (количество виртуальных ядер на сокет)If consistent performance is important, VMware recommends that the total number of vCPUs assigned to all VMs should not exceed the total number of physical cores available on the ESXi host, but you can increase the number of dedicated vCPUs if monitoring indicates unused CPU resources are available.
Systems with Intel Hyper-Threading Technology enabled have twice the number of logical cores (vCPUs) as there are physical cores. In this case, do not assign the total number of vCPUs.
Lower level SQL Server workloads are less affected by latency variability. Therefore, these workloads can run on hosts with a large vCPU to physical ratio. Reasonable CPU usage levels can maximize overall system throughput, maximize license savings, and maintain adequate performance.
Intel Hyper-Threading typically improves overall host throughput by 10-30%, which suggests a vCPU to physical processor ratio of 1,1 to 1,3. VMware recommends enabling Hyper-Threading in the UEFI BIOS whenever possible so that ESXi can take advantage of this technology. VMware also recommends thorough testing and monitoring when using Hyper-Threading for SQL Server workloads.
Memory
Nearly all modern servers use a non-uniform memory access (NUMA) architecture to communicate between main memory and processors. NUMA is a shared memory hardware architecture that implements the division of blocks of physical memory between physical processors. A NUMA node is one or more CPU sockets along with a block of dedicated memory.
During the last decade, NUMA has been a widely discussed topic. The relative complexity of NUMA is due, in part, to implementations from different vendors. In virtualized environments, NUMA complexity is also determined by the number of configuration options and layers, from hardware through the hypervisor to the guest operating system, and finally to the SQL Server application. A good understanding of NUMA hardware architecture is a requirement for any SQL Server DBA working with a virtualized SQL Server instance.
To achieve greater efficiency on servers with a large number of cores, Microsoft introduced SoftNUMA. SoftNUMA software allows you to divide the available CPU resources within one NUMA into several SoftNUMA nodes. According to VMware, SoftNUMA is compatible with VMware's Virtual NUMA (vNUMA) topology and can further optimize DB engine scalability and performance for most workloads…
When virtualizing VMware with SQL Server, use:
- Monitor virtual machines to detect low memory resources for the SQL Server Database Engine. This issue results in increased I/O operations and slower performance.
- To improve performance, prevent memory conflicts between virtual machines by avoiding excessive memory usage at the ESXi host level.
- Consider checking the hardware NUMA physical memory allocation to determine the maximum amount of memory that can be assigned to a virtual machine within the physical NUMA boundaries.
- If achieving adequate performance is your primary goal, consider reserving memory equal to the allocated memory. This parameter setting ensures that the virtual machine receives only physical memory.
Virtualized Storage
Setting up storage in a virtualized environment requires knowledge of the storage infrastructure. As with NUMA, you need to understand how the different levels of I/O work - in this case, from an application in a VM, to physically reading and writing information to a persistent storage medium.
vSphere provides a number of storage configuration options that have useful applications in a SQL Server implementation with a Unity XT array. FS VMFS is the most widely used data storage method in block storage systems such as Unity XT. The Unity XT array is the bottom layer, consisting of physical drives represented by vSphere as logical drives (volumes). Unity XT volumes are formatted as VMFS volumes by the ESXi hypervisor. VMware administrators create one or more virtual disks (VMDKs) that are presented to the guest operating system. RDM allows a virtual machine to directly access Unity XT block storage (via FC or iSCSI) without VMFS formatting. VMFS and RDM volumes can provide the same transaction throughput.
For NFS-based storage for ESXi, Dell EMC recommends using VMware NFS instead of general purpose NFS file systems. A virtual machine running on SQL Server using a VMDK on an NFS datastore is unaware of the underlying NFS layer. The guest operating system treats the virtual machine as a physical server running Windows Server and SQL Server. Shared drives are not supported for Failover Cluster Instance configurations on NFS datastores.
VMware vSphere Virtual Volumes (VVols) offer finer control at the virtual machine level, independent of the underlying representation of physical memory (such as volumes or file systems). Array-based replication with VVols is supported starting with VVol 2.0 (vSphere 6.5). A VVol disk can be used in place of an RDM disk to provide disk resource to a SQL Failover Cluster Instance starting with vSphere 6.7 with support for persistent SCSI redundancy.
Virtualized networks
The network in the virtual world follows the same logical concepts as in the physical world, but uses software rather than physical cables and switches. The impact of network latency on SQL Server workloads can vary greatly. Monitoring network performance metrics on an existing workload or a well-implemented test system over a representative period helps in building a virtual network.
Consider the following when using VMware virtualization with SQL Server:
- Both standard and distributed virtual switches provide the functionality required by SQL Server.
- Use VLAN tagging and virtual switch port groups to logically separate management, vSphere vMotion, and network storage traffic.
- VMware highly recommends enabling jumbo frames on virtual switches where vSphere vMotion traffic or iSCSI traffic is enabled.
- In general, follow the networking guidelines for guest operating systems and hardware.
Conclusion
SQL Server database environments are getting larger and more complex. In SQL Server 2019, Microsoft has improved core SQL Server features and added new features such as support for big data workloads with Apache Spark and HDFS. Dell EMC, in collaboration with Microsoft, continues to provide the necessary infrastructure components for the SQL Server environment - servers, storage, and networking.
We see significant increases in uptime and lower total cost of ownership (TCO) when storage and database professionals work together to build infrastructure solutions for SQL Server on shared storage platforms. The Dell EMC Unity XT Flash Array is a mid-range solution for SQL Server developers and administrators who need high performance and low latency. Designed to run on all flash drives, Unity XT All-Flash supports dual CPUs, dual controller configurations, and multi-core optimization.
Increasingly, organizations are virtualizing their SQL Server environments. Although virtualization adds another layer of design to the architecture stack, it provides significant benefits. We hope you find some of the most commonly used VMware features and tools in SQL Server environments presented above useful. We also recommend links to resources for more information.
Useful links
Dell EMC
- (product page)
- (Youtube)
VMware
Microsoft
Source: habr.com