SQL Server Workloads in Azure with Everpure Cloud Dedicated Technical Solution Design

Compute Layer

Azure Virtual Machines serve as the compute layer, hosting SQL Server instances on either Windows Server or supported Linux platforms. Azure virtual machines provide scalable compute resources to meet the demands of transactional and analytical workloads.

Key considerations for the compute layer include:

Performance optimization: Ensure virtual machines are configured with high-clock-speed processors and sufficient memory to minimize reliance on disk input/output.
Scalability: Resources should align with workload demands, scaling virtual machines as required to accommodate growth and performance requirements.

Virtual Machine Families for SQL Server

Azure Virtual Machines supports a variety of families optimized for different workload types:

E-series: Memory-optimized virtual machines offering up to 672GiB of RAM, ideal for large-scale transactional and analytical workloads.
M-series: High-memory virtual machines designed for extreme-scale and mission-critical SQL Server deployments.
D-series: General-purpose virtual machines that balance compute, memory, and storage performance, suitable for smaller or moderately demanding workloads.

Note: Minimum and recommended configurations depend on workload requirements, with more processor cores and higher clock speeds recommended for large-scale transactional and analytical workloads. The Microsoft Azure virtual machine best practice checklist for SQL Server provides a detailed and up to date overview of the optimized virtual machines for performance workloads.

Network Layer

The network layer ensures secure, low-latency communication between Azure Virtual Machines, Everpure Cloud Dedicated, and client connections. Azure Virtual Network provides the foundation for connectivity, with subnets segmented for specific roles to optimize traffic flow and maintain isolation.

Network Connection between Everpure Cloud Dedicated and Azure Virtual Machines

Everpure Cloud Dedicated communicates with Azure Virtual Machines using iSCSI over TCP/IP. This setup requires proper configuration of iSCSI initiators on Azure Virtual Machines to establish secure connections with Everpure Cloud Dedicated volumes.

To minimize latency and enhance security:

Deploy Everpure Cloud Dedicated and Azure Virtual Machines with storage protocol (iSCSI for Windows, NVM Express over TCP for Linux) connectivity in the same Azure Virtual Network.
Configure dedicated subnets for SQL Server client connections, storage communication, and management traffic.
Configure the Azure virtual machine's network interface with accelerated networking.

Key Network Design Principles

Effective network design ensures optimal performance, reliability, and security. The following principles guide the configuration:

Azure Virtual Network:

Host SQL Server virtual machines and Everpure Cloud Dedicated within a dedicated Azure Virtual Network to ensure isolated and secure communication.
Define subnets for specific roles:
- System subnet: For management and control traffic
- iSCSI subnet: For storage communication between SQL Server databases and Everpure Cloud Dedicated
- Application subnet: For client application traffic

Network latency and proximity:

Deploy SQL Server virtual machines and Everpure Cloud Dedicated in the same Azure region and availability zone to minimize latency and avoid unnecessary network hops.
Enable Azure Accelerated Networking to improve throughput and reduce network jitter on SQL Server virtual machines.

Bandwidth recommendations:

Configure virtual network interface controllers and Azure Virtual Network to meet the performance demands of SQL Server workloads.
Use high-throughput network interface controllers supported by specific Azure Virtual Machines families for data-intensive SQL Server operations.

Protocols and Traffic Configuration

Efficient communication between SQL Server, Everpure Cloud Dedicated, and client applications require optimized traffic flow and protocol usage:

Storage traffic: Use iSCSI over TCP/IP for storage communication. For Linux-based SQL Server deployments, consider NVM Express over TCP for lower latency
Application traffic: Configure Azure Virtual Network to support SQL Server listeners for Always On Availability Groups or Failover Cluster Instances. Assign static IP addresses within appropriate subnets to support seamless client traffic routing, dynamic failover, and high availability for SQL Server applications.

Security and Isolation:

To safeguard communication between SQL Server and Everpure Cloud Dedicated, this architecture leverages Azure-native security features:

Network security groups: Apply network security groups to Everpure Cloud Dedicated and Azure Virtual Machine subnets to restrict traffic to required ports (for example, port 3260 for iSCSI) and authorized source/destination IPs.
Azure Firewall: Optionally deploy Azure Firewall for centralized control and monitoring of network traffic between SQL Server and Everpure Cloud Dedicated.

