Do you think IIT Guwahati certified course can help you in your career?
Introduction
Optimizing for costs vs optimizing for performance is usually a trade-off. This series of performance best practices focuses on optimizing SQL Server performance on Azure Virtual Machines. If your job isn't as demanding, you may not need to implement all of the advised changes. As you consider these suggestions, keep your performance requirements, prices, and workload patterns in mind.
Performance best practices for SQL Server on Azure VMs
VM size
When creating a SQL Server on Azure VM, consider the type of workload you'll need. If you're moving an existing setup, create a performance baseline to evaluate your Sql Server on Azure VM needs. If this is a new VM, construct a new SQL Server VM based on the vendor's specifications.
Let us look at several options for VM size optimization.
Memory optimized
Memory-optimized virtual machine sizes are a fundamental goal for SQL Server VMs, and Microsoft recommends them. Virtual machines with memory optimization have higher memory-to-CPU ratios and medium-to-large cache choices.
Ebdsv5-series
The new Ebdsv5-series has the greatest I/O throughput-to-vCore ratio in Azure and a memory-to-vCore ratio of 8.
This series provides the greatest price-performance ratio for SQL Server workloads on Azure VMs.
For most SQL Server workloads, start with this series.
Edsv5-series
Intended for memory-intensive applications and SQL Server workloads that don't require the same high I/O throughput as the Ebdsv5 series.
These virtual machines feature a large local storage SSD capacity, up to 672 GB of RAM, and extremely high local and remote storage throughput.
Most of these virtual machines have an average of 8 GiB of RAM per vCore, which is ideal for most SQL Server workloads.
M and Mv2 series
The M-series has enough vCores and RAM to handle some of the most demanding SQL Server workloads.
Mv2-series instances are memory-optimized VM sizes that deliver Mv2-series instances are memory-optimized VM sizes that deliver unmatched computational performance for large in-memory databases and workloads.
They have a high memory-to-CPU ratio, making them ideal for relational database servers, large caches, and in-memory analytics. Computational performance for large in-memory databases and workloads.
They have a high memory-to-CPU ratio, making them ideal for relational database servers, large caches, and in-memory analytics.
Premium storage and premium storage caching support, ultra-disk support, and write acceleration are just a few characteristics that make the M and Mv2-series desirable for SQL Server performance.
General purpose
The General Purpose virtual machine sizes are designed to provide balanced memory-to-vCore ratios for lower-level workloads, including development and testing, web servers, and smaller Database servers.
Since a memory-to-vCore ratio of 8 is suggested for production workloads, the minimum recommended setup for a General Purpose VM running SQL Server is four vCPU and 32 GiB of memory.
Ddsv5 series
The Ddsv5-series has a fair balance of vCPU, memory, and temporary disk, although memory-to-vCore support is limited.
Ddsv5 VMs provide lower latency and faster local storage.
These servers are appropriate for SQL and app installations that require quick access to temporary storage and departmental relational databases while running side by side.
All of the virtual machines in this series have the same memory-to-vCore ratio of 4.
B-series
The burstable B-series virtual machine sizes are appropriate for workloads that don't require consistent performance, such as proof-of-concepts and very small application and development servers.
The memory-to-vCore ratio for most burstable B-series virtual machine sizes is 4.
The advantage of the B-series is the compute savings you can get when compared to other VM sizes in other series, especially if you only need processing power for a few hours each day.
Av2-series
The Av2-series VMs are best suited for low-traffic web servers, small to medium app databases, and proof-of-concepts.
These virtual machines are both suitable choices for smaller SQL Server development and test SQL server machines.
Storage optimized
Storage optimized VM sizes are intended for specific use cases. These virtual machines were created with optimum disk performance and IO in mind.
Lsv2-series
The Lsv2-series has a high throughput, low latency, and local NVMe storage configuration. Instead of employing persistent data drives, the Lsv2-series VMs are tailored to use the local disk on the node tied directly to the VM.
These virtual machines are ideal for big data, data warehousing, reporting, and ETL tasks.
VMs in the Lsv2-series can additionally burst disk performance for up to 30 minutes.
These virtual machines range in size from 8 to 80 vCPUs, with 8 GiB of RAM per vCPU and 1.92 TB of NVMe SSD for every 8 vCPUs.
Since NVMe storage is temporary, data on these drives will be lost if your virtual machine is deallocated or transferred to a new host for service healing.
Constrained vCores
High-performance SQL Server operations frequently require more memory, IOPS, and throughput without the higher vCore counts.
Most OLTP workloads are application databases with a high volume of small transactions. Only a small portion of the data is read or updated in OLTP workloads, but the numbers of transactions generated by user counts are substantially greater. It's critical to have enough memory in SQL Server to cache plans, keep recently accessed data for speed, and guarantee that physical reads are read into a memory fast.
These OLTP settings require more memory, faster storage, and the appropriate I/O bandwidth to function appropriately.
To retain this level of performance without incurring increased SQL Server licensing costs, Azure provides VM sizes with limited vCPU counts.
This lowers licensing costs by limiting the number of accessible vCores while keeping the parent virtual machine's memory, storage, and I/O bandwidth the same.
The number of virtual CPUs (vCPUs) can be limited to one-half to one-quarter of the original VM size. Higher memory-to-vCore ratios can be achieved by reducing the virtual machine's available vCores, but the compute cost remains the same.
To make them simpler to recognize, these new VM sizes contain a suffix that provides the number of active vCPUs.
Storage
VM disk types
You may select the degree of performance for your disks. Ordinary hard disc drives (HDD), standard SSDs, premium solid-state drives (SSD), and ultra disks are the several types of managed discs accessible as underlying storage (in order of increasing performance capabilities).
An OS disk, a temporary disk, and your data discs are the three major disk types to consider for your SQL Server on Azure VM. Select what goes on the operating system disc (C:\) and the ephemeral, temporary drive (D:\) with care.
Operating system disk
An operating system disk, also known as the C:\ drive, is a VHD that may be booted and mounted as a running version of an operating system.
Use the operating system disk for data files, log files, and error logs in production SQL Server environments.
Temporary disk
Another disk type used in many Azure virtual machines is the temporary disk (labeled as the D:\ drive).
The temporary disk is ephemeral, which means that when the virtual machine is restarted or relocated to a new host, the disk storage is recreated (as in, it is deallocated and allocated again) (for service healing, for example).
If local cache usage is an issue, put tempdb on the local temporary SSD D:\ drive for SQL Server workloads. If you're using a virtual machine without a temporary disk, you should put tempdb on its isolated disk or storage pool, with read-only caching enabled.
Data disks
Data disks are remote storage disks frequently build in storage pools to provide more capacity and performance to the virtual machine than a single disk could.
Attach the smallest number of disks that meet your workload's IOPS, throughput, and capacity requirements. Do not exceed the smallest virtual machine's maximum number of data drives while resizing.
Place data and log files on data drives that are optimized for performance.
Premium disks
For production SQL Server workloads, use premium SSD storage for data and log files. The IOPS and bandwidth of premium SSDs vary depending on the disk size and type.
Use the P30 and/or P40 disks for SQL Server data files in production workloads to assure cache support and the P30 up to P80 disks for SQL Server transaction log files.
For OLTP workloads, use workloads during peak periods and the Disk Reads/sec + Disk Writes/sec performance counters to match the target IOPS per Disk (or storage pool) with your performance needs. Match the desired throughput for data warehousing and reporting workloads utilizing workloads during peak periods and the Disk Read Bytes/sec + Disk Write Bytes/sec.
Scaling premium disks
Where billing is planned to satisfy the storage performance tier, use the greater performance for as long as needed.
Without boosting capacity, upgrade the tier to meet the performance requirements.
Return to the default tier when the extra performance is no longer necessary.
Azure ultra disk
Ultra disk may be set up to scale capacity and IOPS independently. Administrators may use the ultra disk to provision a disk with the capacity, IOPS, and throughput that an application requires.
Ultra disk isn't compatible with every VM series and has additional drawbacks, including region availability, redundancy, and Azure Backup support.
Standard HDDs and SSDs
Standard HDDs and SSDs have different latencies and bandwidth, thus they're only good for development and testing. Premium SSDs should be used for production workloads. For the optimum performance when using Standard SSD (dev/test scenarios), add the maximum number of data disks supported by your VM size and employ disk striping with Storage Spaces.
Caching
Virtual machines that enable premium storage caching can use an extra feature called Azure BlobCache or host caching to increase their IOPS and performance. These two separate storage bandwidth restrictions can be used together to increase storage performance in virtual machines with both premium and premium storage caching configured.
Without caching, the IOPS and MBps throughput counts against the virtual machine's uncached disk throughput limitations. The maximum cached limits provide readings with an extra buffer, which helps with growth and unexpected peaks.
Premium caching should be used if the option is available to increase significantly read performance against the data disk without incurring additional costs.
Disk striping
Determine the number of data disks necessary by analyzing the throughput and bandwidth requirements for your SQL data files, including the log file and tempdb. The throughput and bandwidth constraints differ depending on the size of the VM.
For increased throughput, add extra data drives and employ disk striping.
Disk capping
At both the disk and virtual machine levels, there are throughput constraints. The maximum IOPS limitations for each VM and disk are different and unrelated to one another.
Applications that consume more resources than these are throttled (also known as capped). Choose a virtual machine and disk capacity in a disk stripe that fulfills your application's needs while avoiding capping issues. Use caching or modify the program such that less traffic is required to avoid capping.
Write Acceleration
Write Acceleration is a disk feature accessible only on M-Series Virtual Machines (VMs). When you need single-digit I/O latency for large volume mission-critical OLTP workloads or data warehouse settings, Write Acceleration improves write I/O latency against Azure Premium Storage.
Improve write latency to the drive hosting the log files by using Write Acceleration. For SQL Server data files, don't use Write Acceleration.
Comparing to Azure ultra disk
Azure ultra disks provide low-latency disk storage for Azure Virtual Machines.
Use Write Acceleration over ultra disks for the transaction log disk if possible.
Use Azure ultra disks for virtual machines that don't support Write Acceleration but need low latency to the transaction log.
Monitor storage performance
The program's number of queries to storage per second is measured in IOPS (Input/Output per Second). IOPS may be measured with Performance Monitor counters Disk Reads/sec and Disk Writes/sec.
The Performance Monitor counters Disk Read Bytes/sec, and Disk Write Bytes/sec can be used to measure throughput.
The goal of data warehousing is to maximize throughput over IOPS.
IOPS and throughput capacities are influenced by I/O unit size, with smaller I/O units yielding greater IOPS and bigger I/O units yielding higher throughput. SQL Server automatically selects the best I/O size.
Security
Microsoft Defender for SQL
Microsoft Defender enables Azure Security Center security features such as vulnerability assessments and security warnings for SQL.
Azure Defender for SQL can help you find and fix potential database flaws and spot unusual activity that might indicate a danger to your SQL Server instance and database layer. Vulnerability Assessments are a feature of Microsoft Defender for SQL that may help you find and fix potential SQL Server vulnerabilities. It gives you a clear picture of your security situation and provides concrete measures for resolving security concerns.
Portal management
After you've registered your SQL Server VM with the SQL IaaS extension, you can use the SQL virtual machines resource in the Azure portal to specify a variety of security options, such as activating Azure Key Vault integration or SQL authentication.
Additionally, after you've activated Microsoft Defender for SQL, you can access Defender for Cloud services such as vulnerability assessments and security warnings from within the SQL virtual machines resource in the Azure portal.
Azure Security Center
Azure Security Center is a single security management solution that analyzes and recommends ways to improve your data environment's security posture. The Azure Security Center provides a centralized view of the security health of all hybrid cloud assets.
In Azure Security Center, use the security score.
Adding the SQL Server IaaS Agent Extension to your SQL Server VM brings Azure Security Center recommendations to the SQL virtual machines resource in the Azure portal.
Azure Advisor
Azure Advisor is a one-on-one cloud expert which helps you optimize your Azure deployments by following best practices. Azure Advisor examines your resource setup and usage patterns before making recommendations to help you enhance your Azure resources' cost-effectiveness, performance, high availability, and security. Azure Advisor may assess virtual machines, resource groups, and subscriptions on a per-virtual-machine, per-resource-group, or per-subscription basis.
Azure Key Vault integration
SQL Server encryption options include transparent data encryption (TDE), column level encryption (CLE), and backup encryption.
These types of encryption necessitate the management and storage of cryptographic keys.
The Azure Key Vault service is intended to improve the protection and administration of these keys in a highly accessible and secure location.
The SQL Server Connector enables SQL Server to use Azure Key Vault keys.
Take into account the following:
To securely restrict access rights and segregate access tracking, Azure Key Vault saves application secrets in a centralized cloud location.
You should store secrets and certificates in the Azure Key Vault when bringing your keys to Azure.
To maintain and manage disk encryption keys and secrets, Azure Disk Encryption uses Azure Key Vault.
Access control
When you create a SQL Server virtual machine using an Azure gallery image, you can choose from three connectivity options: local (within the VM), private (inside the Virtual Network), or public (Internet).
Choose the most restricted option for your scenario for the best security. For example, if you run an application that connects to SQL Server on the same VM, Local is the most secure option. If you're running an Azure application that needs access to SQL Server, Private restricts connectivity to SQL Server to the Azure virtual network you choose. If you need public (internet) access to the SQL Server VM, follow the rest of this topic's recommended practices to decrease your attack surface area.
Encryption
Server-side encryption and Azure Disk Encryption are available with managed disks. Server-side encryption protects your data by encrypting it at rest, helping you achieve your organization's security and compliance goals. Azure Disk Encryption encrypts both the OS and data drives using BitLocker or DM-Crypt technology and connects with Azure Key Vault.
Manage accounts
Account names and passwords should not be easily guessable by attackers. To assist you, consider the following suggestions:
Create a new local administrator account that does not have the name Administrator.
For all of your accounts, use complicated, strong passwords.
During SQL Server virtual machine configuration, Azure defaults to Windows Authentication. As a result, the SA login is blocked, and the setup assigns a password. According to our recommendations, the SA login should not be used or enabled.
Auditing and reporting
Auditing using Log Analytics records events and writes them to an audit log in a protected Azure BLOB storage account. The information of the audit logs may be decoded using Log Analytics. You may save data to a different storage account and establish an audit trail of any occurrences you want using auditing. You may also use Power BI to analyze and acquire insights into your data using the audit log and give a perspective for regulatory compliance.
Virtual Machine level access
Close your machine's management ports - Open remote management ports expose your virtual machine to a significant risk of internet-based threats. These attacks try to get admin access to the computer by brute-forcing credentials.
For Azure virtual machines, enable Just-in-Time (JIT) access.
Use Azure Bastion with the Remote Desktop Protocol (RDP).
Virtual Machine extensions
Antivirus, malware, threat protection, and other Azure Virtual Machine extensions are trusted Microsoft or third-party extensions that can assist address specific needs and risks.
Guest Configuration extension
Install the Guest Configuration extension to ensure safe setups of your machine's in-guest settings.
The operating system configuration, application configuration or presence, and environment settings are included in the in-guest settings.
Once implemented, in-guest policies such as 'Windows Exploit Guard should be enabled' will be available.
Network traffic data collection agent
Security Center uses the Microsoft Dependency agent to collect network traffic statistics from your Azure virtual machines.
Advanced network security features like traffic visualization on a network map, network hardening recommendations, and particular network threats are available with this agent.
To address threats at the operating system, machine, and network levels, evaluate extensions from Microsoft and third parties that address anti-malware, desired state, threat detection, prevention, and remediation.
Frequently Asked Questions
Which are the best practices for Azure SQL server and database?
Ensure that Azure SQL database servers are only accessible through private endpoints. Ensure that Azure SQL databases have an appropriate PITR backup retention period specified. Ascertain that no SQL databases provide unfettered incoming access from 0.0. (any IP address).
How do I improve my Azure performance?
To optimize cache and application performance, change the size or SKU of your cache. Reduce memory strain, server stress, and network traffic by following best practices. Change the size or SKU to one with more capacity.
What is the azure cache for Redis?
Azure Cache for Redis is an in-memory cache that is completely managed and allows for high-performance and scalable systems. Create cloud or hybrid deployments that can handle millions of requests per second with sub-millisecond latency, all while benefiting from managed service configuration, security, and availability.
Conclusion
In this article, we have extensively discussed Performance Best Practices for Azure SQL. Our discussion mainly focused on performance criteria such as VM size, Storage, and Security.
34+ registered 
