This paper provides best practices for using Dell Unity XT storage systems in a Microsoft SQL Server environment. SQL Server is an incredibly robust product that can be used in various solutions. Therefore, the relative priorities of critical design goals such as performance, manageability, and flexibility depend on your environment. This paper provides important considerations and recommendations to help meet your design goals. For general best practices using Dell Unity XT systems, see the Dell Unity XT Best Practices Guide on the Dell Unity Info Hub.

These guidelines are intended to cover most use cases and are recommended by Dell Technologies, but not strictly required.

This paper was developed using the Dell Unity XT 650F All-Flash array but is also applicable when using all Dell Unity XT All-Flash arrays.

If you have questions about the applicability of these guidelines in your environment, contact your Dell Technologies representative to discuss the appropriateness of the recommendations.

This document is intended for Dell Unity XT administrators, database administrators, architects, partners, and anyone responsible for configuring Dell Unity XT storage systems. Some familiarity with Dell unified storage systems is assumed.

Dell Technologies and the authors of this document welcome your feedback on this document. Contact the Dell Technologies team by email.

Author: Doug Bernhardt

Note: For links to other documentation for this topic, see the Dell Unity XT Info Hub.

Dell Unity XT arrays are virtually provisioned, flash-optimized storage systems designed for ease of use. This paper covers the all-flash array models. This section provides foundational array technologies that support the application-specific sections that follow. Additional information for this section can be found in the Dell Unity XT: Best Practices Guide.

Dell Unity XT storage supports two types of storage pools on all-flash storage systems: traditional pools and dynamic pools. Traditional pools apply RAID protection to discrete groups of drives within the storage pool. Dynamic pools apply RAID to groups of drive extents from drives within the pool and allow for greater flexibility in managing and expanding the pool. Dynamic pools must be configured from all-flash drives; dynamic pools cannot be built with HDDs.

In general, it is recommended to use a single storage pool within Dell Unity XT arrays to reduce complexity and increase flexibility. However, it may be appropriate to configure additional storage pools in the following scenarios:

• Separate workloads with different I/O profiles
• Dedicate resources to meet specific performance goals
• Separate resources for multitenancy
• Create smaller failure domains

Additional information can be found in the Dell Unity XT: Dynamic Pools white paper.

Storage pool capacity

Storage pool capacity is used for multiple purposes:

• Store all data written into storage objects—LUNs, file systems, and datastores, and VMware® Virtual Volumes™ (vVols)—in that pool
• Store data that is needed for snapshots of storage objects in the pool
• Track changes to replicated storage objects in that pool

Storage pools must maintain free capacity to operate properly. By default, Dell Unity XT storage will raise an alert if a storage pool has less than 30% free capacity. Also, it will automatically invalidate snapshots and replication sessions if the storage pool has less than 5% free capacity. Dell Technologies recommends that a storage pool always has at least 10% free capacity.

All-flash pool

All-flash pools provide the highest level of performance in Dell Unity XT storage. Use an all-flash pool when the application requires the highest storage performance at the lowest response time.

• Dell FAST™ Cache and FAST VP are not applicable to all-flash pools.
• Compression is only supported on an all-flash pool.
• Snapshots and replication operate most efficiently in all-flash pools.
• Dell Technologies recommends using only a single drive size and a single RAID width within an all-flash pool.

For example: For an all-flash pool, use only 1.6 TB SAS flash 3 drives and configure them all with RAID 5 8+1.

Hybrid pool

Hybrid pools are not applicable when using Dell Unity XT All-Flash arrays.

This section describes some of the native features available on the Dell Unity XT platform. Additional information about each of these features can be found in the Dell Unity XT: Best Practices Guide. Features not applicable to the all-flash array models covered by this paper are noted.

FAST VP accelerates performance of a specific storage pool by automatically moving data within that pool to the appropriate drive technology based on data access patterns. FAST VP is only applicable to hybrid pools within a Dell Unity XT Hybrid flash system.

FAST Cache is a single global resource that can improve performance of one or more hybrid pools within a Dell Unity XT Hybrid flash system. FAST Cache can only be created with SAS flash 2 drives and is only applicable to hybrid pools.

FAST VP and FAST Cache are further explained in the Dell EMC Unity: FAST Technology Overview paper.

Dell Unity XT offers several features including compression and deduplication features for performing data reduction. A full explanation of these features can be found in the Dell Unity XT: Data Reduction white paper.

Data at Rest Encryption (D@RE) is controller-based encryption that does not impact system performance. Dell Technologies recommends ordering Dell Unity XT systems as encryption-enabled, if appropriate for your environment.

Note: Encryption can only be enabled at the time of system installation with the appropriate license.

If encryption is enabled, Dell Technologies recommends making external backups of the encryption keys after system installation and immediately following any change in the system’s drive configuration. This includes creating or expanding a storage pool, adding new drives or replacing a faulted drive. Additional information regarding Data at Rest Encryption can be found in the Dell Unity XT: Data at Rest Encryption white paper.

Dell Technologies recommends setting host I/O limits on workloads that might monopolize pool Technologies and starve other applications of their required performance.

For example: Limit the bandwidth available to large-block applications that may be increasing the latency on other small-block workloads.

Additional information can be found in the Dell Unity XT: Unisphere Overview white paper.

The I/O storage system is a critical component of any SQL Server environment. Sizing and configuring a storage system without understanding the I/O requirements can have disastrous consequences. Analyzing performance in an existing environment using a tool like Live Optics can help define the I/O requirements. For best results, capture performance statistics for at least 24 hours that include the system peak workload.

