Azure SQL Database proposes an automated system to export backup and restore them easily using the Azure Portal or Azure Cli tools. We have nothing to set up to take advantage of this process! However it can be useful to automate Azure SQL backup at specified time every day or every week. To achieve this goal we will host a custom Azure PowerShell script in a scheduled Azure Function.

 

Automated backups, pros and cons

Three types of backup are automatically created by Azure: full, differential and transaction logs. Backup’s frequency and retention period depends on database pricing tier, however transaction logs are saved every 5-10 minutes, differential backups happen every few hours and full backups are done every week. For Basic pricing tier retention period is 7 days, it’s 35 days for Standard and Premium tier (it’s possible to extend the retention period to 10 years using Azure Recovery Service). Full documentation is available here.

This automated system helps us a lot, but we can’t customize it, for example:

  • We can’t access directly to .bacpac files saved by Azure
  • We can’t schedule backup operations and store .bacpac files in a custom Azure Storage Account

 

Create Azure SQL backups in a custom storage account using Azure Powershell

Azure PowerShell permit us to create Azure SQL full Backup. Using the command New-AzureRmSqlDatabaseExport it’s possible to create a backup of an Azure SQL Database and store it in an Azure Storage container.

Here is the command syntax:

New-AzureRmSqlDatabaseExport [-DatabaseName] <String> [-ServerName] <String> -StorageKeyType <StorageKeyType> -StorageKey <String> -StorageUri <Uri> -AdministratorLogin <String> -AdministratorLoginPassword <SecureString> [-AuthenticationType <AuthenticationType>] [-ResourceGroupName] <String>

 

To use it successfully we need to give to the command following information:

  • Sql server credentials (AdministratorLogin, AdministratorPassword) and the target database name (DatabaseName)
  • Target storage information where we will store backups (storageUri, storageKey and storageKeyType)
  • Resource group where the Azure Sql server is placed (ResourceGroupName)

 

Here is the « backup » script:

$subcriptionId = "******"
$resourceGroupName = "project-tra-rg"

#Sql server and target database
$ServerName = "tra-sql-srv"
$DatabaseName = "tra-db"

#target storage informations
$StorageKeytype = "StorageAccessKey"
$StorageKey = "******"
$storageUriTarget = "https://trastorageac.blob.core.windows.net/backup"

#sql credentials
$pwdClear = "******"
$userName = "admin-tra"
$pwd = ConvertTo-SecureString $pwdClear -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential ($userName, $pwd)

#Backup naming logic
$bacpactName = "{0:yyyy-MM-dd}.bacpac" -f (get-date)
$uriTarget = $storageUriTarget + '/' + $DatabaseName + '-' + $bacpactName

Login-AzureRmAccount -SubscriptionId $subcriptionId

$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $uriTarget -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

while ($exportRequest.Status -eq "InProgress")
{
   $exportRequest = Get-AzureRmSqlDatabaseImportExportStatus -
   OperationStatusLink $exportRequest.OperationStatusLink
   [Console]::Write(" Export in progress..")
   Start-Sleep -s 10
}

if ($exportRequest.Status -eq "Succeeded")
{
   [Console]::Write(" Export done")
}

 

 

This script is easily usable from a machine where Azure PowerShell module is installed, but to run it in an Azure function we need to update the log in to Azure step. The following line of script will open a prompt to login to Azure:

Login-AzureRmAccount -SubscriptionId $subcriptionId

 

In the Azure Function we can’t use this log in process because the authentication step must be done without any user interaction. The command Login-AzureRmAccount can authenticate the script to Azure using an Azure AD App and a service principal linked :

Login-AzureRmAccount -ServicePrincipal -TenantId $tenantId -Credential $mycreds

 

$tenantId is the Azure tenantId and $mycreds corresponds to a PSCredential object configured with the servicePrincipalId and the Azure AD App Password.

