Blog Post

Azure SQL Blog
6 MIN READ

Import Export using Private Link now in Preview

Sudhir_Raparla's avatar
Mar 16, 2022

Import-Export overview

With Azure SQL Import-Export users can import a SQL Server database into Azure SQL Database using a BACPAC file or export from an Azure SQL Database into a BACPAC file. BACPAC files can be stored in Azure Blob storage(standard storage only) or local storage in an on-premises location. Importing a database into Azure SQL DB using Import-Export creates the database and imports schema and data into the database from the BACPAC file provided.  Here is an overview of Import Export works:

                    

 

To successfully run Import Export operations in Azure SQL Database, users must set “Allow Access to Azure Services” parameter under Firewall settings to ON. Otherwise, the Import Export operations fail with errors. But in many scenarios users would not want to give such a broad privilege for Import/Export operations and require a more precise and controlled way to perform the operation.

 

Import-Export Private Link

To overcome this problem we are introducing a new option to perform Import Export using Private Link(in Preview). With this option users can perform Import or Export via a Private end point, without allowing all Azure services access to SQL Server. Private Link in Azure SQL Database allows you to connect to various PaaS services in Azure via a private endpoint. For Import/Export a Service Managed Private Endpoint is created and managed by Microsoft and is exclusively used by the Import-Export service for a given import or export operation. Since Import-Export service interacts with SQL Server and Storage, two end points will be created by this feature one for SQL Server and another for Storage account. Here is an overview of how Private link works:

 

Once the service managed private endpoints are approved by user, all communication between import/export, user database and storage will happen over the service managed private link. Note that, user must manually approve the service managed private endpoints via Azure Portal (for both SQL Server & Storage) before they can start using it. The Import-Export operation will wait until the endpoints are approved by the user.

 

Configuring Import-Export Private Link

Import-Export Private Link can be configured via Azure portal, PowerShell or using REST API. Here is a step by step guide to configure Private link in Portal and PowerShell

 

Configure Import-Export Private link using Azure portal

Here are detailed instructions on how to enable Private link for Import and Export operations

 

Create Private Link for Import

  1. Go to server into which you would like to import database. Select Import database from toolbar in Overview page.
  2. In Import Database page, select Use Private Link option 
  3. Enter the storage account, server credentials, Database details and select on Ok
  4. This step will create two Private End points: One for SQL Server and another for Storage.

 

 

Create Private Link for Export

  1. Go to the database that you would like to export. Select Export database from toolbar in Overview page
  2. In Export Database page, select Use Private Link option 
  3. Enter the storage account, server sign-in credentials, Database details and select Ok
  4. This step will create two Private End points: One for SQL Server and another for Storage.

 

 

Approve Private End Points

Two endpoints one SQL Server and another Storage will be created. Users can either approve both of them in Private Link Center or approve them individually in Private end point section of respective services.

 

Approve Private Endpoints in Private Link Center
  1. Go to Private Link Center
  2. Navigate to Private endpoints section
  3. Approve the private endpoints you created using Import/Export service

If for some reason user cannot approve the endpoints in Private Link Center, they can approve them in SQL Server and Storage by following instructions below.

 

Approve Private End Point connection on Azure SQL Database
  1. Go to the server that hosts the database.
  2. Open the ‘Private endpoint connections’ page in security section on the left.
  3. Select the private endpoint you want to approve.
  4. Select Approve to approve the connection.

 

 
Approve Private End Point connection on Azure Storage
  1. Go to the storage account that hosts the BACPAC file.
  2. Open the ‘Private endpoint connections’ page in security section on the left.
  3. Select the Import-Export private endpoints you want to approve.
  4. Select Approve to approve the connection.

 

After the Private End points are approved both in Azure SQL Server and Storage account, Import or Export jobs will be kicked off. Until then, the jobs will be on hold.

You can check the status of Import or Export jobs in Import-Export History page under Data Management section in Azure SQL Server page. 

 

Configure Import-Export Private Link using PowerShell

Below are instructions and a sample script to configure Import-Export Private link using PowerShell

 

