AzureEnterprise TechSQLTech PostsBest Practices for Configuring SQL on Azure IaaS VM March 10, 20164678 views0Share By IG ShareBest Practices for Configuring SQL on Azure IaaS VMRunning SQL Server workloads on Azure as IAAS (Infrastructure as a service) is becoming popular. With more SQL Servers running on Azure virtual machines, there has been a need to know how to effectively and efficiently one can run them with minimal bottlenecks.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.In order to have the maximum performance out of your Azure VM’s, here are the list of steps to look out for:A minimum of A2 Azure VM is recommended for running SQL workloads.Ensure that the storage account and the VM are located in the same region to reduce any network latencyAzure Geo-Replication should be disabled at the storage level. This ensures maximum IOPS for SQL Server to use.Do not use the “TEMP” storage for storing any SQL Server files like Log files and Data files.Disable Data Disk Caching (caching policy = None)Configure RAID or striping using multiple Data Disks for maximum Read-Write performance.Format the Disk’s in 64 KB chunks. Do not use the default “File Allocation Units”Ensure seperate disks for Log files (ldf’s) and Data files (mdf’s). This will get dedicate IOPS for both Data and log files.Less than 10 ms – very good Between 10 – 20 ms – okay Between 20 – 50 ms – slow, needs attention Greater than 50 ms – Serious I/O bottleneckYou need 2 ms – 5 ms of response time for the SQL Server Log files.Enable “Page Compression” for Databases. Do not enable this feature if you have high CPU usage.Disable “Autogrowth” for Databases & TembdbDo not keep the Database files on the default location. Move them to a different disk drive. This includes “System Databases”.SQL Server Error Log’s and Trace Files should be pointing to a different Data Disk.Enable “Lock Pages in Memory” feature. This is only applicable for SQL Server Enterprise Editions only.Se Backup jobs to be saves directly on Azure Storage Blob’s.Enable and use “Buffer Pool Extension” on dedicated premium Azure Storage. These are SSD Disks and will give you close to 400 MB/s of throughput.Configure Tempdb on SSD disks where possible.Hope this covers the basics of what steps needs to be followed in order to run SQL Server more efficiently on Azure VM’s. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
AzureDownload Backup and Restore a Azure SQL Database Locally for TestingAzure SQL Database is Microsoft’s Database as a service on the cloud. While your production ...
AzureStep by Step Deployment for SQL Analysis Services PAAS on Azure CloudSQL Server Analysis Services (Both Multi-Dimensional and Tabular) only supports Windows Authentication. However, Analysis Services ...
AzureMove & Load Data to Azure SQL DataWarehouse using Data Platform StudioThere are multiple ways to populate data into Azure SQL DataWarehouse PAAS (Platform as a ...