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

SQL Server 2008 R2 and Data-Tier Applications

DATPACs
In the August CTP Microsoft introduced Data-Tier Applications and several new features were introduced in the Nov 2009 CTP. Registering; Viewing & comparing and upgrading Data-Tier applications were added.

A Data Application Component is an entity that integrates all data tier related objects used in authoring, deploying and managing into a single unit instead of working with them separately. Programmatically DACs belong to classes that are found in The Microsoft.SqlServer.Management.Dac namespace. DACs are stored in a DacStore and managed centrally. DACs can be authored and built using SQL Server Data-Tier Application templates in VS2010 (now in Beta 2) or using SQL Server Management Studio. This article describes creating DAC using SQL Server 2008 R2 Nov-CTP(R2 server in this article), a new feature in this version.

The following two articles treat DATPACs from two different angles. The first part is what you or a DBA can do in SQL Server Management Studio and in the second what a developer can do in Visual Studio.

Using SQL Server Management Studio
In the first part of this series [http://www.packtpub.com/article/working-with-data-application-components-sql-server-2008-r2] the author shows shows how you may extract a Data Application Component from a SQL Server 2008 and then deploy the component to an SQL Server 2008 R2 Server. The process is Wizard driven and very easy to understand. DAC files in the field of DATA are like *.msi files for applications. Just as you would install an application from an MSI file, you would install a database package with the DAC file with the extension DACPAC. You may even email a database. The cited article shows how all this is done.




Using Visual Studio
In  the second part of this series[http://www.sswug.org/articles/viewarticle.aspx?id=49844] the author shows how you may create a DATPAC from scratch in your Visual Studio IDE. You will start creating a DATPAC which has database with  a table and a view based on the table  even when not  connected to a server. You will then manually deploy the DATPAC to the server after connecting to it and complete the deployment.

Creating a data-tier application definition in SQL Server 2012

Data-tier application in SQL Server defines the schema and objects that are required to support an application.It is really very simple.

There are two ways you can implement a DAC:

• Using Microsoft Visual Studio 2010 with a SQL Server Data-tier application project type
• Using the extraction utility in SQL Server's Extract Data-tier wizard.

In Microsoft Visual Studio 2015 Community there are no templates to do this although you can import one, if it exists in the community samples.

Here is how you extract using the SQL Server Management Studio in SQL Server 2012.

Connect to SQL Server on your computer. Here is the Object Explorer of a named instance of SQL Server 2012.

Dac_01

We now create a DAC using the Northwind database.
Right click Northwind to open the menu and the sub-menu as shown.

Dac_02

Click on Extract Data-Tier Application... to display the Introduction screen of the Wizard.

Dac_03

The above screen pretty well describes the actions we take. It has the three steps:

• Set the DAC properties

• Review object dependency and validation results

• Build the DAC Package

Click Next to display the Set Properties page of the wizard. when you choose the databse the application name gets the database name by default and if it iss the first time the version is also set.

Dac_04

It appears that I have already created a file with that name and I will extract another with a different name NrthWnd.

Dac_05

Click Next. Displays the validation and summary page of the wizard.

Dac_07

Click Next. after a bit of creating and saving animation the process either succeeds or fails.

Dac_08

Click finish (after you get to see the Success of the operationb)  and the DAC  page is saved to the location indicated.

Dac_09

DAC files can be unpacked with programs shown.

 Dac_10
 

Don't miss this huge collection of SQL Server related articles

These were mostly reported during the time SQL Server 2008 was the recent version. The migration related articles and front end related articles basically used SQL Server 2008.

 

  

Creating a matrix report using the Analysis Services Cube 

 In this article by Dr. Jayaram Krishnaswamy, we will be authoring a report based on an analysis services CUBE. Reviewing Jayaram's other OLAP related articles may greatly help in understanding this article. Creating the CUBE will be essential to work with this...

  

Manage SQL Azure Databases with the Web Interface 'Houston'

 

version of SQL Server 2008 is R2. This article by Jayaram Krishnaswamy , is based on a project named 'Houston' which is a web based SQL Azure management tool that has not gone into production but can be tested using the SQL Azure Labs portal at http://www.SQLAzureLabs.com . In this article we look at some of the features of this web...

  

Working with Data Application Components in SQL Server 2008 R2

 

Jayaram Krishnaswamy In the August CTP Microsoft introduced Data-Tier Applications and several new features were introduced in the Nov 2009 CTP. Registering, Viewing & comparing and upgrading Data-Tier applications were added. This article by Dr. Jayaram...

Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

 

Jayaram Krishnaswamy As noted in Part 1 , OLAP presents Business Intelligence via what is known as a CUBE. A Cube has many dimensions and it provides a faster method to access the intelligence compared to the structured querying...

 

Creating a Web Page for Displaying Data from SQL Server 2008

Dr. Jay Krishnaswamy This article by Jayaram Krishnaswamy describes how you may connect to SQL Server 2008 and display the retrieved data in a GridView Control on a web page. Trying to establish a connection to the SQL Server 2008 is ...

 

Microsoft SQL Server 2008 - Installation Made Easy

Jayaram Krishnaswamy SQL 2008 server is the latest in the line of Microsoft database servers and this article by Dr. Jayaram Krishnaswamy discusses the challenges one may face in installing the Developer version of this product  ....

  

Microsoft Lightswitch Application using SQL Azure Database

on August 23rd for the general public while it has been used by MSDN members and Microsoft insiders for couple of months. This article by Jayaram Krishnaswamy shows how you may download and install this program. The article also shows how you may develop a simple database application using this product retrieving data from the Cloud hosted relational ....

  

MySQL Data Transfer using Sql Server Integration Services (SSIS)

 

Jayaram Krishnaswamy There are a large number of posts on various difficulties experienced while transferring data from MySQL using Microsoft SQL Server Integration Services. While the transfer of data from MySQL to Microsoft SQL Server...

  

MySQL Linked Server on SQL Server 2008

 

Jayaram Krishnaswamy Linking servers provides an elegant solution when you are faced with running queries against databases on distributed servers or looking at your distributed assets on disparate databases. This article by Dr. Jay Krishnaswamy explains how...
 

Migrating from MS SQL Server 2008 to EnterpriseDB

Dr. Jayaram Krishnaswamy In this article by Dr. Jayaram Krishnaswamy , we will learn about migration of data from MS SQL Server 2008 to EnterpriseDB. Migration Studio bundled with the EnterpriseDB download is a collection of tools to...

  

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

data. This two part article by Dr. Jayaram Krishnaswamy describes how a Cube is designed using Visual Studio 2008 and how it may be browsed on the Analysis Server. In Part 1, the necessary items for creating the Cube, namely the Data Source and Data Source Views are described.   Reviewing Jayaram's other OLAP...

  

Transferring Data from MS Access 2003 to SQL Server 2008

two non-Microsoft databases. Both Microsoft and proprietary data source providers are available to connect to many different database products. In this article by Dr. Jayaram Krishnaswamy , we will be transferring data from an MS Access database to a database on SQL Server 2008. Both the source of data and the destination database are on the same...

  

Creating a Simple Report with Visual Studio 2008

Jayaram Krishnaswamy Report Services, Analysis Services, and Integration Services are the three pillars of Business Intelligence in Microsoft's vision that continues to evolve. Reporting is a basic activity, albeit one of the...

  

Displaying SQL Server Data using a Linq Data Source

provided by the Linq to SQL classes, a class generator that maps SQL server objects to the model. The class files generated support CRUD operations. In this article by Dr. Jay Krishnaswamy , we will be adding a LinqDataSource control in Visual Studio 2008 to a ASP.NET website and configuring it by providing a data context. The data context will...

  

Overview of SQL Server Reporting Services 2012 Architecture, Features, and Tools

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook () () This article by Jayaram Krishnaswamy  the author of  Learning SQL Server Reporting Services 2012,  provides a summarized overview of SQL Server Reporting Services 2012 and the background information ...

  

Using the Data Pager Control in Visual Studio 2008

or it can be installed manually after installing the ListView. In this article both of them are described. While the number of items displayed in a list can be declaratively coded, it is possible to set it at page load time as well. This article by Dr. Jayaram Krishnaswamy describes how you may connect to SQL Server 2008 and display the retrieved...

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

Jayaram Krishnaswamy There are several options if one wishes to move a database from a SQL Server 2005 to SQL 2008 Server. First of all there is a 'Copy Database Wizard' in SQL 2008 Server which is meant for transferring...

  

Microsoft Lightswitch: Querying and Filtering Data

will not be wrong. Just like you take information out of the table, make changes to it and return it to the table, you do likewise with entity sets. In this article by Jayaram Krishnaswamy , author of Microsoft Visual Studio Lightswitch Business Application Development , we will take a look at querying a single entity...

 

SSIS Applications using SQL Azure

 

Report Server providing full support with a web service frontend for a variety of reporting needs—from web-based reporting to embedded reporting. In this article by Jayaram Krishnaswamy , author of Microsoft SQL Azure Enterprise Application Development , we will be leveraging SSIS, SSRS, and the tools used to address ETL processes, and Report...

  

  

Oracle SQL Developer Tool 1.5 with SQL Server 2005

 

party vendor products to Oracle. Please review the following articles on the earlier versions 1.1 and 1.2: MS Access Queries with Oracle SQL Developer 1.2 Tool and Migrating MS Access 2003 Data using the Oracle SQL Developer 1.2 . In the present article by Dr. Jayaram Krishnaswamy the latest version of this tool [Oracle SQL Developer 1...

  

Binding MS Chart Control to LINQ Data Source Control 

Jayaram Krishnaswamy In this article by Dr. Jay Krishnaswamy , a Microsoft Chart Control will be bound to a Linq Data Source using LinqDataSource control and a pie chart displays the data. We will be going through the following ...

Ground to SQL Azure migration using MS SQL Server Integration Services

Jayaram Krishnaswamy In this article by Dr. Jayaram Krishnaswamy , you will learn how to migrate a table from your ground based SQL Server 2008 to your cloud based SQL Azure instance using MS SQL Server Integration Services...

 

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

Jayaram Krishnaswamy This article by Jayaram Krishnaswamy shows how to migrate a database from SQL Server 2005 (should work for 2000 as well) to SQL Server 2008 using the Copy Database tool in SQL Server 2008. In an earlier article we saw how this can ...

  

Connecting to Microsoft SQL Server Compact 3.5 with Visual Studio

features 128-bit file level encryption. It is referential integrity compliant; supports multiple connections; has transactions support with rich data types. In this tutorial by Jayaram Krishnaswamy , various scenarios where you may need to connect to SQL Server Compact using Visual Studio IDE (both 2008 and 2010) are described in detail. Connecting ...

Authoring an EnterpriseDB report with Report Builder 2.0

Jayaram Krishnaswamy This article by Dr. Jayaram Krishnaswamy shows step-by-step how you may retrieve data from a database on a Postgres Plus server and display the results in a report generated using Report Builder 2.0. Report ... 

 

  

Term Extraction Tasks in SQL Server Integration Services 

stop the flow review it and turn it on to let the data flow to the next step. You may provide a name to the Data Viewer. There are four types of data viewers (Grid, Histogram, Scatter Plot, and Chart) and the 'Grid' type is used in this article. Build the Project and Execute the Package This is easy. Click on the menu item Build...  
 

 

Microsoft SQL Azure Tools 

 

the tools that can be leveraged working with SQL Azure, but a few third-party vendors and others have also created tools for SQL Azure. In this article by Jayaram Krishnaswamy , author of Microsoft SQL Azure Enterprise Application Development , we will discuss the Microsoft Tools.   Microsoft...

  

Data Access with ADO.NET Data Services 

 

operating on the data. In this article by Dr. Jayaram Krishnaswamy , you will learn how to create a ADO.NET Data Service from scratch. You will also learn how you may access data using the URI constructs. The backend data for this tutorial comes from a copy of Northwind database named TestNorthwind on a named instance of SQL Server 2008. In order...

  

Writing XML data to the File System with SSIS

Jayaram Krishnaswamy This article by Dr. Jayaram Krishnaswamy , shows how you may retrieve XML data from a relational database and write it to a folder on your file system as a text or xml file using Microsoft SQL Server Integration...

  

Working with the Report Builder in Microsoft SQL Server 2008: Part 1 

Jayaram Krishnaswamy Report Builder 2.0 is feature-rich reporting tool with the latest Microsoft Office look and feel. In this two part article by Jayaram Krishnaswamy , we will see how the Report Builder 2.0 provides an extremely ...

  

Programmatically Creating SSRS Report in Microsoft SQL Server 2008

 

Jayaram Krishnaswamy In this article by Dr. Jayaram Krishnaswamy , the process of programmatically creating the SQL Server Reporting Services (SSRS) tabular report is described. You will be creating a very simple report using the...

 

Working with the Report Builder in Microsoft SQL Server 2008: Part 2

 

Jayaram Krishnaswamy In the previous part of the article, we had a look at the Report Builder overview and described the Report Builder 2.0 interface . In this part by Jayaram Krishnaswamy , we will discuss about Enabling and reviewing ...

Importing a data-tier application to SQL Server 2016

You have seen how to export a data-tier application of a database in  SQL Server 2012. In fact, we exported one of the oldest of Microsoft's Sample Databases, the TestPubs. We also saw some limitations in this process of export due to incompatible data, although we have not fathomed as yet what type of data is incompatible.

When the process of export succeeds you create a .bacpac file which can be saved either to a local drive or to Microsoft Azure SQL Database. In our previous export we exported a TestPubs.bacpacfile from TestPubs database to the local drive.

In this post, you will see how you can import it into a higher version of SQL Server, SQL Server 2016.

Start up the named instance of SQL Server 2016. You can see that the server has no user database presently except the one we created recently (AdventureWorks2014).

ImportDT_07

Make a right click on the Databases node in SQL Server 2016 as shown.

ImportDT_00

Click on Import Data-tier Application... submenu to launch the import wizard's Introduction page as shown. Read the instructions on this page.

ImportDT_01

Click Next to display the Import Settings page. Click on the ellipsis button to browse the default page where the .bacpac files are stored as shown.

ImportDT_02

The file enters the processing by the wizard as shown.

ImportDT_03

Click Next to display the Database Settings page. The deployment is targeted to the SQL Server 2016 and .mdf and .ldf files are created. You can change the database name and herein it remains unchanged.

ImportDT_04

Click Next to display the Summary page as shown - Deployment details, file and target as shown.

ImportDT_05

Click Finish. There is little bit of processing activity as shown.

ImportDT_06

The operation has succeeded as shown.

ImportDT_08

The TestPubs database is now found in SQL Server 2016 (you may need to Refresh the Databases node).

ImportDT_09
The data has also entered the database as shown in one of the queries on the TestPubs in SQL Server 2016.

ImportDT_10

Well. It is quite simple and not a line of code is needed!

Deploying DAC to SQL Server 2014

The acronym DAC in SQL Server has many different meanings as in the following bullet list:

• DAC - Data-tier Application
• DAC - Dedicated Administrator Connection
• DAC - Data Access Components
• DAC - Data Application Component

This post describes deploying a Data-tier Application extracted from SQL Server 2012 Express database to SQL Server 2014 CTP1 (should work with CTP2 as well). The two servers are on two partitions on a x64 bit Windows laptop, with SQL Server 2012 on Windows 7 Partition and CTP1 on Windows 8 Preview partition.

There are no issues for this deployment and the next few images shows the stages of deployment wizard while deploying to SQL Server 2014 CTP1. We start with a .dac file created on Windows 7 partition and copied over to the desktop on Windows 8 partition.

 

The steps are very clear and you may also review the following for more detailed discussion of DAC with earlier versions on older OS.


https://www.packtpub.com/article/working-with-data-application-components-sql-server-2008-r2
http://hodentekmsss.blogspot.com/2010/07/sql-server-2008-r2-and-data-tier.html

December Update to SSDT

In December 2012 SSDT got updated. Here are some details:

If you are planning to use Visual Studio 2012 you are better off installing the December 2012 update. The December 2012 update to SSDT relates to what you have on your computer in terms of Visual Studio version.

1. If you already have Professional, Ultimate or Premium Edition of Visual Studio 2012 and agreed to install SSDT during installation then the machine will have an earlier version of SSDT. This update will replace the version with the latest.

2. If you do not have Visual Studio Professional or above SSDT will install the Visual Studio 2012 Integrated Shell. This Shell perform very much like the BIDS in the past and you will not be able to do programming as it neither include any Visual Studio programming languages such as VB or C# nor does it support the various types of projects that you can create with the non-shell, full versions of Visual Studio Professional and above. This December update updates the functionality of Express SKUs of Visual Studio 2012. The following are the improvements over the previous version:

Database Unit Testing

Integration of SSDT Power Tools

Updated Data-Tier Application Framework

Bug fixes

If Visual Studio Professional or above is present, SSDT will be integrated with the existing VS program. However SP1 must be manually installed before installing the update.

If VS Professional or above SSDT is not present SSDT installs only the Visual Studio 2010 Integrated Shell. SP1 must first be installed before installing the SSDT (However when the blogger used the web installer the SP1 was already in place). The shell as the name suggests contains only SSDT related items and does not include programming languages such as VB and C#.

Program installed in July 2012 using web installer

The following are the improvements over the previous version:

Database Unit Testing

Integration of SSDT Power Tools

Updated Data-Tier Application Framework

Bug fixes

The platforms that support the updated SSDT are:

Windows Vista SP2 or above

Windows 7 SP1 or above

Windows 8 RTM

Windows Server 2008 SP2 or above

Windows Server 2008 R2 SP1 or above

You can download SQL Server data tools for Visual Studio 2012 from this link:

http://msdn.microsoft.com/en-us/jj650015

Additionally you can get an ISO image or download SSDTSetup.exe to create an administrative install point if there is no internet access. For details visit the previous link.

SQL Server Data Tools for Visual Studio 2013

There are several options for SQL Server Data Tools depending on the version of Visual Studio as described here.

For this post we are using a Toshiba Laptop running Windows 7 (64-bit) Ultimate having a Visual Studio Express 2013 for Web which is one of the requirements in the following list:

• Visual Studio 2013 Professional,
• Ultimate,
• Premium,
• Express for Web, or
• Express for Windows Desktop

You can download the tool here:
https://msdn.microsoft.com/en-us/dn864412

You will need approximately 1.8GB of free space at for the full install point because it includes all possible components that might be required.

You have an option for the language to choose from:

SSDT_01
Choose language and download (English here).

You double click the downloaded program SSDTSetup.exe to begin installation.

SSDT_02

Accept the license terms to proceed.
The download and installation begins immediately.

SSDT_03
It may take a while to download and install.

SSDT_04

After installation you can find Visual Studio 2013 Data Tools as shown.

SSDT_05

A number of items will be added to your Control Panel Programs as shown.

SSDT_06
You can verify it in Visual Studio 2013 Express for web also as shown.

SSDT_07

How do you create SQL Server Projects using the SSDT?
Hints for creating projects:
Launch VS 2013 Web (for example)
Create New Project choosing SQL Server Project Template
Associate a SQL Server Database for the project by directly connecting to SQL Server; choosing to run a script or via a data-tier application

In the next post we look at using SQL Server Data Tools in Visual Studio 2013 Express for Web.

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