Database deployments are notoriously tricky. Luckily, we have some great tools available to ease the pain! I will show how to use Release Management to manage database deployments but first let's talk about using SSDT to manage database.
Step 1: Use SSDT to manage databases
Traditionally, database changes are handled via SQL scripts. When a developer needs to change the database schema, they write a little SQL script and (if we're lucky!) put it into source control. Then, when it's time to deploy the new code, someone (sometimes a developer, sometimes a DBA) runs the script. This quickly gets hard to manage, because we end up having to ask a lot of questions:
- What database scripts should be run for this deployment?
- What order should the database scripts be run in?
- Who should be running them?
- When should they be running them?
- Are the database scripts able to be safely run multiple times?
Luckily, we have a great tool for turning our databases into regular old Visual Studio projects that can be source controlled and deployed with ease: SQL Server Data Tools. With SSDT, we can import an existing database, turn it into a project in Visual Studio, and then let SSDT manage our database.
When you build a SSDT project, SSDT will generate a file containing your database schema -- a DACPAC -- and allow you to publish it. Publishing a DACPAC results in your DACPAC's schema being compared to your database's schema, and automatically generates a SQL script containing the necessary SQL statements to make your database match up with your DACPAC schema. It's very configurable, so you can make sure it only automatically does the actions that you want it to!
Okay, so we've solved the problem of how to manage our database schema... we'll use SSDT! But now the question of how to publish our database schema is looming. Well, if you've read my previous blog posts, you probably already know what I'm going to say: Use Release Management for Visual Studio!
Step 2: Use Release Management for Visual Studio for database deployments
Let's start by looking at my application's database. You can see that it's just another project in my web application's solution, so it will build a DACPAC for me whenever I build all the other pieces of my application.
Now I want to deploy this DACPAC whenever I release my website. Let's look at how we can use Release Management to do that.
Release Management has a built-in tool to handle doing DACPAC deployments, so all we really need to do is make a new component that leverages the existing tool.
For the "Source" setting, we can choose "Builds with Application", and we can set our package path to the root of the build drop folder. Since the Release Template we're going to be deploying is already tied to my automated build that generates the DACPAC and my website, all of the details of where the DACPAC is going to come from is already handled for us.
Here's what my component looks like:
Next, we click over to the "Deployment" tab, and choose the "DACPAC Database Deployer" tool. The tool takes 3 parameters: FileName, ServerName, and DatabaseName. We'll fill those in when we're using our new component in our Release Template.
That's all for the component! Now we can add it to our existing release template, and specify the DACPAC file name to deploy, the SQL server and instance to deploy to, and the name that the database should have. The filename is going to be, by default, the name of the SSDT project. Since my SSDT project is named FabrikamFiber.Database, that's the name of my DACPAC.
Now, when I make a change to my database schema in SSDT and check it in, it'll automatically deploy the schema changes along with my website! Let's add some new columns to the Customers table:
I check my change in, and my Continuous Integration build kicks off, builds my DACPAC and my website, and triggers a release automatically:
And in this release, we can see my database get published:
And that's all there is to it! If you are interested in learning more about rollbacks scenarios with Release Management, I also wrote a blog post about it. Next blog posts will focus on branch and merging strategies with Release Management for Visual Studio.