Import a Database using Private link in PowerShell

Use the New-AzSqlDatabaseImport cmdlet to submit an import database request to Azure. Depending on database size, the import may take some time to complete. The DTU based provisioning model supports select database max size values for each tier. When importing a database use one of these supported values. Here is a sample PowerShell import command:

 

 

$importRequest = New-AzSqlDatabaseImport -ResourceGroupName "<resourceGroupName>" `        -ServerName "<serverName>" -DatabaseName "<databaseName>" `        -DatabaseMaxSizeBytes "<databaseSizeInBytes>" -StorageKeyType "StorageAccessKey" `         -StorageKey $(Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName `                        -StorageAccountName "<storageAccountName>").Value[0]         -StorageUri "https://myStorageAccount.blob.core.windows.net/importsample/sample.bacpac" `        -Edition "Standard" -ServiceObjectiveName "P6" ` -UseNetworkIsolation $true `        -StorageAccountResourceIdForPrivateLink "/subscriptions/<subscriptionId>/resourcegroups/<resource_group_name>/providers/Microsoft.Storage/storageAccounts/<storage_account_name>" `                        -SqlServerResourceIdForPrivateLink "/subscriptions/<subscriptionId>/resourceGroups/<resource_group_name>/providers/Microsoft.Sql/servers/<server_name>" `        -AdministratorLogin "<userID>" `        -AdministratorLoginPassword $(ConvertTo-SecureString -String "<password>" -AsPlainText -Force) 

 

 

 

Export a Database using Private Link in PowerShell

Use the New-AzSqlDatabaseExport cmdlet to submit an export database request to the Azure SQL Database service. Depending on the size of your database, the export operation may take some time to complete. Here is a sample PowerShell export command:

 

 

$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName "<resourceGroupName>" `        -ServerName "<serverName>" -DatabaseName "<databaseName>" `        -DatabaseMaxSizeBytes "<databaseSizeInBytes>" -StorageKeyType "StorageAccessKey" `         -StorageKey $(Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName `                        -StorageAccountName "<storageAccountName>").Value[0]         -StorageUri "https://myStorageAccount.blob.core.windows.net/importsample/sample.bacpac" `        -Edition "Standard" -ServiceObjectiveName "P6" ` -UseNetworkIsolation $true `        -StorageAccountResourceIdForPrivateLink "/subscriptions/<subscriptionId>/resourcegroups/<resource_group_name>/providers/Microsoft.Storage/storageAccounts/<storage_account_name>" `                        -SqlServerResourceIdForPrivateLink "/subscriptions/<subscriptionId>/resourceGroups/<resource_group_name>/providers/Microsoft.Sql/servers/<server_name>" `        -AdministratorLogin "<userID>" `        -AdministratorLoginPassword $(ConvertTo-SecureString -String "<password>" -AsPlainText -Force)

 

 

 

Here is a sample end to end PowerShell script to perform Export operation using Private Link:

 

 

$storageResourceForPrivateLink = "/subscriptions/<<Subscription ID>>/resourcegroups/<<Resource Group >>/providers/Microsoft.Storage/storageAccounts/<<Storage Account>>" 

$sqlResourceForPrivateLink = "/subscriptions/<<Subscription ID>>/resourcegroups/<<Resource Group >>/providers/Microsoft.Sql/servers/<<SQL Server>>"

$storageKey = (Get-AzureStorageKey -StorageAccountName "<<Storage Account>>").Primary

$storageURI = "https://<<Storage Account>>/dbexportsour/SampleExportFile_PS4.bacpac"

$adminPWD = $(ConvertTo-SecureString -String "<<SQLAdminPassword>>" -AsPlainText -Force) 
$adminLogin = "<<SQL Admin Account>>"

$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName "<<Resource Group >>" -ServerName "<<SQL Server>>" -DatabaseName "<<Database Name>>" -StorageKeyType "StorageAccessKey" -StorageKey $storageKey `
    -StorageUri $storageURI -UseNetworkIsolation $true -StorageAccountResourceIdForPrivateLink $storageResourceForPrivateLink -SqlServerResourceIdForPrivateLink $sqlResourceForPrivateLink `
    -AdministratorLogin $adminLogin -AdministratorLoginPassword $adminPWD

