By: Mohammed Moinudheen | Updated: 2020-09-08 | Comments (4) | Related: > Azure
Problem
We would like to automate auto scaling of the Azure SQL database at certain times of the week. In the previous tip, Auto Scale Azure SQL DB using Azure Logic Apps, we saw how we can schedule this using Azure logic apps. Are there any other methods by which we can accomplish this?
Solution
There are certain times in our applications when there is more of a load on the systems and performance may take a hit during at 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 and tip 2) to learn more about the various compute tiers that are available. We can easily scale up the Azure SQL DB using the portal, but this is a manual process and it's fine if you are scaling up on occasion, however, if you have the requirement to scale up the Azure SQL DB regularly, it is best to automate the process in Azure.
Using Azure automation and PowerShell runbooks
Azure automation is a cloud service that helps in automating management tasks thereby saving time and preventing errors. We can also use Azure automation for several other tasks in the cloud as well for on premises configuration items. You can refer to this excellent tip by John Miner where he explains step by step how to set up an Azure automation account, create a runbook, publish a runbook, scheduling, etc. I highly recommend you read this tip if you are not familiar with Azure automation.
In this tip, we will see how to use existing runbooks in Azure automation in order to perform auto scaling of the Azure SQL database. This tip assumes you have already set up an automation account as described in this tip.
Cost of Azure automation pricing
As always, before using any service in Azure, you can easily find the estimated cost. Auto scaling Azure databases falls under the process automation category and you can get the estimate pricing from this Microsoft link. Here, you can see that the first 500 minutes of the job run is free and the cost is only US $0.002/minute for the subsequent runs.
Using existing runbooks from Runbooks gallery
Tasks like auto scaling Azure SQL DB are quite common and performed extensively in order to meet changing performance requirements of critical applications. With this in mind, you may have already realized by now that this is something other teams may have already automated for their critical applications. Runbooks gallery is where you can make use of existing automated runbooks shared by others in order to automate their routine processes. By using this, you don’t have to reinvent the wheel but can leverage existing runbooks and make modifications based on your requirements. As this is community driven, the runbooks are not officially supported by Microsoft but it doesn’t mean that they are not safe to use. You can always review the code in the runbooks and publish them after you test them and make sure they work as expected.
Runbooks from the Runbooks gallery
You can view the "Runbooks gallery" by clicking on the option under the "Process automation" section as shown.
You can view the runbooks that are already available. In order to search for the runbook related to "AutoScaling" just type in the term "scale" in the search bar as shown and press enter.
You can sort by popularity and on the first page you can view the PowerShell runbooks for auto scaling. As you can see, these runbooks are not from Microsoft but published by community members. We will use the first runbook from the screenshot above for this demo. We can see that it has been downloaded around 9500 times and was updated last in November 2018 and has a very high rating. The author of this runbook is Jorg Klein as you can see.
Review the runbook you selected
Double click on the runbook you selected and in the next screen, you will be able to view the code.
Here, in this window you can review the full script and the comments. You can review if it meets your requirements. Also, make sure to see the warning from Microsoft confirming that Microsoft is not responsible for runbooks provided by community members. After you review the code, click on the option to import the runbook.
Import the runbook
Once you click on import, you will need to provide a name for the runbook and click "OK" once done.
The import process is quite fast and you will get confirmation on the notifications section as shown.
Publish the runbook
We can view the imported runbook as shown, under the "Process Automation" section.
Click on the "new" runbook and you will see this screen.
Click on "Edit" to publish the runbook.
Before you publish, you can review the code once again and make sure that there are no errors. Sometimes, some special characters can appear on the code based on the contents of the code. Take for example, I came across these characters in the code as shown. Make sure, you replace those special characters as they will cause the runbook to fail with errors.
In this case, I had to replace those special characters †with a ", so the updated code looks like below.
Once you are done with the review, click on the "Publish" option to publish the runbook. Click on "Yes" to confirm.
You will get confirmation on the notification section as shown.
How to test the runbook?
The next step is to test the runbook. Once you publish the runbook, you will see this screen.
You can test the runbook, by clicking on the "Start" option. However, it may require parameters which we would need to input first. To learn more about this runbook, you may refer to the comments section in the runbook. You can view the code by editing the runbook. Usually the popular runbooks will have a detailed description of what the code does and what kind of parameters need to be provided. In this runbook, there is a detailed description and for testing the runbook, we can see what kind of parameters are required. As expected, in this runbook, there is an example provided for the kind of parameters that are required to perform a test run of the runbook.
Refer to the example that is provided and click on the "Start" option. Make sure to input the correct parameters for your test scenario. Below is the screenshot of the parameters section required for this runbook and a set of parameter values provided in this demo.
You can see there are 9 parameters that are required, a few of which are mandatory. In my case, I entered the below values. You would need to modify based on your server name, DB name, etc.
- ENVIRONMENTNAME: Leave as blank
- RESOURCEGROUPNAME: SQLRG (provide your correct resource group)
- AZURERUNASCONNECTIONNAME: Leave as blank
- SERVERNAME: molabs (provide correct server name, make sure don’t
input full server name)
- DATABASENAME: mo (provide correct DB name)
- SCALINGSCHEDULE: [{WeekDays:[1], StartTime:"10:01:59", StopTime:"23:59:59",
Edition: "Standard", Tier: "S0"}, {WeekDays:[2,3,4,5], StartTime:"10:01:59",
StopTime:"23:59:59", Edition: "Standard", Tier: "S0"}]
Make sure to change this as per your requirement. As mentioned earlier, refer to the code in this runbook for more details on this parameter. This is for testing purposes only. - SCALINGSCHEDULETIMEZONE: New Zealand Standard Time
You can get your required time zone by querying the sys.time_zone_info system catalog and provide the correct time zone based on your requirement
- DEFAULTEDITION: Basic
- DEFAULTTIER: Basic
Testing the runbook
Before you start the runbook, check the current compute tier of the test database. In our case, the current compute tier is on Basic. You can use this code to check.
select Edition = DATABASEPROPERTYEX('mo', 'Edition'),
serviceobjective = DATABASEPROPERTYEX('mo', 'serviceobjective')
Below is the output of the query. As you can see the Azure SQL database is on the basic tier.
As part of the test phase, we want to change the compute tier of this database to Standard S0 on weekdays between 10:01 hours to 23:59 hours. At this time, while I perform the test, the time is 23:00 hours. We will see if the test works as expected by starting the runbook. Make sure to input the parameters as described in the previous section. Once you input all the parameters, click on "OK" to start the runbook. You will see this screen where you can see that the status is queued.
You can click on "Refresh" to check the status of the run. After a few seconds, the status changes to completed.
You can check the "Output" tab and "All Logs" tab to view details of the job run. From the "Output" tab, you can see the below details.
From the output, you can see that the change in edition/tier was initiated as part of the test run and current DB status is showing as "Standard: S0". When you click on the "All Logs" tab, you will see additional details as shown below.
Checking the Azure SQL DB
In order to validate the Azure SQL DB, run the below command to confirm that the compute tier has changed.
select Edition = DATABASEPROPERTYEX('mo', 'Edition'),
serviceobjective = DATABASEPROPERTYEX('mo', 'serviceobjective')
From the screenshot, you can see that the Azure SQL DB is on Standard S0.
As the test run is successful, it validates that the runbook is working as expected. As you can see, you were able to download an already working solution in a few simple steps and implement it on your Azure SQL DB. You can refer to this tip, on how to schedule this runbook, refer to section "Scheduling our runbook" for more details on scheduling.
Next Steps
- In this tip, you saw the benefits of Azure automation
- In this tip, you learned how we could browse for existing runbooks from the gallery
- In this tip, you learned how we could import existing runbooks from the gallery
- In this tip, you learned how to review existing runbooks and make sure the code has no errors
- In this tip, you learned how to test an existing run books and make sure it works as expected
- 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-09-08
