Microsoft SQL Server 2019 and Dell EMC Unity XT Flash Arrays

Today we will introduce you to the features of using SQL Server 2019 with the Unity XT storage system, as well as give recommendations on virtualizing SQL Server using VMware technology, on setting up and managing the basic components of the Dell EMC infrastructure.









In 2017, Dell EMC and VMware published the results of a survey on the trends and evolution of SQL Server - “ SQL Server Transformation: Toward Agility and Resiliency ” ( SQL Server Transformation: Toward Agility and Resiliency ), which used the experience of a community of members of the SQL Server Professional Association (Professional Association of SQL Server, PASS). The results show that SQL Server database environments are growing in both size and complexity due to increasing data volumes and new business requirements. SQL Server databases are currently deployed in many companies, providing critical applications, and often serve as the basis for digital transformation.



Since the time of this survey, Microsoft has released the next generation of DBMSs - SQL Server 2019. In addition to improving the basic functions of the relational engine and data storage, new services and functions have appeared. For example, SQL Server 2019 includes support for big data workloads using Apache Spark and the Hadoop Distributed File System (HDFS).

Dell EMC and Microsoft Alliance

Dell EMC and Microsoft have long been collaborating in developing solutions for SQL Server. The successful implementation of an integrated database platform such as Microsoft SQL Server requires the coordination of software functions with the underlying IT infrastructure. This infrastructure includes processor computing power, memory resources, storage, and network services. Dell EMC offers the infrastructure for the SQL Server platform for any type of workload and application.



The Dell EMC PowerEdge line of servers offers many 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 warehousing, advanced analytics, electronic commerce, etc. 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 a combination of Client Access, Server-to-Server, and Server-to-Server communications. The Dell EMC concept is based on a disaggregated model that offers an open ecosystem. Organizations can choose from a wide range of industry standard network applications, operating systems, and hardware platforms. This approach gives maximum control over technologies and architectures, which leads to tangible cost savings and flexibility.



VMware virtualizes all critical infrastructure components that SQL Server requires to achieve high performance and consistency. In addition to the private cloud, VMware also currently offers hybrid models for workloads covering private and public cloud architectures.



Many organizations have resorted to virtualization to lower infrastructure costs, ensure high availability and simplify disaster recovery. 94% of the SQL Server experts surveyed report a level of virtualization in their environment. 70% of those who use virtualization have chosen VMware. At 60%, SQL Server virtualization is 75% or more. In addition, the survey results convincingly show that high availability and disaster recovery, implemented at the virtualization level, were important factors in deciding whether to virtualize SQL Server databases.

New SQL Server 2019 Features

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 acquired capabilities for managing data integration, data warehousing, reporting and advanced analytics, replication functions, 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 share SQL Server services with virtualization.



Today, enterprises often have to rely on large amounts of data from a wide range of ever-increasing data sets. With SQL Server 2019, you can get valuable information in near real time from all the data. SQL Server 2019 clusters provide a full-blown environment for working with large data sets, including using machine learning and artificial intelligence capabilities. Key new features and updates in SQL Server 2019 are listed in a Microsoft document .

Dell EMC Unity XT Mid-Range Storage

The Dell EMC Unity series of storage systems appeared almost three years ago, and since then more than 40,000 systems have been sold. Customers rated this mid-range array for its simplicity, performance and cost-effectiveness. Dell EMC Unity XT midrange platforms are shared storage solutions that offer low latency, high throughput, and low management overhead for SQL Server workloads. All Unity XT systems use a dual-processor architecture (storage processors, SP) to serve I / O and perform data operations in active / active mode. Unity XT dual SP uses full 12 Gb / s SAS internal connectivity and a proprietary multi-core architecture for high performance and efficiency. Disk arrays allow you to increase storage capacity with the help of additional shelves.









Dell EMC Unity XT, the new generation of arrays (hybrid and entirely flash-based), has significantly increased performance, improved efficiency, and added new features and services for multi-cloud environments.



The Unity XT architecture allows data to be simultaneously processed, reduced in size, and supported by services such as replication without sacrificing application performance. Compared with the previous generation solution, the performance of Dell EMC Unity XT storage systems is doubled, the response time is 75% less. And of course, Dell EMC Unity supports the NVMe standard.



