Migrate Azure SQL DB from DTU to vCore Based Purchasing Model

By: Mohammed Moinudheen   |   Updated: 2021-10-11   |   Comments   |   Related: > Azure

Problem

I see there are few of my Azure SQL databases making use of the DTU based purchasing model. Is there any way by which I can change the Azure SQL DB to make use of the vCore based purchasing model which is the Microsoft recommended option? By any chance, can we revert back to the original DTU based model after changing to the vCore based purchasing model?

Solution

This tutorial assumes that you already have resources in your Azure environment and you are familiar with the Azure portal. If you are new to Azure, you can subscribe to a trial account here, which will give you access to Azure services for a year and a credit of USD $200. It is assumed, that you already have an Azure SQL database making use of the DTU based purchasing model. In order to learn about the DTU based purchasing model you can refer this tip. You can refer this tip which has step by step instructions for creating an Azure SQL database. You may refer both these tips if you don't have a test Azure SQL DB already.

What is DTU and vCore purchasing model?

These are the two purchasing models that are available for Azure SQL database whereas the Azure SQL managed instance supports only the vCore based purchasing model.

DTU stands for database transaction unit and is a bundled version of compute, storage and I/O. We don't have transparency on the amount of memory or CPU that is available on the DTU model. As it is a bundled offering, we need to estimate the performance based on certain DTU values. Higher the value, greater the performance but it all comes at a cost.

With vCore based purchasing model, customers can have transparency on what Azure resources are offered for the price they pay thereby increasing flexibility and this option will be quite useful while performing migrations from on-premises servers as we can easily estimate the kind of resource configuration that may be required for running databases on Azure.

Reasons why you may want to migrate from DTU to vCore based purchasing model

You may want to perform the migration of the Azure SQL DB from DTU to vCore based purchasing model due to some of the reasons below

• Standardize purchasing model across all your Azure SQL databases
• You want greater transparency in the way you select your Azure SQL DB resources
• You may want to make use of the serverless option that is only available in the vCore based model
• You may want to make use of additional savings while using the vCore based purchasing model
• You are performing a new migration from on-premises servers
• You want to align your Azure SQL DB configuration as per Microsoft recommendation

These are some of the reasons due to which you may want to perform the migration. However, as always, you will need to thoroughly test your requirements before making this change as all of this depends on your specific requirements and there is no hard and fast rule to be applied. End of the day, most of these changes will revolve around cost savings.

Azure SQL DB pricing – DTU vs vCore

In order to get an estimate on the pricing estimate for Azure SQL DB either on the DTU or vCore purchase model, you may use this option where you can explore the pricing options using the various combinations that are possible. In this case, you can see the options that are available for a DTU based model.

If you go down further, you can view the costs associated with each service tier. This is the monthly associated costs.

As it is a bundled feature, you know upfront monthly costs when you choose the correct service tier for your database. In the next option, you can choose the vCore purchasing model.

In the vCore model, you can see for yourself the payments required for the various components that are used in this purchasing model. For the compute resources, you can see this window.

You can view details of the costs/month for the vCore model. Here, you can see pricing details for the logical cores and the memory used as compared to what you saw for similar option using the DTU purchasing model. Additionally, in the vCore model, you pay separately for the storage used.

By using and comparing these options, you can get an idea on the pricing cost associated for either the DTU or the vCore purchasing model.

Steps to migrate Azure SQL database from DTU to vCore model using Azure portal

The steps are straight forward using the Azure portal. In this demo, we are using an Azure SQL database which is in the Basic pricing tier which has a capacity of 5 DTUs. First click on the pricing tier option on the 'Overview' blade on the left as shown.

Once you click on the 'Pricing tier', you will see this window.

Here, you can validate the service tier of the Azure SQL database. Once you click on the section highlighted above for 'Service tier', you will see the various other service tier options that are available.

Here, in the highlighted section, you can view the options that are available in the vCore based purchasing model. In this example, we will select the 'General Purpose (Scalable compute and storage options)'. Once you click on the 'General Purpose' option, you will see this confirmation window where you can review the details and click on 'Apply'. We will configure a 'Provisioned' Azure SQL database with 2 vCores, 2 GB maximum size and an estimated monthly cost of 408 NZD as shown.

Once you click on 'Apply', you will get this message in the notifications section as shown.

This process will complete in a few minutes and you will see this confirmation in the notifications section.

Once this is done, you can view the status of the service tier of the Azure SQL database from the overview section as shown earlier.

As you can see from the screenshot, the pricing tier has changed to General Purpose: Gen5, 2 vCores which falls under the vCore purchasing model. With this example, you saw how we can make this change from the DTU to vCore purchasing model using the Azure portal. In the next section, we will see how we can make this change using T-SQL.

Steps to migrate Azure SQL database from DTU to vCore model using T-SQL

In this section, we will repeat the steps we performed earlier for migrating Azure SQL DB from DTU to vCore based model using T-SQL.

You can use below script to check the current details of the Azure SQL database.

-- Update database name in place of Ad
select Edition = DATABASEPROPERTYEX('Ad', 'Edition'),
serviceobjective = DATABASEPROPERTYEX('Ad', 'serviceobjective')

You will see this output when you run the above command on the 'Ad' database.

As you can see, the Azure SQL database is on the Basic tier which is in the DTU purchasing model. In order to migrate this database to the vCore purchasing model, you can run this command on the 'Ad' database.

-- Make sure to update database name
ALTER DATABASE [Ad] 
MODIFY (EDITION = 'GeneralPurpose', 
        MAXSIZE = 2 GB, 
        SERVICE_OBJECTIVE = 'GP_Gen5_2');

Once you run this, this will trigger the change to migrate to vCore purchasing model. The process will complete in few minutes and you can check the status by clicking on the 'Overview' option on the Azure portal.

Once the status changes, you can run below command again to check the service tier details and validate that the change has completed as expected.

-- Update database name in place of Ad
select Edition = DATABASEPROPERTYEX('Ad', 'Edition'),
serviceobjective = DATABASEPROPERTYEX('Ad', 'serviceobjective')

The output will be as below.

From the output, you can confirm that the Azure SQL DB is migrated to the vCore purchasing model as it is on the 'General Purpose' pricing tier and is using GP_Gen5_2 which stands for General purpose using Gen 5 hardware with 2 vCores.

With this, you know to migrate Azure SQL DB from DTU to vCore purchasing model.

Can we revert back to original, that is, migrate back from vCore purchasing model to DTU based model?

Yes, it is possible. Just refer the same steps for the reverse process. Only note, that this is not possible if you migrate Azure SQL DB to the Hyperscale service tier. At this time, it is not allowed to change service tier of an Azure SQL DB in hyperscale service tier. Hyperscale service tier supports databases up to a size of 100 TB. So, remember if you are migrating to the Hyperscale service tier, you cannot bring the database back to any other service tier.

Next Steps

• You can validate the steps in this tip by creating a sample Azure SQL DB in the Basic tier
• Migrate the database to the vCore based purchasing model by using the Azure portal
• Revert the database to the basic tier
• Migrate the database to the vCore based purchasing model by using T-SQL
• Revert the database to the basic tier
• In order to read more on Azure topics, you can refer the Azure section on MSSQLTips

About the author

Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips