Azure SQL Database scheduled autoscaling

One of the major benefits of Azure is scalability, the cloud has been built to be elastic to be as cost-effective as possible. Azure solutions can be scaled in ways that would never be possible with your own hardware. You are able to scale up and out when the demand arises, and, more importantly, you can scale down and in when you don’t need all the resources anymore, thereby saving a lot of money.

Autoscaling based on a schedule allows you to scale your solution according to predictable resource demand. For example you could require the highest capacity on Monday during peak hours, while the rest of the week the traffic is decreased allowing you to scale down. Outside business hours and during weekends you could then scale down further to the absolute minimum.

Azure SQL Database currently offers four service tiers: Basic, Standard, Premium, and Premium RS. Databases are isolated from each other and portable. You can change between tiers easily and without downtime.
Azure SQL database tiers and performance levels


Scheduled autoscaling with Azure Automation

To automate scaling of Azure SQL Databases based on a schedule I have created an Azure Automation PowerShell Runbook called AzureSQLDatabaseScheduledAutoScaling. You can easily import this runbook into your Azure Automation account and then execute it against your database by entering values for a few parameters that specify your database details, your Automaton Run As Account and a database-specific scaling schedule. A step-by-step guide can be found at the bottom of this post.

Scheduling of the runbook itself can be done by creating a recurring hourly Automation Schedule. A single runbook can have multiple jobs running at one time. This means it is possible to create multiple schedules for the AzureSQLDatabaseScheduledAutoScaling runbook, enabling scaling of multiple databases simultaneously.

Scaling of the database will only be performed in case the runbook execution moment matches a day/time slot specified in the scaling schedule parameter. If the runbook executes and the database is in the desired edition/tier already, no work will be done. Don’t worry about the costs of Azure Automation, the first 500 run minutes are free and after that you pay €0.002 / minute (as per september 2017).

Azure Automation Runbook Architecture

When you start using this solution, keep the following in mind:

  • The length of time for a switch of edition/tier varies, but is generally under 4 seconds, and under 30 seconds in 99% of the time. The duration of the entire scale process depends on both the size and service tier of the database before and after the change. For example a 250 GB database that is changing to, from, or within a Standard service tier, should complete within 6 hours. For a database of the same size that is changing performance levels within the Premium service tier, it should complete within 3 hours.
  • Behind the scenes, changing the edition/tier of a database creates a replica of the original database at the new edition/tier, and then switches connections over to the replica. No data is lost during this process. During the switch over to the replica, connections to the database are disabled, so some transactions in flight may be rolled back. If there are large numbers of transactions in flight at the moment connections are disabled, the length of time for the switch over may be longer.
  • To scale a database down, the database must be smaller than the maximum allowed size of the target service tier.
  • Objects that are not supported in target tiers can become inaccessible. For example when you scale down a Premium or Premium RS database that contains clustered columnstore indexes to Standard or Basic, the tables will be inaccessible until you scale back to Premium (RS) again.
  • Premium RS tier is still in preview and currently not yet supported by the Set-AzureRmSqlDatabase cmdlet that is used in the code of this solution. Trying to scale to it results in the following error message : Cannot bind parameter ‘Edition’. Cannot convert value “Premium RS” to type “Microsoft.Azure.Commands.Sql.Database.Model.DatabaseEdition”. Error: “Unable to match the identifier name Premium RS to a valid enumerator name. Specify one of the following enumerator names and try again: None, Premium, Basic, Standard, DataWarehouse, Free”


The Runbook PowerShell script

The PowerShell script below can be used in an Azure Automation Runbook with type “PowerShell”.

The script contains the following logic:

  1. Authenticate with Azure Automation Run As account.
  2. Get the current day of week and current time, and convert it to the time zone that is specified.
  3. Check which of the specified ‘slots’ in the scaling schedule is currently active.
  4. Scale database to edition/tier that is specified in the scaling schedule.
    If no schedule for the current day/time is found, scale to default edition/tier.
    If database is already in target edition/tier, do nothing.

.SYNOPSIS
Vertically scale an Azure SQL Database up or down according to a
schedule using Azure Automation.

