Data Centers

Auto-deploy and version your SQL Server database with SSDT

Microsoft's free SQL Server Data Tools ease the burden on database administrators when versioning databases. Here's how they can help you with this sometimes tricky task.

By Keith Schreiner

I have read and been told many times that it is a “best practice” to version control my database. To do so, I’ve tried past tools from Microsoft, RedGate, and others, but none have really caught on for me. I’ve also disliked the “messy” process of having to manage multiple SQL scripts when upgrading a production database. But Microsoft’s latest (free) tool, SQL Server Data Tools (SSDT), has restored my faith that versioning my database should not be a dreaded chore.

Define 

SSDT adds a lot of functionality to Visual Studio for working with SQL Server databases. The main things it adds are a “SQL Server Database Project” and something called a DACPAC (Data-Tier Application Package). A DACPAC is a single deployment file that contains your entire database schema and some related SQL files (like look-up data), basically, everything to deploy a new version of your database in one file.  It is similar to a BACPAK, which is a DACPAC plus all of the data in every table (like a standard database backup).  But before we talk more about DACPAC’s let’s discuss what SSDT adds with its SQL Server Database Project.

Develop 

In Visual Studio, after creating a new SQL Server Database Project, you have an option to import a database, which nicely creates SQL scripts of all your database objects (tables, views, stored procedures, and more). The next step that I do is to create a “Data” folder and add a post-deployment SQL script to populate the look-up data and optionally create some test data.

In a database project you can only have one post-deployment and one pre-deployment file, so it is a best-practice to use the SQLCMD syntax to include other files in your main file to help with organization and maintenance. As you are creating these SQL scripts, or editing the SQL for the tables or stored procedures, you will start to notice all of the other things SSDT adds to Visual Studio: SQL IntelliSense, SQL code navigation, a SQL Server Object Explorer, debugging of SP’s, unit tests for SP’s, a visual Table Designer, a Schema Compare tool, a Data Compare tool, and more.  All of these features make database development and maintenance feel natural in Visual Studio.

ssdt_image001.png
  
ssdt_image002.png

Deploy 

After making your database changes, the next step is to deploy them, and SSDT provides several ways.  The first way is “Publish”, which works great if you have a direct connection to the database from your Visual Studio machine (like to your local or “Dev” database).  Right-click the database project and select “Publish…” which opens a dialog to save a profile.  Once a profile is saved as part of your project, you can just double-click it to directly publish your database project to the database or to just generate a script of the changes that it would run.  

The second way is to use a DACPAC, which we talked about above, when developers do not have access to the target database (like for Production). To create a DACPAC, just right-click the database project and select “Snapshot Project”, which then creates a DACPAC in your project’s “Snapshots” folder. Then to deploy this DACPAC you have several options. If you have a DBA that always applies database updates, you can give him or her the DACPAC to apply using SSMS (SQL Server Management Studio), which has a built-in “Upgrade Data-tier Application” task; it uses a wizard to help apply the changes (and allows all changes to be reviewed for problems, like possible data loss, at every step).

ssdt_image003.png
 Another DACPAC deployment option is to call “SqlPackage.exe” (with command line parameters) or to write some C# code (from the Microsoft.SqlServer.Dac namespace) that deploys the DACPAC itself. This makes deployment automatic, almost to a point where you don’t have to worry about it.
ssdt_image005.png
  

Real-life sample

On the first project I used SSDT (a new MVC web site), I encountered three issues with SSDT.  Here is how I solved them and greatly simplified database development. First, I only wanted to auto-deploy the DACPAC on the website’s Application_Start when the database had actually changed. So I added a piece of logic to check for a new version number in the latest DACPAC before applying it. The version number of the DACPAC is set in the Database Project properties -> Project Settings -> Properties dialog, and the current database version is stored in the system table, msdb.dbo.sysdac_instances_internal.  Having this version number not only made startup faster, it made others aware of a “database version”. 

ssdt_image006.png

ssdt_image007.png
The second issue was how to set up the database project to include “test data” for the Dev database, but exclude it for Production. This was accomplished by creating a second database project (DatabaseAndTestData), which had a “database reference” to the first project.  In this second project, its post-deployment script includes the first project’s post-deployment script and then all of the test data.  I used the first project (with no test data) to create the DACPAC for Production, and then just published the second project (with test data) directly to Dev.
ssdt_image008.png

Lastly, some developers complained that after updating the schema or look-up data, they still have to do several boring and repetitive tasks to get the DACPAC ready to be deployed. To solve this, I wrote a simple application (UpdateDatabaseVersion.exe) that can be called from the “Solution Explorer” of the database project using the Visual Studio extension, “VSCommands”.  It increases the database version number, creates the DACPAC, and then puts it in the correct folder for deployment. By implementing these SSDT processes, it streamlined my team’s database related tasks, allowing us to spend more time on other tasks.

ssdt_image009.png

Conclusion

In the past, making a change to a database felt like a long, complicated process of making a schema change (to hopefully the correct database version), manually creating a change script, reminding the deployment person about the database change, and crossing my fingers that everything went correctly. With SSDT all of this uncertainty has disappeared. SSDT is a great tool that makes it easy to create, deploy, and version your SQL Server database updates. To learn more, download the sample code, which demonstrates how to auto-deploy and version a SQL Server database.

Notes:

Keith Schreiner is a Software Architect with Geneca, a custom software development company based in Chicago. He has over 15 years of experience working in the software development industry.

7 comments
PURPLEWORLD
PURPLEWORLD

Is there a link to download the UpdateDatabaseVersion project? I'd like to have a copy.

jason carlson
jason carlson

Incredible! This is really an informative blog about SQL Server database with SSDT. I was anxiously waiting for this types of blog because I like to read this type information... Thanks for sharing this blog with us! Looking forward for such more posting in future.

bsfiosco
bsfiosco

[Removed Duplicate Comment]

leubeach
leubeach

Keith, have you posted code from your UpdateDatabaseVersion project? I'd like to have a copy.

kohku
kohku

Nice article Keith!

yonision
yonision

Hi,

Just wanted to let you know because its relevant for deployment - when it comes to scripting the database, we have a new thing we call 'context free scripting', which lets you script a database in a way that can be executed on any other database - and make it like the one you scripted. we call it 'context free scripting' and found it quite useful. its a new feature in our tool now (free, no catches no gimmics)

http://www.nobhillsoft.com/Diana.aspx