Storage
Mobility
Protection
Analysis and control
TOPICShare
Deployment of Azure SQL Server using the IaaS model in Azure comes with its own share of benefits—full control over the environment and configuration flexibility being the most prominent among them. The performance of the storage layer being used in such deployments plays an extremely important role in the overall success of your Azure database deployment.
Block storage on Azure disks provides the highest level of storage performance in Azure. But with several different flavors of Azure disk storage available, finding the best fit for your database can take some careful consideration.
This blog will explore the different types of disks that can be used while deploying SQL Server, their benefits, the best fit use cases, and how NetApp Cloud Volumes ONTAP adds even more value.
Azure Disk Storage: Block Storage for Azure Workloads
In Azure, block storage is primarily provided by disks, which are either managed or unmanaged. Unmanaged disks are page blobs in an Azure storage account, created and managed by the user. Unmanaged disks are bound by the limits of the storage accounts where they are placed. For example, there is a maximum of 20000 IOPS per storage account, which in turn limits the number of VHDs that can be placed in that storage account without impacting read/write performance. It’s up to the user to optimally place the VHDs to avoid these limitations.
Managed disks, as the name indicates, enable users to provision disks just like any other managed cloud service. Users can indicate the disk size, type, and provision—but all the other underlying configurations, such as page blob creation and storage management, are abstracted and handled by the Azure platform. This provides a high level of agility which can greatly benefit SQL workloads in Azure. Listed below are some of the key features and benefits of Azure managed disks.
Resilient: Azure managed disks have an assured 99.999% SLA for high availability. A key part of this availability is that Azure stores three copies of your data to ensure resiliency, which is important for mission-critical SQL databases. The disk can also be placed in availability sets or Azure availability zones for additional resiliency and thereby prevent single points of failure for your workloads.
Secure: Access to managed disks can be secured by the principle of least privilege using Azure Role Based Access Control (RBAC). The access can also be restricted to a single person for a specific duration of time using a shared access signature created for the disk. While the management plane is secured through these measures, the data plane for Azure disk storage can be secured through Azure disk encryption, which uses industry-standard BitLocker encryption for Windows and DM-Crypt encryption for Linux machines.
Scalable: Azure managed disks help to meet SQL scalability demands, since you can create up to 50,000 VM disks in an Azure subscription. That means you can both create thousands of VMs or virtual machine scale sets and attach disks to them as required by your application.
Cost-effective: There are multiple SKUs available for managed disks and their costs depend on factors such as IOPs, throughout provided by the SKU, and disk size, which starts at 4 GB. Customers can choose to start with the precise disk SKU meeting SQL DB sizing and performance benchmarks, and then resize them on the go if required to optimize the cloud spend on disks.
Built-in data protection: Native data protection features including Azure SQL backup and snapshots will help you to restore point-in-time data stored in your SQL databases in case of events like disk corruption, data loss due to malware attacks, or human error. These are cost-optimized solutions where customers will be charged only for the actual data in the disk being protected.
Types of Azure Disk Storage for Microsoft SQL Server
While deploying Azure SQL Server database, it is important to select the right VM SKUs with memory to vCPU ratio optimized for the best OLTP performance. Some ideal VM SKUs that do that are Es, Eas, DS, Das, and M series, to name just a few. For disks, customers can choose from the different types of managed disks available:
Standard HDD: Standard hard disk drives (HDD) provide a maximum throughput of 500 Mb/s and maximum of 2000 IOPS per disk and are targeted for non-critical workloads. Compared to other disks, this is the cheapest disk option available. Considering the lower IOPS and throughput, this option can be considered for your lower non-prod SQL environments or for storing SQL backup files.
Standard SSD: Standard solid-state drives (SSD) offer higher IOPS (6000) and maximum throughput of 750 Mb/s when compared to Standard HDDs, but may not be enough to sustain production workloads. They can however still be used in dev/test environments for optimizing cost of the deployment.
Premium SSD: Premium SSDs provide a maximum throughput of 900Mb/s and maximum of 20,000 IOPs per disk, and hence would be a best fit for performance sensitive production workloads. Premium disks are proven to be more effective with “readonly” cache enabled for data disks in a storage pool when used for SQL servers. Blobcache is the multi-tier caching technology used by Azure premium disks, that leverages both host RAM and local SSD for caching for faster reads and increased throughput. Since disks that store log files will be write heavy, it is recommended to use no cache setting for them. The throughput available for SQL workloads can be increased simply by including more premium disks to the storage pool. For example, a storage pool with 4 P30 data disks in an E64s_V3 machine could achieve up to 1.6 million TPM for SQL during performance testing.
Ultra disk: Ultra disk is the low-latency disk option available for use with SQL Server that provides the highest throughput and IOPs available on Azure. They are custom built for transaction-heavy DB workloads such as SQL, Oracle, and IO-intensive LOB applications like SAP HANA. Ultra disks are capable of throughput up to 2000 MB/s and max IOPs of 160,000. They are best suited for SQL deployments that demand storage latencies less than 1 ms. Ultra disks also support performance enhancements, for example upgrading the IOPS and throughput during runtime without downtime for the workloads. This helps in highly volatile environments where there could be spikes in demand during the lifecycle of the application.
The standard disks are typically used for non-production environments, while Premium and Ultra disks are recommended for production deployments.
Conclusion
The choice of block-based Azure disk storage you’ll use for Microsoft SQL Server depends on factors like type of environment (prod/non-prod), criticality of the application, expected DB performance, affordable storage latency, etc. To get more value-add from your investments in native Azure cloud block storage, you can leverage a data management solution like NetApp Cloud Volumes ONTAP.
NetApp Cloud Volumes ONTAP brings the trusted NetApp storage management capabilities to Azure, which helps to meet the stringent standards of SQL in terms of availability, agility, performance and data protection. The built-in storage efficiency features such as thin-provisioning, deduplication and compression helps to reduce the cloud storage footprint of SQL deployments. The service is designed for Azure high availability, to ensure zero data loss and RTO < 60 seconds.
The intelligent caching technology built-in to Cloud Volumes ONTAP helps to further reduce data access latency for SQL DB transactions on both the Azure and AWS clouds. Cloud Volumes ONTAP also ensures best in class data protection for SQL through NetApp Snapshot™ copies that enables app-consistent point-in-time SQL DB snapshots to recover from data corruption. The FlexClone® feature of Cloud Volumes ONTAP can be used to create writable clones of DB volumes with minimal impact on storage cost, which is helpful in building cost efficient test/dev environments. In addition to these features, Cloud Volumes ONTAP also helps with easy Azure migration of SQL data from on-premises to Azure through features like SnapMirror® data replication and Cloud Sync service.
Cloud Volumes ONTAP works as a unified data management solution across cloud-only as well as hybrid deployments of SQL databases. The solution when used along with Azure managed disks can help you meet your SQL Server requirements.
Read to learn more about the advantages of using Cloud Volumes ONTAP for your Azure-based SQL Server deployment: Azure SQL Server: Managed Service Vs. Managed Storage Deployment Options.
