What would be the most cost effective way of spinning up an instance of SQL Server in Azure in order to restore a small SQL Server database from a .bak file to Copy Data to the Data Lake?

Question

What would be the most cost effective way of spinning up an instance of SQL Server in Azure in order to restore a small SQL Server database from a .bak file to Copy Data to the Data Lake?

Amie Barkes 41

What do you think would be the most cost effective way of spinning up an instance of SQL Server in azure in order to restore a small database from a .bak file (note we only have .bak file so this excludes the possibility of using Azure SQL Database which requires a bacpac file). Ultimately the goal is to extract the data from the 40 tables and copy to the Azure Data Lake.
The frequency of data transfer required would be to repeat this process every day so this should be a consideration in the cost calculation.

BhargavaGunnam-MSFT 10,156 Reputation points Microsoft Employee

2022-08-12T22:03:36.68+00:00
Hello @Amie Barkes ,

Thanks for the question and using MS Q&A platform.

If you are looking at less demanding workloads, I recommend you to go with the Basic- DTU model. With 2 GB of data, Azure SQL DB will cost roughly $5 per month(considering your data won't grow)

If your workload requires typical performance, I recommend you use the Standard-DTU pricing model. With 10 DTUs and up to 250 GB of data costs roughly $15 per month.

If your workload is not running very often, I recommend you go with GeneralPurpose- Serverless. Using this pricing model, you will get billed based on vcores/seconds. The main advantage is "Auto pause delay."

The database automatically pauses if it is inactive for the time period specified here, and automatically resumes when database activity recurs. Alternatively, auto-pausing can be disabled.

If you need high availabilty options for your DB, then you can choose v-core(business critical) or DTU- premium pricing tiers.

Also please use this pricing calculator to explore pricing options for your DB.

https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/

If you have any further questions, please let me know.

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Amie Barkes 41 Reputation points

2022-08-15T08:24:57.137+00:00

Thank you for your reply. However the pricing model relates to running Azure SQL Server but I actually need to run an instance of SQL Server inj a VM in order to restore a .bak file.
Azure SQL does not support the restore of a database from a .bak file requiring a .bacpac file which I do not have. Hence I need to restore the .bak file to an instance of SQL Server running in a VM.

The architecture is illustrated below (The component I am requiring pricing information on is 'Virtual Machine in Azure - SQL Server and Self Hosted IR'

BhargavaGunnam-MSFT 10,156 Reputation points Microsoft Employee

2022-08-12T22:03:36.68+00:00

Hello @Amie Barkes ,

Thanks for the question and using MS Q&A platform.

If you are looking at less demanding workloads, I recommend you to go with the Basic- DTU model. With 2 GB of data, Azure SQL DB will cost roughly $5 per month(considering your data won't grow)

If your workload requires typical performance, I recommend you use the Standard-DTU pricing model. With 10 DTUs and up to 250 GB of data costs roughly $15 per month.

If your workload is not running very often, I recommend you go with GeneralPurpose- Serverless. Using this pricing model, you will get billed based on vcores/seconds. The main advantage is "Auto pause delay."

The database automatically pauses if it is inactive for the time period specified here, and automatically resumes when database activity recurs. Alternatively, auto-pausing can be disabled.

If you need high availabilty options for your DB, then you can choose v-core(business critical) or DTU- premium pricing tiers.

Also please use this pricing calculator to explore pricing options for your DB.

https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/

If you have any further questions, please let me know.

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Amie Barkes 41 Reputation points

2022-08-15T08:24:57.137+00:00

Thank you for your reply. However the pricing model relates to running Azure SQL Server but I actually need to run an instance of SQL Server inj a VM in order to restore a .bak file.
Azure SQL does not support the restore of a database from a .bak file requiring a .bacpac file which I do not have. Hence I need to restore the .bak file to an instance of SQL Server running in a VM.

The architecture is illustrated below (The component I am requiring pricing information on is 'Virtual Machine in Azure - SQL Server and Self Hosted IR'

Answer 1

Hello @Amie Barkes ,

Thanks for more details.
If you want to develop, test, or build a proof of concept, use the freely licensed SQL Server Developer edition. This edition has all the features of SQL Server Enterprise edition, allowing you to build and test any type of application. A SQL Server Developer edition VM only incurs charges for the cost of the VM, because there are no associated SQL Server licensing costs.

Based on your needs, there are multiple options available. Here are the latest and greatest versions. You can go with one of the SQL VM.

SQL Server 2019 developer on Windows Server 2019 - gen2
SQL Server 2019 developer on Windows Server 2022 - gen2
SQL Server 2017 developer on Windows Server 2019
SQL Server 2017 developer on Windows Server 2016

And regarding the VM size, DS1_V2 is the most popular VM for smaller workloads. Also, please see the below screenshot for other popular VMs and their associated cost.

Also, SQL on VM is available on Linux operating systems too.

For paid SQL versions, you can use Bring your own license (BYOL)

The BYOL images are currently only available for Windows virtual machines.

If you go with the licensed version, Choose a VM size that matches your expected CPU, memory, storage, and I/O bandwidth needs. The licensing cost of SQL Server is directly related to the number of vCPUs.

To reduce cost, shut down your VM when possible.
If you are using any workloads that do not run continuously, consider shutting down the virtual machine during the inactive periods. You only pay for what you use.

To use BYOL with a SQL Server VM, you must have a license for SQL Server Standard or Enterprise and Software Assurance, a required option through some volume licensing programs and an optional purchase with others.

Please see the below document for SQL on VM pricing guidelines.

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/pricing-guidance?view=azuresql

Please let me know if you have any further questions.

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

BhargavaGunnam-MSFT 10,156 Reputation points Microsoft Employee

2022-08-16T22:46:40.017+00:00

Hello @Amie Barkes ,
I am just checking in to see if you have any further questions here.

What would be the most cost effective way of spinning up an instance of SQL Server in Azure in order to restore a small SQL Server database from a .bak file to Copy Data to the Data Lake?

1 answer

Activity