While every environment is unique, an online transaction processing (OLTP) workload typically consists of small, random reads and writes. A storage system that services this type of workload is primarily sized based on capacity and the number of IOPS required.

An online analytic processing (OLAP) or decision support system (DSS) workload typically consists of large, sequential reads. A storage system that services this type of workload is primarily sized based on throughput. When designing for throughput, the performance of the entire path between the server and the drives in the Dell Unity XT array needs to be considered. For best throughput, consider using 16 Gbps Fibre Channel (FC) or 10 Gbps iSCSI connectivity to the array, and 12 Gbps SAS connectivity from the controllers to the disk enclosures. To meet high throughput requirements, multiple HBAs may be required in the server, the array, or both.

The most common scenario is a mixed workload environment. Typically, SQL Server I/O patterns do not strictly fall into an OLTP or OLAP pattern. This behavior makes SQL Server workloads challenging because no two workloads behave the same. In addition, the same SQL Server host or instance may be servicing multiple applications or transaction workloads.

Mixed workload can also imply that multiple applications (in addition to SQL Server) are residing on the same host or accessing the same storage. The combined workload of these applications invalidates any typical application I/O usage pattern. For these reasons, gather performance metrics for best sizing results.

In general, it is recommended to use fewer storage pools within Dell Unity XT arrays because this reduces complexity and increases flexibility. Dell Technologies recommends using a single virtual disk pool when implementing SQL Server. This provides better performance by leveraging the aggregate I/O bandwidth of all disks to service I/O requests from SQL Server. A single drive pool is also easier to manage, allowing an administrator to quickly and easily adapt the storage system to satisfy the ever-changing workloads that are common in SQL Server environments. Before creating multiple storage pools to separate workloads, understand the various Dell Unity XT features that are available for managing and throttling specific workloads.

By default, Dell Unity XT storage chooses RAID 5 as the RAID protection level when creating a storage pool. This contradicts traditional guidance advocating RAID 1/0 for database workloads. The issue with traditional guidance is that it assumes spinning disks are used and does not consider SSDs and storage systems such as Dell Unity XT arrays that have been optimized for flash storage. Internal testing has shown that the performance difference in most configurations between RAID 5 and RAID 1/0 in Dell Unity XT All-Flash systems is negligible unless the workload is extremely write intensive for an extended time. Usually, the small performance gain of RAID 1/0 is not worth the reduced capacity and it is recommended to use the default configuration of RAID 5. For heavy write workloads where maximum write performance is required, RAID 1/0 can be used.

Once the I/O requirements have been defined, it is easy to determine whether the hardware can provide the wanted performance by running some simple tests. Diskspd is a free Microsoft Windows based utility that can simulate I/O patterns generated by SQL Server, FIO is a tool that performs similar functions on Linux.  There are several other utilities available as well. When selecting a utility to simulate I/O, verify that it meets the following requirements:

• Ability to configure block size
• Ability to specify number of outstanding requests
• Ability to configure test file size
• Ability to configure number of threads
• Support for multiple test files
• Does not write blocks of zeros during tests

Validating the I/O path

The first item to test on a new configuration is the path between the server and the array. Running a large block sequential read test using small files should saturate the path between the server and the array. This test verifies that all paths are fully functional and can be used for I/O traffic. Run this test on a dedicated server and array; a live system could cause significant performance issues.

To validate the I/O path, run a large block sequential read test using the following guidelines:

• Create one LUN per storage processor
• Format the volumes using a 64 KB allocation unit
• Use a block size of 512 KB for the test
• Configure the test for 32 outstanding I/Os
• Use multiple threads; eight is the recommended starting point

If the displayed throughput matches the expected throughput for the number of HBA ports in the server, the paths between the server and Dell Unity XT array are set up correctly.

Validating the drives

The first item to test on a new configuration is the path between the server and the array. Running a large block sequential read test using small files should saturate the path between the server and the array. This test verifies that all paths are fully functional and can be used for I/O traffic. Run this test on a dedicated server and array; a live system could cause significant performance issues.

To validate the I/O path, run a large block sequential read test using the following guidelines:

• Create one LUN per storage processor
• Format the volumes using a 64 KB allocation unit
• Use a block size of 512 KB for the test
• Configure the test for 32 outstanding I/Os
• Use multiple threads; eight is the recommended starting point

If the displayed throughput matches the expected throughput for the number of HBA ports in the server, the paths between the server and Dell Unity XT array are set up correctly.

Proper architecture and configuration of the SQL Server environment is critical to optimize performance and manageability of the Dell Unity XT and SQL Server environment. Apply the following best practices when designing, configuring, and managing SQL Server databases on Dell Unity XT.

Creating volumes

Dell Unity XT storage is virtualized to take advantage of all the drives in the storage pool. Characteristics such as RAID level and storage tier can have a big impact on performance and are configured at the volume level. Dell Unity XT snapshots are also configured at the volume level. There are many types of files that are part of a SQL Server instance. Those types of data often have different performance and snapshot requirements. For performance-sensitive applications, Dell Technologies recommends creating at least five volumes for an instance of SQL Server as shown in the following table.

Table 1.      Volume provisioning recommendations

Place databases that span multiple LUNs within a consistency group. This helps ensure that other features such as snapshots, replication, and thin clones will be configured properly and maintain data consistency.

Performance considerations

