This blogpost continues my series of blogposts regarding advanced capacity planning techniques on data migration. In my previous blogpost I wrote about scaling up SQL Servers. This blogpost is about right sizing Microsoft SQL Server workloads in different capacity planning scenarios:
- On-premises (new physical server or virtual machine)
- On-premises to Azure (new virtual machine)
- Azure to Azure (new virtual machine)
- AWS to Azure (new virtual machine)
Business needs
Typical drivers for right sizing SQL Server workloads are such as:
- Server hardware is getting obsolete
- Existing physical host having insufficient capacity for VMs
- Existing VM is not enough to handle its workload
- Existing Azure VM having insufficient capacity for current workload
- Business / data strategy -driven transition to the cloud
Microsoft SQL Server estate TCO and Time-To-Solution
When comparing TCO and Time-To-Solution in between scaling up and right sizing SQL Servers in general, right sizing is typically more time taking operation: There are things like ordering new hardware, configuring networking / virtualization / OS / SQL Server, migrating workload into the new server, testing and deployment. Also, server uptime typically takes a bigger hit in these scenarios as well.
TCO is not that straightforward, it depends. For example, if you are transitioning into the Azure cloud from an on-premises owned data center, it is often challenging to estimate CAPEX vs. OPEX over desired period. Which server licenses can be converted, what is the optimal size for each VM on Azure, what kind of disk setup is needed, how do we grow / scale etc. SQL Governor software can help you a lot in optimizing the SQL Server workloads to the Azure cloud. In this way, it is much easier to estimate the capacity and OPEX needs for the current state and into the future, such as for the next 1 or 3 years. It is not uncommon to be able to gain up to 50-60% OPEX savings in transitions to the Azure cloud by using SQL Governor software, compared to blindfold lift-and-shift operations, which I very rarely recommend to anyone.
By lift-and-shift, you are basically hoping, yes, hoping, that everything goes alright, and you do not know your existing baselines and how they would behave in the Azure cloud. This leads sysadmins and cloud DBAs into a situation wherein they need to estimate an extra 50-100% safety margin for their VMs in the cloud, just to ensure sufficient capacity in all scenarios. With SQL Governor software, you do not need this safety margin, because you know much better your current and future workload usage and how it would behave in the Azure cloud. Therefore, by right sizing your workloads before transitioning to the cloud with SQL Governor software, you unlock great saving potential, up to 50-60% on TCO of your SQL estate.
It is true that lift-and-shift typically has better Time-To-Solution, but in addition to the much higher OPEX on Azure, it often leads into a mess with existing VMs: You need to reconfigure most of you VMs at some point due the under- / oversized VMs. This is quite a tricky and time-consuming task to do and creates much more server downtime than proper right sizing. And I can guarantee, users and stakeholders are not satisfied. In my opinion, this is one of the biggest distinct reasons for the failed Azure transition projects.
Company politics
Company politics is typically a great driver to the Azure transitions: So many IT leaders want to follow the megatrend of transitioning the workloads into the Azure cloud as a part of their data strategy, followed by the business strategy from the stakeholders. This typically means a more offensive data strategy, wherein as much data is available as ubiquitously as possible, independent of place, time, and organization level, depending on company policies and regulations. Traditionally, more defensive data strategy -driven organizations such as banks, some public institutions and healthcare domain have not been the first ones with strong transition focus to the public cloud, but during the last few years there has been significantly more movement to the public cloud also on these domains. Catalysts for the change are diverse: COVID-pandemic, change in regulations, improved security and locality in the public cloud etc.
What comes to company politics in right-sizing the SQL Servers on-premises, it can be so many things, such as:
- Seeking savings and flexibility from virtualization
- Preparing transition to the cloud by Server virtualization
- Changing hardware / virtualization vendor
- Other contractual changes
Data center and data platform architecture
When transitioning the workloads into the Azure cloud, existing architecture necessarily does not need to change a lot, especially if you were running Hyper-V or VMware virtual machines or Nutanix workloads on-premises. In the case of ordinary VMs, you can use those templates when transitioning to the Azure cloud and converting VM images suitable to the Azure.
You can find more information about migrating VMware VMs to Azure from here:
You can find more information about migrating Hyper-V VMs to Azure from here:
In the case of Nutanix, Azure nowadays offers a PaaS option of Nutanix setup: It works just as it was running on-premises. You can read more of it from here:
Accelerate Cloud Adoption on Microsoft Azure | Nutanix
About Microsoft SQL Server Licensing
First and foremost: I am not a SQL Server licensing specialist. But here’s a couple basic takeaways on it. What comes to SQL Server licensing in on-premises, virtualization may or may not be a cheaper option compared to the server hardware licensing, what comes to the SQL Server enterprise edition. This depends on many factors, such as VM sizing on host, the host CPU physical core count, provisioning of the CPU cores, VM usage of the physical cores vs. hyper threads etc. In SQL Server 2022, production VMs need to have minimum of 4 core licensed (licenses are sold in 2-corepacks), even if you used just 2 cores on your VM / container. You may benefit from just simply licensing the physical host server with SQL Server Enterprise licensing, because then you are licensing only the physical host cores instead of licensing each individual VM with their logical cores. There is no limit for VM provisioning in this licensing model, yet though it is never a good idea to over provision your SQL Server virtual machines, especially on Enterprise edition!
Software Assurance (SA) is typically good to have, unless company politics deviate from this from reason or another. For example, with SQL Server 2022 subscription licenses, or licenses with active SA, customers can use passive replicas together with their primary workloads in case of failover as follows:
- One passive fail-over replica for High Availability in a separate server or VM
- One passive fail-over replica for Disaster Recovery in a separate server or VM
SQL Server Standard edition offers a possibility to Server + CAL pricing model in addition to the core-based model. This can be cheaper in cases, where there are not that many SQL Server users. This is because Server license + one to few user CALs are much cheaper altogether than SQL Server standard edition core pack.
SQL Server licensing basics is simple in general, but the devil is in details... All this is bit of “arcane knowledge” and the rules in licensing change over time, so I strongly recommend you to consult a Software Asset Management (SAM) specialist company to guide you through this area, especially if you have a remarkable asset on your SQL Server licensing and you are planning transitioning to the Azure cloud and you need to understand your possibilities in converting your licenses into the Azure. But with that said, Azure license pricing for SQL Servers itself is straightforward and transparent in general. For SQL Server VMs, you basically have an option of Web, Standard or Enterprise edition to choose from.
Here is the link for the on-premises SQL Server pricing and price guide:
https://www.microsoft.com/en-us/sql-server/sql-server-2022-pricing
Other technical constraints
Sometimes, when transitioning massive SQL Servers into the Azure cloud, there is just not enough capacity on Azure. Things like tempdb performance, logging, and storage in general, are the greatest constraints for such workloads and you may end up having serious latencies. Luckily enough, this part has evolved in the last few years both on IaaS and PaaS side of the Azure cloud, but every now and then I have faced such server workloads, that are simply just too huge and costly to host in the Azure. That’s why it is important to be able to calculate these bottlenecks before transitioning into the cloud and that’s also why lift-and-shift is rarely a good option. SQL Governor software can significantly reduce this risk of VM under provisioning by automatically calculating an optimal Azure storage pool configuration for the SQL Server VMs to be transitioned to the cloud.
Storage performance best practices on Azure SQL Server VMs:
Azure IaaS VM storage disk types:
https://learn.microsoft.com/en-us/azure/virtual-machines/disks-types
Are non-functional requirements met?
From a non-functional perspective, right sizing your SQL Servers gives you better performance and availability, when capacity planning is done properly with a software such as SQL Governor. Microsoft Azure offers great availability for VMs, up to 99.95% and Azure Managed Instances up to 99.99%.
Read more information on Azure VM availability from here:
Availability options for Azure Virtual Machines - Azure Virtual Machines | Microsoft Learn
Learn about Azure Managed Instance availability from here:
What is Azure SQL Managed Instance? - Azure SQL Managed Instance | Microsoft Learn
Check Azure IaaS security best practices for VMs here:
Security best practices for IaaS workloads in Azure | Microsoft Learn
Right sizing SQL Server – how it is done?
Right sizing does not differ a lot from scaling up SQL Server in general, but it needs some mathematics applied. The biggest difference is that when right sizing SQL Servers, you need to know the CPU performance ratio in between the source and target server, to be able to calculate the processing capacity needs on target server. This feature is kept up to date in performance benchmark repository in SQL Governor software, enabling smooth extrapolation in between the source and target server processors, physical cores, and hyper threads. You can right size from and to on-premises physical servers / VMs, on Nutanix, AWS and on Azure.
Excerpt from SQL Governor: Right sizing continuously growing, low criticality test VM from on-premises into the Azure for next 1 year with optimal SLAs.
Right sizing SQL Server from on-premises to Azure with SQL Governor software
Right sizing SQL Server VM or physical SQL Server from on-premises to Azure with SQL Governor very simple and straightforward task. Steps are as follows:
- Select parameters such as prediction length, telemetry data start and end date and some telemetry data filtering criteria (business rules like service time and service breaks).
- Select desired VM template (any Azure VM) for your target server and configuration parameters such as server criticality level and OS reserve for the template. You can for example bypass the trend calculation if there is insufficient telemetry data for long-term trend calculations.
- Drag the SQL Server you want to migrate into the target VM template.
- Process the dataset and review the results.
SQL Governor confirms that all the adjusted source server telemetry data matches the target criteria in all levels (server, instance, database) and for the performance counters of maximum CPU usage, average CPU usage and RAM usage with given technical SLA criteria. It also checks whether the current SQL Server workload fits target Azure VM template with its given constraints such as IOPS, throughput latency and storage capacity.
Excerpt from SQL Governor: Right sizing on-premises SQL Server VM into Azure database optimized VM notifies insufficient RAM. You would need to use CPU-constrained E-series VM instead with doubled up RAM and same logical processor count).
Excerpt from SQL Governor: Automated storage needs calculation against Azure VM storage pool disks (simple mode).
Excerpt from SQL Governor: Comparing source and target VM baselines after Azure transition.
Are you planning the right sizing of your SQL Servers? Are you planning the transition of your SQL Server workloads into the cloud? If yes, contact me, and we can discuss how we can do this with optimized costs and carbon footprint!
Jani K. Savolainen
CEO & Chairman,
SQL Governor
