Click here to Skip to main content
15,072,735 members
Articles / Database Development / SQL Server
Article
Posted 24 Oct 2017

Tagged as

Stats

37K views
1K downloads
21 bookmarked

SQL Server Database Project - SSDT - Continuous Delivery and Deploy Database Changes using Data-Tier Application Package [DacPac]

Rate me:
Please Sign up or sign in to vote.
4.50/5 (6 votes)
24 Oct 2017CPOL9 min read
Create a Data-Tier Application Package (DacPac) to easily manage, deploy and deliver chronological database changes onto multiple environments

Introduction

The main intention of this article is to help understand ways to simplify the process of database contribution and deploying changes onto different environments. Also, to bring together code changes and its corresponding database schema/script changes into one single change set. Finally, automated Database Delivery as part of Database Life-cycle Management (DLM) and Dev-Ops.

Prerequisite to Create a SQL Server Database Project

  1. Visual Studio
  2. SQL Server 2005 or higher
  3. SQL Server Data Tools (SSDT) [Download]
  4. Data-Tier Application Framework (DacFx) and SqlPackage must be on the machine performing the installation

Steps to Create a SQL Server Database Project or Data-Tier Application

We first need any database to create a SQL Server Date-Tier application in Visual Studio. In this article, we are going to use Northwind sample database which includes some Tables, Views and Stored Procedures to start with. You can download the database backup file from here.

Once you downloaded and restored the database in your SQL Server Management Studio, start by creating a new project in Visual Studio (For this article, I’m going to use Visual Studio 2015.)

From the installed templates, Select SQL Server and choose SQL Server Database Project. Name the project and solution as DacPac and SSDT respectively.

SSDT Project

Clicking on "OK" button will bootstrap empty database project as shown in the below image:

Empty Project

Now, we need to import our Northwind database to our new project. Right click on the DacPac project and from Import menu, choose Database...

Database Import

Image 4

Connect to DB

From the Import Database window, select the Source database connection... (Ignore “Target Project” and “Import Settings” as we are not going to use them). Clicking "Start" button, the project will scan for all database objects like Tables, Views, Stored Procedures, Triggers, functions, etc.

Gathering Database Objects

Click "Finish" button to complete the database import process. Now, we can see all the database objects as .sql script files, one for each object within our project.

After Import Project

If you open any of the .sql files… all it contains is a create SQL statements for an object. When we open Products tables (Products.sql), we can see the table design view as well as its create table statements. Also, we can see the other table information like Primary & Foreign keys, Check Constraints and Indexes as well as triggers, etc.

Sample Table Sql file

That’s it… our SQL Server Database Project is ready!

Build the project to know if there are any compile time errors and to see if there are any issues with database schema or syntax, ambiguous column names or mismatch column comparison in where clauses and data types, etc. In our case, the application builds successfully.

Now, add the solution to any Source Code Control System (SCCS) like GIT or TFS thereby we can achieve Continuous Integration (CI) and later Continuous Deployment (CD). The Best practice is to bring SQL Server Database Project together with its corresponding Web/application source code under one single repository.

Build success means we got a new database release artefact file called Data-Tier Application Package (DacPac.dacpac) in the application Bin/Debug folder.

What is DacPac File?

The ‘portable’ version of a database design is a DacPac. It is described by the pundits as a ‘self-contained database entity used by an application’, representing the ‘declarative model’ of the database, which can be authored alongside application code.

A DacPac can be built from scratch, created from a Visual Studio 2010 Data-Tier project, compiled from source code, or extracted from existing databases. We can deploy DacPac both to Azure SQL Databases or SQL Server, either as a new database or to upgrade an existing database.

All DacPacs contain at least…

  • an XML file that represents an XML ‘model’ of the database’s metadata
  • a file containing information about the DAC framework that produced the DACPAC file

DacPac is merely a zip file. Just rename its extension to .zip to see its content and read XML files. [Sample DacPac as zip file]