When one group of databases requires high performance and another group does not, consider creating a set of volumes for each group of databases, placing each set in its own consistency group. Even if there is only one tier of storage, this strategy will make it easier to adjust the storage configuration in the future.

Having multiple volumes has an additional benefit in dual-controller systems. All I/O requests for a given volume are processed by the controller that owns the volume. While a volume can be owned by either controller, a volume is owned by one controller at a time. Having many volumes makes it easier to distribute the I/O load evenly across both controllers. Databases that have high-performance requirements can be spread across two or more datafiles on separate volumes to leverage resources on both controllers. In cases where maximum performance is required from the array, as many as 16 volumes may be required.

Flexibility and manageability

For ultimate flexibility, create a volume for each user database file. This allows storage and snapshot configuration for each individual database. With thin provisioning, there is no space penalty for creating a lot of volumes. However, many volumes can be difficult to manage, especially in virtualized environments. It is up to the DBA or storage administrator to find the right balance between flexibility and maintainability when determining the number of volumes to create. Virtualized SQL Server environments are a good example of where it may make sense to place multiple file types on a single volume. Understanding the database I/O patterns is critical to making the best decisions.

Windows setup and configuration

Allocation unit size

Use a 64 KB allocation unit size when formatting volumes that will contain database files (transaction log and data) or database backups.

MPIO

Set the MPIO policy to round robin for all database volumes. Round robin is the default for Windows 2008 and newer. It allows all paths to be used, enabling higher throughput between the server and the array. This setting works best for most environments as it is easy to manage and performs well.

For database servers with many I/O ports, the overhead of the round robin MPIO policy can reduce the maximum throughput to the storage array. To maximize throughput, create a volume for each port on the server and use the failover only MPIO policy to define a single, unique, active path for each volume. Define all other paths as standby. By using a datafile of the same size on each volume, the data will be evenly distributed across the volumes. This strategy is more complex to set up and maintain. Use it only in environments that require maximum throughput.

Creating SMB file shares for SQL Server deployment is a simple process with three basic steps. These steps include creating a NAS server, the file system, and the SMB share. Dell Unisphere™ contains setup wizards that walk through the entire process. However, there are some important considerations to keep in mind for SQL Server workloads. These recommendations are specific to hosting SQL Server databases on Dell Unity XT storage and do not necessarily apply to using SMB file shares as backup files.

NAS server

The configuration of the NAS server has the greatest impact on the performance of the SMB file shares. When configuring the NAS server, this is where the storage pool and storage processor are selected. Since the NAS server resides on the selected storage processor, balancing NAS servers across both storage processors is important for efficient system utilization and best performance. A NAS server can only use resources on the storage processor that it is running on. A minimum of two NAS are required to balance workload across both storage processors.

Network interfaces are configured on the NAS server and are important to performance and availability. Multiple network interfaces are available for performance and redundancy. These interfaces map to a specific I/O port and multiple NAS servers can share the same port.

File system

When configuring the file system for SQL Server, make sure both the Sync Writes Enabled and Oplocks Enabled settings are turned on. These SMB protocol settings are found under the advanced file system properties. These are the only two SMB protocol settings that are recommended for use with SQL Server.

SMB shares

Enable the Continuous Availability option in the SMB properties when configuring an SMB share for SQL Server. This option, along with the SMB protocol settings Sync Writes Enabled and Oplocks Enabled ensures that the file handles used by SQL Server will persist in the event of a NAS server failover to the other storage processor.

VMware datastore

When creating file-based VMware datastores for SQL Server on file-based systems, select SQL Server for the host I/O size. This will result in a 64 KB block size, which is recommended for SQL Server.

Memory

Unnecessary I/O can be avoided, and performance can be increased by allocating the proper amount of memory to SQL Server. SQL Server performs all I/O through the buffer pool (cache) and therefore uses a large portion of its memory allocation for the buffer pool. Ideally, when SQL Server performs I/O, the data is already in the buffer pool and it does not need to go to disk. This type of I/O is referred to as logical I/O and is the most desirable because it results in the best performance. If the SQL Server data does not need to reside in the buffer pool, it will need to access disk resulting in physical I/O.

Proper memory allocation is critical to SQL Server performance and can improve storage performance as well. Often, SQL Server and storage performance can be further improved by adding additional memory. Allocating more memory is better, but there is a point of diminishing returns that is unique to each environment. Refer to the Microsoft documentation about server memory configuration options for more information,

Buffer pool extension

With SQL Server 2014, the buffer pool can be extended to a file on the file system to provide additional space to cache data or index pages. Using this feature can provide significant performance benefits without adding memory to the database server sometimes. By caching more pages on the server, the I/O load on the array is reduced.

When placing the buffer pool extension on the array, create a separate volume for the buffer pool extension and do not take snapshots of the buffer pool extension volume. The buffer pool data is repopulated by SQL Server when the instance is restarted therefore data recovery does not apply.

Persistent memory

SQL Server 2016 introduced support for Persistent memory (PMEM) and the capabilities are being expanded with SQL Server 2019 to cover more scenarios and the Linux® operating system. Often, PMEM can be used to accelerate challenging I/O workloads and make I/O patterns more efficient with SQL Server PMEM features such as tail-of-log-cache and Hybrid Buffer Pool. Virtualized environments running Hyper-V or VMware can also take advantage of PMEM making it a wise investment. Dell supports PMEM starting with Dell PowerEdge™ 14th-generation (14G) servers. For more information, see Watch your transactional database performance climb with Intel Optane DC persistent memory and the Dell NVDIMM-N Persistent Memory User Guide.

