How to import a sample bacpac file to an Azure SQL Database September 22, 2016 by Daniel Calbimonte 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 In Azure Portal, create a storage account. Using MASE, create container and add the WideWorldImporters.pac file. 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. About Latest Posts Daniel CalbimonteDaniel 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 is also a writer for SQL Server training material for certification exams.View all posts by Daniel Calbimonte Latest posts by Daniel Calbimonte (see all) How to work with SQL random numbers in SSIS - February 7, 2019 Step by step installation of SQL Server 2017 - May 23, 2018 FAQ and examples about the SQL Server Agent - May 11, 2018 Related posts: How to import a sample bacpac file to an Azure SQL Database using sqlpackage and PowerShell How to use BULK INSERT to import data locally and in Azure Working with table, blob, queues and file storage in Azure How to import a flat file into a SQL Server database using the Import Flat File wizard How to connect to the Azure Storage Account with SQL Server Management Studio (SSMS)