Dacpac as Zip file

Dealing with Schema Changes

Say suppose we got a schema change to the Products table. We intend to add new column CreatedDate of type datetime to the table. So, go ahead and open the Products.sql file under Tables folder. Add new column either in design tab or add necessary script in T-SQL tab.

Editing Table Schema

Notice that as I update the table in design mode… its corresponding script has been generated in T-SQL mode. Nice, isn’t it…

To Compare Data-Tier Application Changes with the Database Itself

After the changes we made to the Products table in Visual Studio Data-Tier project, let’s compare it with what we initially have in database before we apply these changes back to database.

Go to SQL Server under Tools menu and select New Schema Comparison… and save the comparison file Project_Database.scmp to the database project. [Download Project_Database.zip]

SQL Schema Compare file

Choose Data-Tier Project DacPac as Source Schema:

Source as Database Project

Choose Northwind database as Target Schema:

Target to Northwind DB

Click "Compare" button and wait to see the difference between the Data-Tier Project and Database. Once comparison is complete, we will see the table change as new column being added to the Products table in Visual Studio.

Schame Comparision

Publish Data-Tier Project Changes to Database

There are three different ways to apply SQL Server Database project changes back to the database:

  1. Publish the project from Visual Studio and select target database
  2. Using SQLpackage.exe command, and
  3. DacFx (Advanced concept)

In this article, we are going to see Publish… from project and SQLPackage.exe options. DACFx is an advanced concept and a bit complicated but is the right choice to achieve CD as part of Database Lifecycle Management (DLM).

#1 Publishing Changes from the Database Project

Before publishing the Data-Tier application, we need to build the project first so that there is a valid build artifact DacPac file in Bin/Debug folder. Right click on the project and select Publish…

Publish Database Window

Publish Database Dialog

This is the window where we will select “Target Database connection”, “Database name” and the file name. Also, we can Create, Save and Load publish settings as a profile (.publish.xml) for later usage.

Once we select Target database connection with Database name, we can either hit "Generate Script" button which will only produce a .publish.sql file with the changes and can then be executed separately on the database as T-SQL.

Generated Sql

Or hit "Publish" button, which will not only generate .publish.sql file but also applies changes to the selected database.

Note: Publish option will create new database if we do not specify database name in “Target database connection” connection string but uses “Database name” value.

We see the Data Tool Operation failed to apply the change to the database and raised the error message “Rows were detected. The schema update is terminating because data loss might occur".

Error Description: The column [dbo].[Products].[DateCreated] on table [dbo].[Products] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue, you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.

Makes sense...

Error While Publishing

To fix the error, we will allow Products table new column DateCreated to accept null values.

Correcting Table Schema

Save the file and build it to generate new DacPac file. Now publish the project again. But, this time with one advance option “Block incremental deployment if data loss might occur” unchecked. This will allow the operation to continue even if there might have been any data loss.

Advance Publish Settings

Now, after fixing the design flaw with the table and with advanced option, we successfully updated changes to database.

Operation Success

Now, if we compare our Data-Tier project with the database… both look identical.

Identical after schema correction

#2 Using SQLpackage.exe Command

SqlPackage.exe is a command line utility that automates several database development tasks like Extract, Publish, Export, Import, DeployReport, DriftReport and Script. This command line allows you to specify these actions along with action specific parameters and properties.

In this article, we are looking at Publish action. Publish incrementally updates a database schema to match the schema of a source .dacpac file. If the database does not exist on the server, the publish operation will create it. Otherwise, an existing database will be updated.

A SqlPackage.exe publish operation incrementally updates the schema of a target database to match the structure of a source database. Publishing a deployment package that contains user data for all or a subset of tables will update the table data in addition to the schema. Note that data deployment will overwrite the schema and data in existing tables of the target database. Data deployment will not change existing schema or data in the target database for tables not included in the deployment package.