Database compression

The overall I/O workload can be reduced by enabling database compression in SQL Server. While there is a tradeoff in terms of CPU utilization on the database server, it is still a viable option to consider and test in any environment. Database compression reduces I/O by reducing the amount of data that needs to be stored. The SQL Server data pages are compressed in memory before being written to disk resulting in fewer pages needed to store the same number of rows and therefore less I/O.

Instant file initialization

By default, SQL Server writes zeros to the datafile during the allocation process. The process of zeroing out the datafiles consumes I/O and acquires locks as the SQL Server data pages are written. This activity can occur for minutes or even hours depending on the file size. While this may seem minor, writing zeros to these files can occur at critical periods.  Time duration and performance are critical during database auto growth, datafile expansion, replication, or restoring a database as part of a disaster recovery event.

When Instant File Initialization is enabled, SQL Server will skip the process of zeroing out its datafiles when allocating space. Dell Technologies recommends enabling Instant File Initialization to maximize datafile growth performance.

Resource Governor

Resource Governor can be used to reduce the impact of a user running an I/O intensive report by limiting the maximum number of CPU or IOPS that user can perform. Resource Governor can also be used to protect against poorly developed queries. While a query throttled by the Resource Governor may take more time to complete, overall database performance will be better.

Database design considerations

Reducing SQL Server I/O requires a holistic approach. Many of the items in this section will require involvement from the whole team responsible for the SQL Server applications. This includes the business owner, architect, developer, database administrator, and system administrator. Decisions at the design level have a multiplied downstream impact. Data is written and read multiple times and duplicated in various types of database copies including databases copied for other uses such as testing and reporting, replicated databases, replicated storage, and backups.

One of the most challenging aspects of SQL Server is that the I/O pattern and the amount of I/O that is generated can vary greatly depending on the application, even if those applications have databases of the same size. This is because the design of both the database and the data access code control SQL Server I/O.

Database tuning can be one of the most cost-effective ways to reduce I/O and improve scalability. At a high level, consider the following areas when tuning a database to reduce I/O.

Database design

The foundation of the entire database and the schema for how data will be stored and ultimately accessed is determined by the database design. The database design should support both usability and efficient data access. This includes efficient table design and datatypes as well as indexes, partitioning, and other features that can improve efficiency. It is common for database design to only be focused on usability while performance and scale are overlooked.

Query design

How a query is written can greatly affect the amount of I/O SQL Server needs to perform when running the query. Queries should return only the required amount of data in the most efficient manner possible. Tune the queries responsible for consuming a relatively large number of resources for best performance and scale.

Application design

Consider how applications are using the data and how it is requested. Sometimes code and component reuse can result in the same data being unnecessarily retrieved repeatedly. All data access should be purposeful.

Maintenance

SQL Server uses a cost-based optimizer to generate query plans for data access. These plans are based on the statistics regarding how data is distributed in the tables. If the statistics are inaccurate, bad query plans may result and unnecessary I/O will be performed. Proper database maintenance includes ensuring that statistics are up to date.

Frequent data modifications can also lead to fragmentation within SQL Server datafiles, producing unnecessary I/O. Fragmentation can be addressed through index reorganization or rebuilds as part of regular database maintenance.

The database maintenance process itself can also have a large I/O impact. Typically, every table and index does not need to be rebuilt or reorganized every time maintenance is run. In addition, table partitioning strategies can also be leveraged to make the maintenance process more selective. Consider implementing intelligent maintenance scripts that use usage statistics to perform maintenance on an as-needed basis.

For mission-critical databases, maintenance activities need to be considered as part of the overall design. If maintenance is not considered as part of the overall process, issues can arise, such as unmanageable sizes and feature incompatibilities that limit available options and strategies.

There are several features in Dell Unity XT arrays that when used in a SQL Server environment warrant additional discussion. The following sections provide some best practices on these features and their integration with SQL Server.

The Dell Unity XT Data Reduction feature includes both deduplication and compression of 8k data blocks at an individual LUN or file system level. Since both Dell Unity XT storage and Microsoft SQL Server offer data compression, there are several factors to consider when choosing what to use. There is no single recommendation. The best choice will depend on several factors such as the contents of the database, the storage layout of the database, the amount of available CPU on both the storage and the SQL Server hosts, and the amount of I/O resources.

In addition to data reduction, the Advanced Deduplication feature can be enabled if data reduction is enabled. Advanced Deduplication reduces the amount of storage needed for user data by keeping only a few copies (often one copy) of a data block with a given content. The deduplication scope is a single LUN or file system, so keep this in mind when choosing the storage layout of the database. Fewer LUNs will result in better deduplication, but more LUNs are needed for maximum performance.

Snapshots provide a fast and space-efficient way to protect SQL Server databases. When using snapshots with SQL Server databases, there are some important considerations to ensure a successful database recovery.

All components of a SQL Server database must be protected as a set. When data and log files are on separate LUNs, these LUNs need to be part of a consistency group. The consistency group will ensure that the snapshot is taken at the exact same time on all LUNs in that group. When data and log files are on multiple SMB file shares, the shares need to reside on the same file system.

When performing a SQL Server database recovery from a block-based snapshot, if the SQL Server instance is to remain online, the Attach to Host action in Unisphere is recommended. For file-based recovery, an additional SMB share is created using the snapshot as the source. Once the volumes are attached, the database can be attached under a different name or used to replace the existing one.