.DESCRIPTION
This Azure Automation runbook enables vertically scaling of
an Azure SQL Database according to a schedule. Autoscaling based
on a schedule allows you to scale your solution according to
predictable resource demand. For example you could require a
high capacity (e.g. P2) on Monday during peak hours, while the rest
of the week the traffic is decreased allowing you to scale down
(e.g. P1). Outside business hours and during weekends you could then
scale down further to a minimum (e.g. S0). This runbook
can be scheduled to run hourly. The code checks the
scalingSchedule parameter to decide if scaling needs to be
executed, or if the database is in the desired state already and
no work needs to be done. The script is Timezone aware.

.PARAMETER resourceGroupName
Name of the resource group to which the database server is
assigned.

.PARAMETER azureRunAsConnectionName
Azure Automation Run As account name. Needs to be able to access
the $serverName.

.PARAMETER serverName
Azure SQL Database server name.

.PARAMETER databaseName
Azure SQL Database name (case sensitive).

.PARAMETER scalingSchedule
Database Scaling Schedule. It is possible to enter multiple
comma separated schedules: [{},{}]
Weekdays start at 0 (sunday) and end at 6 (saturday).
If the script is executed outside the scaling schedule time slots
that you defined, the defaut edition/tier (see below) will be
configured.

.PARAMETER scalingScheduleTimeZone
Time Zone of time slots in $scalingSchedule.
Available time zones: [System.TimeZoneInfo]::GetSystemTimeZones().

.PARAMETER defaultEdition
Azure SQL Database Edition that wil be used outside the slots
specified in the scalingSchedule paramater value.
Example values: Basic, Standard, Premium RS, Premium.
For more information on editions/tiers levels,
http://msdn.microsoft.com/en-us/library/azure/dn741336.aspx

.PARAMETER defaultTier
Azure SQL Database Tier that wil be used outside the slots
specified in the scalingSchedule paramater value.
Example values: Basic, S0, S1, S2, S3, PRS1, PRS2, PRS4,
PRS6, P1, P2, P4, P6, P11, P15.

.EXAMPLE
-resourceGroupName myResourceGroup
-azureRunAsConnectionName AzureRunAsConnection
-serverName myserver
-databaseName myDatabase
-scalingSchedule [{WeekDays:[1], StartTime:”06:59:59″,
StopTime:”17:59:59″, Edition: “Premium”, Tier: “P2″},
{WeekDays:[2,3,4,5], StartTime:”06:59:59″,
StopTime:”17:59:59”, Edition: “Premium”, Tier: “P1”}]
-scalingScheduleTimeZone W. Europe Standard Time
-defaultEdition Standard
-defaultTier S0

.NOTES
Author: Jorg Klein
Last Update: 18/09/2017

param(
[parameter(Mandatory=$true)]
[string] $resourceGroupName, 

[parameter(Mandatory=$true)]
[string] $azureRunAsConnectionName, 

[parameter(Mandatory=$true)]
[string] $serverName, 

[parameter(Mandatory=$true)]
[string] $databaseName, 

[parameter(Mandatory=$true)]
[string] $scalingSchedule, 

[parameter(Mandatory=$true)]
[string] $scalingScheduleTimeZone, 

[parameter(Mandatory=$true)]
[string] $defaultEdition, 

[parameter(Mandatory=$true)]
[string] $defaultTier
) 

Write-Output "Script started." 

