PowerShell script to help migrate from DTU to vCore model of Azure SQL DB.

Published Feb 03 2022 10:44 AM 1,063 Views

Introduction

 

There are two billing models for Azure SQL Database (SQL DB); the Database Transaction Unit (DTU) model, which is a blended measure of CPU, memory and IOPS, and the vCore model that allows you to choose the number of virtual CPUs you need. The vCore model also allows you to choose the generation of hardware and newer hardware types such as the M-series, FSv2-series and DC-series for confidential computing. The other advantage of the vCore model is the ability to bring your box product SQL Server licenses to Azure to reduce the running costs via the Azure Hybrid Benefit (AHUB).

 

 

Conversion from DTU to vCore

 

There is a simple approximation you can use to estimate the number of vCores from the number of DTUs;

 

              100 DTU in Basic or Standard tiers = 1 vCore in General Purpose tier

              125 DTU in Premium tier = 1 vCore in Business Critial tier

 

This article provides more details and a query to run on a SQL DB to provide an estimate.

 

 

Scaling to the Enterprise

 

The problem that many larger customers run into is that running a query across their estate of SQL DBs is the prohibitive amount of effort required.

 

Instead, we have written a PowerShell script that an Azure Subscription(s) contributor can use to both discover the DTU model SQL DBs and based on the configured number of DTUs it provides an estimate of the number of vCores you should use as the starting point for conversion to the vCore model.

 

Note that for SQL DBs in an elastic pool, the script estimates the size of the Elastic Pool in vCores instead of the number of vCores for that specific SQL DB.

 

The PowerShell script creates a comma separated values (CSV) file, for each of the subscriptions the account running the script has access to, with the properties of the discovered SQL DBs and the conversion estimate.

 

The PowerShell script can be downloaded from the Microsoft Download Center.

 

 

Feedback and suggestions

 

If you have feedback or suggestions for improving this data migration asset, please contact the Data Platform Engineering Team. Thanks for your support!

%3CLINGO-SUB%20id%3D%22lingo-sub-3101798%22%20slang%3D%22en-US%22%3EPowerShell%20script%20to%20help%20migrate%20from%20DTU%20to%20vCore%20model%20of%20Azure%20SQL%20DB.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3101798%22%20slang%3D%22en-US%22%3E%3CH2%20id%3D%22toc-hId-358577783%22%20id%3D%22toc-hId-359530878%22%3EIntroduction%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%20two%20billing%20models%20for%20Azure%20SQL%20Database%20(SQL%20DB)%3B%20the%20Database%20Transaction%20Unit%20(DTU)%20model%2C%20which%20is%20a%20blended%20measure%20of%20CPU%2C%20memory%20and%20IOPS%2C%20and%20the%20vCore%20model%20that%20allows%20you%20to%20choose%20the%20number%20of%20virtual%20CPUs%20you%20need.%20The%20vCore%20model%20also%20allows%20you%20to%20choose%20the%20generation%20of%20hardware%20and%20newer%20hardware%20types%20such%20as%20the%20M-series%2C%20FSv2-series%20and%20DC-series%20for%20confidential%20computing.%20The%20other%20advantage%20of%20the%20vCore%20model%20is%20the%20ability%20to%20bring%20your%20box%20product%20SQL%20Server%20licenses%20to%20Azure%20to%20reduce%20the%20running%20costs%20via%20the%20%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fen-us%2Fpricing%2Fhybrid-benefit%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Hybrid%20Benefit%3C%2FA%3E%20(AHUB).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1448876680%22%20id%3D%22toc-hId--1447923585%22%3EConversion%20from%20DTU%20to%20vCore%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20is%20a%20simple%20approximation%20you%20can%20use%20to%20estimate%20the%20number%20of%20vCores%20from%20the%20number%20of%20DTUs%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20100%20DTU%20in%20Basic%20or%20Standard%20tiers%20%3D%201%20vCore%20in%20General%20Purpose%20tier%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20125%20DTU%20in%20Premium%20tier%20%3D%201%20vCore%20in%20Business%20Critial%20tier%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fmigrate-dtu-to-vcore%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Earticle%3C%2FA%3E%20provides%20more%20details%20and%20a%20query%20to%20run%20on%20a%20SQL%20DB%20to%20provide%20an%20estimate.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1038636153%22%20id%3D%22toc-hId-1039589248%22%3EScaling%20to%20the%20Enterprise%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20problem%20that%20many%20larger%20customers%20run%20into%20is%20that%20running%20a%20query%20across%20their%20estate%20of%20SQL%20DBs%20is%20the%20prohibitive%20amount%20of%20effort%20required.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%2C%20we%20have%20written%20a%20PowerShell%20script%20that%20an%20Azure%20Subscription(s)%20contributor%20can%20use%20to%20both%20discover%20the%20DTU%20model%20SQL%20DBs%20and%20based%20on%20the%20configured%20number%20of%20DTUs%20it%20provides%20an%20estimate%20of%20the%20number%20of%20vCores%20you%20should%20use%20as%20the%20starting%20point%20for%20conversion%20to%20the%20vCore%20model.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20that%20for%20SQL%20DBs%20in%20an%20elastic%20pool%2C%20the%20script%20estimates%20the%20size%20of%20the%20Elastic%20Pool%20in%20vCores%20instead%20of%20the%20number%20of%20vCores%20for%20that%20specific%20SQL%20DB.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20PowerShell%20script%20creates%20a%20comma%20separated%20values%20(CSV)%20file%2C%20for%20each%20of%20the%20subscriptions%20the%20account%20running%20the%20script%20has%20access%20to%2C%20with%20the%20properties%20of%20the%20discovered%20SQL%20DBs%20and%20the%20conversion%20estimate.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20PowerShell%20script%20can%20be%20downloaded%20from%20the%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D103893%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EMicrosoft%20Download%20Center%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--768818310%22%20id%3D%22toc-hId--767865215%22%3EFeedback%20and%20suggestions%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20feedback%20or%20suggestions%20for%20improving%20this%20data%20migration%20asset%2C%20please%20contact%20the%26nbsp%3B%3CA%20href%3D%22mailto%3Adatasqlninja%40microsoft.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EData%20Platform%20Engineering%20Team%3C%2FA%3E.%20Thanks%20for%20your%20support!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3101798%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20already%20own%20SQL%20Server%20licenses%2C%20there%20are%20cost%20advantages%20of%20switching%20from%20the%20DTU%20model%20to%20vCore%20model%20in%20the%20form%20of%20Azure%20Hybrid%20Benefits%20(AHUB)%20discounts.%20There%20are%20also%20more%20hardware%20deployment%20options%20available%20for%20vCore%20model%3B%20including%20memory%20optimized%2C%20compute%20optimized%20and%20confidential%20computing%20enabled%20hardware.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3101798%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDatabase%20Platform%20CSE%20%E2%80%93%20SQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Feb 03 2022 10:44 AM
Updated by: