Using a Bacpac file to construct the Wide World Importers Database

In my previous post I showed you how easy it is to bring the Wide World Importers (WWI) database into a named instance of SQL Server 2016 SP1 on your computer(desktop / laptop).

In this post I describe how you may construct the database on the named instance using a BacPac file.
If you have not used this kind of file please review the following link here.

In what follows I describe using the .bacpac file to construct the WWI database
Go here:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

Get this bacpac file:
Azure SQL Database Standard tier

    WideWorldImporters-Standard.bacpac - standard edition OLTP sample database in bacpac format. For Azure SQL Databases in the Standard tier.

Download the 58.4 MB file from the above site.


SQL Server 2016 SP1 like the other versions has a wizard to bring the database using a .bacpac file such the one you downloaded.

You invoke the wizard as shown in the next image and follow through.

bakpacWWI_01.png

Click Import Data-tier Applicaiton... to open the window shown in the next image.  Read the steps in this introductory page.

bakpacWWI_02.png

 Click Next. You click the Browse... button to locate the  indicated file. This is the location to which I saved the WideWorldImporters-Standard.bacpac file. 
It does not matter it is for Azure SQL Database.

bakpacWWI_03.png

After getting the file location from the location as shown in the next image proceed to next step.

bakpacWWI_04.png


After getting the file location in the wizard's interface (the image one above the previous) click Next.

bakpacWWI_05.png

 Just verify that the location for files are what you want. Herein defaults are accepted. You could browse and park elsewhere.

The database name was changed to WWI_Bacpac.
 
Click Next. The Summary of processing is presented as shown.

bakpacWWI_06.png


Click Next. The next four images shows the progress captured in images. Indexes are dropped and created, etc.

 bakpacWWI_07.png

 bakpacWWI_08.png

 bakpacWWI_09.png

bakpacWWI_10.png

Object explorer refreshed - last processing step.

The WWI database is imported successfully.

Here is the final verification of the import into the named instance.

 

bakpacWWI_10.png

Exporting a Data-tier application from SQL Server using SSMS has a problem

According to MSDN (https://msdn.microsoft.com/en-us/library/hh213241.aspx)  it is quite simple to Export a data-tier application. Here is an overview of exporting a database as a Data-tier .bacpac file which can be deployed to another server.

"Exporting a deployed data-tier application (DAC) or database creates an export file that includes both the definitions of the objects in the database and all of the data contained in the tables. The export file can then be imported to another instance of the Database Engine, or to Azure SQL Database. The export-import operations can be combined to migrate a DAC between instances, to create an archive, or to create an on-premise copy of a database deployed in SQL Database.
"
The steps are very clear as the Export data-tier application task brings up a wizard and you just have to follow the steps.
The following screen shots show the process for exporting from a SQL Server 2012 instance.

Initiating the Export Data-Tier Wizard. Click Export Data-tier Application...

Wizard shows up.

Click Next.

In the above you can save it to a local disk (Folder/File) or save to Microsoft Azure. Here the default is accepted.

The Next is greyed as there was an earlier file. However the database had changes and a new one was created with a different name.

Data in the tables will also be exported.

Click Finish. The Wizard starts processing.

The result of processing is that the operation did not succeed.

The reason is in the error shown below.

Looks like some implied incompatibility with some Microsoft Azure SQL database. It may be noted that the target file was supposed to be saved to the local disk and not Microsoft Azure.

This happens in SQL Server 2014 as well.

According to a recent MSDN forum post, the failure in SQL Server 2012 and SQL Server 2014 for this particular case of Northwind database is perhaps related its

Read these popular blog posts related to data-tier applications:
http://hodentekmsss.blogspot.com/2010/07/sql-server-2008-r2-and-data-tier.html
http://hodentekmsss.blogspot.com/2013/12/deploying-dac-to-sql-server-2014.html