As part of your Azure SQL VM Migration project, if you do NOTHING ELSE, read this: Checklist: Best practices & guidelines – SQL Server on Azure VMs | Microsoft Learn
Do you want to make certain you have a successful Azure SQL VM Migration? Then, keep reading. 🙂
If you would have asked us 5 years ago if we would still be dealing with the “nobody seems to be able to setup an Azure SQL Server VM correctly Problem” in 2023, we would have said “Nahh…that will be handled”. Well here we are, and it’s still a PROBLEM!
The number of Azure SQL VM environments we have fixed/reimplemented due to the of lack of reading the article above in depth is too many to count. Azure is NOT On-Prem. Say it out loud now. Azure is not On-Prem. Everything in Azure (cloud in general) is different and requires new skills and a different approach. Concepts are the same but best practices and methods of achieving top performance are different. We all must take the time to understand the platforms we are using and become experts in our domain.
MSFT in general has not made Azure Virtual Machines simple to achieve a highly performant SQL Environment, but the Azure SQL Team has put a considerable amount of effort into making this less painful for you.
Checklist: Best practices & guidelines – SQL Server on Azure VMs | Microsoft Learn
Before starting an Azure SQL Virtual Machine Build, EVERY SINGLE PERSON on your team needs to read the article above top to bottom and be quizzed on it. You will not have success if you don’t understand the core requirements for building a SQL Server VM in Azure.
Statements we have heard over the years as to why companies didn’t read this Holy Grail of Azure SQL VM information:
“I didn’t know this article existed!” Fair, it’s kind of hidden.
“This is just a small production environment, we don’t need Premium Storage that’s why we didn’t use it.” -> Yes, you do.
“We are going to set this up like we do On-Prem, it will work fine for us” -> No, it won’t.
“I’m on the infrastructure team, that’s all about SQL so I don’t need to worry” Yes, You do. More than the SQL team to be honest.
The infrastructure team needs to be the most familiar with how to deploy SQL Server Virtual Machines in Azure. What are the top items that we need to consider when taking on a move to Azure VM?
Storage is Front and Center – get this wrong and nothing will work well. Unfortunately, MSFT leaves PLENTY of room to get this wrong. In Addition to the Article mentioned above, also spend time to 100% understand the following: Storage: Performance best practices & guidelines – SQL Server on Azure VMs | Microsoft Learn
Amount of Storage being Provisioned – Pay attention to the size of the storage and the number of IOPS you are provisioning. Recently, we reduced Azure Storage Costs for a customer by $10,000 per month by architecting storage to best practice (following articles mentioned exactly). More storage/IOPS is not always a better solution! We have seen many implementations where customers will over provision the amount of IOPS for the size of Virtual Machine they are using. There are two primary issues with this
- You will flood the VM with IOPS if you push it beyond the limits, and from experience, this can cause hang conditions on the VM normally during maintenance in the middle of the night during heavy IO operations. NOT FUN!
- You are paying more than you should be paying, wasting money.
Data Disk – MDF and NDF files
Host Caching – Enable Read-only caching for the disks hosting SQL Server data files.
Reads from cache will be faster than the uncached reads from the data disk. Uncached IOPS and throughput plus Cached IOPS and throughput will yield the total possible performance available from the virtual machine within the VMs limits, actual performance will vary based on the workload’s ability to use the cache (cache hit ratio).
Transaction Log Disk – LDF Files
Host Caching – Set the caching policy to None for disks hosting the transaction log files. There is no performance benefit to enabling caching for the Transaction log disk. Having Read-only or Read/Write caching enabled on the log drive can degrade performance of the writes against the drive and decrease the amount of cache available for reads on the data drive.
Write Accelerator – for M Series VMs that make this feature available, enable it. This will help to reduce the latency for writes in SQL Server which can be the most debilitating problem of running in the Cloud.
Azure Write Accelerator – Azure Virtual Machines | Microsoft Learn
TempDB:
Many customers overlook the importance of TempDB in their environment. Developers over the years have become reliant on temp tables to accomplish operations. If you are using any Microsoft Product (Dynamics, Sharepoint, etc, this is important)
Local TempDB – placing TempDB on the ephemeral drive is a common strategy in SQL Server on Azure VMs. This takes advantage of the built-in, local, fast storage. There is one major drawback with this strategy, the amount of storage that is presented on this drive is static. If you ever need more TempDB storage you either have to scale up the VM to a higher service tier OR move TempDB to a different volume. These are both invasive.
Placing TempDB on it’s own Volume – If tempdb cannot be placed on the ephemeral drive D:\ due to capacity reasons, either resize the virtual machine to get a larger ephemeral drive or place tempdb on a separate data drive with Read-only caching configured.
The virtual machine cache and ephemeral drive both use the local SSD, so keep this in mind when sizing as tempdb I/O will count against the cached IOPS and throughput virtual machine limits when hosted on the ephemeral drive.
Storage Spaces:
Make CERTAIN that you are setting up storage spaces correctly. We DO NOT want to setup the disks as mirror, we want to use SIMPLE volumes to take full advantage of all of the IOPS presented to the VM. We have seen too many times that customers will miss this step or make a mistake, only to have to rebuild the storage.
Allocation Sizes:
Format your data disk to use 64-KB allocation unit size for all data files placed on a drive other than the temporary D:\ drive (which has a default of 4 KB). SQL Server VMs deployed through Azure Marketplace come with data disks formatted with allocation unit size and interleave for the storage pool set to 64 KB.
Network:
Don’t be tempted to create multiple Network Adapters. In the days of On-Prem, we added multiple network adapters to achieve higher levels of performance. That is not the case in Azure. We can achieve all of the levels of performance required by having just one adapter.
Accelerated Networking: If you are in a VM Tier that supported Accelerated Network, make certain you have this feature enabled. This feature plays a big role in reducing latencies in AlwaysON AG Environments where the nodes are in the same region.
Accelerated Networking overview | Microsoft Learn
VM Sizing and Misc Items:
VM Sizing: This step is more forgiving than others, because we can easily scale and environment up and down. At least that is easy, right!? The main items we need to consider here are: VM sizes – Azure Virtual Machines | Microsoft Learn
- Total IOPS the VM Requires – Look at the Total Cached and Uncached IOPS and make certain this will work with the amount of storage your environment requires.
- Temp Drive Size – Make certain the Temp Drive size satisfies the requirements for you workload.
- Memory – Basic understanding of Memory requirements from your current workload will translate well here to the Azure Environment.
- CPU Cores – Same applies with CPU, comparable to On-Prem.
AntiVirus/Security Software Exclusions – this one seems like “old news”, but we run into this every week still. MSFT has long had the guidance to exclude SQL Server from AV Scanning, ALL SQL Files. This means all MDF, NDF, LDF, TRN, BAK, Binaries, DLLs, etc. Anything related to SQL Server should be excluded.
Be very cautious of Security tools such as Carbon Black. We have had several customers with this software running that brought million dollar per year implementations of VM/Storage to a breaking point. The second we remove this software (The problem filter driver), everything works great. There is quite possible an “acceptable” way to setup Carbon Black and software like this, but it CANNOT include scanning SQL Files. If you are running software like this, and you still need to scan SQL Files, talk to the vendor to get guidance on best practices for a SQL Server Environment.
Summary Action Items:
- Whole Azure SQL Migration team NEEDS to review in depth the following Article: Checklist: Best practices & guidelines – SQL Server on Azure VMs | Microsoft Learn
- Anyone on the team responsible for provisioning Storage for Azure SQL VMs needs to fully understand everything in this article: Storage: Performance best practices & guidelines – SQL Server on Azure VMs | Microsoft Learn
Now you have the keys to a successful migration. If you don’t want to deal with this by yourself, get in touch with us!
