Posts in this series:

• Setting Up (this post)
• Deployment Scripts and Breaking Changes
• Temporal Tables

Many companies wrestle with deploying SQL Server changes. This is not a new problem and has been a source of pain for some time. Some companies have written their own process to manage the deployment of databases while others use third party solutions, like RedGate’s SQL Compare.

However, who better qualified to provide a database deployment solution for SQL Server than the company who offers the product? Enter Microsoft’s Visual Studio Database Projects.

Database Projects are not new (they’ve been available since 2007) and have many benefits over rolling your own or using other 3rd party solutions. I’ve worked with several companies to refactor their release process to use database projects and will be sharing lessoned learned in this blog post series.

This first post walks you through getting started and how to setup your first project.

Before you Begin

The tools you need to get started:

• Visual Studio Community Edition
• SQL Server
• Adventure Works database
• My database project in GITHUB

Once you have installed SQL Server, Visual studio and created the Adventure Works database, you are ready to create the database project. I find myself importing databases to start the database project versus starting from scratch most of the time.

Create a New Database Project and Import a Database

Create your new project and select SQL Server Database Project

To import an existing database into your project, go to:

• Project/Import/Database
• Configure the connection information to the database server and select the database name.

Configure the objects to import. I do not import the referenced logins as I do not manage security in the database project. Security is usually different between environments and a discussion for another blog post.

Once the process is finished, you should see all the objects in Visual Studio like this. All the objects are categorized by schema and then object type.

You are ready to start using the database project.

Benefits of Database Projects

• Visual Studio becomes the development environment for both application developers and database developers.
• Database code is easily added to your source control the same as managed code. This makes it simple to check history for a change, roll back a change and integrate with deployment solutions like Octopus and Microsoft Dev Ops.
• Deployment process is state based. The deployment process determines what changes exist between the database project and the database being deployed to. A delta file is created as a result of that compare. Developers no longer have to keep track of what changes they have made and need to be deployed.
• Deployment process only evaluates the database objects that exist in the database project. This is useful in a development environment where you may have new objects that are being evaluated and have not been formally added to the database project. When the database compares runs, the delta file that is created contains the changes for the objects that exist in the database project.
• There is a setting in the deployment process to create a drop script for all objects that do not exist in the database project. This is useful when cleaning up the development environment of objects that were created outside of the database project.
• If you have stored procedures that reference objects in another database, whether it’s on the same server or a remote server, you can add a database reference to your project that resolves those references.

Rename/Refactor

Database projects have built in functionality that makes refactoring quite simple. All of these options are found by right mouse click on a table or column in Visual Studio.

• Rename: renames a column and updates the column name in the constraints associated with the tables/columns that it has references to. It does not update the column name in the other tables (the FK columns).
• Move to Schema: move an object to a new schema.
• Expand Wild Cards: Expand a select * to the column list.
• Fully Qualify Names: fully qualifies the SQL statements within a procedure or view.

Build then Publish

Build

When you do a build of your database project, one of the outputs of the process is the DACPAC file. The DACPAC represents all the objects in the database project and is used in the publish process to generate the change script between the database being published to and the objects in the DACPAC file. This file is part of the DB project and will be added to source control. The DACAPAC file will be important when we look at the publish process.

Do a build of the database project prior to checking in your changes or publishing your changes. This will verify if there are any errors or warnings in the project. There should be no warnings and no errors after the build finishes. Warnings should be evaluated and resolved as these have the potential to be problems when deployed. Warnings are telling you that there is the potential for this object to be referencing something that may not exist when deployed. You can publish with warnings in the DB project, but errors will not allow you to publish until they are resolved.

Publish

When you are ready to publish your changes, this can be done manually from Visual studio or via your deployment process whatever that is. When you publish using a database project, the DACPAC file is used to generate the differences between the objects in the DACPAC file and the database you are publishing to. There are ways to control what objects are published along with how you want to treat objects in the destination that are not in the DACAPC file.

Feature versus State Based Deployments

State

• Source of truth is how the database should be.
• Compares the entire database (the dacpac) against the environment being deployed to. Determines the differences and builds the appropriate script to make the environment being deployed to match the source (dacpac).
• If there is drift between the database and source code due to manual changes in the destination database, the state deployment will identify those objects and create the scripts to change those objects to match the DACPAC.

Feature

• Source of truth is how the database should change
• Creates the build based off changed scripts in source control.
• If there is drift between the database and source code due to manual changes to the database objects, the migration process will not bring the environments into sync.

To publish the changes from Visual Studio, right mouse click on the project name and select publish.

• Target database connection is the destination server you are publishing to.
• Database name is the database you are publishing to.

The advanced option allows you to configure settings like what objects to ignore or drop as part of the deployment process. This is where you can allow for data loss to be enabled or disabled. It is disabled by default.

You can save the settings to a profile that can be used in future deployments. By saving your settings to a profile, you configure the options you want for that environment being published to and can reuse those settings going forward.

The generate script option will create the deployment script. It does not execute the script against the database. This is a useful option if you want to see the changes that would be applied to the database you are publishing to.

The publish option creates the change script and applies the changes to the database. You do not see the script in this process.

Next post: pre and post deployment scripts. Dealing with breaking changes in your project and adding static data to deployment.