Let’s create the Azure AD app and the service principal linked with the following script. It defines 3 outputs that we will need to log in to Azure (tenantId, Azure AD App Password and ServicePrincipalId)

 


# Service principal role
$role = "owner"

# Azure AD app configuration
$uniqueIdentifier = [guid]::NewGuid()
$appDisplayName = [String]::Format("app.{0}.{1}", $env:USERNAME, [guid]::NewGuid())
$appHomePage = [String]::Format("http://{0}", $appDisplayName)
$AppUris = $appHomePage
$password = "addProxyApp"
$subsciptionId = "******"

Login-AzureRmAccount -SubscriptionId $subsciptionId

$tenantId = (Get-AzureRmSubscription -SubscriptionId $subId).TenantId

#Create the Azure Ad app
$azureAdApp = New-AzureRmADApplication -DisplayName $appDisplayName -HomePage $appHomePage -IdentifierUris $AppUris -Password $password
$azureAdAppId = $azureAdApp.ApplicationId

#Define a service principal on the app
$sp = New-AzureRmADServicePrincipal -ApplicationId $azureAdAppId

#Assign a role to the service principal
New-AzureRmRoleAssignment -RoleDefinitionName $role -ServicePrincipalName $sp.ServicePrincipalNames[0]

Write-Output "Service principal Id: $azureAdAppId"
Write-Output "Azure tenant Id: $tenantId"
Write-Output "App password : $password"

 

Once the script executed, we have to get outputs and replace the line with the Login-AzureRmAccount command in the backup script with the following code:


#Azure credentials

$password = "******"

$tenantId = "*******"

$servicePrincipalId = "******"

$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force

$mycreds = New-Object System.Management.Automation.PSCredential ($servicePrincipalId, $secpasswd)

<br data-mce-bogus="1">

Login-AzureRmAccount -ServicePrincipal -TenantId $tenantId -Credential $mycreds

The final script is available here 🙂

 

Schedule backups using a time trigger Azure Function

We have an Azure SQL Database (tra-db), an Azure SQL Server (tra-sql-srv) and a storage account (trastorageac). In this storage account a container « backup » was created.

 


In the last part, we finished to implement the backup logic in Powershell with a login process usable in an Azure Function, now we need to setup the Azure Function.

Here are the function characteristics:

  • Type : Time trigger
  • Language : Powershell
  • Service plan type: Consumption (We want to execute a backup every day at 11PM, so we will pay only during the backup script execution and no more!)

 

 

The function app is named « tra-function », we create it in the existing resource group « project-tra-rg ». The function is named « backup_tra-db », we want to execute it every day at 11PM, that’s why we use the following Cron expression: 0 0 23 * * *

 

 

In the backup script, several parameters are required. The Azure Function host in an Azure Web app, so the good practice is too store parameter values in the App Service Application Settings. Parameter values will be then accessible from the Azure Function App using environment variables.

 

 

Once the parameter values stored in the web app application settings, let’s add the backup script logic in the Azure Function : we just have to map environments variables with script parameters and here we go, the automatic backup process is ready to use 🙂

 

 

The AzureRM.* Powershell modules are already installed in the Azure Function Host, that’s why we do not need to install them from Kudu!

 

Increase Azure Function timeout

We are actually using a service plan of type “consumption plan” to run the function. By default this type of service plan do not authorize process longer than 5 minutes, the problem is that depending of the database size, the process time can exceed this limit:

 

 

The solution is to increase the timeout limit editing the host.json metadata file. This can be done directly using kudu. My function app name is « tra-function », so we can access to the host file here : https://tra-function.scm.azurewebsites.net/dev/wwwroot/host.json. We have to update the functionTimout property to increase the timeout limit to 10 minutes:

 "functionTimeout": "00:10:00" 

 

Happy coding 🙂


Parse ASP net core configuration files and push them as Azure Web App application settings using Azure Powershell Clean empty Azure Resource Groups using Azure Cloud Shell

Leave a Reply

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *