Import database when policy “Azure SQL DB should avoid using GRS backup” is enforced

This article is contributed. See the original author and article here.

Symptoms:

Creating or importing database failed due to violation of policy “Azure SQL DB should avoid using GRS backup”

When creating database, you should specify the backup storage to be used, while the default is GRS (Globally redundant storage) and it violates the policy.

While importing database either using Azure Portal, RestAPI, SSMS or SQLPackage you cannot specify the backup storage as it’s not implemented yet.

This cause the import operation to fail with the following exception:

Configuring backup storage account type to ‘Standard_RAGRS’ failed during Database create or update.

 

Solution:

You can either

• create the database with local backup storage and then import to existing database.


• Use ARM Template to do both create the database with local backup storage and import the bacpac file.

 

Examples:

Option (A)

1. Create the database with Azure Portal or by using T-SQL

CREATE DATABASE ImportedDB WITH BACKUP_STORAGE_REDUNDANCY = ‘ZONE’;

2. Import the database using SSMS or SQLPackage

Option (B)

 

You may use ARM template to create database with specific backup storage and import the database at the same time.

This can be done with ARM template by using the extension named “import”

Add the following Json to your ARM template to your database resource section and make sure you provide the values for the parameters or set the values hardcoded in the template

The needed information is:

Storage Account key to access the bacpac file

Bacpac file URL

Azure SQL Server Admin Username

Azure SQL Server Admin Password

 

 

 

 

 

"resources": [
                {
                    "type": "extensions",
                    "apiVersion": "2014-04-01",
                    "name": "Import",
                    "dependsOn": [
                        "[resourceId('Microsoft.Sql/servers/databases', parameters('ServerName'), parameters('databaseName'))]"
                    ],
                    "properties": {
                        "storageKeyType": "StorageAccessKey",
                        "storageKey": "[parameters('storageAccountKey')]",
                        "storageUri": "[parameters('bacpacUrl')]",
                        "administratorLogin": "[parameters('adminUser')]",
                        "administratorLoginPassword": "[parameters('adminPassword')]",
                        "operationMode": "Import"
                    }
                }
            ]

 

 

 

 

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.