For detailed guidance, refer to Azure network security best practices and documentation on network security groups, Azure Firewall, and Accelerated Networking.

Storage Layer

The storage layer in this reference architecture integrates Azure managed disks, local ephemeral disks, and Pure Cloud Block Store to optimize SQL Server performance, scalability, and reliability. Azure managed disks are utilized primarily for system databases, ensuring high IOPS and low latency for critical SQL Server system operations. TempDB is deployed on local ephemeral disks to leverage their superior performance characteristics, benefiting workloads that require rapid temporary data operations.

Everpure Cloud Dedicated, itself built on top of managed disk and serves as the primary storage solution for user database data and log files. It delivers data reduction, efficient resource allocation, and consistent performance tailored specifically for SQL Server user workloads.

Figure 1. Storage layer overview diagram of the SQL Server in Azure Virtual Machines with Everpure Cloud Dedicated solution

Everpure Cloud Dedicated

Everpure Cloud Dedicated is the primary block storage solution for SQL Server user databases, providing enterprise-grade capabilities that optimize performance and reduce storage costs. This layer ensures consistent performance and seamless integration with Azure services.

Storage considerations include:

Storage placement: Deploy SQL Server data files (.mdf/.ndf) and log files (.ldf) on separate Everpure Cloud Dedicated volumes to optimize input/output throughput and reduce contention.
Snapshot management: Use crash consistent volume snapshots in Everpure Cloud Dedicated for fast, space-efficient backups and rapid recovery. Consider offloading these snapshots to Azure Blob Storage for cost-effective, long-term retention. Application consistency can be achieved through the use of T-SQL database snapshots.

The following table outlines design recommendations for Everpure Cloud Dedicated.

Table 1. Everpure Cloud Dedicated design recommendations
Configuration Category	Design Recommendation
Volume configuration	Configure Everpure Cloud Dedicated to host the volumes for SQL Server user databases, such as data files (.mdf and .ndf) and log files (.ldf). As a design best practice, dedicate a group of storage volumes exclusively to a single database instance. Avoid placing multiple databases on the same set of volumes to maintain optimal performance, simplify management, and enhance reliability.
High availability, disaster recovery and data protection	Use Everpure Cloud Dedicated space-efficient volume snapshots for backups, quick restores, and disaster-recovery scenarios. Volume snapshots should complement an existing data protection strategy that utilizes a T-SQL or ISV-driven backup solution. Implement ActiveDR between Everpure Cloud Dedicated or FlashArray deployments for near-synchronous replication to a secondary availability zone, ensuring robust disaster recovery with no impact on primary zone performance. Implement synchronous active/active replication with ActiveCluster to provide high availability for SQL Server, offering zero or near-zero recovery point objectives and recovery time objectives.
Proximity and latency	Deploy Everpure Cloud Dedicated in the same Azure region and availability zone as the SQL Server virtual machine to minimize latency and reduce network hops.

For more information see the Everpure Cloud Dedicated for Azure Overview.

Azure Managed Disks

Azure managed disks complement Everpure Cloud Dedicated by hosting SQL Server system databases and TempDB, critical for database operations and performance.

Key design considerations:

TempDB Configuration: Provision TempDB on Azure Premium SSD v2 or Ultra SSD managed disks to efficiently handle intensive input/output workloads. This aligns with Microsoft's best practices, leveraging the disks' native performance capabilities and ensuring optimal performance under heavy TempDB utilization.

Operating System Layer

The operating system layer serves as the foundation for SQL Server operations, providing the necessary environment to support performance, scalability, and stability. It encompasses key optimizations for file systems, resource management, and input/output handling to maximize the efficiency of SQL Server workloads.

This architecture supports Windows Server and Linux operating systems in line with the requirements for SQL Server, both of which are fully compatible with SQL Server. These operating systems can be deployed on Azure Virtual Machines using templates provided by Azure, which streamlines the setup process.

For details on supported operating systems for SQL Server, refer to the SQL Server hardware and software requirements. Azure templates for deploying these operating systems are available in the Azure Marketplace.

Best Practices for Deployments on Windows Server

Windows Server best practices include the actions detailed in Table 2.

