SQL Server DACPAC deployment

Starting with SQL Server 2008, Microsoft introduced a new project type called Database Projects. These projects use the state-based approach to applying changes to your database. Initially, Database Projects were not available as part of the initial Visual Studio install and had to be downloaded separately. This download was referred to as SQL Server Data Tools (SSDT) and included project types for Database projects, SQL Server Reporting Services (SSRS) projects, and SQL Server Integration Services (SSIS) projects. Modern versions of Visual Studio have SSDT available to choose when installing or modifying an existing installation.

Installing SSDT for Visual Studio

For earlier versions of Visual Studio such as 2015 and below, installing the SSDT was a matter of locating the download for your version of Visual Studio. Microsoft has provided a convenient way of finding the appropriate download on this page.

For more modern versions of Visual Studio (2017+), checkout Microsoft’s installation instructions

Connect the project to the database

With SSDT for Visual Studio installed you can connect the project to the database with the following steps. First, we create the project:

1. Navigate to the Other Toolsets category.
2. Click the Data storage and processing option.
3. Select SQL Server Database Project and click Next.
4. Enter the project name and click Create.

The project has been created, now we connect it to a database. This example uses a pre-existing database called OctoFXDemo:

1. Right-click the project name, then click Import ➜ Database.
2. Click Select Connection.
3. Add the Server Name and select the type of authentication. In this screenshot, a SQL Account is used to connect to the database server.

4. Click Connect and then click Start to import the database.

Importing the database will populate your project with the existing objects from the database. You will see a summary of the importing process:

The project is now ready for creating database schema objects (tables, views, stored procedures, etc…)

Compare the project to the database schema

When the project has some objects, we can compare the project to the target database.

1. Right-click on the project and choose Schema Compare….
2. Select the target database connection by clicking Select Target ➜ Select Connection, and select the connection.
3. Click Compare.

Visual studio will now compare the project to the database and list the steps it will take during a deployment:

Build definition

You can use most build servers to build the SQL Server Database project, you just need to install the Visual Studio build tools for the version of Visual Studio that you’re using on the build agent.

This guide uses Azure DevOps as the build platform, but any build server can do this.

Create the build definition

To create the build definition, take the following steps:

1. From the Azure DevOps repo, click Pipelines ➜ New Pipeline.
2. Select Empty job to start.
3. Choose a build pool, then click on the + to add a step to the build definition.
4. Click on the Build category and scroll down to Visual Studio build.

5. Add /p:OutDir=$(build.StagingDirectory) to the MSBuild Arguments so that the built artifacts are separated from the source code.

6. Click on the +, select Package, and select Package Application for Octopus.

7. Add the properties for the task:
   • Package ID: Give the package a meaningful name.
   • Package Format: Chose whichever package type you wish.
   • Package Version: Use the build server build number to associate a package version back to a build number.
   • Source Path: This will be the same path as what we set the MSBuild argument to, $(build.StagingDirectory).
   • Output Path: Location to store the created package.

8. Expand the Advanced Options section and add:
   • Include: The only file we need for deployment is the .dacpac itself. Add the filename here, this example uses OctoFXDemo.dacpac.
9. The final step in the definition pushes the package to a repository. This guide uses Octopus Deploy’s built-in package repository. Click on the +, select Package, and select Push Package(s) to Octopus.
10. Next, create a connection to the Octopus Server, by clicking + New and add the connection details, then click OK.
11. Select the space in your Octopus instance to push to from the drop-down menu.
12. Enter the package(s) that you would like pushed to the Octopus repository and the individual packages or use wildcard syntax:
    1. Individual packages, for instance, $(build.StagingDirectory)\OctoFXDemo.dacpac.$(Build.BuildNumber).nupkg
    2. A wildcard $(build.StagingDirectory)\*.nupkg.

Queue the build to push the artifact to the Octopus Server:

Create the Octopus Deploy project

Now that the build server has been configured to push the artifact to the Octopus Server, we need to create a project in Octopus deploy to deploy the package.

1. From the Octopus Web Portal, click the Projects tab.
2. Select the Project Group and click the ADD PROJECT.
3. Give the project a unique name, a description (optional) , select the Project Group and the Lifecycle, and click SAVE.

Define the project variables

1. Click Variables from the project’s overview screen.
2. Define the following variables:
   • Project.SQLServer.Name
   • Project.SQLServer.Admin.User.Name (optional)
   • Project.SQLServer.Admin.User.Password (optional)
   • Project.Database.Name
   • Project.DACPAC.Name

It is considered best practice to namespace your variables. Doing this helps prevent any variable name conflicts from variable sets or step template variables. Prefixing Project. to the front indicates that this is a project variable.

Note, both Project.SQLServer.Admin.Password and Project.SQLServer.Name have multiple variables that are scoped to different environments. Learn more about scoping variables.

Define the deployment process

With variables defined, we can add steps to our deployment process.

1. Click the Process tab.

2. Click ADD STEP.

3. Search for dacpac steps, select the SQL - Deploy DACPAC using SqlPackage step, and enter the following details:

   • DACPACPackageName: The name of the dacpac file. The Project.DACPAC.Name variable was created for this field.
   • Publish profile name: Complete this field if you use Publish profiles.
   • Report: True.
   • Script: True.
   • Deploy: False.
   • Target Servername: Project.SQLServer.Name variable.
   • Target Database: Project.Database.Name variable.
   • Authentication type: Choose the authentication for your use case.
   • Username: Project.SQLServer.Admin.User.Name variable (used only with SQL Authentication type).
   • Password: Project.SQLServer.Admin.User.Password variable (used only with SQL Authentication type).
   • DACPAC Package: The package from the repository, OctoFXDemo.dacpac for this guide.
   • Command Timeout: Override the default script execution timeout.
   • SqlPackage executable location: If you have the sqlpackage.exe installed, specify the location, otherwise, leave blank to dynamically download it.
   • Additional arguments: Any additional sqlpackage.exe arguments not provided by the template.

4. Add a manual intervention step, scoped to production, so the report from the previous step can be examined before deploying to production.

5. Add another SQL - Deploy DACPAC using SqlPackage step, and change the Report and Script values to False, and the Deploy value to True.

The deployment process should look like this:

Create and deploy a release

1. Create a release by clicking on the CREATE RELEASE button.
2. Click SAVE.
3. Click the DEPLOY TO DEVELOPMENT button.
4. Finally, click DEPLOY.

The results will look like:

The first part of this process gathers the changes and creates two artifacts, an XML file that reports which objects will be changed and the script it will use to apply those changes. The deployment (deploy DACPAC) uses that generated script and applies it to the target so the database matches the desired state.

Help us continuously improve

Please let us know if you have any feedback about this page.

Send feedback

Page updated on Monday, August 19, 2024