#Authenticate with Azure Automation Run As account (service principal)
$runAsConnectionProfile = Get-AutomationConnection `
-Name $azureRunAsConnectionName
Add-AzureRmAccount -ServicePrincipal `
-TenantId $runAsConnectionProfile.TenantId `
-ApplicationId $runAsConnectionProfile.ApplicationId `
-CertificateThumbprint ` $runAsConnectionProfile.CertificateThumbprint | Out-Null
Write-Output "Authenticated with Automation Run As Account." 

#Get current date/time and convert to $scalingScheduleTimeZone
$stateConfig = $scalingSchedule | ConvertFrom-Json
$startTime = Get-Date
Write-Output "Azure Automation local time: $startTime."
$toTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById($scalingScheduleTimeZone)
Write-Output "Time zone to convert to: $toTimeZone."
$newTime = [System.TimeZoneInfo]::ConvertTime($startTime, $toTimeZone)
Write-Output "Converted time: $newTime."
$startTime = $newTime 

#Get current day of week, based on converted start time
$currentDayOfWeek = [Int]($startTime).DayOfWeek
Write-Output "Current day of week: $currentDayOfWeek." 

# Get the scaling schedule for the current day of week
$dayObjects = $stateConfig | Where-Object {$_.WeekDays -contains $currentDayOfWeek } `
|Select-Object Edition, Tier, `
@{Name="StartTime"; Expression = {[datetime]::ParseExact($_.StartTime,"HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}}, `
@{Name="StopTime"; Expression = {[datetime]::ParseExact($_.StopTime,"HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)}} 

# Get the database object
$sqlDB = Get-AzureRmSqlDatabase `
-ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $databaseName
Write-Output "DB name: $($sqlDB.DatabaseName)"
Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)" 

if($dayObjects -ne $null) { # Scaling schedule found for this day
    # Get the scaling schedule for the current time. If there is more than one available, pick the first
    $matchingObject = $dayObjects | Where-Object { ($startTime -ge $_.StartTime) -and ($startTime -lt $_.StopTime) } | Select-Object -First 1
    if($matchingObject -ne $null)
    {
        Write-Output "Scaling schedule found. Check if current edition & tier is matching..."
        if($sqlDB.CurrentServiceObjectiveName -ne $matchingObject.Tier -or $sqlDB.Edition -ne $matchingObject.Edition)
        {
            Write-Output "DB is not in the edition and/or tier of the scaling schedule. Changing!"
            $sqlDB | Set-AzureRmSqlDatabase -Edition $matchingObject.Edition -RequestedServiceObjectiveName $matchingObject.Tier | out-null
            Write-Output "Change to edition/tier as specified in scaling schedule initiated..."
            $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
            Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)"
        }
        else
        {
            Write-Output "Current DB tier and edition matches the scaling schedule already. Exiting..."
        }
    }
    else { # Scaling schedule not found for current time
        Write-Output "No matching scaling schedule time slot for this time found. Check if current edition/tier matches the default..."
        if($sqlDB.CurrentServiceObjectiveName -ne $defaultTier -or $sqlDB.Edition -ne $defaultEdition)
        {
            Write-Output "DB is not in the default edition and/or tier. Changing!"
            $sqlDB | Set-AzureRmSqlDatabase -Edition $defaultEdition -RequestedServiceObjectiveName $defaultTier | out-null
            Write-Output "Change to default edition/tier initiated."
            $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
            Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)"
        }
        else
        {
            Write-Output "Current DB tier and edition matches the default already. Exiting..."
        }
    }
}
else # Scaling schedule not found for this day
{
    Write-Output "No matching scaling schedule for this day found. Check if current edition/tier matches the default..."
    if($sqlDB.CurrentServiceObjectiveName -ne $defaultTier -or $sqlDB.Edition -ne $defaultEdition)
    {
        Write-Output "DB is not in the default edition and/or tier. Changing!"
        $sqlDB | Set-AzureRmSqlDatabase -Edition $defaultEdition -RequestedServiceObjectiveName $defaultTier | out-null
        Write-Output "Change to default edition/tier initiated."
        $sqlDB = Get-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName
        Write-Output "Current DB status: $($sqlDB.Status), edition: $($sqlDB.Edition), tier: $($sqlDB.CurrentServiceObjectiveName)"
    }
    else
    {
        Write-Output "Current DB tier and edition matches the default already. Exiting..."
    }
} 

Write-Output "Script finished."


Step-by-step setup guide

Navigate to the Azure portal and create an Azure Automation account in the same Azure Subscription as where the Azure SQL Database that you want to scale resides. Make sure the Run As account has been created. When you create a new Automation account in the Azure portal, it automatically creates a new service principal(Run As account) and assigns it the Contributor role-based access control (RBAC) role in the subscription by default. These rights are sufficient to scale Azure SQL Databases.

I have uploaded my PowerShell script to TechNet Script Center so you can easily import it as Runbook. Navigate to your Automation Account and click the “Runbooks Gallery” menu item. Make sure you filter the Gallery Source on “Script Center” and search for “Azure SQL Database scheduled autoscaling”. Open the item from the gallery and click Import.
Runbooks Gallery

Before you can use the runbook you have to publish it first. Click on “Overview” if you are not already on that page and then click “Edit”.
Edit Runbook

Now click “Publish”.
Publish Runbook

The runbook is now ready to use. Next step is to schedule it to run hourly, of course with your desired parameters. Click “Schedules” and then “Add a schedule”.
Schedule Runbook

Give your schedule a name, set the start time on a whole hour, the Recurrence on “Recurring”, Recur every “1 Hour” and Set expiration to “No”.
Recurring Schedule Runbook

Now configure the parameters. Explaination can be found under “The Runbook PowerShell script” earlier in this article.
Runbook parameters

When you now navigate to “Jobs” you get an overview of all executions.
Runbook Jobs

When you open a job and look at the Output, you will see all details of that particular run. For example, I can see my database was scaled down from P1 to S0 on friday at 18:00:
Runbook Job Output

Hybrid-BI data platform network architecture

This blog post gives an overview of a typical hybrid Azure/on-premises BI data platform network architecture. The most important settings, configurations, shortcomings and other things you should take into account are described. For more details and in-depth documentation links to Microsoft pages are provided at the bottom of this post.

The following common scenarios are covered:

·       Write data from on-premises data sources to an Azure SQL Database or Azure SQL Data Warehouse using SQL Server Integration Services.

·       Use the On-Premises Data Gateway (or ADF gateway) to make on-premises sources available for various Azure services like PowerBI.com and Azure Data Factory, or to be able to process an Azure Analysis Services cube with on-premises data.

·       Connect to an Azure SQL Database using client tools or PowerBI.com.

·       Connect to an Azure Analysis Services tabular model using client tools or PowerBI.com.

The architecture diagram below (click to open) shows how the different on-premises resources connect to Azure services. For every connection the ports that should be open in your firewall, network protocols, encryption methods and authentication types are shown.

Azure DDP

  

1.       For the SSIS ADO.NET driver, use the following connection string parameters to use an encrypted connection: Encrypt=True and TrustServerCertificate=False.

 

2.       All connections to Azure SQL Database require encryption (SSL/TLS) at all times while data is “in transit” to and from the database.

3.       Set “Allow access to Azure services” property for your Azure SQL Database server to ON. Be aware that this means not only the Azure services in your Azure Subscription can reach your Azure SQL Database server, but all Azure services worldwide, also from other customers.

 

4.       Only SQL Server Authentication is currently supported when connecting to an Azure SQL Database from SSIS, Power BI Desktop or Excel. Power BI Desktop models deployed to PowerBI.com will therefore also connect to an Azure SQL Database using SQL Server Authentication. The latest version of SQL Server Management Studio does support Azure Active Directory Integrated Authentication.

 

5.       To access Azure SQL Database from your local computer, ensure the firewall on your network and local computer allows outgoing communication on TCP port 1433. Outgoing traffic can be filtered to allow only traffic to Azure datacenter IP addresses for your region. This is sometimes a requirement before organizations want to allow outgoing traffic through port 1433. Inbound connections for port 1433 can be blocked.

 

6.       By default in Power BI Desktop, the “Encrypt connections” option is checked for your data source. If the data source doesn’t support encryption, Power BI Desktop will prompt to ask if an unencrypted connection should be used.

 

7.       Port 443 is used for default communication to PowerBI.com. Ports 5671 and 5672 can be used for Advanced Message Queuing Protocol (AMQP). Ports 9350 thru 9354 can be used for listeners on Service Bus Relay over TCP. If you use the ADF gateway to write data to Azure SQL Database or Azure SQL DW outgoing traffic through port 1433 needs to be allowed.

Links:

Setup Azure Data Lake Analytics federated U-SQL queries to Azure SQL Database

One of the major value propositions of U-SQL is that it allows to query data where it lives. For external systems, such as Microsoft Azure SQL Database, this is achieved with federated queries against data sources.

image

 

 

In order to query these external data sources, a data source object has to be created and referenced that abstracts the connection information as well as information about its capabilities to execute query expressions passed and translated from U-SQL to the its local query engine.

 

Prerequisites

·         An Azure Subscription

·         Azure Data Lake Store (ADLS)

·         Azure Data Lake Analytics (ADLA)

·         Azure SQL Database (ASQLDB) or Azure SQL Data warehouse (ASQLDWH) with SQL login/password

·         Visual Studio 2015. Optional, to create and execute U-SQL queries, this can also be done in the Azure portal.

https://www.visualstudio.com/products/free-developer-offers-vs

·         Azure Data Lake Tools for Visual Studio 2015

https://www.microsoft.com/en-us/download/details.aspx?id=49504

·         Azure PowerShell

http://aka.ms/webpi-azps

·         RX Permissions on your ADLS (data level) to be able to create the Catalog Secret

o   Navigate to ADLS in Azure Portal

o   Click Data Explorer

o   Click Access

o   Validate you have Read/Execute permissions.

·         Allow IP range in the ASQLDB server firewall for the ADLA services that fire the U-SQL queries

o   Navigate to ASQLDB server in Azure Portal

o   Click Settings

o   Click Firewall

o   Create new rule with range 25.66.0.0 to 25.66.255.255

 

 

Steps:

1.       Create ADLA Database using a U-SQL query:

DROP DATABASE IF EXISTS YourADLADatabase;

CREATE DATABASE YourADLADatabase;

 

2.       Create Catalog Secret in ADLA Database which contains the password for the SQL login and connection string for the ASQLDB database using the Azure PowerShell script below:

#Login (login pop up appears)

Login-AzureRmAccount

#Show your available Azure Subscriptions

Get-AzureRmSubscription

#Connect to the Azure Subscription in which your ADLA Database exists 

Set-AzureRMContext -SubscriptionId 00000000-0000-0000-0000-000000000000

 

#$passwd: password for ASQLDB / ASQLDWH which you want to federate

#-Account: ADLA account name

#-DatabaseName: Data Lake Analytics Database name

#-Host: Host of ASQLDB / ASQLDWH 

$passwd = ConvertTo-SecureString “YourPassword” -AsPlainText -Force

$mysecret = New-Object System.Management.Automation.PSCredential(“YourASQLDB_Secret”, $passwd)

New-AzureRmDataLakeAnalyticsCatalogSecret -Account “youradlaaccount” -DatabaseName “YourADLADatabaseName” -Secret $mysecret -Host “yourasqldb.database.windows.net” -Port 1433

 

3.       Create CREDENTIAL with IDENTITY that matches the AzureRmDataLakeAnalyticsCatalogSecret name as used in the PowerShell script (YourASQLDB_Secret) in ADLA Database using the U-SQL query below:

//Connect to ADLA Database

USE DATABASE YourADLADatabaseName;

 

//Create CREDENTIAL

//USER_NAME: ASQLDB Username

//IDENTITY: ADLA Catalog Secret, must match name chosen in PowerShell script

CREATE CREDENTIAL IF NOT EXISTS YourASQLDB_Secret WITH USER_NAME = “YourASQLDB_Username”, IDENTITY = “YourASQLDB_Secret”;

 

4.       Create Data Source in ADLA Database with a reference to the ASQLDB using the U-SQL query below:

// Create External Data source on AZURESQLDB

CREATE DATA SOURCE IF NOT EXISTS ASQL_YOURDB FROM AZURESQLDB WITH

       ( PROVIDER_STRING = “Database=YourASQLDB;Trusted_Connection=False;Encrypt=True”

       , CREDENTIAL = YourASQLDB_Secret

       , REMOTABLE_TYPES = (bool, byte, sbyte, short, ushort, int, uint, long, ulong, decimal, float, double, string, DateTime)

       );

 

5.       Create an External Table in ADLA Database based on the Data Source using the U-SQL query below:

// CREATE EXTERNAL TABLE

// External tables are optional. You can refer to data source tables in queries directly (Lazy Metadata loading):

// FROM EXTERNAL ASQL_PATIENTS LOCATION “dbo.sometable”

CREATE EXTERNAL TABLE someExternalTable (

       [someColumn] string

) FROM ASQL_YOURDB LOCATION “dbo.someTable”;

 

6.       Query the federated external ASQLDB table and output result to file using the U-SQL query below:

@query =

    SELECT someColumn

    FROM someExternalTable;

 

 OUTPUT @query TO “/Output/file.csv”

 USING Outputters.Csv();