Restore Azure SQL Databases

Overview

The Restore Azure SQL Databases Action is used to create a database and import a .bacpac file, restoring it to a previous state.

This action addresses the need for secure and streamlined restoration processes. GorillaStack uses Azure Functions and Key Vault integration to overcome the challenge of storing admin credentials. Through automation, you can proficiently manage databases, leading to reduced costs and optimized usage of Azure resources.

Azure Key Vault credentials will never be directly read by GorillaStack.

Use of this action in a rule

This action is intended to be used in conjunction with a separate rule containing the Export Azure SQL Databases action. Together they can create a workflow to reduce spend on Azure SQL Databases.

The import operations invoked by this action require admin credentials to the database server being affected. For security reasons we will not store these credentials within GorillaStack. To work around this limitation, this action will invoke an Azure Function in the targeted Azure subscription, where the admin credentials will be store in Azure Key Vault. See the Function Setup section below for further details.

Important to Note

This action will be created to target a database by its name. The .bacpac file used to restore must be created by the Export Azure SQL Databases action.

By default, SQL Servers will have the "Allow Azure services and resources to access this server" option disabled. For servers containing databases affected by this action, you will need to enable this option, which can be found in the server settings under "Security" -> "Firewalls and virtual networks". Enabling this option allows the created Azure Function to perform the Import/Export operations.

Function Setup

The Azure Function created will handle both import and export operations. As such, if it has been run while creating an Export action, it does not need to be run again for a corresponding Restore action targeting the same database

The Function Setup view will display a custom PowerShell command that will need to be run either in PowerShell, or Azure Cloud Shell. Simply click on the "Copy to Clipboard" button and paste the command into your PowerShell or Azure Cloud Shell terminal. This command will download and execute a setup script, which can be viewed here).

The script will require some user input, including the admin username & password for the SQL Server of the targeted database. These credentials will be stored in Azure Key Vault and will never be directly read by GorillaStack. The script will create the following resources in the targeted subscription (some will be reused if this action is set up for multiple databases):

Function (one per targeted SQL Database)
Function App (one per targeted SQL Server)
Resource Group (one per subscription)
Storage Account (one per subscription)
Key Vault (one per subscription)

After the script has run, all created resources will belong to a resource group named gorillastack-sql-db-lifecycle.

Function Setup Troubleshooting

When using Azure Cloud Shell, stale sessions can cause unexpected errors. Starting a new session will often resolve these issues.

When a Function App is first initialized (this will be each time the script is run for a new SQL Server) Functions may respond with timeout errors if called immediately. It is advisable to wait an hour or so after setup before running you new rule.

Action Options

Wait for Import

This option effects how the action behaves when it is used in a sequence of actions. When enabled, the import operation is performed synchronously, and the next action in the sequence will not be executed unless the import operation successfully completes. When disabled, the import operation is performed asynchronously, and the next action in the sequence will be executed immediately without regard for the status of the import operation.

Permissions

When you link GorillaStack to your Azure account, GorillaStack creates a service principal that provides access to the following permissions that are used by this action:

Microsoft.Sql/servers/databases/read
Microsoft.Sql/servers/read
Microsoft.Storage/storageAccounts/blobServices/*
Microsoft.Storage/storageAccounts/listkeys/action
Microsoft.Web/sites/functions/listkeys/action
Microsoft.Web/sites/functions/read

Getting Started

Link your Azure account to your GorillaStack account if you have not done so already
Create a database with the SKU name of DataWarehouse using the Azure Synapse Analytics service
Apply tags to the database so it can be targeted in GorillaStack
Create a new rule in GorillaStack
In the rule’s context settings, select Azure as the platform, and then select one or more linked Azure Subscriptions to target
In the rule’s trigger settings, select any available trigger — Schedule and Manual Execution are the most popular
In the rule’s actions settings, select Restore Azure SQL Databases as the action
In this action’s Targeting panel, select the intended database to restore
If you have not done so for the targeted database, run the Function Setup command in the targeted Azure subscription