NVMe storage systems demonstrate their best in latency-sensitive applications. For example, in applications such as massive databases, NVMe provides low latency and high peak data rates. Reducing latency and increasing concurrency greatly improves read / write performance. It is no accident that, according to IDC's forecast, by 2021, NVMe and NVMe-oF (NVMe over Fabric) flash arrays will account for about half of all revenues from external storage systems in the world.



Storage efficiency is enhanced by data compression algorithms. Dell EMC Unity XT can reduce data by five times. Another important indicator is the overall system performance. Dell EMC Unity XT utilizes 85% system capacity. Compression and deduplication are performed inline - at the controller level. Data is saved in compressed form. The system also automates the work with data snapshots.



Easy-to-use Unity flash arrays with unified (block and file) access provide stable response time, integrate with cloud storage services, support upgrade without data migration. In the basic configuration, this universal storage system is installed in 30 minutes.



The technology of data storage called “dynamic pools” allows you to switch from static to dynamic memory growth, gives high operational flexibility and ease of increasing the capacity of the system. Dynamic pools save capacity and budget, and require less time to rebuild. Building capacity and performance Dell EMC Unity does not require data migration.



Many companies today, in combination with their local infrastructure, use several public cloud services. Dell EMC Unity XT can function as a component of the Dell Technologies Cloud. This storage can be used in a public cloud and transfer data to a 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 becoming increasingly popular as it allows you to increase your return on investment by lowering infrastructure costs. Cloud Storage Services Cloud Storage Services extends client data centers to the cloud, providing Dell EMC storage (directly connected to public cloud resources) as a service. Third-party providers can provide a high-speed (low latency) public cloud connection 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.

Unified Hybrid and Flash Arrays

Intel processor-based Unity XT Hybrid and Unity XT All-Flash storage systems provide an integrated architecture for block, file access and VMware VVols volumes with support for Network Attached Storage (NAS), iSCSI and Fiber Channel (FC) protocols. Unity XT Hybrid and Unity XT All-Flash platforms are ready to use NVMe drives.



Unity XT Hybrid Systems support multi-cloud environments. Support for multi-cloud environments means expanding your storage to the cloud or deploying to the cloud with flexible resource options. Multi-cloud storage is designed to provide mobility, data portability between multiple cloud platforms - private and public. This affects not only the processes of data movement, but also the organization of access of applications to data in several public clouds.









These hybrid arrays provide the following features:

• Scalability up to 16 PB of raw capacity.
• Built-in data reduction features for all flash pools.
• Quick installation and configuration (on average, it takes 25 minutes).

Solid state drive technologies are rapidly improving, and in the coming years, new revolutionary products will appear on the market. In the meantime, organizations will continue to replace traditional HDDs with solid state drives to increase productivity, ease of management and energy savings. New generations of flash arrays will feature improved storage automation, public cloud integration and integrated data protection.



Unity XT All-Flash systems deliver high speed, efficiency and multi-cloud support. Their features:

• Double performance.
• Reduce data to 7: 1.
• Quick installation and setup (the process takes less than 30 minutes).

UnityVSA

UnityVSA is a software-defined storage system for VMware ESXi virtual environments that uses server, shared, or cloud storage. UnityVSA HA, a configuration with two UnityVSA repositories, provides additional fault tolerance. UnityVSA Storage offers:

• Up to 50 TB of full-featured unified storage capacity.
• Compatible with Unity XT systems and features.
• Support for high availability systems (UnityVSA HA).
• Connecting both NAS and iSCSI.
• Replicate data from other Unity XT platforms.

Unity Cloud Edition

For file synchronization and disaster recovery operations with the cloud, the Unity XT family includes a version of Unity Cloud Edition that provides:

• Full-featured storage capabilities using software-defined storage (SDS) deployed in the cloud.
• Easily deploy block and file storage with VMware Cloud in AWS.
• Disaster recovery support, including data testing and analysis.

Unity XT All Flash for SQL Server

