search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.55k stars 898 forks source link

Support AzureSQL Database Tier for *.bacpac import #22049

Open caroysMSFT opened 1 year ago

caroysMSFT commented 1 year ago

Is your feature request related to a problem? Please describe. The Data Tier Application Wizard does not give you the ability to specify the settings for the restored database.

When you have certain types of features present in the backup, it will fail to restore with the general purpose edition used as the default. I just saw this limitation with a customer when In-Memory tables were captured in the backup:

https://learn.microsoft.com/en-us/azure/azure-sql/in-memory-oltp-overview?view=azuresql#benefits-of-in-memory-technology

Their import fails with error:

Import bacpac: Could not import package.Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 40536, Level 16, State 2, Line 1 'MEMORY_OPTIMIZED tables' is not supported in this service tier of the database.

Describe the solution or feature you'd like

Provide a setting for Service Tier during import.

Describe alternatives you've considered

I was able to work around this with a simple database by:

  1. Start the *.bacpac import wizard
  2. Select the *.bacpac and give it the name of a database which doesn't exist, but don't click "next"
  3. Create the database with the appropriate tier setting
  4. After the database is created, click "next" to continue the wizard

(This fails on more complex databases so is not a perfect workaround)

I've also advised working around this in powershell or AZ CLI, which exposes these options.

https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqldatabaseimport?view=azps-9.4.0#-edition https://learn.microsoft.com/en-us/cli/azure/sql/db?view=azure-cli-latest#az-sql-db-import

ahumeniy commented 1 year ago

Adding to this, it should give a warning about this. Creating the database with default settings which may result in increased costs for testing databases and lower performance for production scenarios if the user is not aware of this and don't change the settings in the Azure portal/CLI afterwards.