Refer to MSDN for more details about "Publish" action and its properties.

The benefits of using SqlPackage are:

  1. It's straight-forward and the API is clear.
  2. You can include it in a batch file.
  3. You can use it to automate your deployment process.

Here are the basic steps for deploying a DACPAC using SqlPackage from the command line:

  1. Open a command prompt (cmd.exe).
  2. Execute SqlPackage.exe with the desired parameters.

Here is the syntax to use SQLPackage command with parameters:

PowerShell
<Path to your Microsoft SQL Server>\DAC\bin\sqlpackage.exe
/Action:Publish
/SourceFile:"<Complete Source Path to the .dacpac file located in Bin/Debug>"
/TargetConnectionString:"<Target Database Connection string>"

Note: If possible, you can create environment specific Batch files (.bak) which include SQLPackage.exe command with predefined parameters to publish database. [Download .Bat file]

#3 Using DacFx

This is the most suitable method to automate Continuous Deployment process for all database related changes. Take a quick look at the article, “Microsoft and Database Lifecycle Management (DLM): The DacPac” written by “Phil Factor” [https://www.red-gate.com/simple-talk/sql/database-delivery/microsoft-and-database-lifecycle-management-dlm-the-dacpac/]

Main Advantages of Using SQL Data-Tier Application

  1. Having more control over database that ever before
  2. Time & Resource saving
  3. Quick deliverables
  4. No need to manually maintain separate .sql script files for all database changes made
  5. Brings complete database objects (Tables, Views, Stored Procedures, Triggers, etc.) into Source Code Control System, track changes
  6. Having single change set for all database changes and its interrelated application (C# or any language) changes
  7. Very useful when dealing with multi-tenant database applications
  8. Maintain environment (Dev/QA/Stage/Prod) specific publish profiles
  9. Continuous Integration & Continuous Deployment as part of Database Life-cycle Management (DLM) and Dev-Ops
  10. Less database conflicts or mismatch issues avoiding blame game

My Other Articles on CodeProject

Hope you learnt something new from this article. Feel free to "Rate This Article", "Bookmark" and give suggestion in "Comments" section below.

History

  • 24th October, 2017: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sreekanth Mothukuru
Technical Lead
India India
No Biography provided

Comments and Discussions

 
QuestionWhat if some one forgot to check in after ALTER store procedure Pin
vishal_h11-Oct-19 1:59
Membervishal_h11-Oct-19 1:59 
If some one forgot to check-in db script after ALTER the script [ like alter of store procedure ] then it leads to inconsistent script ( object ) on DB server and DB project which is under source control (TFS).

Above scenario will create major problem in patch deployment.

QuestionHow to SQL rollback previous version Dacpac using devops/VSTS Pin
Member 980370126-Jun-19 17:15
MemberMember 980370126-Jun-19 17:15 
GeneralExcellent Article Pin
LakshmiKanth51116-Mar-18 21:06
professionalLakshmiKanth51116-Mar-18 21:06 
QuestionExcellent! Pin
vamsi Mogaliipuvu26-Oct-17 2:25
Membervamsi Mogaliipuvu26-Oct-17 2:25 
AnswerRe: Excellent! Pin
Sreekanth Mothukuru26-Oct-17 2:59
MemberSreekanth Mothukuru26-Oct-17 2:59 
GeneralMy vote of 4 Pin
san2debug24-Oct-17 18:31
professionalsan2debug24-Oct-17 18:31 
GeneralRe: My vote of 4 Pin
Sreekanth Mothukuru24-Oct-17 20:03
MemberSreekanth Mothukuru24-Oct-17 20:03 
PraiseNice Work! Pin
Haroon Ashraf24-Oct-17 10:03
MemberHaroon Ashraf24-Oct-17 10:03 
GeneralRe: Nice Work! Pin
Sreekanth Mothukuru24-Oct-17 20:02
MemberSreekanth Mothukuru24-Oct-17 20:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.