When upgrading an application, it’s fairly easy to deploy your changes; delete all the existing dlls and replace them with a new set. The problem with databases is that they are often infested with pesky data. You can’t just drop the database and copy in a new one. Database changes need to be incremental, updating data where necessary and issuing ALTER statements to tables.
Broadly speaking, there are two ways you can go about database deployment, high tech & low tech. Low tech is keeping an update script with DDL statements making all the changes between the last release and the next one. High tech is using a third party comparison tool like Red Gate’s SQL Compare or Visual Studio’s database projects, these can generate a change script by comparing the schemas of two databases.
Going the low tech route can quickly get out of hand, especially with multiple developers working on a project or if you have many dependencies between your objects. The high tech path also has serious drawbacks, as the auto-generated change scripts are often limited on the kind of operations they can perform. For instance, you’d never be able to get a comparison tool to update all NULL values of an INT column to zero and then make the column NOT NULL.
This article describes workable solution which leverages the power of VS database projects but at the same time allows for incorporation of hand-written scripts which can handle the more involved schema changes.
The problem with database projects and the workaround
The idea behind database projects is that all the objects in the database are stored as CREATE scripts, from these CREATE scripts a schema is inferred, this is then compared against an existing database to generate the update script. If you rename or change the datatype of a column in the generated script will attempt to drop the table and recreate it (there are plenty of warnings to stop this happening by mistake though).
What database projects are very good at, is managing the dependencies between stored procedures/functions & views (the Programmability). We are going to set up a project which only contains Programmability, and leave the changes to tables (Structure) to our hand-written scripts. The problem is that now the project no longer builds as the Programmability has invalid references to the Structure.
Database projects support the idea of database references, similar to how ddl references work in code, you can references objects in a *.dacpac file and it is equivalent those objects being present as CREATE scripts in your project.
All of our Structure objects will be contained in a dacpac file, the project will build so we can use the generated script to manage our Programmability. The Structure changes just need to be applied beforehand.
Releases will be more robust and less labour intensive. This method will also allow you to seamlessly integrate with Team Foundation Server Team Build. Team build is a very powerful tool which you can use to set up multiple sets of builds all automatically triggered either by check-ins or scheduled. Details of how to go about this are in the second half of this article.
- Visual Studio 2012 with Sql Sever Data Tools
- Team Foundation Sever
- Sql Server (developer machine)
Get started straight away by downloading this sample solution I’ve prepared, or follow the steps below.
The database project
To create a database project you need to have the Visual Studio Database Tools installed, then just use the wizard.
This is how I have laid out my project. How you lay out your project doesn’t matter, Visual Studio doesn’t care where you keep your CREATE scripts so you should just lay them out as you see fit. I’ve opted to split mine up by schema and then into object types.
My folders are:
- Build: Logic required to generate database reference, not part of the database itself.
- Data: Static data creation scripts.
- Schemas: Programmability as defined above.
- Structure: Structure as defined above.
Structure database reference
Create an empty database in Sql Server, then use the below command to create a dacpac file. SqlPackage.exe is a tool that comes with Sql Server Data Tools (SSDT).
SqlPackage.exe /a:Extract /ssn:127.0.0.1 /sdn:DatabaseName /tf:DatabaseName.dacpac
Save your dacpac file in your Structure folder and then adding a reference is very similar to adding a dll reference:
It might be a good idea to include a test table or two in your database before creating the dacpac file, then add some test stored procedures/functions to see that the project builds successfully.
Structure update scripts
There is no built-in functionality that will allow us to apply update scripts, we will have to do this ourselves. I have chosen to split update scripts into folders for each major release. Each folder has a series of scripts that need to be run in alphabetical order. Each script contains a chunk of work, a group of related changes relating to a single enhancement or new requirement. The scripts are named so that they start with a number which determines the order they go in.
As these scripts will be run against the development database multiple times, they need to be written so that they don’t error if the change has already been applied.
IF OBJECT_ID(‘mySchema.myTable’) IS NULL
CREATE TABLE myschema.mytable(myColumn INT)
There is one file in the root of this directory which is marked in the project as a PreDeployment build type. When Visual studio generates the database update script, this will be tagged on at the beginning. This script will run, in turn, each of the individual update scripts.
Data update scripts
You can skip this step if you’re just looking to get a quick introduction.
The data scripts contain all static data that applications need in order to function. In my football-orientated example there is a script to create rows in the positions table for all of the allowed positions a footballer can play in eg. Forward, Midfield, Goalie etc etc.
Similar to the Structure scripts, there is one script that references all of the others, but this time it is marked in the project as PostDeployment build type. This will run after all the schema changes have been applied. Again, it’s important that these can be run over and over again, so write them as MERGE statements not INSERTs.
Tricking the build
In order for the project to build we need to convert our update scripts into a dacpac file. This is done with the help of PreBuild tasks. The diagram on the right shows the stages in the standard process, in green, with the additional steps in blue.
First of all all the various scripts are combined into a single script, then SqlCmd.exe is used to apply this script to a database on a Sql Server instance on the development machine, then SqlPackager.exe is used to extract the schema out into the dacpac file which is already referenced in our project. This ensures that all the schema objects are updated before the build is attempted.
I’ve included SqlPackager.exe in the solution so that the build agent doesn’t need to have SSDT installed.
The bat files used to do this are included in the sample solution file, they are of course dependent upon the hierarchy of my project, however, I have tried to write them so they are as flexible as possible. Go to the Properties dialog of the project file -> Build Events and add the calls to these bat files.
To publish via Visual Studio, go to the Build menu, then Publish… You’ll see the screen below, fill in the Target Database Settings then click on advanced. There are two checkboxes you’ll need to change.
- Ensure that “Include composite objects” is unchecked.
- Ensure that “Treat verification errors as warnings” is checked.
By unchecking “Include composite objects” you are telling the comparison tool to ignore everything in the referenced database project, we want this as we are making our own changes in the PreDeployment scripts. Treat verification errors as warnings means that although the build process thinks that the publish will fail, it will only show up warnings and the publish will continue.
So that’s it, you should now be able to manage all of your changes within the database project and easily deploy to your development environment. If you really want to reap the benefits of database projects though, you need to hook up to Team Foundation Server and take advantage of Team Build.
Download the solution FootballDatabase.zip
Download the data script DataGeneration.sql