When performing a recovery using the Snapshot Restore method in Unisphere, bring the SQL Server instance offline. SQL Server is unaware of the underlying recovery operations. Taking the instance offline will ensure the volumes are not corrupted by database write activity before recovery. Once the instance is restarted, SQL Server crash recovery will run and bring the databases to a consistent state.

Thin clones are based on snapshot technology and are a great way to present a read/write copy of databases. They are the preferred way to use snapshots for presenting database copies because they have many of the same data services as a regular LUN. Thin clones present SQL Server database copies in a fast and space-efficient manner that cannot be performed 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.

When using the refresh feature with thin clones, make sure to take all databases offline that are contained on the thin clone before performing the refresh operation. SQL Server is unaware of the thin-clone-based LUNs being reverted. Failure to offline databases before this operation can result in data inconsistency errors or incorrect data results from SQL Server.

SQL Server 2022 introduced the Transact-SQL (T-SQL) snapshot backup feature that allows array-based snapshots to be used for all common backup and restore scenarios. T-SQL snapshot backup supports point-in-time restores using differential backups and transaction log backups. This feature was implemented as extensions to existing T-SQL commands. Also, it works across all SQL Server platforms and does not require any additional components such as Microsoft Volume Shadow Copy Service (VSS) provider. The Dell Technologies Blog Post SQL Server 2022 – Time to Rethink your Backup and Recovery Strategy covers some additional history and information about this new SQL Server 2022 feature.

The T-SQL snapshot backup feature allows Unity XT snapshots to be used as a primary option for all backup and recovery workflows, including disaster recovery, repurposing, and reseeding Always-On Availability Groups. This allows backup and recovery operations to be run in seconds, regardless of the database size.

The following workflow examples detail the process for performing T-SQL snapshot backup and restore on Windows. For Linux operating systems, the workflow is the same; substitute Windows disk commands with the appropriate Linux commands for mounting and unmounting file systems.

Backup workflow

1. Issue the following ALTER DATABASE command to prepare the database for snapshot where SnapTest is your database name: ALTER DATABASE SnapTest SET SUSPEND FOR SNAPSHOT_BACKUP=ON
2. Create the snapshot on Unity XT using the Unisphere UI or Unisphere CLI.
3. Issue a BACKUP DATABASE command using the option WITH METADATA_ONLY. This will create an entry in the backup metadata file for this backup. The MEDIANAME and MEDIADESCRIPTION fields are used to store information about the location of the snapshot: BACKUP DATABASE SnapTest TO DISK = 'c:\temp\SnapTest_SQLBackupFull.bkm' WITH METADATA_ONLY,NOFORMAT,MEDIANAME='Unity XT U8',MEDIADESCRIPTION='consistency group: SQLDemo',NAME='SnapTest backup',DESCRIPTION='desc'

Note: Although the BACKUP DATABASE command is used here, it functions differently. It only writes a small amount of metadata used to restore the database. This metadata file combined with the related Unity XT snapshot is required for a database recovery. Without the correct metadata file, the database files can be attached for recovery but point in time recovery using differential or T-LOG backups will not be possible. Therefore, descriptive fields are used in the examples to document how the backup metadata file and its Unity snapshot are related and where to find them.

Restore workflow when replacing existing database

1. Drop the existing database.

2. Offline the disks used by the existing database with Windows Disk Management or PowerShell. The following command will offline a Windows disk with the drive letter “E”: Set-Disk (Get-Partition -DriveLetter E | Get-Disk | Select number -ExpandProperty number) -isOffline $true
3. Restore the Unity XT snapshot either by using the Unisphere UI Manager to select the proper volume group and performing “Restore” or the UEMCLI.
4. Online the disks with Windows Disk Management or PowerShell. The following command will online all offline disks: Get-Disk | Where-Object IsOffline -Eq $True | Select Number | Set-Disk -isOffline $False
5. Issue the RESTORE DATABASE WITH METADATA_ONLY command to begin the recovery process. The examples below recover the database named SnapTest using a backup metadata file “c:\temp\SnapTest_SQLBackupFull.bkm”.
   1. When performing a simple recovery, use the RECOVERY option to immediately bring the database online: RESTORE DATABASE SnapTest FROM DISK = 'c:\temp\SnapTest_SQLBackupFull.bkm' WITH FILE=1,METADATA_ONLY,RECOVERY

NOTE: WITH FILE=1 is used to reference a file in a backup set. T-SQL snapshot backup is a good use for backup sets since the backup metadata files are small.

2. When performing a point-in-time recovery and applying differential, transaction log backups, or both use the NORECOVERY option: RESTORE DATABASE SnapTest FROM DISK = 'c:\temp\SnapTest_SQLBackupFull.bkm' WITH FILE=1,METADATA_ONLY,NORECOVERY
3. For point in time recoveries, apply differential or transaction log backups per the standard recovery process.

Restore workflow when restoring as a copy

1. Create a copy of the database volumes from a Unity XT snapshot using the Unisphere UI or UEMCLI.

2. If using the Unisphere UI:
   1. Select the proper Unity XT Consistency Group and under “More Actions” choose “Clone”. This will launch a wizard.
   2. Select the snapshot.
   3. Provide a name for the thin clone.
   4. Verify the storage.
   5. Configure Access (map volumes to the host).
   6. Configure additional snapshots or replication if needed.
3. If using the UEMCLI, run the proper commands to perform the following:
   1. Create a clone of the Consistency Group.
   2. Mount the newly created volumes to the host.
   3. Create additional snapshots schedule or replication if wanted.