In a 2017 Unisphere Research report, “ SQL Server Transformation: Toward Agility and Resiliency ” ( SQL Server Transformation: Toward Agility and Resiliency ), 22% of respondents said they were using flash storage technology in their product (16%) or planned to do (6%). 30% use hybrid arrays, including flash memory. 13% use direct-attached flash arrays. 13% back up SQL Server databases to flash storage.



This rapid implementation of flash storage for use with SQL Server means that Unity XT All-Flash arrays are especially 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, which are ready to implement NVMe (for even higher high performance and low latency), have a 2U form factor, support dual-core processors, two active / active controllers.



Unity XT All-Flash Models



Details can be found in the array specifications ( Dell EMC Unity XT Storage Series Specification Sheet ).

Storage pools

Many SQL Server professionals know that all modern storage arrays provide the ability to group disks into larger storage units with a fixed RAID level of protection. Separate RAID groups of drives are traditional storage pools. While Unity XT hybrid systems only support traditional pools, Unity XT All-Flash arrays also offer dynamic storage pools. For dynamic storage pools, RAID protection applies to disk extents — storage units smaller than a full disk. Dynamic pools provide greater 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 that you 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 individual resources for multi-tenancy.
• Create smaller domains to protect against failure

Storage Volumes (LUN)

How to find a compromise between management and flexibility when choosing the number of volumes in an array? For maximum flexibility in Unity with SQL Server, it is recommended that you create volumes for each database file. In practice, most organizations use a tiered approach where critical databases get maximum flexibility and less important database files are grouped on fewer large volumes. We recommend that you study all database requirements and any related applications, because data protection and monitoring technologies depend on file isolation and placement.



Multiple volumes are often difficult to manage, especially in virtual environments. SQL Server virtualized environments are a good example of when it might make sense to host multiple file types on a single volume. When determining the number of volumes to create, the database or storage administrator (or both) must choose the right balance between flexibility and ease of maintenance.

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 multi-protocol file system, both protocols can be used simultaneously. To connect the host to SMB, NFS and multi-protocol file systems, as well as to VMware NFS data stores and VMware virtual volumes, NAS servers use virtual interfaces. File systems and virtual interfaces are isolated within a single NAS server, which allows multiple NAS servers to be used for multi-tenancy. NAS servers automatically fail over if the storage processor fails. The associated file systems also fail over.



SQL Server 2012 (11.x) and later versions support the Server Message Block (SMB) 3.0 protocol, which allows you to share a network file for storage. For both a stand-alone installation and a failover cluster, you can install system databases (master, model, msdb and tempdb) and custom Database Engine databases with the SMB storage option. Using SMB storage is a good option when using Always On Availability Groups, because a file share requires access to a highly available network share.



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, file system, and SMB share. Dell EMC Unisphere Storage Management software includes a configuration wizard to help you complete this process. However, when placing SQL Server workloads on SMB file shares, you should not forget about some important considerations that do not necessarily apply to using 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 Microsoft docs for details.

Data snapshots

Data has become a critical resource for companies, and critical environments today require more than redundancy. It is necessary that the applications are 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 snapshot capabilities for blocks and files, all using common workflows, operations, and architecture. The Unity snapshot methodology provides a simple and effective way to protect data. Snapshots make data recovery easier — roll back to an earlier snapshot, or you can copy selected data from a previous snapshot. The following table summarizes the retention periods for Unity XT systems.

Local and remote storage of data snapshots

Snapshots are not a direct replacement for other data protection methods, such as backups. They can only complement traditional backups as the first line of defense for small 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 storage resource that supports data reduction features, data in the source can 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 dataset. When data and log files are on different LUNs, these 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 located on several 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 Unisphere host join. For file-based recovery, an additional SMB share is created using the snapshot as the source. After mounting the volumes, you can attach the database under a different name or replace the existing database with a restored one.

• When performing recovery using the Snapshot Restore snapshot recovery method in Unisphere, take the SQL Server instance offline. SQL Server does not know about recovery operations. Taking the instance offline ensures that the volumes will not be corrupted during write operations to the database before recovery. Once the instance is restarted, SQL Server Disaster Recovery will bring the databases to a consistent state.
• Allow snapshots for several storage objects at the same time, and then, before including additional snapshots, when monitoring the system, make sure that it is in the recommended operating modes.

