How to import a sample bacpac file to Azure SQL Database

Introduction

In SQL Server 2000, the sample databases were pubs and northwind. They contained sample information to learn SQL Server.

Starting in SQL Server 2005, the Adventureworks was the new Sample Database. Each SQL version (2005, 2008, 2012, 2014 and now 2016) included new samples included to show new features. In Adventureworks there were different adventurework versions:

OLTP version (Adventureworks for transactional databases)
Datawarehouse (AdventureworksDW for business intelligence including SSAS)
Lite version (AdventureworksLT, a light transactional database)

In SQL Server 2016, a new sample database came:

WideWorldImporters, we created an article about this new database.

WideWorldImporters contains a real life database following design recommendations.

This time we will talk about WideWorldImporters for Azure. This Database is a new database to work with Azure. By default, you can install the AdventureLT Database, however, it is now possible to import the WideWorldImporters to Azure.

We will explain how to import WideWorldImporters, but the instructions are valid for any bacpac file.

A bacpac file is the extension to deploy Data Tier Application packages.

Requirements

SSMS in a local machine or Virtual machine.
An Azure Account to the Azure Portal.
MASE (Microsoft Azure Storage Explorer)

Getting started

To import the WideWorldImporters database, we will explain two methods:

Importing using the Portal and MASE
1. In Azure Portal, create a storage account.
2. Using MASE, create container and add the WideWorldImporters.pac file.
3. In Azure Portal, import the file.
Another method is using SQL Server Management Studio (SSMS). This option is great if you have SSMS already installed.

Method 1. Importing a database using Azure Portal and MASE

This option is recommended if you do not have a local machine with SSMS. You only need MASE (which can be installed in a MAC, Windows and Linux machine).

First, in Azure Portal, go to New>Data+Storage>Storage Account and create a storage account. Storage account is used to store files, messages, etc. For more information about storage account, please refer to our article about MASE.

Figure 1. Creating storage accounts

In MASE, connect to the storage account in figure 1 and create a container named mybackups or any name of your preference:

Figure 2. An Azure Container

Download the Azure WideWorldImporters-Standard.bacpac file.

In MASE, Upload the WideWorldImporters-Standard.bacpac just downloaded in the container created in figure 2:

Figure 3. Uploading the pac file.

In Azure Portal, go to more Services ➜ SQL Servers

Figure 4. Creating Azure SQL Servers

Press Add to create a new SQL Server. Add a name, password and a group in Azure to create a new SQL Server:

Figure 5. Azure SQL Server Information

Select the database created and click Import Databases:

Figure 6. Importing databases

Select your subscription (this is important when you have multiple subscriptions):

Figure 7. Selecting subscription

Select the storage account created in Figure 1 and the container created in figure 2:

Figure 8. Selecting Storage account and container

Select the file WideWorldImporters-Standard.bacpac uploaded on step 3:

Figure 9. Selecting the bacpac file

Specify a database name, login and password for the database and press OK:

Figure 10. Database information

To verify in your local SSMS, in Azure Portal, select the SQL Server and go to properties. Add the current client IP and Save:

Figure 11. IP Enabled

In your local machine, you can connect to the Azure database already created:

Figure 12. Connection information

If everything is fine, you will be able to access to the WideWorldImporters-Standard database:

Figure 13. WideWorldImporters database

Method 2. Importing using SSMS

This method is recommended if you have SSMS installed in a local machine. You do not need MASE if you import from a local file.

Another way to do import the file in Azure is in SSMS using the Import Data-tier Application. In you Azure Machine, right click and select this option:

Figure 14. SSMS option to import data in Azure

A wizard will be displayed:

Figure 15. Import Data-tier wizard

You can download the bacpac file to your machine with SSMS and import from the local file:

Figure 16. Importing from a local disk

Another option is to import from an Azure Account. To do it, you need to connect the Azure Account by pressing connect:

Figure 17. Connecting to Azure Storage Account

We will connect to Azure. In the MASE, you can find the storage account name created in figure 1 and the primary key used to connect to the Azure Storage Account:

Figure 18. Azure Account information

Use in Storage account the name of Figure 18 and the primary key as the account name:

Figure 19. Storage Account information

Select the container and file name of figure 2 and 3:

Figure 20. Selecting Azure Container and file

In settings, you can specify the database name, the edition. Basic is the cheapest option and standard is the option by default. The most expensive option is premium.

Figure 21. Azure SQL Database settings

The summary will show all the options selected in the wizard:

Figure 22. Wizard summary

The new WideWorldImporters cannot be imported in SSMS 2014. When I try to import from my SQL Server 2014 machine. I receive the error message that the model version ‘2.9’ is not supported:

Figure 23. Error when the bacpac file is not compatible with the SSMS version

In SQL Server 2016, you will not receive errors, unless your internet connection is slow. I received the following error message when trying to import the data in a machine with slow internet connection:

Figure 24. Error when internet connection is slow

The error message says that a connection was successfully established with the server, but then an error occurred during the pre-login handshake. If you have this error, we recommend using the first method to import the database to Azure.

If your internet connection is fine and you have SSMS 2016 or later, everything should be fine:

Figure 25. The database created

Conclusion

In this article, we explained how to import a database to Azure. The first option is using the Portal and MASE. This option is recommended. The second option requires SSMS 2016 or later and it ofte fails with clients that have slow internet connections.

Author
Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

SQL interview questions - September 3, 2020
SQL Certifications - August 7, 2020
TSQL history - September 3, 2019