Table 2. Azure Virtual Machines Windows Server best practices
Configuration Category	Best Practices
Folder structure	Both virtual and physical disks can use either drive letters or mount points. Make sure to provision specific locations for each database file function in alignment with the following examples: SQL Server binaries: Install SQL Server on the operating system drive. Data files: Do not place database and backup files on the same disk where the operating system and binaries exist. Do not put SQL Server data and log files on the root of a drive or a mount point. Do not create data files on the root of a file system in Windows Server. Use naming conventions for drive letters or mount points that make sense for that environment. The following are two examples of naming conventions: Use a folder on the D drive, such as D:\DATA. All the data files can be in a single virtual disk, or each database can have a virtual disk provisioned for it (recommended) and then mounted under this structure. The data files in "UserDB" would be placed in a virtual disk mounted D:\DATA\UserDB. Transaction log files: All transaction log files can be located on a single virtual disk, or each database can have a virtual disk provisioned for it (recommended) and then mounted under this structure. The following is an example of a naming convention: Create a folder named LOGS on the L drive, L:\LOGS. The log files in "UserDB" would be placed in a virtual disk mounted at L:\LOGS\UserB. TempDB files: Provision a single virtual disk to contain the data and log files for TempDB.
File system and disk formatting	Format all SQL Server volumes with the NTFS file system and a minimum of 64KB allocation units before placing any data on the disks.
Block alignment	Check if the block alignment is configured properly. See the Windows Server File Systems FAQ for FlashArray knowledge article for more information.
iSCSI Configuration	Configure iSCSI with a minimum of four sessions per volume for optimal throughput, reliability, and redundancy. This configuration helps maximize network bandwidth utilization and overall storage performance.
Antivirus and threat-detection systems	The minimum recommendation is to configure antivirus and threat detection, so they exclude files with .mdf, .ndf, .ldf, and .bak extensions. Other folders and files might need to be excluded as well. For more information, see Configure antivirus software to work with SQL Server.
Page file size	Enable instant file initialization as per Database instant file initialization.

Best Practices for Deployments on Linux

Linux is only supported as a platform from SQL Server 2017 onwards. Best practices for Linux virtual machines include those detailed in Table 3.

Table 3. Azure Virtual Machines Linux best practices
Configuration Category	Best Practices
Disk storage-protocol selection	While this reference architecture is validated for Windows Server deployments using iSCSI, NVM Express over TCP offers a compelling option for Linux environments, where its benefits align with workload requirements. NVM Express over TCP is recommended for Linux virtual machines to take advantage of: Lower latency: Provides faster data access, improving performance for transactional and analytical workloads Higher throughput: Supports workloads with high input/output demands, ensuring consistent performance under heavy load
Folder structure	The default folder for all SQL Server data files is /var/opt/mssql. Default folders for data, transaction log, and TempDB files are created during installation: Data: /var/opt/mssql/data Log: /var/opt/mssql/log TempDB: /var/opt/mssql/tempdb TempDB files should be migrated to a separate virtual or physical disk to achieve the best performance. The virtual or physical disk can be mounted at the default TempDB location after the files have been migrated to it. Create separate folders for data and transaction log files for any additional user databases.
File system and mount options	Format virtual disks with the XFS or EXT4 file systems and mount them with the "rw,attr2,noatime" attributes.
Open file limitations	Set a soft limit of 16,000 and a hard limit of 32,727.
Forced unit access input/ output subsystem capability	For Linux operating systems that support this capability, configure forced unit access using the commands control.writethrough =1 and control.alternewritethrough = 0.
Disk settings	Input/output scheduler: Use the none scheduler for all virtual disks that contain SQL Server data files. For more information, see the How to use the Noop or None IO Schedulers solution page. Disk readahead: Set the readahead setting to 4,096. vm.swappiness: Set vm.swappiness to 1 for high-performance systems. *vm.dirty_: Set vm.dirty_ratio to 80 and vm.dirty_background_ratio to 3. kernel.sched_:* Set kernel.sched_min_granularity_ns to 15,000,000 and kernel.sched_wakeup_ granularity_ns to 2,000,000.
SQL Server	The Microsoft documentation details out the best practices for SQL Server in Azure virtual machines.