Start-Sleep -Seconds 30

## Approve the Private Endpoint Connection for the Storage Account
$storagePrivate_Endpoint_Connection_Resource = Get-AzPrivateEndpointConnection -PrivateLinkResourceId $storageResourceForPrivateLink | `
        Where-Object {($_.PrivateEndpoint.Id -like "*ImportExportPrivateLink_Storage*" -and $_.PrivateLinkServiceConnectionState.status -eq "Pending")}
Approve-AzPrivateEndpointConnection -ResourceId $storagePrivate_Endpoint_Connection_Resource.Id


## Approve the Private Endpoint Connection for the SQL Account

$sqlPrivate_Endpoint_Connection_Resource = Get-AzPrivateEndpointConnection -PrivateLinkResourceId $sqlResourceForPrivateLink | `
                    Where-Object {($_.PrivateEndpoint.Id -like "*ImportExportPrivateLink_SQL*" -and $_.PrivateLinkServiceConnectionState.status -eq "Pending")}
Approve-AzPrivateEndpointConnection -ResourceId $sqlPrivate_Endpoint_Connection_Resource.Id

Get-AzSqlDatabaseImportExportStatus  -OperationStatusLink $exportRequest.OperationStatusLink | Where-Object {($_.Status -eq "InProgress")}

 

 

 

Limitations

  1. Import using Private Link does not support specifying a backup storage redundancy while creating a new database and creates with the default geo-redundant backup storage redundancy. As a work around, first create an empty database with desired backup storage redundancy using Azure portal or PowerShell and then import the BACPAC into this empty database.
  2. Import and Export operations are not supported in Azure SQL DB Hyperscale tier yet.
  3. Import using REST API with private link can only be done to existing database since the API uses database extensions. To workaround this create an empty database with desired name and call Import REST API with Private link.

More information about the feature can be found in Import-Export Private Link documentation

