By: Mohammed Moinudheen | Updated: 2020-08-27 | Comments (1) | Related: > Azure
Problem
We would like to auto scale our Azure SQL database to a higher compute tier during the weekends for around 5 hours. I know we can do that manually from the Azure portal. Is there a way by which we can automate this process every week?
Solution
There are certain scenarios in our applications when there is more load on the systems and performance may take a hit during that time. In Azure, we can easily scale up the Azure SQL database to a higher compute tier in order to meet the performance requirement. We can scale up the Azure SQL DB any time using the portal. You can refer to these tips (tip1, tip 2) to learn more about the various compute tiers that are available.
Upgrading the Azure SQL DB Compute Tier using the Portal
By using the configure blade in the settings option on the Azure SQL DB, you can upgrade or downgrade the compute tier for the Azure SQL database.
Here, you can see that the Azure SQL DB is at Basic. If you want to change it to "Standard", just click on the option and select the required sizes and click on "Apply".
Scaling up or down is usually quick. However, as per Microsoft, the below steps are performed:
- New compute instance is created for the database
- Connections are routed to the new instance
It is mainly during the switch over process there is a brief interruption. This may take longer based on activity on the database. One good thing to note is there will never be any data loss.
Benefits of scaling up the Azure SQL database based on business requirements
You can estimate the cost of the various compute tiers by using the Azure pricing link.
Take for example, if you are running the database on P15 compute tier it will cost you USD $23.8087/hour. For the entire month, it will be USD $17,380. If you have to run the database for the entire year on P15, it will cost you a fortune. But instead, you can scale up the database to a higher compute tier only based on your requirements.
Another good thing is you will be charged only on an hourly basis for the amount of time the database was running on the higher compute tier. Take for example, if the database was on P15 for 4 hours 25 minutes, you will be charged for 5 hours. It doesn’t matter if you ran the database for 10 minutes or 15 minutes on the higher compute tier, it will be rounded off to the hour.
Performing these steps using Azure Logic Apps
Azure Logics Apps is a cloud service that helps us to automate, orchestrate tasks, etc. You can refer to this tip to learn more about Azure logic apps. You can search for ‘Logic Apps’ in the portal and start with the process of designing the app.
Once done, click on the "Add" option to start the process to create the app.
This will take you to the below screen where you can fill in the required fields like name, etc. Enter the required fields and click on "Review + Create".
Once you click on "Review + create" button, you will see this screen. Click on "Create".
Logic App Deployment
Once you click on "Create", the deployment process will start and will finish in a few seconds. You will see this screen.
Click on "Go to resource", and you will see this screen. Click on "Logic app designer".
You will see this screen where you can make some selections.
Recurrence
From the screen, select a common trigger "Recurrence" as we need this process to be triggered every weekend. Once done, you will see a screen with options for you to select the required parameters. For this demo, as we want auto scaling to occur on weekends, we will make this selection. After reviewing, click on "New Step".
Execute a SQL Query
In the next step, we will perform the upgrade of the compute tier. Select the option as shown to "Execute a SQL query (V2)".
Once you click on "Execute a SQL query (V2)", you will see this option where you need to enter the required details for server name and database name. We will be using below SQL query for upgrading the compute tier.
ALTER DATABASE [mo] MODIFY (SERVICE_OBJECTIVE = 'S0');
Once done, click on "New step".
Delay
Once the Azure SQL DB is scaled up, we would like to leave it at the higher compute tier for around 5 hours. So, we will call have an action to set a delay timer. Search for "Delay" action as shown.
In the "Delay" step, you can set the counter in hours as shown. Once done, click on "New Step".
In the next step, you can scale down the Azure SQL database back to original tier. In our case, it is the "Basic" tier. You can use the similar step like what was performed for scaling up.
Use below script for scaling down.
ALTER DATABASE [mo] MODIFY (SERVICE_OBJECTIVE = 'Basic');
Once this is done, save the workflows you created on the logic app designer.
Once the workflow is saved, you can initiate a run to validate that the design is working as expected. You may reduce the "Delay" time to something like 10 minutes instead of the original 5 hours.
Testing
In order to check the current compute tier, you can use this query
select Edition = DATABASEPROPERTYEX('mo', 'Edition'),
serviceobjective = DATABASEPROPERTYEX('mo', 'serviceobjective')
You can see the compute tier is basic.
Once you run the workflows, use the query above to validate that the compute tier is indeed changing. During the running phase, the workflow tasks will progress as shown.
You can see the tick mark across the workflow tasks. For the "Delay" action, you can see a timer. Also, once you check the status of the compute tier, you will see that it has changed to "Standard S0".
Checking status of all the tasks
You can check the status of the triggered jobs by clicking on the overview section of the Logic apps.
You can see that the trigger ran successfully and it completed in 5.01 minutes. You can click on the "Status" to view additional details. Here, you can see that each of the steps completed successfully.
You can check the compute tier on the database to validate that the database is back on the original Basic compute tier.
With this, you can see how easily we can scale up and scale down an Azure SQL database using the Azure Logic apps.
Next Steps
- In this tip, you saw how you could scale up an Azure SQL DB using the Azure portal
- In this tip, you learned about using the Azure logic apps for auto scaling Azure SQL DB
- In this tip, you saw how easily Azure logic apps can be used for automating routine tasks
- You could try this demo on a trial version of Azure or any test Azure SQL database
- To know more about Microsoft Azure, refer to this link 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
Article Last Updated: 2020-08-27
