Given the management and usage simplicity of Everpure products, there is little that has to be done to get great performance out of Everpure FlashArray with Microsoft SQL Server.
Adhering to best practices for Microsoft SQL Server and for the planned operating system stack, whether deployed on a hypervisor or on bare metal, is essential to getting the most performance out of FlashArray.
Microsoft SQL Server Best Practices
-
Change Windows multipath-io timings for bare metal/in-guest deployments: Multipath-IO and Storage Settings
-
Ensure new disks are initialized as GPT: Initializing New Disks
-
Format all volumes as NTFS with 64KB allocation units: SQL Server Best Practices Article
-
Format all volumes using Large File Record Segments
-
This is a best practice for avoiding NTFS limitations when dealing with large files that could become highly fragmented on disk, read more here: NTFS Overview
-
-
Create dedicated volumes for:
-
TempDB
-
Create at least one data file per logical CPU core to reduce the likelihood of contention: Recommendations to reduce allocation contention in SQL Server tempdb database
-
-
System databases
-
-
User databases
-
Data and log files should be placed on separate volumes
-
Multiple volumes per database with multiple files distributed across them can increase IO throughput for both write-heavy loads as well as backup and restore operations.
-
If volume snapshots will be used, think about the granularity of the snapshots before deciding on volume layout
-
Dedicated log and data volumes per database, for example, enable snapshot operations at the individual database level
-
-
-
Create data files of equal size for a given database to reduce hot spots and contention: File and filegroup fill strategy
-
Enable instant file initialization: Database instant file initialization
-
Set fixed-size autogrowth settings on all files
Snapshots
Configure daily snapshots for critical systems that need high-availability and quick recovery. Snapshots can be used to solve a number of pain points:
-
Almost instant seeding of availability groups, regardless of data size
-
Taking near-instantaneous snapshots of multi TB databases to supplement backups
-
Offloading database consistency checks to another server
-
Near-instant rollback after a bad release or cyber-security incident
See our whitepaper on Using Databases with FlashArray Volume Snapshots for more information
Compression and Encryption
When using native SQL Server compression and encryption options, these features may impact the data reduction capabilities of FlashArray. Keep this in mind when capacity planning.
| SQL Server Feature | Data Reduction Impact |
|---|---|
| Column-level Encryption | Affects only the encrypted columns; those values become less compressible and less de‑duplicable, reducing overall data reduction. |
| Always Encrypted | Deterministic encryption does not compress well but may still de‑duplicate effectively; non‑deterministic encryption neither compresses nor de-duplicates well, leading to poor data reduction. |
| Transparent Data Encryption (TDE) | Severely degrades data reduction, with effective reduction ratios approaching 1:1. |
| Page/Row Level Compression | Reduces the amount of compression possible, though global de‑duplication can still provide some efficiency gains. |
| Backup Compression | When FlashArray is the backup target, backup compression typically improves backup and restore times but makes backup data far less compressible; global de‑duplication can still provide some reduction. |
| Backup Encryption | Produces highly random backup data that is neither compressible nor de‑duplicable, resulting in minimal data reduction. |
If TDE is being used for compliance reasons, see FlashArray Security for more information on how FlashArray may fulfill compliance needs.