Posts in this series:
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.
The tools you need to get started:
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 your new project and select SQL Server Database Project
To import an existing database into your project, go to:
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.
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.
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.
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.
To publish the changes from Visual Studio, right mouse click on the project name and select publish.
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.