Home > Workload Solutions > SQL Server > White Papers > Microsoft SQL Server with Dell EMC and VMware Technology > Unity XT All-Flash unified storage for SQL Server
In the 2017 Unisphere Research report SQL Server Transformation: Toward Agility and Resiliency, respondents reported their use of flash storage technology as follows:
This rapid adoption of all-flash storage for use with SQL Server means that the 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 features and performance that go beyond what typical storage area networks (SANs) offer.
The Unity XT All-Flash systems, which are NVMe-ready to deliver high performance and low latency, have an efficient 2U form factor with a modern architecture. They are designed for all-flash performance that supports dual-socket CPUs, dual-active controllers, and multicore optimization. The following table provides an overview of each of the four Unity XT All-Flash models.
Table 1. Unity XT All-Flash models
Unity XT model | Processors (per SP) | Memory (GB per SP) | Maximum number of drives | Maximum raw capacity (PB) |
380F | 1 Intel E5-2603 v4 6c/1.7 GHz | 64 | 500 | 2.4 |
480F | 2 Intel Xeon Silver 4108 8c/1.8 GHz | 96 | 750 | 4.0 |
680F | 2 Intel Xeon Silver 4116 12c/2.1 GHz | 192 | 1,000 | 8.0 |
880F | 2 Intel Xeon Gold 6130 16c/2.1 GHz | 384 | 1,500 | 16.0 |
For more information about the full line of Unity XT Series All-Flash systems, see the Dell EMC Unity XT Storage Series Specification Sheet.
Many SQL Server professionals are aware that all modern storage arrays provide the ability to group disks into larger units of storage with a fixed level of RAID protection. We refer to discrete groups of drives with RAID protection as traditional pools. While Unity XT Hybrid systems support only traditional pools, Unity XT All-Flash arrays also support dynamic storage pools. With dynamic storage pools, RAID protection is applied to drive extents, which are storage units that are smaller than a full disk. Dynamic pools enable greater flexibility in managing and expanding the disk pools.
Dell EMC Unity Storage with Microsoft SQL Server provides best practices for managing storage pools to achieve the best performance with the least complexity. For example, Dell EMC recommends minimizing the number of Unity XT storage pools to reduce complexity and increase flexibility. However, configuring additional storage pools might be appropriate in some circumstances, including those where you need to:
The concept of thin provisioning (just-in-time allocation) has become ubiquitous in the virtualization and storage technology arena. Although Unity XT arrays also support thick provisioning, few reasons remain for using that technology, especially on all-flash storage where new allocations can be performed quickly. By default, the Unity XT system creates thin storage objects, which are virtually provisioned and space efficient. For a more detailed discussion of storage object types, see the Dell EMC Unity: Best Practices Guide.
It is more difficult to determine the best tradeoff between management and flexibility when deciding how many volumes to create on an array. A best practice for Unity with SQL Server is to create a volume for each user database file for ultimate flexibility. In practice, most organizations adopt a multitier approach where the most mission-critical databases get maximum flexibility and less critical databases group files on fewer, larger volumes. We recommend that you examine all the requirements for the databases and any related applications because many data protection and monitoring technologies depend on file placement isolation.
Many volumes can be difficult to manage, especially in virtualized environments. Virtualized SQL Server environments are a good example of where placing multiple file types on a single volume might make sense. The DBA or storage administrator, or both, should find the right balance between flexibility and maintainability when determining the number of volumes to create.
NAS servers host file systems on the Unity XT storage system. File systems can be accessed via SMB or NFS protocols and can be shared to both protocols simultaneously by using a multiprotocol file system. NAS servers use virtual interfaces to enable host connectivity to SMB, NFS, and multiprotocol file systems, as well as VMware NFS datastores and VMware Virtual Volumes. File systems and virtual interfaces are isolated to a single NAS server, allowing for multitenancy over multiple NAS servers. NAS servers are hosted on a storage processor and automatically fail over if the storage processor becomes faulted. Any associated file systems fail over as well.
SQL Server 2012 (11.x) and later versions support the Server Message Block (SMB) 3.0 protocol, enabling network file sharing for storage. For both stand-alone 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 witnesses require access to a highly available network-accessible resource.
Creating SMB file shares for SQL Server deployment with Unity XT storage is a simple, three-step process: Create a NAS server, create the file system, and create the SMB share. Dell EMC Unisphere storage management provides setup wizards that guide you through the 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 using SMB file shares as backup files. Microsoft has compiled a list of installation and security considerations together with currently known issues; for details, see Install SQL Server with SMB fileshare storage in Microsoft SQL Docs.
Data has become a critical strategic business initiative, and today’s mission-critical environments demand more than redundancy. They require that applications always be online, with nondisruptive operations and upgrades. They also require high performance and the increased data availability that local snapshot replication and remote replication provide.
The Unity XT storage array offers snapshot capabilities for block and file storage resources that use common workflows, operations, and architecture. The Unity snapshot methodology provides a simple and effective way to protect data. Snapshots facilitate data recovery by enabling rollback to an earlier snapshot, or you can copy select data from the snapshot to protect against a storage system outage. The following table shows snapshot retention timeframes for Unity XT systems.
Table 2. Local and remote snapshot retention
Snapshot type | CLI | UI | REST | |||
Manual | Scheduled | Manual | Scheduled | Manual | Scheduled | |
Local | 1 year | 1 year | 5 years | 4 weeks | 100 years | No limit |
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 native backup. Snapshots can augment traditional backup as a first line of defense for extremely low RTO scenarios.
The Dell EMC Unity snapshots feature incorporates data reduction and advanced deduplication. Snapshots also benefit from the space savings that are achieved on the source storage resource. When you take a snapshot of a data-reduction-enabled storage resource, the data on the source can be compressed or deduplicated.
Important considerations to ensure a successful database recovery when using snapshots with SQL Server databases include:
You can automate snapshots on Unity XT systems. The following default snapshot settings are available in Unisphere storage management: Default Protection, Protection with Shorter Retention, and Protection with Longer Retention. Each setting takes daily snapshots and retains them for various lengths of time.
You can choose one or both of two scheduling options—every x number of hours (between 1 and 24) and daily/weekly. Daily/weekly snapshot scheduling enables you to specify certain times on specific days for snapshots to occur. For each selected option, you must also set a retention policy, which can be set to pool automatic deletion or time-based retention.
For more information about Unity snapshots, see the Dell EMC Unity: Snapshots and Thin Clones White Paper.
A thin clone is a read-write copy of a thin block storage resource, such as a volume, consistency group, or VMware VMFS datastore, that shares blocks with the parent resource. Thin clones are a great way to present SQL Server database copies in a fast and space-efficient manner that cannot be achieved with traditional SQL Server tools. After the thin clone is presented to the host, the volumes can be brought online, and the database is attached using the attach database method in SQL Server.
Note: When using the refresh feature with thin clones, take all databases that are on the thin clone offline before performing the refresh operation. SQL Server is unaware of when the thin-clone-based LUNs are being reverted. Failure to take the databases offline before performing a refresh can result in data inconsistency errors or incorrect data results from SQL Server, or both.
Replication is a software feature that synchronizes data to a remote system within the same site or at a different location. Unity replication and configuration options provide an effective way to meet the recovery time objective (RTO) and recovery point objective (RPO) requirements of SQL Server databases while maintaining a good balance of performance and bandwidth efficiency.
When using Dell EMC Unity replication to protect SQL Server databases on multiple volumes, contain all data and log volumes for a database within a single consistency group or file system. Replication is then configured on the consistency group or file system and can contain volumes or shares for more than one database. Databases that require different replication settings must be on separate LUNs, consistency groups, or file systems.
Thin clones are compatible with both synchronous and asynchronous replication. When a thin clone is replicated to the destination, it becomes a full copy of the volume, consistency group, or VMFS datastore. After being replicated, the thin clone is a fully independent volume with its own settings. The following figure shows the replication process of a thin clone volume:
Figure 1. Replication of a thin clone volume
Replication of tempdb is never required because the file is rebuilt when SQL Server is restarted and so the metadata does not match that of another SQL Server instance. Carefully selecting the volumes to replicate and the contents of those volumes eliminates unnecessary replication traffic.
SQL Server professionals have been extremely cautious when considering the use of storage-based snapshots and replication. Although different vendors might use different marketing terms to describe product features, most modern storage products (including all Dell EMC products) can produce “operating-system consistent” copies of any type of file by:
Unfortunately, the database community refers to the copies that are produced by these features as “crash-consistent copies.” The term came into use because recovering a database from this type of copy is the same mechanism that is used when the operating system shut downs unexpectedly and SQL is unable to resolve the changes that are stored only in memory with what is on disk. The use of term “crash-consistent copies” mostly fuels fears and limits legitimate use cases of storage-based copies and replication.
With the widespread adoption of highly scalable storage appliances, the Windows and SQL teams at Microsoft have developed an API for use by storage vendors. The API enables storage vendors to coordinate with the SQL Server database software to create “application-consistent copies” by using the Volume Shadow Copy Service (VSS). These copies mimic how SQL Server and the operating system cooperate during a planned and orderly shutdown of SQL Server. All write buffers are flushed and transactions are suspended until all the disks are up to date and consistent at a known point in time that is recorded in the SQL log.
Dell EMC AppSync software integrated with Unity XT snapshots simplifies and automates the process of generating, consuming, and managing application-consistent copies of production data. The software addresses copy management use cases for database recovery and database repurposing and is especially useful in a consolidated SQL database environment. AppSync software automatically discovers application databases, learns the database structure, and maps the file structure through the hardware or virtualization layers, or both, to the underlying Unity XT storage. It orchestrates all required activities, from copy creation and validation through mounting the snapshots at the target host and launching or recovering the database. The AppSync solution supports and simplifies SQL Server workflows that include refreshing, expiring, and restoring the production database.
The Dell EMC Unity family of storage systems offers feature-rich, easy-to-use data reduction services. Data reduction savings are achieved not only on the configured primary storage resources but also on the snapshots and thin clones of those resources. Snapshots and thin clones inherit the data reduction setting of the source storage resource, which increases the space savings provided.
The data reduction feature includes deduplication, compression, and zero-block detection activities that potentially increase the amount of useable storage space for user objects and internal uses. The Unity XT data reduction feature replaces the compression feature in Unity OE 4.3 and later versions. Compression is an algorithmic data reduction method that can reduce the physical allocation of storage required to save a dataset.
Unity XT systems also provide an advanced deduplication feature, which can be enabled if data reduction is enabled. Advanced deduplication reduces the amount of required storage for user data by keeping only a small number of copies (often just one copy) of a Unity data block. The deduplication scope is a single LUN, so consider this fact when choosing the storage layout. Fewer LUNs result in better deduplication, but more LUNs provide increased performance. The space savings from advanced deduplication can provide the greatest return in most environments but also requires the most CPUs in Unity storage. In OE 5.0, advanced deduplication, if enabled, deduplicates any block (compressed or uncompressed). For more information, see the Dell EMC Unity: Data Reduction White Paper and the Dell EMC Unity: Best Practices Guide.
The following table shows the supported configurations for data reduction and advanced deduplication:
Table 3. Unity (all models) data reduction and advanced deduplication support
Unity OE version | Technology | Supported pool type | Supported models |
4.3 / 4.4 | Data reduction | All-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 is an available option. After data reduction is enabled, advanced deduplication is available but is disabled by default.
SQL Server 2008 Enterprise Edition was the first version and edition to include native data compression capability. SQL Server 2008 row- and page-level compression uses knowledge of the internal database table format of SQL Server to reduce the space that database objects consume. The space reduction enables more rows to be stored on a page and more pages to be stored in the buffer pool. Since any data not stored in the 8k data page format—for example, off-row data such as NVARCHAR(MAX)—will not benefit from the row or page compression techniques, Microsoft introduced the Transact-SQL COMPRESS and DECOMPRESS functions. These functions use a traditional data compression approach (GZIP algorithm) that must be invoked for each data section to compress or decompress data.
Unity XT compression, which is not specific to SQL Server only, uses a software algorithm to analyze and compress data within a storage resource. Unity data compression has been available for block storage volumes and VMFS datastores in an all-flash pool since the release of Unity OE 4.1. Compression is also available for file systems and NFS datastores in all-flash storage pools starting with Unity OE 4.2.
The best choice of data compression for SQL Server data depends on multiple factors. These factors include the type of database content, the amount of available CPU on both the storage and the database servers, and the amount of I/O resources that are required to maintain service-level agreements. In general, you can expect additional space savings with Unity XT arrays for data that is compressed by SQL Server when compression relies on product-specific data page construction as opposed to a general-purpose data compression technology. Data compressed with the TSQL Compress function using the GZIP algorithm is unlikely to gain significant additional reduction from Unity XT compression features because most of the advantages are gained from the first general-purpose algorithm applied.
Unity compression provides space savings if the data on the storage object is at least 25 percent compressible. Before enabling compression on a storage object, determine if it contains data that can be compressed. Do not enable compression on a storage object if space savings will not result. Contact your Dell EMC representative for tools that can analyze the data compressibility. For more information about compression, see the Dell EMC Unity: Compression White Paper.
When deciding whether to use Unity data reduction, SQL Server database-level compression, or both, consider the following information:
For more information about SQL Server row and page compression for rowstore tables and indexes, and columnstore and columnstore archival compression for columnstore tables and indexes, see Data Compression in Microsoft SQL Docs.
Note: All compression requires CPU resources. At high throughput levels, compression can have a detectable impact on performance. The heavy write ratios of OLAP workloads can also reduce the benefits of compression for a SQL Server database.
The Dell EMC Unity engineering team explored the potential savings using real-world data reduction ratios on a Unity array. The team gathered data from VMware virtual machines (VMs), file-share data, SQL Server databases, Microsoft Hyper-V VMs, and more.
The study results showed that the SQL Server log file reduction was nearly 10 times that of the data file:
The SQL Server database was provisioned with two volumes. The database files were stored on one volume and the transaction logs on another. Using data reduction technology with database volumes can produce storage savings; however, you must consider the performance impacts when deciding whether to enable deduplication on database volumes. While actual database reduction levels can vary based on the data being stored, study results showed that storage space for SQL Server transaction logs can be reduced significantly.
For more information about the Unity engineering team’s study and findings, see Dell EMC Unity: Data Reduction Analysis.
Before enabling data reduction on a storage object, consider these best practices:
Note: Data reduction on previous Unity x80F models running OE 5.0 provided space savings if the storage block data was at least 25 percent compressible.
Contact your Dell EMC representative for tools that can analyze the potential for data compressibility.
For more information about compression, see the Dell EMC Unity: Data Reduction White Paper.