Auto Scale Azure SQL DB using Azure Logic Apps ,PowerShell Script and SQL Agent .

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.

There are certain scenarios in our application when there is less load on the system and performance may not causing any issue during that time ,In Azure We can easily scale down the Azure SQL database to lower computer tire in order to reduce the cost .

Benefits of scaling up and scaling down to reduce the cost as well to improve the performance.

During the peak hours some jobs are causing the performance and DTU and CPU hits are happen ,in order to improve the performance scaleup the instance .It will be charged only on an hourly basis for the amount of time the database was running on the higher compute tier.

Plan 1: connectors in Azure Logic Apps

Azure Logic Apps helps you create and run automated integration workflows that can scale in the cloud.

Connectors overview for Azure Logic Apps - Azure Logic Apps

A trigger specifies the event that starts the workflow and is always the first step in any workflow. Each trigger also follows a specific firing pattern that controls how the trigger monitors and responds to events.

Open Logic Apps in the portal and start with the process of designing the app.

Click Add option and create the logic App

Cl

Provide the below details to create the Logic App.

You will see this screen after clicking on the “Review + Create” button. Click “Create”.

When you click “Go to resource”, you will see this screen. Click “Logic app designer”.

Recurrence

Select “Recurrence” as we need this process to run every weekend. Once you do this you will see a screen with options for you to select the parameters. For the Screenshot below, auto scaling happens every weekend.

Execute a SQL Query:

Go to next step and select Execute a SQL query (V2).

Provide the Azure SQL credentials and the scaleup query in the query box

Delay:

When the Azure SQL DB has been scaled down, we would like to leave it at a lower compute tier for about 2days.
So, we will create an action to create a delay timer. Find the “Delay” action, as shown below.

Next, you can scale up the Azure SQL database back to its original tier.

When performance is low on business hours , it will be useful to implement this concept.

Finally triggered the Auto Scale job and monitor it.

While ruining the logic App design job automatically background the instance will be scale down.

It has been changed from s0 to s3.

After two day again it will scale back to original status as per our schedule .

Plan 2: Powershell script for Auto scale instance.

1. Provide the DB name ,user details and query .
2. Schedule the job in task scheduler .
3. Task-Basic task-Provide the script path .

Triggers the Task scheduler job

#Variable to hold variable 
$SQLServer = “testaj1.database.windows.net” 
$SQLDBName = “Aj_Test” 
#$uid =”ajay\test” 
#$pwd = “password123” 
$SQLUsername=”sql”
$SQLPassword=”Ajay@123" 
#SQL Query 
$SqlQuery = “ALTER DATABASE [Aj_Test] 
MODIFY (EDITION = ‘GeneralPurpose’, 
 MAXSIZE = 2 GB, 
 SERVICE_OBJECTIVE = ‘GP_Gen5_2’)” 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = “Server = $SQLServer; Database = $SQLDBName; User ID=$SQLUsername; Password=$SQLPassword” 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.CommandText = $SqlQuery 
$SqlCmd.Connection = $SqlConnection 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 
#Creating Dataset 
$DataSet = New-Object System.Data.DataSet 
$SqlAdapter.Fill($DataSet)

Plan 3: On-prem SQL Agent

1. Configure the linked server from VM to Azure SQL Database.
2. Create the SP for Auto scalling .
3. Configure the job as per the schedule .

Linked server from VM to Azure SQL:

Create the Auto scaling Sp on Azure Pass Server.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[scaledown]
AS
BEGIN
SET NOCOUNT ON
ALTER DATABASE [Aj_Test] MODIFY (EDITION = ‘standard’, SERVICE_OBJECTIVE = ‘S3’, MAXSIZE = 10 GB)
END
GO

Configure the Auto scale job on VM SQL server agent .