Database Build & Deployment with TFS

By | 2012-03-26T09:00:06+00:00 March 26th, 2012|Team Foundation Build, Team Foundation Server, Visual Studio ALM|1 Comment

Over the past few weeks, I have been working with database deployment using Visual Studio 2010 and Team Foundation Server. While putting a solution together, I gathered a number of resources that may be of value to you or someone on your team!

When starting a complex topic, such as database deployment and management, it is hard to know where to start looking for resources. Additionally, it is difficult to know which resources you need to review and in what order. While gathering my initial resources, I found that having a good order to review the resources was very helpful to my journey of getting a complete understanding of Application Lifecycle Management (ALM) best practices for database development, management, build, deployment, and release. I bet that you, like all DB developers and CM’s, find that there is very little time in the day to spend searching websites and running into unfruitful resources! Hopefully, this post will provide direction and save you or your team time!

The first place I started was reviewing theVisual Studio Database Guide (VSDBGuide) that is on CodePlex. This is a fantastic guide! It has hands-on labs that covers all aspects of the Database Application Lifecycle Management (ALM) best practices. To quote the ALM Ranger’s site on CodePlex: “This release includes common guidance, usage scenarios, hands on labs, and lessons learned from real world engagements and the community discussions.” After reviewing and figuring out how and why these pieces go together, it was time to get my hands dirty and build a project. The ALM Rangers Visual Studio Database Guide (VSDBGuide) has a handful of Hands-on Labs to practices with – which is exactly what I did! As I started building the labs, I found that I needed more in-depth knowledge in specific some areas to best utilize the practice labs.

Set Up – Sprint Zero

One of the first areas was on how to setup my environment for preforming a database build and deployment. Another specific area was how to run the VSDBCMD.EXE application – these both are worth becoming familiar with. VSDBCMD is straightforward and can be executed from a PowerShell script, which is great because it provides a more scalable and flexible deployment and release method. That is something I would like to cover in a later blog post; but for now, getting a demo of the fundamentals is a good starting point as Sprint Zero.

The requirements (if you are using Scrum) or acceptance criteria (if you are using Agile) of Sprint Zero was to get an environment setup with a working demo. The focus was on the basic functions and concepts you need to build and deploy a solution using Visual Studio 2010 and the VSDBCMD command line. A second part, was to make changes to a schema object, then build and deploy that change. Although this may not be a huge, complex, set of actions, the focus was to get the build and deployment working! The environment I used was VM with a single install of TFS 2010, SQL Server 2008, and Visual Studio 2010. It was a bonus that I had a ready-made database project that could be used for my solution. Note: the Hands-on Labs uses Adventure Works database as the test model.

Once I got the environment set and I created a new empty database, I then needed to build and deploy the SQL server project from Visual Studio. The Visual Studio deployment is very simple. Below are references that cover the DB project configuration and setting.

· How to: Configure Deployment Settings for Database and Server Projects

· Build and Deploy Databases to an Isolated Development Environment

TFS Team Build – Sprint One

Next, I needed to setup the .dbproj file to recreate the database and its schema, as well as to build and deploy my database project. This process adds all of the schema objects into my new empty database based on my ready-made database project. As a helpful resource, the following links cover the configuration, settings, and deployment topics in more detail:

· Introducing New Features In The VSTS Database Edition GDR

· How to: Configure Database Properties for Database Projects

· Property Files in Database and Server Projects

· An Overview of Database Project Settings

Next, I wanted to make a small change to a table and store a procedure, so that I could then deploy the update from a command line. I made the changes, built the project again within Visual Studio, and used the VSDBCMD command line utility to do the deployment. The below links are good references for the VSDBCMD command.

· Command-Line Reference for VSDBCMD.EXE (Deployment and Schema Import)

· How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE

·How to: Import a Schema from a Command Prompt.

Here are some additional less advanced links as a Technical Reference for Visual Studio Application Lifecycle Management, which has the two parts:

· Automation Command Reference for Database Features of Visual Studio

· Command-Line Reference for VSDBCMD.EXE (Deployment and Schema Import)

At this point, I had completed my Sprint Zero story of creating a working demo using both Visual Studio and the VSDBCMD! This process really cemented the fact that these basic steps are a good way to do a quick reality check as well as get the deployment working on a developer machine (and NO not for Production releases).

This brought me to the next step: to get a Team Build in TFS working. To get the Team Build setup and working, I started with a new story and new tasks in Visual Studio. This way I could track my progress and, if I got pulled away from my work (as we all do), I could come back and know exactly where I had left off and what my next steps needed to be. The acceptance criteria for this story was to have a basic Team Build in place with an automated deployment using the VSDBCMD. The tasks that I created for the DB Team Build story were:

1. Create a basic Team Build using the default template (without any deployment).


2. Verify that the Team Build can be deployed by using the VSDBCMD.exe command.


3. Create a new custom Team Build template called VSDBCMD.


4. Test the new custom Team Build template with a Team Build that had already been completed successfully.

Team Build in itself is a large topic – I’m asked all the time: “why do we need to have Team Build?”. Well, here are my top three answers:

1. You want to have predictable results. Think of a science lab environment, where you create and test new chemicals; you would want to reduce the number of variables that might corrupt or pollute the batch, right? Software builds and deployments are no different. The software change should be the only variable and change.


2. Do you want to have a computer do the work or do you want to do it manually? Automation is an amazing thing -use it!


3. You want to have a known and reliable: build, deployment, and release process. Team Builds are critical to achieving the “Real Time” or “Continuous” deliveries we all read about and strive to accomplish!

If you are new to Team Build here are a few links that cover the subject:

· Understanding a Team Foundation Build System

· Control Where the Build System Places Your Binaries

· Guidance for Build, Deploy and Test Workflows

· Visual Studio Build Customization Guidance

When I create a basic Team Build using the default template, I always verify the code in the drop location and perform the following quick checkpoints:

1. I can make a change to a SQL schema object.


2. I check-in my code and associate it to a work item task that are in my user story.


3. Verify that I can check a change into source control and see the history and associated work item for reporting.


4. Verify that the Team Build had dropped the correct files in the Team Build drop folder.


5. Verify that I can deploy the SQL solution using the VSDBCMD command with the Team Build drop location.  

This completed two of my tasks in the Team Build DB story. The next step was to create a Team Build template that would run the VSDBCMD commands as part of the Team Build. The following links provide a good walkthrough on creating custom build template that use VSDBCMD:

· Walkthrough: Define a Custom Workflow to Deploy a Database from Team Foundation Build

· Deploy a database project with tfs build 2010

· Continuous Integration with TFS2010, MSDeploy & VSDBCMD  (I really like this template, it let’s you pass in the parameter AND has a web deployment section)

At the end of the day, I now have a working demo using ALM practices. I also now have fundamental framework that I can reference, as well as an understanding of Database development using Visual Studio and VSDBCMD. The main lesson learned, was to make sure that I made small, iterative steps and ran checkpoints to ensure that I did not have to go through a number of changes before I found any breakdowns or problems.

Give it a try, yourself – I think you will agree that it is worth the effort!




About the Author:

One Comment

  1. Pat Pattillo January 19, 2013 at 7:26 pm

    nice comprehensive guide. useful

Leave A Comment