Updated Oct 25, 2022
Version 2.0
  • zlovcevic's avatar
    zlovcevic
    Copper Contributor

    Sudhir_Raparla If an export that is using this feature is already running and I try to create another one the private endpoints for storage and database do not appear in Private Link Center until the running export is completed. It's like the private endpoints get queued somewhere in the background and we can't access them.

  • zlovcevic - You should be able to run Import/Export operations concurrently on multiple databases using Private end points even today. What error do you see?

  • zlovcevic's avatar
    zlovcevic
    Copper Contributor

    Sudhir_Raparla you have a bug in a sample PS script, the $storageResourceForPrivateLink has resourcegroups instead of resourceGroups. I figured out this the hard way and I'm still confused why are these things case sensitive. Another thing that I found out is that I can't have more then one concurrent export using private links because it can't create private endpoints while existing export for some other database is still running. I hope that this is because the feature is in preview but it is really a pain when I need to run this for more than 1 database. If there is a way to run multiple exports with private endpoints on different databases please let me know. Thanks in advance

  • stanpilcher's avatar
    stanpilcher
    Copper Contributor

    I  may be in the wrong place but i have a question about disk access and setting the Network Connectivity to private endpoint. If I understand correctly i need to create a disk access "resource" and then within that resource I need to add all of the private endpoint connections i wish to utilize?

     

  • zlovcevic's avatar
    zlovcevic
    Copper Contributor
    DEBUG: ============================ HTTP REQUEST ============================
    
    HTTP Method:
    POST
    
    Absolute Uri:
    https://management.azure.com/subscriptions/xxxxxxxxxxxxxxxx/resourceGroups/xxxxxxxxxx/providers/Microsoft.Sql/servers/xxxxxxxxxx/databases/xxxxxxxxxxx/export?api-version=2021-02-01-preview
    
    Headers:
    x-ms-client-request-id        : 8d7a39f8-1bd2-4a7d-9c9d-2d17577588a2
    accept-language               : en-US
    
    Body:
    {
      "storageKeyType": "StorageAccessKey",
      "storageKey": "xxxxxxxxxxxxxxxxxxxxx",
      "storageUri": "https://xxxxxxxxxxxxxx.blob.core.windows.net/prod-general-storage/xxxxxxxxxxxxx-2022-04-06-10-12.bacpac",
      "administratorLogin": "xxxxxxxxxx",
      "administratorLoginPassword": "xxxxxxxxxxxxxxxxxx",
      "networkIsolation": {
        "storageAccountResourceId":
    "/subscriptions/xxxxxxxxxxxxxxx/resourcegroups/xxxxxxxxxxxxxx/providers/Microsoft.Storage/storageAccounts/xxxxxxxxxxxxxx",
        "sqlServerResourceId":
    "/subscriptions/xxxxxxxxxxxxxxx/resourceGroups/xxxxxxxxxxxxxxx/providers/Microsoft.Sql/servers/xxxxxxxxxxxxxx"
      }
    }
    DEBUG: ============================ HTTP RESPONSE ============================
    
    Status Code:
    BadRequest
    
    Headers:
    Pragma                        : no-cache
    x-ms-request-id               : db664f13-c094-443a-983a-15f5d7549ff8
    x-ms-ratelimit-remaining-subscription-writes: 1199
    x-ms-correlation-request-id   : 59c3c546-c567-4b16-a34a-f3866e816436
    x-ms-routing-request-id       : NORTHEUROPE:20220406T082415Z:59c3c546-c567-4b16-a34a-f3866e816436
    Strict-Transport-Security     : max-age=31536000; includeSubDomains
    X-Content-Type-Options        : nosniff
    Cache-Control                 : no-cache
    Date                          : Wed, 06 Apr 2022 08:24:15 GMT
    Server                        : Microsoft-HTTPAPI/2.0
    
    Body:
    {
      "error": {
        "code": "InvalidImportExportParameter",
        "message": "The ImportExport input parameter
    /subscriptions/xxxxxxxxxxxxxxxxx/resourcegroups/xxxxxxxxxxxxxxx/providers/Microsoft.Storage/storageAccounts/xxxxxxxxxxxxxx is
    invalid. The Storage Account resource ID used for network isolation should be formatted like:
    '/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/Default-SQL-SouthEastAsia/providers/Microsoft.Storage/storageAccounts/test-privatelink'."
      }
    }
  • zlovcevic's avatar
    zlovcevic
    Copper Contributor

    I followed instructions from this post and it doesn't work for me. When I run the New-AzSqlDatabaseExport command in debug mode I get the following:

    DEBUG: ============================ HTTP RESPONSE ============================

    Status Code:
    BadRequest

    Headers:
    Pragma : no-cache
    x-ms-request-id : db664f13-c094-443a-983a-15f5d7549ff8
    x-ms-ratelimit-remaining-subscription-writes: 1199
    x-ms-correlation-request-id : 59c3c546-c567-4b16-a34a-f3866e816436
    x-ms-routing-request-id : NORTHEUROPE:20220406T082415Z:59c3c546-c567-4b16-a34a-f3866e816436
    Strict-Transport-Security : max-age=31536000; includeSubDomains
    X-Content-Type-Options : nosniff
    Cache-Control : no-cache
    Date : Wed, 06 Apr 2022 08:24:15 GMT
    Server : Microsoft-HTTPAPI/2.0

    Body:
    {
    "error": {
    "code": "InvalidImportExportParameter",
    "message": "The ImportExport input parameter
    /subscriptions/xxxxxxxxxxxxx/resourcegroups/ism-prod-backend-uks/providers/Microsoft.Storage/storageAccounts/ismprodbackenduks is
    invalid. The Storage Account resource ID used for network isolation should be formatted like:
    '/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/Default-SQL-SouthEastAsia/providers/Microsoft.Storage/storageAccounts/test-privatelink'."
    }
    }

    When I run Export with private link from Azure Portal it works fine.