logo
slack-logoJoin Our Slack Community

Export Azure SQL Databases

Overview

The Export Azure SQL Databases Action is used to export a database as a .bacpac file and store it in Azure Blob storage.

Use of this action ensures you have reliable and portable backup file that can be used to restore your database to another Azure SQL instance or to another cloud platform therefore supporting data recovery, data migration and test & development purposes.

Use of this action in a rule

This action is ideal for use is a sequence with the Delete Azure SQL Databases action. When used in this way, enabling the Wait for Export option is necessary to ensure the export operation successfully completes prior to executing the delete action.

Important to Note

The export 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.

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".

Azure SQL Server allow service access

Function Setup

The Azure Function created will handle both import and export operations. As such, if it is 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).

Export SQL Databases: Function Setup

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. Azure Cloud Shell: New session

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 Export

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

Maximum wait time

When Wait for Export is enabled, a maximum wait time can also be configured. If this time period (specified in hours) is reached and the export operation is not reported as complete, the export operation will be considered unsuccessful.

Retain all exports

When this option is disabled, only the most recent .bacpac file for a database will be stored. On a successful execution of the export action, all old .bacpac files will be deleted. When the option is enabled, all .bacpac files will be stored indefinitely. If Wait for Export is disabled, this option must be enabled

Export SQL Databases: Options

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

  1. Link your Azure account to your GorillaStack account if you have not done so already
  2. Create a database with the SKU name of DataWarehouse using the Azure Synapse Analytics service
  3. Apply tags to the database so it can be targeted in GorillaStack
  4. Create a new rule in GorillaStack
  5. In the rule’s context settings, select Azure as the platform, and then select one or more linked Azure Subscriptions to target
  6. In the rule’s trigger settings, select any available trigger — Schedule and Manual Execution are the most popular
  7. In the rule’s actions settings, select Export Azure SQL Databases as the action
  8. In this action’s Targeting panel, select the intended database to export
  9. If you have not done so for the targeted database, run the Function Setup command in the targeted Azure subscription
  10. In the Options panel, select the desired options for this rule ()