Automate and schedule snapshots

Unity XT snapshots can be automated. The following snapshot options are available in the Unisphere storage management system by default: default protection, protection with a shorter retention period, and protection with a longer retention period. Each option creates daily snapshots and saves them for different periods of time.



You can choose one (or both) of the planning options - every x hours (1 to 24) and daily / weekly. Daily / weekly snapshot scheduling allows you to specify a specific time and days for creating snapshots. For each option selected, you need to set a retention policy, which can be configured to automatically delete the pool or temporary storage.



For more information on Unity snapshots, see the Dell EMC Unity documentation .

Thin Clones

A thin clone is a read / write copy of a thin block storage resource, such as a volume, a consistent group, or VMware VMFS data storage, which shares blocks with the parent resource. Thin clones are a great way to quickly and compactly represent copies of a SQL Server database, which cannot be achieved using traditional SQL Server tools. After the thin clone is presented to the host, the volumes can be brought online (online), and the database will be attached using the database attach method in SQL Server.



When using the update function with thin clones, disconnect (transfer offline) all databases located on the thin clone. This must be done before the update operation. If you do not take the databases offline before performing the upgrade, this can lead to data inconsistency errors or incorrect data results on SQL Server.

Data replication

Replication is a software function that synchronizes data with a remote system on the same site or in another place. Unity's replication and configuration options allow you to choose an effective way to meet RTO / RPO requirements for SQL Server databases, while maintaining a balance of performance and throughput.



If you are using Dell EMC Unity replication to protect SQL Server databases on multiple volumes, you should limit all the data and volumes of the database logs to one consistent group or file system. Replication is then configured in a group or file system and can include volumes or shared resources of several databases. Databases that require different replication parameters must reside in separate LUNs, consistent groups, or file systems.



Thin clones are compatible with both synchronous and asynchronous replication. When a thin clone is replicated to its destination, it becomes a full copy of the volume, jubilee group, or VMFS storage. After replication, the thin clone is a completely independent volume with its own settings.









The process of replicating a thin clone between the source and target systems.



Database tempdb replication is not required because the file is rebuilt when SQL Server restarts, and therefore the metadata does not match the method of other SQL Server instances. A careful selection of volumes for replication and the contents of these volumes eliminates unnecessary replication traffic.

Microsoft SQL Server Integrated Data Copy Management

Most modern storage products (including all Dell EMC products) can create “OS-compatible” copies of any type of file by:

• A consistent recording procedure by the operating system at all levels - from the host to the drive.
• Grouping volumes so that for several files on different volumes the recording order is maintained.

With the widespread adoption of scalable storage devices, Microsoft has developed APIs for storage providers. This API allows storage providers to coordinate with SQL Server database software to create “application-consistent copies” using Volume Shadow Copy Service (VSS). These copies mimic the interaction of SQL Server and the operating system during the scheduled and shutdown of SQL Server. All write buffers are cleared, and transactions are suspended until all disks are updated and agreed at a certain point in time, which is recorded 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 operational data. This software is intended for use in copy management scripts to restore and reuse databases.



AppSync software automatically discovers application databases, examines the database structure and maps the file structure through hardware or virtualization levels to the Unity XT base storage. It organizes all the necessary actions, from creating and checking a copy to mounting snapshots on the target host and starting or restoring the database. AppSync supports and simplifies SQL Server workflows, which include updating and restoring a production database.

Data Reduction and Advanced Deduplication

The Dell EMC Unity family of storage systems 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 setting to reduce the amount of data in the source storage, which increases capacity savings.



The data reduction function includes deduplication, compression and detection of zero blocks, which potentially increases the amount of usable storage space for user objects and internal use. The Unity XT 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 distribution of capacity needed to store a data set.



Unity XT systems also provide an advanced deduplication feature that can be enabled if data reduction is enabled. Advanced deduplication reduces the capacity required for user data by storing only a small number of copies (often only one copy) of Unity data blocks. The deduplication area is one LUN. Consider this when choosing a storage scheme. Fewer LUNs result in better deduplication, but more LUNs provide increased performance.



