Skip to content

Database Projects – Deploying Changes

In a previous blog post (Database Projects – Merging changes), we successfully merged our feature branch into our development branch. Now, as a final step in our development process, we want to get our changes deployed to our development environment.

In this blog post, we will go through the process step by step to execute a manual deployment. We will take a look at what happens behind the scenes, how deployment works and we also will take a look at Publishing Profiles.

Quick Overview

Deploying by example
Advanced Deployment Options
Publishing Profiles

Deploying by example

As a first step, start Visual Studio and open your Database Project. Now, navigate to Solution Explorer and select the project that you would like to deploy. In this blog post, I will be deploying the changes that I have previously made in the AdventureWorksDW project (blog post: Database Projects – Making changes – Feature Branching).

To start with deployment, right-click on your project, in my case AdventureWorksDW and choose Publish…

Behind the scenes, Visual Studio will first try to Build your project, as soon as you click the Publish… action, you will see the Output window showing up where Visual Studio will show the Build process. If the Build process finishes successfully, a couple of different files will be generated. The most important file, for now, is the AdventureWorksDW.dacpac file.

The .dacpac file, dacpac is the abbreviation for Data Tier Application Package, contains our new database model. If you want to find out which components there are in a dacpac file, you can change the extension from .dacpac to .zip and unzip to start exploring.

What is important, is that as long as your Build process is failing, you won’t be able to deploy your changes to the database server. You first will need to bugfix all the issues and have a successful Database Project build.

After a successful build (when you chose to Publish) you will get the following screen:

As you will see in the screenshot above, I already connected to my destination database. To get this done correctly, choose Edit…, provide the target server name, the preferred Authentication type and credentials, and of course, in case you want to update an existing database, choose the right Database Name and finalize by clicking OK.

Now we can publish our change to the specified destination database by choosing the Publish option. As soon as we have chosen this option, the Data Tools Operations window shows up and deployment starts. As soon as the deployment is done we will get the following result:

If you want to find out which changes got deployed, you can get the executed change script by navigating to View Script in the Data Tools Operations window:

If you have clicked “View Script”, you can view the SQL script that has been generated during the deployment phase. This generated SQL script only contains the changes that were identified between your database project and your destination database.

If you don’t want to find out what has been deployed by going through the deployment SQL script, you can get the information from the Publish Preview. This file contains a high-level overview of the deployed changes. In my case:

** Highlights
     Tables that will be rebuilt
     Clustered indexes that will be dropped
     Clustered indexes that will be created
     Possible data issues

** User actions
       [dbo].[DimProduct] (Table)

** Supporting actions
       [dbo].[vDMPrep] (View)
       [dbo].[vAssocSeqLineItems] (View)
       [dbo].[vAssocSeqOrders] (View)
       [dbo].[vTargetMail] (View)
       [dbo].[vTimeSeries] (View)

At this point, we have successfully deployed our changes to our development environment, though there are a couple of things that we might want to reconsider. In the following section, Advanced Deployment Options, we will take a look at some important options that we might need to use.

Advanced Deployment Options

As mentioned earlier, there might be a couple of things that we want to reconsider during deployment. To get to the Advanced Deployment options, we go back to the Publish database window.

Instead of publishing, we now choose the Advanced… option.

The Advanced Publish Settings window is divided into 3 different Tabs: General, Drop and Ignore. We will start on the General Tab.

On the General Tab, the are 2 options that I would like to point out. The first option is the Deploy database properties option.

In the past, I have been in a couple of situations where I wasn’t the database owner and where I only was allowed to maintain my own database objects. If this is your case as well, it would be a good idea to unmark this option since this will try to overwrite the existing database properties with the ones that have been configured in your Database Project.

The second option is “Block incremental deployment if data loss might occur”. This option has been a lifesaver for me in the past. If you’re planning on dropping a column for some reason, your might want to unmark this option.

Let’s continue to the Drop Tab, at the Tab, there also are a couple of options marked by default.

To describe what happens if you keep these options marked, I will take the “Drop index not in source” as an example.

If you have chosen to leave indexes out of your Database Project, this option will remove all your created indexes on the target database since the indexes are not available in the source (your Database Project).

To be fair, I forgot about this option the first time and removed all my existing indexes from my development environment by accident.

Unmarking this option might be a good idea.

The other available options on this tab are more of the same, but then for constraints, DML triggers and extended properties. For now, I will not be explaining the Ignore Tab, since I never found any need to use it in the past.

As soon as all Advanced Deployment Options as set as we want, we can confirm our choice and create a publishing profile.

Publishing Profiles

So, what is a publishing profile? A Publishing profile is an XML file that contains all the deployment configurations that we have been discussing in the previous section (Advanced Deployment Options).

To generate a Publishing Profile, you need to navigate to the Publish… action (as explained earlier), configure your deployment options, and finally, in the Publish Database window choose “Save Profile As…”

As soon as you hit the “Save Profile As…” button, you will get a Save Dialog window, choose a proper name and Save. I have named mine: AdventureWorksDWH_DEV.

For future deployments, I can just double-click on my Publishing Profile and all configurations will be preloaded properly in the Publish Database Window.

Published inAzure DevOpsDatabase Projects

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *