CPU and memory overhead in IaaS SQL server VMs compared to PaaS Azure SQL database

Question

Hello everyone,

We are planning to reverse-migrate from Azure SQL database to SQL Server in VMs (IaaS). While capacity planning for CPU cores and Memory, do we need to consider any overhead for Operating System when compared to running in PaaS model Azure SQL database?

Are there any guidelines for capacity planning for reverse-migration from PaaS to IaaS model?

Thanks.

Answer 1

Hi @Praveen Babu Vejandla Thank you for posting your query on Microsoft Q&A and for using Azure services.

My understanding is that you are planning a reverse migration from Azure SQL Database to SQL Server in VMs (IaaS) and want to know if you need to consider any overhead for operating system when compared to running in PaaS model Azure SQL database.

Firstly, on SQL VMs (IaaS) you would need to consider all important resource limits compared to PaaS namely:

• CPU
• Memory
• Disk / Storage IOPS
• Throughput
• I/O latency

Then you would need to allow some additional buffer for OS related tasks and processes (also dependent on what else is running on the VM besides just the SQL Server engine). In the PaaS model, this is not considered an issue as we manage the underlying the platform to deliver the service.

Beyond just CPU and memory (with the right memory-to-core ratio), storage IOPS and throughput play an important part in selecting the right-sized VM as VMs also have IOPS and throughput limits in addition to disk storage. It is important to select the right VM SKU that meets these needs.
For the best price-performance in SQL VMs, we recommend the new Ebdsv5 series VMs that have good mem-to-core ratio along with high IOPS and throughput limits which will save you from provisioning heavy CPU VMs just to get high IOPS. There is a published article by Gigaom recently that highlights this VM series: https://research.gigaom.com/report/sql-transaction-processing-and-analytic-performance-price-performance-testing/
Like any migration, it would be recommended to migrate and test your workload on the right SQL VM on Azure in a Dev/Test/Pilot environment.

Hope that helps

Regards,
Oury

Answer 2

Please set the "Maximum server memory" on SQL Server IaaS so you can reserve 4-6 GB of RAM for the operating system.

I have created and tested rollback plans for ny customers, as is usually one of their requirements before migrating to Azure SQL Database. My suggestion in this scenario, create a SQL Server 2019 VM, with the latest cumulative update applied and try tpo restore a bacpac from Azure SQL Database there.

Some considerations:

• If you are using Enterprise Edition features like Columnstore that are available on Azure SQL, then make sure you install SQL Server 2019 Enterprise on the SQL VM.
• Update SQL Server Management Studio or use sqlpackage latest version to import the bacpac
• Make sure the collation of Azure SQL and the SQL VM are compatible
• If you will be using SqlPackage set the timeout to zero. "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Import /SourceFile:"path\to\MyDB.bacpac" /TargetConnectionString:"Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True;" /p:CommandTimeout="0"
• Rowversion numbers in use on Azure SQL will change once the bacpac is restored
• Use sp_configure to set 'contained database authentication' to 1. Seethe reason here.
• Script indexes on Azure SQL, remove them prior to create the bacpac on Azure SQL. See the reason here.