Capacity savings through advanced deduplication can deliver the most performance in most environments, but also require the use of Unity array processor resources. In OE 5.0, advanced deduplication, if enabled, deduplicates any block (compressed or uncompressed). See your Dell EMC documentation for more information.



The following table lists the supported configurations for data reduction and advanced deduplication:

Unity data reduction (all models) and advanced deduplication support

* Data reduction is disabled by default and must be enabled before advanced deduplication becomes an option. After enabling data reduction, advanced deduplication is available, but it is disabled by default.

Unity data reduction and data compression in SQL Server

SQL Server 2008 Enterprise Edition was the first release with native data compression capabilities. SQL Server 2008 row and page compression uses knowledge of the table format of the internal SQL Server database to reduce the space occupied by database objects. Reducing space allows you to store more lines per page and more pages in the buffer pool. Because data not saved in 8k data page format, such as off-line data such as NVARCHAR (MAX), will not use row or page compression methods, Microsoft introduced Transact-SQL COMPRESS and DECOMPRESS functions.



These functions use the traditional data compression approach (GZIP algorithm), which must be called for each data section for compression or decompression.



Unity XT compression, which is not specific to SQL Server, uses a software algorithm to analyze and compress storage data. Since Unity OE 4.1, Unity data compression has been available for block storage volumes and VMFS data storage volumes in a flash pool.Starting with Unity OE 4.2, compression is also available for file systems and NFS datastores in storage pools on flash drives.



The choice of data compression method for SQL Server depends on several factors. These factors include the type of database content, the available CPU resources, both on storage and on the database servers, as well as the I / O resources needed to maintain the SLA. In general, you can expect additional space savings for data that is compressed by SQL Server, however, 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 advantages are achieved thanks to the first universal algorithm.



Unity compression saves space if data on the storage object is compressed by at least 25%. Before you enable compression for a storage object, determine whether it contains data that can be compressed. Do not enable compression for the storage object if this does not save capacity.



When deciding whether to use Unity data reduction, SQL Server database compression, or both, consider the following:

• Data that is written to the Unity system is confirmed by the host after it is saved 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 resource clones.
• , , . , , , .
• , . - , .

For more information about row and page compression in SQL Server for tables and indexes, see Microsoft docs .



Do not forget that any compression requires processor resources. With high throughput requirements, compression can have a significant impact on performance. High OLAP workload recording rates can also reduce the compression benefits for a SQL Server database.



Dell EMC has explored potential savings using real data reduction ratios in the Unity array. The group collected data on VMware virtual machines, file sharing, SQL Server databases, Microsoft Hyper-V virtual machines, etc.



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. Database files are stored on one volume, and transaction logs are stored on another. Using data reduction technology with database volumes can provide storage savings; however, you should consider the performance impact when deciding whether to enable deduplication on database volumes. Although the actual reduction in database size may vary depending on the data stored, the study showed that the storage space for SQL Server transaction logs can be significantly reduced .

Best practices for data reduction

Before enabling data reduction on a storage object, consider the following recommendations:

• Use storage monitoring to ensure that it has available resources to support data reduction.
• Enable data reduction for multiple storage objects at once. Before turning it on at additional storage facilities, monitor the system to make sure that it is in the recommended operating modes.
• On Unity XT x80F models, reducing data volume will save capacity 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 compressible by at least 25%.

• , . , , , , . , .
• , .

VMware

VMware vSphere is an efficient and secure platform for virtualization and the cloud. The core components of vSphere are VMware vCenter Server and the VMware ESXi Hypervisor.



vCenter Server is a unified vSphere environment management platform. It is easy to deploy and proactively optimize resources. ESXi is an open source hypervisor that installs directly on physical servers. ESXi has direct access to key 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 - up to 128 virtual CPUs, 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 hardware structure. With the advent of multi-core and multi-node heterogeneous memory access systems (NUMA), it has become especially important to understand the relationships between cores, logical and physical processors.

Processors

Virtual processor (vCPU) - A virtual central processor assigned to a virtual machine. The total number of assigned virtual CPUs is calculated as:

Total vCPU = (  ) * (    )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

