This section will discuss the best practices for SQL Server for Everpure Cloud Dedicated deployments.
Everpure SQL Server Best Practices
Everpure maintains recommended best practices for use with SQL Server in the topic Best Practices for Microsoft SQL Server on FlashArray. Although the title says FlashArray, it contains general best practices for all SQL Server deployments including Everpure Cloud Dedicated.
Formatting Volumes for Use with SQL Server
With an in-guest iSCSI volume provisioned from Everpure Cloud Dedicated and connected to the Windows VM instance running SQL Server, the next step is to setup and format that volume within the Windows operating system. Disk format type and allocation size are important selections to make for optimal performance of SQL Server volumes. To see Pure's best practices for formatting disks for SQL Server, consult the document Best Practices for Microsoft SQL Server on FlashArray.
Edition of SQL Server
The edition of SQL Server (Standard or Enterprise) has a direct impact in the amount of system resources that can be allocated for use with the SQL Server instance. The below screenshot demonstrates the number of variants available for SQL Server 2019 images in Azure using Windows Server 2022 as of the writing of this document. To read more about the different editions of SQL Server, see the topic Editions and supported features of SQL Server 2022 in Microsoft Learn. There will be a version for your target version of SQL Server as long as it is in current support.
The below table outlines the maximum amount of VM CPUs and RAM that can be allocated to the SQL Server service based upon SQL Server edition. This is an important consideration as it can be wasteful to oversize a VM type if only a Web or Standard license is available. To see updated specifications for SQL Server, see the topic Compute capacity limits by edition of SQL Server in Microsoft Learn.
|
SQL Server Edition |
Maximum Compute Capacity for a SQL Server Instance |
Maximum Memory Utilization for a SQL Server Instance |
|---|---|---|
|
Standard |
Lesser of 4 sockets or 24 cores |
128 GB |
|
Enterprise |
Operating System Maximum / Azure VM Type |
Operating System Maximum / Azure VM Type |
For large-scale production workloads, it is likely the SQL Server Enterprise Edition will be required in order to utilize enough VM resources to make performance acceptable.
tempdb Best Practices in Azure
tempdb is an ephemeral SQL Server system database where data is temporarily cached before being written to a permanent location. Because of its ephemeral nature, performance is the most important factor to consider. Enterprise features such as snapshots and volume resiliency should not be prioritized for tempdb so native block storage offerings from Azure provide the optimal solution. For more information on tempdb, see the tempdb section of the best practices document linked earlier.
Whenever possible, our recommendation for tempdb is to store it on the attached SQL Server data volume that is provisioned with the SQL VM upon deployment. This scenario is ideal as no additional cost will be incurred beyond the VM expense itself. However, in some scenarios very large SQL Server instances may overrun the included SQL Server data volume (from a capacity and/or performance standpoint) and in that scenario we recommend provisioning and attaching an additional Ultra SSD to the SQL Server VM to serve as the new tempdb location.
High Availability and Disaster Recovery
High Availability and Disaster Recovery (HADR) is concept that ensures a given SQL Server instance will be available when necessary and in the event a problem occurs, recover. Backing up and restoring databases is a fundamental cornerstone of any availability strategy. Follow Microsoft's best practices for highly available SQL Server deployments in the section "HADR configuration" in the Microsoft Learn topic Checklist: Best practices for SQL Server on Azure VMs.
Useful SQL Server Troubleshooting Articles and Options
Customers experiencing performance, resiliency or other issues with SQL Server and Everpure Cloud Dedicated should consult the following articles for further steps to diagnose, troubleshoot and resolve such issues.