4. Online the disks with Windows Disk Management or PowerShell and assign new drive letters or mount points.
5. Issue the RESTORE DATABASE WITH METADATA_ONLY command to begin the recovery process. The examples below recover the database named SnapTest using a backup metadata file “'c:\temp\SnapTest_SQLBackupFull.bkm” and specify the database files should reside on drive letters “P”, “Q” and “R”.
   1. When performing a simple recovery, use the RECOVERY option to immediately bring the database online: RESTORE DATABASE SnapTest FROM DISK = 'c:\temp\SnapTest_SQLBackupFull.bkm' WITH FILE=1, MOVE 'SnapTest1' to 'p:\sql\SnapTest1.mdf',MOVE 'SnapTest2' to 'q:\sql\SnapTest2.mdf',MOVE 'SnapTest_log' to 'r:\sql\SnapTest_log.ldf', METADATA_ONLY,RECOVERY
   2. When performing a point-in-time recovery and applying differential, transaction log backups, or both use the NORECOVERY option: RESTORE DATABASE SnapTest FROM DISK = ''c:\temp\SnapTest_SQLBackupFull.bkm' WITH FILE=1, MOVE 'SnapTest1' to 'p:\sql\SnapTest1.mdf',MOVE 'SnapTest2' to 'q:\sql\SnapTest2.mdf',MOVE 'SnapTest_log' to 'r:\sql\SnapTest_log.ldf',METADATA_ONLY,NORECOVERY
   3. For point in time recoveries, apply differential or transaction log backups per the standard recovery process.

Kubernetes

Due to the clustered architecture of Kubernetes, there are special storage considerations when running SQL Server instances on Kubernetes. Kubernetes has created its own method of dealing with persistent storage in a Kubernetes cluster using persistent volumes.

Dell Container Storage Modules (CSM) for Unity XT enable integration and automation of Unity XT storage resources to Kubernetes. Unity XT storage integrates with Kubernetes through the Unity XT CSI driver. The Unity XT CSI driver and CSI drivers for other Dell Technologies products can be found on GitHub.

Whenever possible, perform storage operations through Kubernetes. In some cases, modifying CSI volumes directly on the Unity XT appliance can cause CSI operations to fail or volumes to be orphaned. For example, CSI volumes added to a consistency group need to be removed from the consistency group before the CSI driver can delete them.

The T-SQL snapshot backup and restore process is streamlined on Kubernetes. All snapshot and volume orchestration is handled by Kubernetes and the Dell Unity CSI driver. Therefore, all operations can be performed through the Kubernetes control plane. This eliminates the need for interaction with Dell Unity or the host operating system.

Backup workflow

1. Issue the following ALTER DATABASE command to prepare the database for snapshot where SnapTest is your database name: ALTER DATABASE SnapTest SET SUSPEND FOR SNAPSHOT_BACKUP=ON

2. Create the snapshot on Unity XT. This is done by creating a K8s Volume Snapshot. Below is an example of the YAML used to create a Volume Snapshot “mssql-data-snap” of a Persistent Volume Claim “mssql-data”. Perform a Volume Snapshot for each Persistent Volume Claim that contains database data or log files.

apiVersion: snapshot.storage.k8s.io/v1

kind: VolumeSnapshot

metadata:

  name: mssql-data-snap

spec:

  volumeSnapshotClassName: unity-snapshot

  source:

    persistentVolumeClaimName: mssql-data

NOTE: At the time of writing, it is possible to snapshot a volume group with the Unity XT CSI driver, but not recover the volume group. Recovery of a volume group is done at the volume level. Therefore, this example operates at the volume, not the volume group level for workflow consistency.

3. Optionally, volume snapshots can be viewed with the kubectl get volumesnapshot command to verify they were created successfully. The new snapshots will also be visible in the Unisphere UI. 
4. Issue a BACKUP DATABASE command using the option WITH METADATA_ONLY. This will create an entry in the backup metadata file for this backup. The MEDIANAME and MEDIADESCRIPTION fields are used to store information about the location of the snapshot: BACKUP DATABASE SnapTest TO DISK = '/var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm' WITH METADATA_ONLY,NOFORMAT,MEDIANAME='Dell UnityXT PS8',MEDIADESCRIPTION='K8s volume snapshots',NAME='Unity U8',DESCRIPTION='PVCs: mssql-data mssql-data2 mssql-log'

Restoring SQL Server databases on K8s

Due to storage volume functionality in Kubernetes, the concept of removing or altering storage volumes from running containers (in this case a SQL Server Instance) is not supported. Therefore, it is recommended to run a single database per containerized instance of SQL Server. A single database per instance allows the entire container/SQL Server instance to be dropped and re-created to accommodate storage addition and removal. The examples below assume a single database per SQL Server instance.

For more information about K8s Persistent Volumes, refer to the Kubernetes documentation on Persistent Volumes.

Restore workflow

1. If the database exists, drop the existing database by deleting the pod definition for the SQL Server instance and the Persistent Volume Claims (PVC) that contains database data and log files. A suggested way to do this is to contain all objects in a K8s deployment, and then delete the deployment.

2. Create PVCs and the SQL Server pod. The new PVCs will reference a snapshot as the dataSource. In the example below, the PVC “mssql-data-copy” is being created based on the data source “mssql-data-snap”. Create a PVC for each volume required.

Note: Make sure the storage size matches the size of the source volume or an error will result.

kind: PersistentVolumeClaim