If stable performance is important, VMware recommends that the total number of virtual CPUs assigned to all virtual machines do not exceed the total number of physical cores available on the ESXi host, but you can increase the number of dedicated virtual CPUs if monitoring shows that unused CPU resources are available.



On systems with Intel Hyper-Threading Technology enabled, the number of logical cores (vCPUs) is twice the number of physical cores. In this case, do not assign the total number of virtual CPUs.



Lower-level SQL Server workloads are less affected by delay variability. Thus, these workloads can be performed on hosts with a large ratio of virtual CPUs to physical ones. Reasonable CPU utilization levels can increase overall system throughput, maximize license savings and maintain adequate performance.



Intel Hyper-Threading typically increases overall host throughput by 10–30%, which implies a ratio of virtual CPUs to physical processors from 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 that you perform rigorous testing and monitoring when using Hyper-Threading for SQL Server workloads.

Memory

Nearly all modern servers use a non-uniform memory access (NUMA) architecture for communication between main memory and processors. NUMA is a hardware architecture for shared memory that implements the separation of blocks of physical memory between physical processors. A NUMA node is one or more CPU sockets along with a dedicated memory block.



Over the past decade, NUMA has been a widely discussed topic. The relative complexity of NUMA is due, in particular, to implementations from different vendors. In virtualized environments, NUMA complexity is also determined by the number of configuration parameters and levels — from hardware through the hypervisor to the guest operating system and, finally, to the SQL Server application. A good understanding of the NUMA hardware architecture is a must for any SQL Server database administrator 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 split 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 the scalability and performance of the database engine for most workloads ...



When virtualizing VMware with SQL Server, use:

• Monitoring virtual machines to detect running out of memory for SQL Server Database Engine. This problem leads to increased I / O and reduced performance.

• To improve performance, prevent memory conflicts between virtual machines by avoiding excessive memory load at the ESXi host level.
• Consider checking the hardware allocation of NUMA physical memory to determine the maximum amount of memory that can be allocated to a virtual machine within the NUMA physical boundaries.
• If achieving adequate performance is a primary goal, consider backing up memory equal to allocated memory. This parameter setting ensures that the virtual machine receives only physical memory.

Virtualized storage

Configuring storage in a virtualized environment requires knowledge of the storage infrastructure. As in the case of NUMA, you need to understand how the different I / O levels work - in this case, from the application in the VM to the physical reading and writing of information on a permanent storage medium.



vSphere provides a number of repository configuration options that have useful applications in a SQL Server implementation with an Unity XT array. FS VMFS is the most widely used storage method for block storage systems such as Unity XT. The Unity XT array is a lower 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 formatting VMFS. 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 that uses VMDK in the NFS datastore does not know about the underlying NFS layer. The guest operating system considers the virtual machine as a physical server running Windows Server and SQL Server. Shared drives for failover cluster instance configurations in NFS datastores are not supported.



VMware vSphere Virtual Volumes (VVols) offer finer virtual machine management, regardless 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 instead of an RDM disk to provide disk resource to an SQL failover cluster instance, starting with vSphere 6.7, with support for persistent SCSI redundancy.

Virtualized networks

A 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 well-implemented test system over a representative period helps in creating a virtual network.



When using VMware virtualization with SQL Server, consider the following:

• Both standard and distributed virtual switches provide the functionality necessary for SQL Server.
• To logically separate management traffic, vSphere vMotion, and network attached storage, use VLAN tagging and virtual switch port groups.
• VMware strongly recommends enabling large frames on virtual switches that include vSphere vMotion traffic or iSCSI traffic.
• In general, follow the network guidelines for guest operating systems and hardware.

Conclusion

SQL Server database environments are becoming increasingly large and complex. In SQL Server 2019, Microsoft improved the core features of SQL Server and added new ones, such as support for big data workloads using 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 networks.



We see a significant increase in uptime and lower total cost of ownership (TCO) if storage and database professionals work together to create 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. Unity XT All-Flash, designed to run on all flash drives, supports dual-processor 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 enjoy some of the most commonly used VMware features and tools in the SQL Server environments presented above. We also recommend links to resources for more information.

useful links