ReleaseBytes
aws Modernizing with AWS Blog ·

AWS Details Best Practices for SQL Server on FSx for NetApp ONTAP

bloginfraawsengineer
announcement

This post details best practices for running Microsoft SQL Server databases on Amazon FSx for NetApp ONTAP, addressing challenges in I/O performance, storage scaling, and cost optimization. It guides database administrators and solutions architects through configuration parameters from the Performance Tier SSD and aggregate level, through volume creation and management, to LUN provisioning and OS-level optimization. The article covers storage tiers, generations, deployment options, tenant isolation with Storage Virtual Machines, and scaling strategies for high-performance SQL Server deployments. Prerequisites include an AWS account, an FSx for ONTAP file system, and a Windows EC2 instance with iSCSI Initiator enabled.

  • →Understanding FSx for ONTAP Storage Tiers
  • →FSx for ONTAP Generations and Deployment Options
  • →Multi-tenant Isolation with Storage Virtual Machines (SVMs)
  • →Controlling Disk Access with Initiator Groups (igroups)
  • →Scaling Beyond a Single File System
Notes (6) ›
  • Understanding FSx for ONTAP Storage Tiers

    FSx for ONTAP utilizes a two-tier storage approach: Performance Tier SSD for active SQL Server files requiring predictable sub-millisecond latencies, and Capacity Pool for cost-effective storage of cold/archive data like backups and snapshots. The SSD tier is recommended to be provisioned at 1.35x the total database size to accommodate features like snapshots and clones, with seamless data tiering supported.

  • FSx for ONTAP Generations and Deployment Options

    Amazon FSx for NetApp ONTAP is available in two generations: Generation 1 (Single-AZ 1, Multi-AZ 1) with a scale-up architecture supporting up to 192 TiB on the Performance Tier, and Generation 2 (Single-AZ 2, Multi-AZ 2) supporting up to 1 PiB and both scale-up and scale-out architectures. Performance is influenced by throughput, cache, and SSD IOPS, varying by generation and region.

  • Multi-tenant Isolation with Storage Virtual Machines (SVMs)

    FSx for ONTAP provides robust tenant isolation through Storage Virtual Machines (SVMs), which offer independent administrative credentials, security domains, performance domains, and network endpoints for block storage protocols. A common isolation pattern involves dedicating separate SVMs for Production, Staging, and Development environments, with volumes acting as data containers within each SVM.

  • Controlling Disk Access with Initiator Groups (igroups)

    Initiator groups (igroups) manage access control for block storage by mapping logical unit numbers (LUNs) to specific compute resources identified by their iSCSI Qualified Name (IQN). This ensures that only authorized EC2 instances can access designated disks, preventing data corruption, especially in scenarios like SQL Server Failover Cluster Instances where multiple nodes require access to shared disks.

  • Scaling Beyond a Single File System

    For workloads exceeding the capabilities of a single FSx for ONTAP file system, multiple file systems can be utilized and load-spread. This allows for optimization of different I/O patterns, such as placing database data files on an IOPS-optimized file system and transaction logs on a throughput-optimized file system.

  • SQL Server Storage Layout Patterns on FSx for ONTAP

    Optimizing performance, backup, and restore times for SQL Server on FSx for ONTAP involves strategic storage layout. It is recommended to maintain a 1:1 relationship between volumes and LUNs, and to place data files (.mdf) and transaction log files (.ldf) on separate volumes to leverage volume-level snapshots for granular and rapid recovery.

Read the original announcement →

https://aws.amazon.com/blogs/modernizing-with-aws/running-mission-critical-microsoft-sql-server-databases-on-amazon-fsx-for-netapp-ontap/

Related releases