apiVersion: v1

metadata:

  name: mssql-data-copy

  labels:

    volume-group: db

spec:

  accessModes:

  - ReadWriteOnce

  resources:

    requests:

      storage: 5500Gi

  storageClassName: unity-xfs

  dataSource:

    name: mssql-data-snap

    kind: VolumeSnapshot

    apiGroup: snapshot.storage.k8s.io

3. Connect to the new instance and issue the RESTORE DATABASE WITH METADATA_ONLY command to begin the recovery process. The examples below recover the database named SnapTest using a backup metadata file “/var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm” and specify the location of the data and log files. These locations must match the mount paths in the SQL Server instance deployment definition.
   1. When performing a simple recovery, use the RECOVERY option to immediately bring the database online: RESTORE DATABASE SnapTest FROM DISK = /var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm' WITH FILE=1, MOVE 'SnapTest1' to '/var/opt/mssql/data1/snaptest1.mdf',MOVE 'SnapTest2' to '/var/opt/mssql/data2/snaptest2.ndf',MOVE 'SnapTest_log' to '/var/opt/mssql/log/snaptest.log', METADATA_ONLY,RECOVERY
   2. When performing a point-in-time recovery and applying differential, transaction log backups, or both use the NORECOVERY option: RESTORE DATABASE SnapTest FROM DISK = ' /var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm' WITH FILE=1, , MOVE 'SnapTest1' to '/var/opt/mssql/data1/snaptest1.mdf',MOVE 'SnapTest2' to '/var/opt/mssql/data2/snaptest2.ndf',MOVE 'SnapTest_log' to '/var/opt/mssql/log/snaptest.log', METADATA_ONLY,NORECOVERY
   3. For point in time recoveries, apply differential or transaction log backups per the standard recovery process.

Creating a high availability solution for SQL Server often requires a copy of the data on another storage device and synchronizing that data in some manner. Dell Unity XT replication provides data synchronization between Dell Unity XT systems. Data is replicated at the consistency group or at the LUN level allowing a choice of replication settings on a per-volume basis. Using Dell Unity XT replication can be an effective way to protect SQL Server databases due to the flexibility and configuration options that it provides. The variety of options provide a robust way to develop a replication scheme that provides the proper mix of performance and bandwidth efficiency while still meeting RTO and RPO requirements.

SQL Server databases consist of a set of one or more datafiles and one or more transaction log files that can be distributed across one or more volumes. When using Dell Unity XT replication to protect SQL Server databases that are on multiple volumes, contain all data and log volumes for a database within a single consistency group or file system. Replication is configured on the consistency group or file system which can contain volumes or shares for more than one database. Databases that require different replication settings will need to be on separate LUNs, consistency groups, or file systems.

When replicating volumes for SQL Server database protection, only replicate the volumes required for the user databases and ensure only those database files exist on replicated volumes. In addition, there is no need to replicate the tempdb database as it is used for temporary workspace only and is rebuilt when SQL Server is restarted. Carefully selecting which volumes to replicate and the contents of those volumes will eliminate unnecessary replication traffic.

In addition to Dell Unity XT replication features, SQL Server also contains various methods of data replication such as its own replication types and Always On Availability Group features as well as asynchronous and synchronous modes.

Many SQL Server applications have data encryption requirements on data at rest. Data at Rest Encryption (D@RE) can be used as an encryption solution for SQL Server without requiring any database or application changes. This also avoids any potential performance impact to the database server or the applications and has no performance impact on the array.

Note: D@RE is a licensed feature. It must be selected during the ordering processes and enabled at system initialization. D@RE cannot be enabled later.

Typically, a Dell Unity XT array is used to service multiple hosts and applications. These applications can have different service level requirements and different storage demands. In addition, a single array can provide services to multiple environments concurrently such as development, testing, and production. Traditionally, these scenarios have been difficult to manage to ensure that critical applications get the resources they need while managing less critical resources to make sure they do not overconsume.

Host I/O limits or Quality of Service (QoS)) provide an excellent means to manage these types of workloads. Instead of trying to manage these workloads with multiple storage pools, host I/O limits allow LUNs to be restricted to a specified amount of IOPS or bandwidth so they do not adversely impact other applications. Also, this allows storage administrators to ensure applications and environments adhere to budgeted limits which greatly simplify planning and management.

Host I/O Limits are great for SQL Server environments for several reasons. First, storage administrators can ensure that demanding SQL Server instances do not overwhelm the entire array by setting limits on database volumes. Also, if SQL Server is the priority application they can set limits on other LUNs on the system to ensure that SQL Server can get the resources it requires. Another great component of host I/O Limits is the ability to burst for a given limit for a specific period, both are which are user-configurable. In this way, small exceptions can still be allowed while maintaining balanced performance.

In development and testing environments, it can be difficult to determine if an application meets performance requirements. Typically, test and development databases are smaller than production databases. It is not always feasible to keep a copy of production data in these environments due to the storage costs or privacy concerns surrounding confidential data. The issue with smaller datasets is that the application can run faster on a smaller database during development or testing phases, and then encounter serious performance issues when deployed on a real dataset in production.

Host I/O Limits can be used to restrict the I/O on smaller datasets to highlight I/O intensive queries. Setting limits on databases in development and testing environments will help identify problem areas so they can be resolved before production deployment. The result is improved SQL Server service levels and greater scalability.

With SQL Server 2022, Microsoft introduced a feature called Ledger that allows database tables to be examined for tampering. This functionality is based on blockchain technology and creates a ledger table of data hash values that are stored in a blockchain. This ledger digest and its verification can be stored on a write once read many (WORM) storage device. This allows organizations to comply with data storage requirements such as SEC rule 17a-4(f) which address unauthorized data tampering and/or data deletion.

The Unity XT File-level retention (FLR) feature allows a WORM storage device to be created by creating a Unity XT file system and then setting the file-level retention. There are two levels of file-level retention with this feature, Enterprise (FLR-E) and Compliance (FLR-C). Essentially FLR-E prevents data changes from users and storage administrators to individual files, but the storage administrator can delete the entire file system. FLR-C prevents all deletion, including deletion of the file system.

To create a Unity XT file system to be used as a WORM device, enable FLR, select Enterprise or Compliance, and then leave the default and maximum retention period set to Unlimited.

Once the file system is in place, digests for Ledger tables can be created and stored on the Unity XT file system with File-level retention enabled.

The following article talks about how to configure digest storage for on-premises WORM storage such as Unity XT: https://techcommunity.microsoft.com/t5/sql-server-blog/ledger-automatic-digest-upload-for-sql-server-without-azure/ba-p/3630992

For more information about Unity XT FLR, refer to the Unity File-Level Retention paper.

This section covers features or products used for data protection. It also covers how application data protection features may be used or are integrated. These items are actionable for the storage administrator or application administrator role.

Dell AppSync™ is software that enables integrated Copy Data Management (iCDM) with the Dell primary storage systems, including Dell Unity XT arrays.

Dell AppSync, integrated with Dell Unity XT snapshots, simplifies and automates the process of generating, consuming, and managing copies of production data. This solution addresses copy management use cases in a consolidated SQL database environment for database recovery and database repurposing. AppSync automatically discovers application databases, learns the database structure, and maps it through the virtualization layer to the underlying Dell Unity XT storage. Orchestration of all the activities required from copy creation and validation through mounting the snapshots at the target host and launching or recovering the application. This solution supports and simplifies Microsoft SQL Server workflows that include refreshing, expiring, and restoring the production database.

Additional information about AppSync can be found in the paper AppSync Integration with Microsoft SQL Server and the AppSync Performance and Scalability Guidelines.

Snapshots create a slight increase the overall CPU load on the system and increase the overall drive IOPS in the storage pool. Snapshots also use pool capacity to store the older data being tracked by the snapshot, which increases the amount of capacity used in the pool until the snapshot is deleted. Although these overhead costs are small, consider the overhead of snapshots when planning both performance and capacity requirements for the storage pool.

Before enabling snapshots on a storage object, it is recommended to monitor the system and ensure that existing resources can meet the additional workload requirements. (See the section “Hardware Capability Guidelines” and Table 2 in the Dell Unity XT: Best Practices Guide.) Enable snapshots on a few storage objects at a time, and then monitor the system to be sure it is still within the recommended operating ranges before enabling more snapshots. Additional information can be found in the Dell Unity XT: Snapshots and Thin Clones white paper.

Dell Technologies recommends including a flash tier in a hybrid pool where asynchronous replication will be active. This is applicable to both the source and the destination pools.

Asynchronous replication takes snapshots on the replicated storage objects to create the point-in-time copy, determining the changed data to transfer and maintain consistency during the transfer. Consider the overhead of snapshots when planning performance and capacity requirements for a storage pool that will have replication objects.

Setting smaller RPO values on replication sessions will not make them transfer data more quickly, but smaller RPOs will result in more snapshot operations. Choosing larger RPOs, or manually synchronizing during nonproduction hours, may provide more predictable levels of performance. Additional information can be found in the Dell Unity XT: Replication Technologies white paper.

Dell Technologies recommends including a flash tier in a hybrid pool where synchronous replication will be active.

Synchronous replication transfers data to the remote system over the first Fibre Channel port on each SP. When planning to use synchronous replication, it may be appropriate to reduce the number of host connections on this port. When the CNA ports are configured as FC, CNA port 4 is defined as the synchronous replication port. If the CNA ports are configured as 10 GbE, port 0 of the lowest numbered FC I/O Module is the replication port. Additional information can be found in the Dell Unity XT: Replication Technologies white paper.

Dell RecoverPoint virtual edition provides continuous data protection with multiple recovery points to restore applications instantly to a specific point in time. RecoverPoint virtual edition consists of RecoverPoint Appliance (RPA) software deployed as a virtual appliance in an existing VMware ESXi™ VM environment. RecoverPoint virtual edition is a flexible deployment option which offers maximum simplicity with no dependency on a physical appliance, lowering TCO.

Dell.com/support is focused on meeting customer needs with proven services and support. Storage technical documents and videos provide expertise that helps to ensure customer success on Dell storage platforms. The Dell Unity XT Info Hub provides helpful links to document and tools.

Consult the following publications located on the Dell Unity XT Info Hub site.

• Dell Unity XT: Best Practices Guide
• Dell Unity XT: NAS Capabilities
• Dell Unity XT: Dynamic Pools
• Dell Unity XT: Data Reduction
• Dell Unity XT: Performance Metrics
• Dell Unity XT: Replication Technologies
• Dell Unity XT: Snapshots and Thin Clones
• Dell Unity XT: Unisphere Overview

There is a tremendous amount of SQL Server information available online. The following list includes several useful links.

• Dell SQL Server solutions
• Microsoft SQL Server homepage
• PASS Data Community Summit
• SQL Server Central